mysql-utilities工具体验

页面导航:首页 > 数据库 > Mysql > mysql-utilities工具体验

mysql-utilities工具体验

来源: 作者: 时间:2016-02-21 09:46 【

mysql-utilities工具体验我主要体验了下几个关于REPLICATION的工具。先说下我的环境:MASTER: 192.168.1.131SLAVE: 192.168.1.132, 192.168.1.133三台DB都有对外的ALL权限用户。各个配置文件如下,[sql][root
-utilities工具体验
 
我主要体验了下几个关于REPLICATION的工具。 
先说下我的环境:
MASTER: 192.168.1.131
SLAVE: 192.168.1.132, 192.168.1.133
三台DB都有对外的ALL权限用户。
各个配置文件如下,
 
[sql] 
[[email protected] 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::3306 --slave=root::3306;...  
[[email protected] 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] 
[[email protected] home]# mysqlrplcheck --master=root::3306 --slave=root::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.  
[[email protected] home]#  
 
3. mysqlrplshow. 显示主从的架构。
 
[sql] 
[[email protected] home]# mysqlrplshow --master=root::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)  
  
[[email protected] home]#  
 
4. mysqlfailover. 监视主从健康状态。
 
[sql] 
[[email protected] home]# mysqlfailover --master=root::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  
[[email protected] home]#  
 
5. mysqlrpladmin. 对主从进行管理。
 
[sql] 
<span style="color:#FF0000;">停止从机服务:</span>  
 [[email protected] home]# mysqlrpladmin  --master=root::3306 --slaves=root::3306,root::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.  
[[email protected] home]#  
<span style="color:#FF0000;"> 开启从机服务:</span>  
[[email protected] home]# mysqlrpladmin  --master=root::3306 --slaves=root::3306,root::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.  
[[email protected] home]#  
  
<span style="color:#CC0000;">选择最好的备机准备以后切换用.</span>  
[[email protected] home]# mysqlrpladmin --master=root::3306 --slaves=root::3306,root::3306 elect  
# Checking privileges.  
# Electing candidate slave from known slaves.  
# Best slave found is located on 192.168.1.132:3306.  
# ...done.  
[[email protected] home]#  
<span style="color:#FF0000;">进行主从切换。</span>  
  
[[email protected] home]#  mysqlrpladmin --master=root::3306 --slaves=root::3306,root::3306  --new-master=root::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.  
[[email protected] home]#  
<span style="color:#FF0000;">显示下新的主从架构:</span>  
[[email protected] home]# mysqlrplshow --master=root::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)  
  
[[email protected] home]#  
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<