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