Reset MySQL Replication Slave – Replica_SQL_Running No

MySQL Replication is a great tool and also a needed tool for high availability. Unfortunately, it’s temperamental, to say the least. Often if an error occurs, you will notice that the status of the slave’s Replica_SQL_Running changes from Yes to No. This means your MySQL database on the slave is still working but no updates will be processed. But it gets worse. Because your slave has stopped processing changes, its database is now out of sync with the master. To get back on track, you will need to reset MySQL replication slave.

This guide deals with the older binary logging MySQL Replication and not the transaction-based MySQL replication.

Replica_SQL_Running No

When checking the status of your slave with the SHOW REPLICA STATUS\G; command you may see the Replica_SQL_Running statement shows no. You are not getting updates processed by the slave from the master. Further down you will see the last error.

mysql -u root -p

SHOW REPLICA STATUS\G;

The issue will look like this;

The only way to resolve this is to reset the MySQL Replication slave. You do not need to reinstall the slave.

Activate Read Lock & Get Log Position

Resetting the MySQL Replication slave involves dumping the master database and setting a new position for the slaves to start processing changes. When you reset the replication slave it’s just like starting from scratch. You must follow this process in order. Have two consoles open to your master. In one console, lock the tables and get the log file and position.

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

EDIT: apply the grants again to allow your slaves to connect to your master.

GRANT ALL ON *.* TO 'user'@'slaveip';

Dump MySQL Master Database

Do not close or exit MySQL. If you do the read lock will be released. In the other console, dump the master’s database and send it to your slave using SCP.

mysqldump -u root -p --all-databases > /home/dump.sql

scp /home/dump.sql root@slaveip:/home

You can now unlock the tables in the master.

UNLOCK TABLES;

Reset MySQL Replication Slave

So now in your NVMe VPS slave let’s reset it. Like above, open two connections to your MySQL slave. In one console login to MySQL and stop the slave.

mysql -u root -p 
STOP SLAVE;

In the other console import the database from the master.

mysql -u root -p < /home/dump.sql

So now you have a full copy of the Master database back in your slave. In the other console, reset the slave and specify the bin log and position. You got this information from the master when you locked the tables before dumping the database.

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=7735;
START SLAVE;

Your slave should now be back in sync with your master. Repeat the process on any other MySQL slaves you have. To confirm everything works, issue the below command and inspect the Slave_IO_Running: & Slave_SQL_Running: statements. Both should read yes.

SHOW REPLICA STATUS\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

You have now reset a MySQL Replication Slave using binary logging.