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