Resync MySQL Master-Slave Replication

This process enables a MySQL Slave database to be re-syncronised with a Master database. It does not cover the steps required to initialise the Master-Slave databases and aims to resync an existing pair.

Step 1 – Master Screen 1

# mysql -uroot -ppassword

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Note the master log file and log position, do not close or exit mysql

Step 2 – Master Screen 2

# mysqldump -hlocalhost -uroot -ppassword --all-databases --lock-all-tables | gzip > slave-db.sql.gz

Step 3 – Master Screen 1

UNLOCK TABLES;

These three consecutive steps ensure a consistent copy of the database suitable for slave replication use. Copy (scp) the consisten slave-db.sql.gz backup to the slave server.

Step 4 – Slave Screen 1

# mysql -uroot -ppassword

STOP SLAVE;

Step 5 – Slave Screen 2

# gunzip slave-db.sql.gz

# mysql -uroot -ppassword < slave-db.sql

Step 6 – Slave Screen 1

CHANGE MASTER TO

MASTER_HOST='192.168.0.10',

MASTER_PORT=3306,

MASTER_USER='replication_user',

MASTER_PASSWORD='replication_password',

MASTER_LOG_FILE='master-bin.123456',

MASTER_LOG_POS=1234567;

START SLAVE;

Step 7 – Master Screen 1

SHOW MASTER STATUS \\G

Step 8 – Slave Screen 1

SHOW SLAVE STATUS \\G

On the slave, if you see:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Job Done …

Restart Replication from Slave

This is probably not good practice, but in the case of slave replication errors sometimes its necessary to force the slave to continue replication (YMMV) …

stop slave; set global sql_slave_skip_counter=1; start slave;

Run it two or three times until Slave_IO_Running and Slave_SQL_Running = yes