Use the mysql-utilities tool to experience bitsCN.com
Mysql-utilities tool experience
I mainly tried several REPLICATION tools.
Let's talk about my environment:
MASTER: 192.168.1.131
SLAVE: 192.168.1.132, 192.168.1.133
ALL three databases have ALL external users.
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 = table
The other two servers except SERVER-ID are basically the same, so I will not post them.
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.ted:... connected.
# Checking 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 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. displays 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.20.: 3306 [IO running: Yes]-(SLAVE)
[Root @ mysql56-master home] #
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.20.: 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.20.| 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. Manage the master and slave nodes.
[SQL]
Stop the slave service:
[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] #
Enable the slave service:
[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] #
Select the best backup machine to be used after preparation.
[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] #
Perform master-slave switchover.
[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:
+ ---------------- + ------- + --------- + -------- + ------------ + --------------------------- +
| 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] #
The following figure shows the new master-slave architecture:
[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] #
BitsCN.com