BLOG.BRAVI.ORG

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;