mysql-utilities工具體驗 我主要體驗了下幾個關於REPLICATION的工具。 先說下我的環境： MASTER: 192.168.1.131 SLAVE: 192.168.1.132, 192.168.1.133 三台DB都有對外的ALL權限用戶。 各個配置文件如下， [sql] [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 搭建主從的腳本，這裡我搭建了兩台從機。 [sql] mysqlreplicate --master=root:[email protected]:3306 --slave=root:[email protected]: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 檢查主從的運行情況。 [sql] [root@mysql56-master home]# mysqlrplcheck --master=root:[email protected]:3306 --slave=root:[email protected]: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. [root@mysql56-master home]# 3. mysqlrplshow. 顯示主從的架構。 [sql] [root@mysql56-master home]# mysqlrplshow --master=root:[email protected]: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. 監視主從健康狀態。 [sql] [root@mysql56-master home]# mysqlfailover --master=root:[email protected]: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. 對主從進行管理。 [sql] <span style="color:#FF0000;">停止從機服務：</span> [root@mysql56-master home]# mysqlrpladmin --master=root:[email protected]:3306 --slaves=root:[email protected]:3306,root:[email protected]: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]# <span style="color:#FF0000;"> 開啟從機服務：</span> [root@mysql56-master home]# mysqlrpladmin --master=root:[email protected]:3306 --slaves=root:[email protected]:3306,root:[email protected]: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]# <span style="color:#CC0000;">選擇最好的備機准備以後切換用.</span> [root@mysql56-master home]# mysqlrpladmin --master=root:[email protected]:3306 --slaves=root:[email protected]:3306,root:[email protected]: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]# <span style="color:#FF0000;">進行主從切換。</span> [root@mysql56-master home]# mysqlrpladmin --master=root:[email protected]:3306 --slaves=root:[email protected]:3306,root:[email protected]:3306 --new-master=root:[email protected]: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]# <span style="color:#FF0000;">顯示下新的主從架構：</span> [root@mysql56-master home]# mysqlrplshow --master=root:[email protected]: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]#
