我主要體驗了下幾個關於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 = 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 搭建主從的指令碼，這裡我搭建了兩臺從機。
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 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. 監視主從健康狀態。
[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. 對主從進行管理。
停止從機服務：
[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 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]#