我主要体验了下几个关于REPLICATION的工具。 其他的可以参见手册。
先说下我的环境：
MASTER: 192.168.1.131
SLAVE: 192.168.1.132, 192.168.1.133
三台DB都有对外的ALL权限用户。
各个配置文件如下，
[root@mysql56-master home]# cat /etc/my.cnf[mysqld]user = yttskip-name-resolveinnodb_buffer_pool_size = 128Mbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306server_id = 131socket = /tmp/mysql.sockexplicit_defaults_for_timestamplog-bin=mysql56-master-binbinlog-ignore-db=mysqlgtid-mode=onenforce-gtid-consistencylog-slave-updatesbinlog-format=ROWsync-master-info=1report-host=192.168.1.131report-port=3306master_info_repository=tablerelay_log_info_repository=table
其他两台SERVER，除了SERVER-ID，都基本相同，我就不贴了。
1. MYSQLREPLICATE 搭建主从的脚本，这里我搭建了两台从机。
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 检查主从的运行情况。
[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. 显示主从的架构。
[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 Graph192.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. 监视主从健康状态。
[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 UtilityFailover Mode = auto Next Interval = Tue May 14 12:27:56 2013Master Information------------------Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB mysql56-master-bin.0 151 mysql GTID Executed SetNoneReplication 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. 对主从进行管理。
停止从机服务：
[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]#
开启从机服务：
[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]#
选择最好的备机准备以后切换用.
[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]#
进行主从切换。
[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]#
显示下新的主从架构：
[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 Graph192.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]#