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