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.
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 [email protected]:/home
You can now unlock the tables in the master.
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.
How was this article? – Reset MySQL Replication Slave
You might also like
More from MySQL/MariaDB
Load Balance HAProxy MySQL Connections Over MySQL Cluster
Recently, we went through the process of load balancing HTTPS connections with HAProxy. In this article, we are going to …
How To Configure MySQL Master-Master Replication
MySQL Master-Master replication evolved from the standard Master-Slave replication. In a Master and Slave environment, database changes are made on …
How To Replicate MySQL Database Using MySQL_Replication
WOKE STATEMENT: We use the terminology Master + Slave in this post. Whilst some may feel we should use …