MySQL: Resync a Master-Slave replication.
Replication enables data from one MySQL database server (the Master) to be replicated to one or more MySQL database servers (the Slaves).
Replication is asynchronous: slaves need not be connected permanently to receive updates from the master.
Depending on the configuration, it is possible to replicate all databases, selected databases, or even selected tables within a database.
This is a Step-by-Step procedure to RESYNC a Master-Slave MySQL Cluster.
Phase 1, FROM MASTER DB.
Connect to MySQL DB, (for example using mysql client) and type commands:
mysql> RESET MASTER; Query OK, 0 ROWS affected (0.14 sec) mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 ROWS affected (0.03 sec) mysql> SHOW MASTER STATUS\G *************************** 1. ROW *************************** File: mysql-bin.000001 POSITION: 106 Binlog_Do_DB: Binlog_Ignore_DB: 1 ROW IN SET (0.00 sec) |
Wihtout closing the connection to MySQL client (this would release the READ LOCK) get a complete dump of the Master DB (for example from a different SSH Shell):
root@master_db:~# mysqldump -u root -p --all-database > /DUMP.sql |
Release the lock, even if the dump hasn’t end:
mysql> UNLOCK TABLES; Query OK, 0 ROWS affected (0.16 sec) |
Copy the dump file to the slave (for example using scp):
root@master_db:~# scp /root/DUMP.sql root@slave_db:/root/ |
Phase 2, FROM SLAVE DB.
Load Master data dump:
root@slave_db:~# mysql -u root -p < DUMP.sql |
Connect to MySQL DB, (for example using mysql client) and type commands:
mysql> STOP SLAVE; Query OK, 0 ROWS affected (0.14 sec) mysql> RESET SLAVE; Query OK, 0 ROWS affected (0.18 sec) mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; Query OK, 0 ROWS affected (1.08 sec) |
IMPORTANT NOTE: Values of the above fields are the ones obtained from “SHOW MASTER STATUS” on Master DB.
mysql> START SLAVE; Query OK, 0 ROWS affected (0.21 sec) |
Check that the replication is working:
mysql> SHOW SLAVE STATUS; |