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