在上一篇中已经实现了MySQL服务的高可用，MySQL的数据目录放在drbd的共享目录中，并且只有获取到heartbeat资源的VIP才能挂载共享目录，从而启动MySQL服务，但是两端的数据使用drbd同步，保证发生故障时，服务和资源能够从一个节点切换到另外一个节点，下面是一个简略的架构图：
对于MySQL服务，一般在生产环境中都要做主从结构，从而保证数据的完整性，所以这次要在这个架构的前提下，在两个heartbeat节点下再部署一台MySQL从库，而主库是heartbeat集群中的一台（主库的IP设置为VIP地址），从而实现发生故障时，从库可以自动切换到另一台主库下面，主从同步的状态保持稳定。
当heartbeat01获取VIP时，MySQL主库跑在heartbeat01上，MySQL从库从heartbeat01同步数据，heartbeat02相当于备份服务器，通过drbd的块设备同步heartbeat01上drbd目录的内容。
当heartbeat02获取VIP时，MySQL主库跑在heartbeat02上，MySQL从库从heartbeat02同步数据，heartbeat01相当于备份服务器，通过drbd的块设备同步heartbeat02上drbd目录的内容。
注意：本文中出现的节点1/节点2都是指heartbeat的节点，而Master节点/Slave节点都是指MySQL节点。
一、环境准备
这里需要添加一台主机，主要信息如下：
| 主机名 | 角色 | IP地址 |
| server136.contoso.com | MySQL从库（Slave） | 192.168.49.136 |
以下都在上一篇的基础上完成《Heartbeat+Drbd+MySQL高可用》
1）两个节点上依次启动heartbeat服务：
[root@heartbeat01 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO: Resource is stopped
Done.
[root@heartbeat02 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO: Resource is stopped
Done.
2）检查两个节点的状态
节点1heartbeat01：
[root@heartbeat01 ~]# ip a |grep 49.100
inet 192.168.49.100/24 scope global eth1
[root@heartbeat01 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
ns:216 nr:0 dw:216 dr:3929 al:4 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat01 ~]# ll /data
total 16
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 lib
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:21 lock
drwxr-xr-x 2 mysql mysql 4096 Sep 27 01:18 log
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 run
[root@heartbeat01 ~]# /etc/init.d/mysqld status
mysqld (pid 2414) is running...
[root@heartbeat01 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2414 mysql 18u IPv4 12859 0t0 TCP *:mysql (LISTEN)
节点2 heartbeat02：
[root@heartbeat02 ~]# ip a |grep 49.100
[root@heartbeat02 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----
ns:0 nr:784 dw:784 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat02 ~]# ll /data
total 0
[root@heartbeat02 ~]# /etc/init.d/mysqld status
mysqld is stopped
二、MySQL主从复制
1、配置heartbeat01
因为节点1（heartbeat01）目前正在运行数据库，所以需要先在heartbeat01上进行配置，当然如果MySQL和heartbeat的资源正在运行在heartbeat02上，也可以先对heartbeat02进行配置，配置的目的是保证两个节点上MySQL Master节点的配置完成，且能正常运行。
1）配置/etc/my.cnf，添加Master配置
[root@heartbeat01 ~]# cp /etc/my.cnf /etc/my.cnf.bak$(date +%F)
[root@heartbeat01 ~]# vi /etc/my.cnf
[root@heartbeat01 ~]# diff /etc/my.cnf.bak2016-09-27 /etc/my.cnf
6a7,10
> ######################################
> #add master settings
> log-bin=mysql-bin
> server-id=1
# 主从配置就不多讲了，网上的教程有很多，这里也只是开启了binlog并添加serverID，没有做详细的设置。
2）导出Master节点的数据
[root@heartbeat01 ~]# mysqldump -uroot -p123456 --database tb01 > /root/tb01.sql
# 因为之前我创建了一个tb01的数据库，所以这里先将该数据库备份
[root@heartbeat01 ~]# ll tb01.sql
-rw-r--r-- 1 root root 2008 Sep 27 22:14 tb01.sql
[root@heartbeat01 ~]# scp tb01.sql 192.168.49.136:/tmp/
root@192.168.49.136's password:
tb01.sql 100% 2008 2.0KB/s 00:00
# 然后通过scp拷贝到MySQL从库（server136）上
3）创建备份账号
[root@heartbeat01 ~]# mysql -uroot -p123456
# 登录MySQL
|
1
2
|
mysql>
grant
replication slave
on
*.*
to
'replication'
@
'192.168.49.136'
identified
by
'rep@123'
;
Query OK, 0
rows
affected (0.00 sec)
|
# 创建主从同步账号并授权
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql>
select
Host,
User
,
Password
from
mysql.
user
;
+
-------------------------+-------------+-------------------------------------------+
| Host |
User
|
Password
|
+
-------------------------+-------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| heartbeat01.contoso.com | root | |
| 127.0.0.1 | root | |
| localhost | | |
| heartbeat01.contoso.com | | |
| 192.168.49.136 | replication | *6E0E8E398CC10050A0AF2E9B4B27FBF3181D220A |
+
-------------------------+-------------+-------------------------------------------+
6
rows
in
set
(0.00 sec)
|
# 检查一下创建的结果
|
1
2
3
4
|
mysql> flush
privileges
;
Query OK, 0
rows
affected (0.00 sec)
mysql> quit
Bye
|
4）重启MySQL，查询Master状态
|
1
2
3
4
5
6
7
8
9
10
|
[root@heartbeat01 ~]# /etc/init.d/mysqld restart
mysql> show master status;
+
------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
------------------+----------+--------------+------------------+
| mysql-bin.000001 | 344 | | |
+
------------------+----------+--------------+------------------+
1 row
in
set
(0.00 sec)
mysql> quit
Bye
|
2、配置heartbeat02
因为heartbeat02暂时没有获取VIP，也未运行MySQL服务，所以只需要修改/etc/my.cnf文件，将Master的配置加入进去即可，这里采用scp的方式从节点1上复制过来。
[root@heartbeat02 ~]# mv /etc/my.cnf /etc/my.cnf.bak$(date +%F)
[root@heartbeat02 ~]# scp heartbeat01:/etc/my.cnf /etc/
Warning: Permanently added the RSA host key for IP address '172.16.49.133' to the list of known hosts.
root@heartbeat01's password:
my.cnf 100% 345 0.3KB/s 00:00
[root@heartbeat02 ~]# diff /etc/my.cnf.bak2016-09-27 /etc/my.cnf
6a7,10
> ######################################
> #add master settings
> log-bin=mysql-bin
> server-id=1
3、配置Slave节点（server136）
1）安装MySQL
[root@server136 ~]# yum -y install mysql-devel mysql-server
[root@server136 ~]# /etc/init.d/mysqld start
[root@server136 ~]# mysqladmin -uroot password '123456'
2）修改配置文件
[root@server136 ~]# cp /etc/my.cnf /etc/my.cnf.bak$(date +%F)
[root@server136 ~]# vi /etc/my.cnf
[root@server136 ~]# diff /etc/my.cnf.bak2016-09-28 /etc/my.cnf
6a7,9
> ####################################
> #add slave settings
> server-id=2
# 只需要在/etc/my.cnf中添加一行server-id=值
3）导入Master上导出的数据
[root@server136 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@server136 ~]# mysql -uroot -p123456 < /tmp/tb01.sql
下面进入MySQL数据库查看一下导入的数据：
[root@server136 ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from tb01.staff;
+----+-------+
| id | name |
+----+-------+
| 1 | Tom |
| 2 | Jerry |
+----+-------+
2 rows in set (0.01 sec)
mysql> quit
Bye
4）启动slave节点
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
mysql> change master
to
-> master_host =
'192.168.49.100'
,
-> master_user =
'replication'
,
-> master_password =
'rep@123'
,
-> master_log_file =
'mysql-bin.000001'
,
-> master_log_pos = 344;
Query OK, 0
rows
affected (0.10 sec)
mysql> start slave;
Query OK, 0
rows
affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.49.100
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 344
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #这两项为Yes说明主从复制成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 344
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row
in
set
(0.00 sec)
|
5）主从复制测试
现在在Master上创建一张表，并插入数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[root@heartbeat01 ~]# mysql -uroot -p123456
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
11
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracle
is
a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates. Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql> use tb01;
Reading
table
information
for
completion
of
table
and
column
names
You can turn
off
this feature
to
get a quicker startup
with
-A
Database
changed
mysql>
create
table
test01( id
int
not
null
,
name
varchar
(100), age
int
, sex
varchar
(20), city
varchar
(40) );
Query OK, 0
rows
affected (0.05 sec)
mysql>
insert
into
test01
values
(001,
'zhangsan'
,20,
'Male'
,
'Beijing'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
test01
values
(002,
'lisi'
,25,
'Male'
,
'Shanghai'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
test01
values
(003,
'wangwu'
,28,
'Female'
,
'Shenzhen'
);
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
test01;
+
----+----------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+----------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
+
----+----------+------+--------+----------+
3
rows
in
set
(0.00 sec)
|
然后到Slave上去查看，是否存在对应的表和数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
[root@server136 ~]# mysql -uroot -p123456
mysql> use tb01;
Reading
table
information
for
completion
of
table
and
column
names
You can turn
off
this feature
to
get a quicker startup
with
-A
Database
changed
mysql> show tables;
+
----------------+
| Tables_in_tb01 |
+
----------------+
| staff |
+
----------------+
1 row
in
set
(0.00 sec)
mysql> show tables;
+
----------------+
| Tables_in_tb01 |
+
----------------+
| staff |
| test01 |
+
----------------+
2
rows
in
set
(0.00 sec)
mysql>
select
*
from
test01;
+
----+----------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+----------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
+
----+----------+------+--------+----------+
3
rows
in
set
(0.00 sec)
|
在Slave上可以看到Master上创建的表和插入的数据，说明主从复制正常。
三、MySQL的Master节点故障切换测试
1、首先停止heartbeat01上的heartbeat服务
[root@heartbeat01 ~]# /etc/init.d/heartbeat stop
Stopping High-Availability services: Done.
[root@heartbeat01 ~]#
此时，heartbeat01上的状态为：
[root@heartbeat01 ~]# ip a |grep 49.100
[root@heartbeat01 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----
ns:1004 nr:416 dw:1420 dr:3981 al:7 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat01 ~]# ll /data/
total 0
[root@heartbeat01 ~]# /etc/init.d/mysqld status
mysqld is stopped
heartbeat02上的状态为：
[root@heartbeat02 ~]# ip a |grep 49.100
inet 192.168.49.100/24 scope global eth1
[root@heartbeat02 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
ns:340 nr:1004 dw:1344 dr:3937 al:6 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat02 ~]# ll /data
total 16
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 lib
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:21 lock
drwxr-xr-x 2 mysql mysql 4096 Sep 27 01:18 log
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 run
[root@heartbeat02 ~]# /etc/init.d/mysqld status
mysqld (pid 2629) is running...
[root@heartbeat02 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2629 mysql 18u IPv4 14300 0t0 TCP *:mysql (LISTEN)
2、检查主从同步状态
1）在Slave节点上查看Slave状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.49.100
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row
in
set
(0.00 sec)
|
2）在heartbeat02上进入MySQL新增一条数据，查看从库能否同步
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
[root@heartbeat02 ~]# mysql -uroot -p123456
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
3
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracle
is
a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates. Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>
select
*
from
tb01.test01;
+
----+----------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+----------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
+
----+----------+------+--------+----------+
3
rows
in
set
(0.04 sec)
mysql> use tb01;
Reading
table
information
for
completion
of
table
and
column
names
You can turn
off
this feature
to
get a quicker startup
with
-A
Database
changed
mysql>
insert
into
test01
values
(4,
'zhaosi'
,40,
'Male'
,
'Tieling'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
test01
values
(5,
'xiaoshenyang'
,34,
'Male'
,
'Shenyang'
);
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
tb01.test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
5
rows
in
set
(0.01 sec)
|
然后到Slave上进行查看同步状况：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> show tables;
+
----------------+
| Tables_in_tb01 |
+
----------------+
| staff |
| test01 |
+
----------------+
2
rows
in
set
(0.00 sec)
mysql>
select
*
from
test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
5
rows
in
set
(0.00 sec)
|
在Slave上能看到Master上新插入的两条数据，说明主从同步没有问题，主节点的切换不影响Slave的同步。
3、再次开启heartbeat01上的heartbeat服务，查看heartbeat01上是否有heartbeat02上插入的数据
[root@heartbeat01 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO: Resource is stopped
Done.
1）heartbeat01的状态
[root@heartbeat01 ~]# ip a |grep 49.100
inet 192.168.49.100/24 scope global eth1
[root@heartbeat01 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
ns:1256 nr:740 dw:1996 dr:7918 al:8 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat01 ~]# ll /data/
total 16
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 lib
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:21 lock
drwxr-xr-x 2 mysql mysql 4096 Sep 27 01:18 log
drwxr-xr-x 3 mysql mysql 4096 Sep 27 01:17 run
[root@heartbeat01 ~]# /etc/init.d/mysqld status
mysqld (pid 4211) is running...
[root@heartbeat01 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4211 mysql 19u IPv4 16116 0t0 TCP *:mysql (LISTEN)
mysqld 4211 mysql 39u IPv4 16163 0t0 TCP 192.168.49.100:mysql->192.168.49.136:36693 (ESTABLISHED)
2）heartbeat02的状态：
[root@heartbeat02 ~]# ip a|grep 49.100
[root@heartbeat02 ~]# cat /proc/drbd
version: 8.3.16 (api:88/proto:86-97)
GIT-hash: a798fa7e274428a357657fb52f0ecf40192c1985 build by phil@Build64R6, 2014-11-24 14:51:37
0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----
ns:740 nr:1360 dw:2100 dr:4005 al:8 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@heartbeat02 ~]# ll /data
total 0
[root@heartbeat02 ~]# /etc/init.d/mysqld status
mysqld is stopped
3）在heartbeat01上登录MySQL查看是否有heartbeat02上插入的数据
[root@heartbeat01 ~]# mysql -uroot -p123456
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql>
select
*
from
tb01.test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
5
rows
in
set
(0.03 sec)
|
可以看到，heartbeat02上插入的数据也已经通过drbd同步成功了。
4）再次检查Slave节点上的主从同步状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 192.168.49.100
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row
in
set
(0.00 sec)
|
在server136上可以看到，主从状态依然正常。
5）在heartbeat01上删除一条记录
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql>
select
*
from
tb01.test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 2 | lisi | 25 | Male | Shanghai |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
5
rows
in
set
(0.03 sec)
mysql>
delete
from
tb01.test01
where
name
=
'lisi'
;
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
tb01.test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
4
rows
in
set
(0.00 sec)
|
6）在Slave节点（server136）上进行查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@server136 ~]# mysql -uroot -p123456
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
6
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracle
is
a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates. Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>
select
*
from
tb01.test01;
+
----+--------------+------+--------+----------+
| id |
name
| age | sex | city |
+
----+--------------+------+--------+----------+
| 1 | zhangsan | 20 | Male | Beijing |
| 3 | wangwu | 28 | Female | Shenzhen |
| 4 | zhaosi | 40 | Male | Tieling |
| 5 | xiaoshenyang | 34 | Male | Shenyang |
+
----+--------------+------+--------+----------+
4
rows
in
set
(0.00 sec)
mysql> quit
Bye
[root@server136 ~]#
|
可以看到，数据已经同步，说明主从同步没有问题。至此，heartbeat+drbd+MySQL主从高可用的部署完成。
本文转自 jerry1111111 51CTO博客，原文链接：http://blog.51cto.com/jerry12356/1857391，如需转载请自行联系原作者
