【原創】mysql-utilities工具體驗

我主要體驗了下幾個關於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]#

本文出自 “上帝，咱們不見不散！” 部落格，請務必保留此出處http://yueliangdao0608.blog.51cto.com/397025/1199960