Reset slave 5-step

from the Artful MySQL Tips List


1 Reset master: On the master in the mysql client program execute ...


RESET MASTER;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS\G



Note the File, Position values, save the result somewhere.

2 Dump databases: If all master databases are being replicated, from the master terminal prompt execute ...


mysqldump -uUSR -pPWD --add-drop-database -K -E -R --all-databases >BACKUPPATH



... or if only some databases are being replicated then ...


mysqldump -uUSR -pPWD --add-drop-database -K -E -R --databases DBLIST >BACKUPPATH



... in each case substituting your values for USR, PWD, DBLIST, BACKUPPATH.

On the master in the mysql client execute ...


UNLOCK TABLES;



3 Stop slave, import the dump file:


STOP SLAVE;



Import the synchronising dump on the slave, eg with the source command.

4 Reset the slave: In the slave client program execute ...


RESET SLAVE;

CHANGE MASTER TO 

  MASTER_LOG_FILE='FILE', 

  MASTER_LOG_POS=POSITION;



... substituting for FILE, POSITION the values from step 1.

5 Restart the slave:


START SLAVE;

SHOW SLAVE STATUS\G




Return to the Artful MySQL Tips page