mysql-utilities工具体验_MySQL

bitsCN.com

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: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  检查主从的运行情况。

 

[sql] 

[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.  

[root@mysql56-master home]#  

 

3. mysqlrplshow. 显示主从的架构。

 

[sql] 

[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. 监视主从健康状态。

 

[sql] 

[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. 对主从进行管理。

 

[sql] 

停止从机服务：  

 [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]#  

 

bitsCN.com