I mainly tried several REPLICATION tools. For other information, see the manual. Let's talk about my environment: MASTER: 192.168.1.20.slave: 192.168.1.132, 192.168.1.20.all three databases have external
I mainly tried several REPLICATION tools. For other information, see the manual. Let's talk about my environment: MASTER: 192.168.1.20.slave: 192.168.1.132, 192.168.1.20.all three databases have external
I mainly tried several REPLICATION tools. For other information, see the manual.
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,
[Root @ mysql56-master home] # cat/etc/my. cnf [mysqld] user = yttskip-name-resolveinnodb_buffer_pool_size = 128 Mbasedir =/usr/local/mysqldatadir =/usr/local/mysql/dataport = 3306server_id = 131 socket =/tmp/mysql. sockexplicit_defaults_for_timestamplog-bin = mysql56-master-binbinlog-ignore-db = mysqlgtid-mode = onenforce-gtid-consistencylog-slave-updatesbinlog-format = ROWsync-master-info = 1report-host = 192.168.1.20.report-port = 3306master_info_repository = External table
The other two servers except SERVER-ID and Hong Kong SERVER lease 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.
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. # Checking for binary logging on master... # Setting up replication... #... done.
2. mysqlrplcheck checks the running status of the master and slave nodes.
[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 DescriptionStatus --------------------------------------------------------------------------- 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 eventMaster_Host: 192.168.1.20.master _ User: rplMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql56-master-bin.000002Read_Master_Log_Pos: Connector: mysql56-slave-relay-bin.000003Relay_Log_Pos: Connector: mysql56-master-bin.000002Slave_IO_Running: failed: Last_Errno: 0Last_Error: Skip_Counter: Failed: Until_Log_Pos: Failed: Master_SSL_Cert: Master_SSL_Key: seconds_Behind_Master: 0 rows: NoLast_IO_Errno: 0Last_IO_Error: Last_ SQL _Errno: 0Last_ SQL _Error: Role: Master_Server_Id: Role master_uuid: Role: mysql. failed: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: Failed: Master_SSL_Crl: Master_SSL_Crlpath: Failed: Executed_Gtid_Set: auto_Position: 1 #... done.
3. mysqlrplshow. displays the master-slave architecture.
[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 Graph192.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. Monitor the Master/Slave health status.
[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 Mode = autoNext Interval = Tue May 14 12:27:56 2013 Master Information about Binary Log FilePosition statistics mysql56-master-bin.0 151 mysqlGTID Executed SetNoneReplication Health Status + ---------------- + ------- + ------------ + role + | host | port | role | state | gtid_mode | health | + ------------------ + ------- + --------- + -------- + ------------ + role + | 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. | + ---------------- + ------- + --------- + -------- + ------------ + response + Q-quit R-refresh H-health G-GTID Lists U-UUIDs [root @ mysql56-master home] #
5. mysqlrpladmin. Manage the master and slave nodes.
