Mysql-utilities tool experience

Mysql-utilities tool experience I mainly experienced several REPLICATION tools. Let's talk about my environment: MASTER: 192.168.1.20.slave: 192.168.1.132, and 192.168.1.20.all three databases have external users with ALL permissions. The configuration files are as follows: [SQL] [root @ mysql56-master home] # cat/etc/my. cnf [mysqld] user = ytt skip-name-resolve innodb_buffer_pool_size = 128 M 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 = the other two servers except the SERVER-ID are basically the same, I will not post it. 1. Create a master-slave script using MYSQLREPLICATE. Here I have set up two slave servers. [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.6.2 :... connected. # Che Cking for binary logging on master... # Setting up replication... #... done. 2. mysqlrplcheck checks the running status of the master and slave nodes. [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 the event Master_Host: 192.168.1.131 Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: m Ysql56-master-bin.000002 Read_Master_Log_Pos: 151 Relay_Log_File: mysql56-slave-relay-bin.000003 Relay_Log_Pos: 379 runtime: mysql56-master-bin.000002 runtime: Yes runtime: mysql runtime: Last_Errno: 0 Last_Error: Skip_Cou Nter: 0 character: 151 Relay_Log_Space: 819 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 character: No character: Master_SSL_Cert: character: Master_SSL_Key: character: 0 character: No character: 0 Last_IO_Error: Last_ SQL _Errno: 0 Last_ SQL _Error: Replicate_Ignore_Server_Ids: Mas Ter_Server_Id: 131 MAID: 4d89ad1d-bc12-11e2-87e9-080027338857 Master_Info_File: mysql. slave_master_info SQL _Delay: 0 keys: None keys: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Keys: Master_SSL_Crl: keys: retrieved_G Tid_Set: Executed_Gtid_Set: Auto_Position: 1 #... done. [root @ mysql56-master home] #3. mysqlrplshow. Show the master-slave architecture. [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 ho Me] #4. mysqlfailover. Monitor the Master/Slave health status. [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.ave: 3306 # Found slave: 192.168.1.ave: 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 runtime mysql56-master-bin.0 151 mysql GTID Executed Set None Replication Health Status + ---------------- + ------- + --------- + -------- + others ------------------------------------------- + | host | port | role | state | gtid_mode | health | + ---------------- + ------- + --------- + -------- + ------------ + Hosts + | 192.168.1.131 | 3306 | MASTER | UP | ON | OK | 192.168.1.132 | 3306 | SLAVE | UP | ON | OK | 192.168.1.20.| 3306 | SLAVE | UP | ON | Binary log and Relay log filters differ. | + ---------------- + ------- + --------- + -------- + ------------ + --------------------------------------------- + Q-quit R-refresh H-health G-GTID List S U-UUIDs [root @ mysql56-master home] #5. mysqlrpladmin. Manage the master and slave. [SQL] <span style = "color: # FF0000;"> stop slave service: </span> [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. # specify Ming 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 s Tyle = "color: # FF0000;"> enable the slave service: </span> [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. # specify Ming 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; "> select the best backup machine for failover. </span> [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. # The Best slave found is located on 192.168.1.132: 3306. #... done. [root @ mysql56-master home] # <span style = "color: # FF0000;"> Master/Slave switchover. </Span> [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. # login Ming 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. # specify Ming STOP on all slaves. # Demoting old master to be a slave to the new master. # Switching slaves to new master. # Starting all slaves. # specify Ming START on all slaves. # Checking slaves for errors. # Switchover complete. # Replication Topology Health: + ---------------- + ------- + --------- + -------- + ------------ + response + | host | port | role | state | gtid_mode | health | + ---------------- + ------- + --------- + -------- + response + | 192.168.1.132 | 3306 | MASTER | UP | ON | OK | 192.168.1.131 | 3306 | SLAVE | UP | ON | OK | 192.168.1.ave | 3306 | SLAVE | UP | ON | OK | + ---------------- + ------- + --------- + -------- + ------------ + ----------------------------- + #... done. [root @ mysql56-master home] # <span style = "color: # FF0000;"> display the new Master/Slave architecture: </span> [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.20: 3306 [IO running: Yes]-(SLAVE) [root @ mysql56-master home] #