转载地址：http://yueliangdao0608.blog.51cto.com/397025/1199960
我主要体验了下几个关于REPLICATION的工具。 其他的可以参见手册。
先说下我的环境：
MASTER: 192.168.1.131
SLAVE: 192.168.1.132, 192.168.1.133
三台DB都有对外的ALL权限用户。
各个配置文件如下，
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@mysql56-master home]# cat /etc/my.cnf
[mysqld]
user
= ytt
skip-
name
-resolve
innodb_buffer_pool_size = 128M
basedir = /usr/
local
/mysql
datadir = /usr/
local
/mysql/data
port = 3306
server_id = 131
socket = /tmp/mysql.sock
explicit_defaults_for_timestamp
log-bin=mysql56-master-bin
binlog-
ignore
-db=mysql
gtid-mode=
on
enforce-gtid-consistency
log-slave-updates
binlog-format=ROW
sync-master-info=1
report-host=192.168.1.131
report-port=3306
master_info_repository=
table
relay_log_info_repository=
table
|
其他两台SERVER，除了SERVER-ID，都基本相同，我就不贴了。
1. MYSQLREPLICATE 搭建主从的脚本，这里我搭建了两台从机。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysqlreplicate
--master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306;...
[root@mysql56-master home]# ./replicate_create
# master
on
192.168.1.131: ... connected.
# slave
on
192.168.1.132: ... connected.
# Checking
for
binary
logging
on
master...
# Setting up replication...
# ...done.
# master
on
192.168.1.131: ... connected.
# slave
on
192.168.1.133: ... connected.
# Checking
for
binary
logging
on
master...
# Setting up replication...
# ...done.
|
2. mysqlrplcheck 检查主从的运行情况。
|
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
[root@mysql56-master home]# mysqlrplcheck
--master=root:root@192.168.1.131:3306 --slave=root:root@192.168.1.132:3306 -s
# master
on
192.168.1.131: ... connected.
# slave
on
192.168.1.132: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking
for
binary
logging
on
master [pass]
Are there binlog exceptions? [WARN]
+
---------+--------+------------+
| server | do_db | ignore_db |
+
---------+--------+------------+
| master | | mysql |
| slave | | mysql |
+
---------+--------+------------+
Replication
user
exists? [pass]
Checking server_id
values
[pass]
Checking server_uuid
values
[pass]
Is
slave connected
to
master? [pass]
Check
master information file [pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
#
# Slave status:
#
Slave_IO_State : Waiting
for
master
to
send event
Master_Host : 192.168.1.131
Master_User : rpl
Master_Port : 3306
Connect_Retry : 60
Master_Log_File : mysql56-master-bin.000002
Read_Master_Log_Pos : 151
Relay_Log_File : mysql56-slave-relay-bin.000003
Relay_Log_Pos : 379
Relay_Master_Log_File : mysql56-master-bin.000002
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
Replicate_Do_DB :
Replicate_Ignore_DB : mysql
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 : 151
Relay_Log_Space : 819
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 :
Replicate_Ignore_Server_Ids :
Master_Server_Id : 131
Master_UUID : 4d89ad1d-bc12-11e2-87e9-080027338857
Master_Info_File : mysql.slave_master_info
SQL_Delay : 0
SQL_Remaining_Delay : None
Slave_SQL_Running_State : Slave has
read
all
relay log; waiting
for
the slave I/O thread
to
update
it
Master_Retry_Count : 86400
Master_Bind :
Last_IO_Error_Timestamp :
Last_SQL_Error_Timestamp :
Master_SSL_Crl :
Master_SSL_Crlpath :
Retrieved_Gtid_Set :
Executed_Gtid_Set :
Auto_Position : 1
# ...done.
|
3. mysqlrplshow. 显示主从的架构。
|
1
2
3
4
5
6
7
8
9
10
|
[root@mysql56-master home]# mysqlrplshow
--master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root -v
# master
on
192.168.1.131: ... connected.
# Finding slaves
for
master: 192.168.1.131:3306
# Replication Topology Graph
192.168.1.131:3306 (MASTER)
|
+
--- 192.168.1.132:3306 [IO running: Yes] - (SLAVE)
|
+
--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)
[root@mysql56-master home]#
|
4. mysqlfailover. 监视主从健康状态。
|
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
|
[root@mysql56-master home]# mysqlfailover
--master=root:root@192.168.1.131:3306 --discover-slaves-login=root:root
# Discovering slaves
for
master
at
192.168.1.131:3306
# Discovering slave
at
192.168.1.132:3306
# Found slave: 192.168.1.132:3306
# Discovering slave
at
192.168.1.133:3306
# Found slave: 192.168.1.133:3306
# Checking
privileges
.
MySQL Replication Failover Utility
Failover Mode = auto
Next
Interval = Tue May 14 12:27:56 2013
Master Information
------------------
Binary
Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql56-master-bin.0 151 mysql
GTID Executed
Set
None
Replication Health Status
+
----------------+-------+---------+--------+------------+-------------------------------------------+
| host | port | role | state | gtid_mode | health |
+
----------------+-------+---------+--------+------------+-------------------------------------------+
| 192.168.1.131 | 3306 | MASTER | UP |
ON
| OK |
| 192.168.1.132 | 3306 | SLAVE | UP |
ON
| OK |
| 192.168.1.133 | 3306 | SLAVE | UP |
ON
|
Binary
log
and
Relay log filters differ. |
+
----------------+-------+---------+--------+------------+-------------------------------------------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs
[root@mysql56-master home]#
|
5. mysqlrpladmin. 对主从进行管理。
停止从机服务：
|
1
2
3
4
5
6
7
|
[root@mysql56-master home]# mysqlrpladmin
--master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
# Checking
privileges
.
# Performing STOP
on
all
slaves.
# Executing stop
on
slave 192.168.1.132:3306 Ok
# Executing stop
on
slave 192.168.1.133:3306 Ok
# ...done.
[root@mysql56-master home]#
|
开启从机服务：
|
1
2
3
4
5
6
7
|
[root@mysql56-master home]# mysqlrpladmin
--master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 stop
# Checking
privileges
.
# Performing STOP
on
all
slaves.
# Executing stop
on
slave 192.168.1.132:3306 Ok
# Executing stop
on
slave 192.168.1.133:3306 Ok
# ...done.
[root@mysql56-master home]#
|
选择最好的备机准备以后切换用.
|
1
2
3
4
5
6
|
[root@mysql56-master home]# mysqlrpladmin
--master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 elect
# Checking
privileges
.
# Electing candidate slave
from
known slaves.
# Best slave found
is
located
on
192.168.1.132:3306.
# ...done.
[root@mysql56-master home]#
|
进行主从切换。
|
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
|
[root@mysql56-master home]# mysqlrpladmin
--master=root:root@192.168.1.131:3306 --slaves=root:root@192.168.1.132:3306,root:root@192.168.1.133:3306 --new-master=root:root@192.168.1.132:3306 --demote-master switchover
# Checking
privileges
.
# Performing switchover
from
master
at
192.168.1.131:3306
to
slave
at
192.168.1.132:3306.
# Checking candidate slave prerequisites.
# Checking slaves configuration
to
master.
# Waiting
for
slaves
to
catch up
to
old master.
# Stopping slaves.
# Performing STOP
on
all
slaves.
# Demoting old master
to
be a slave
to
the new master.
# Switching slaves
to
new master.
# Starting
all
slaves.
# Performing START
on
all
slaves.
# Checking slaves
for
errors.
# Switchover complete.
#
# Replication Topology Health:
+
----------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+
----------------+-------+---------+--------+------------+-----------------------------+
| 192.168.1.132 | 3306 | MASTER | UP |
ON
| OK |
| 192.168.1.131 | 3306 | SLAVE | UP |
ON
| OK |
| 192.168.1.133 | 3306 | SLAVE | UP |
ON
| OK |
+
----------------+-------+---------+--------+------------+-----------------------------+
# ...done.
[root@mysql56-master home]#
|
显示下新的主从架构：
|
1
2
3
4
5
6
7
8
9
10
|
[root@mysql56-master home]# mysqlrplshow
--master=root:root@192.168.1.132:3306 --discover-slaves-login=root:root -v
# master
on
192.168.1.132: ... connected.
# Finding slaves
for
master: 192.168.1.132:3306
# Replication Topology Graph
192.168.1.132:3306 (MASTER)
|
+
--- 192.168.1.131:3306 [IO running: Yes] - (SLAVE)
|
+
--- 192.168.1.133:3306 [IO running: Yes] - (SLAVE)
[root@mysql56-master home]#
|
本文出自 “上帝，咱们不见不散！” 博客，请务必保留此出处http://yueliangdao0608.blog.51cto.com/397025/1199960
499
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
