Reset database slave

reset-database-slave.md

Based on https://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

Reset database slave

Master

Step 1

RESET MASTER;
FLUSH TABLES WITH READ LOCK;

Do not close this connection yet

Step 2

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master

--master-data => the CHANGE MASTER TO command is written into the dump file and thus saves the step of executing it after importing the dump file into the slave

mysqldump -u root -p --master-data --all-databases | gzip > dump.sql.gz

Step 3

UNLOCK TABLES;

Slave

Step 1

STOP SLAVE;
RESET SLAVE;

Step 2

zcat dump.sql.gz | mysql -uroot -p

Step 3

START SLAVE;

Step 4

Check status

SHOW SLAVE STATUS;

You should see

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

and *_Error columns should be empty