Some times MySQL replication creates problems and slave could not sync properly from the master database server. It may cause due to lots of reason’s. Only the question is how to fix it?
This article will guide you to how to reset MySQL replication and it will start again from scratch.
At Slave Server:
At first we need to stop slave on slave server. Login to the MySQL server and execute the following command.
mysql> STOP SLAVE;
At Master Server:
After stopping slave go to master server and reset the master state using following command.
mysql> RESET MASTER; mysql> FLUSH TABLES WITH READ LOCK;
Take a dump of database is being replicated using following command.
# mysqldump -u root -p mydb > mydb-dump.sql
After taking backup unlock the tables at master server.
mysql> UNLOCK TABLES;
At Slave Server:
Restore database backup taken on slave server using following command.
# mysql -u root -p mydb < mydb-dump.sql
Login to mysql and execute following commands to reset slave state also.
mysql> RESET SLAVE; mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
After resetting slave start slave replication
mysql> START SLAVE;
Now your replication has been re sync same as newly configured. you can verify it using the following commands.
mysql> show slave status \G
Good article, do following commands has any impact if we are running on production environment
mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
as per my understanding it put some sort of lock on tables so in production it can stop application to make any update isn’t it ?
Great help–thanks. Noticed one glitch. The last command “show slave status G”. Shouldn’t it be “show slave status\G”
Mine wouldn’t work until I added the \. Thanks for your post.
Thanks for post!!!
I have a question on the order of the tasks on the slave server.
Should we run “reset slave;” first and then restore database backup taken from the master server to the slave server?
Thank you,
This is works but before you do the “change master to” in slave server , please make sure you put correct MASTER_LOG_FILE , and MASTER_LOG_POS values and must match with master server. So, check at the master server by ” show master status”. You simply cannot put or assign CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′ and MASTER_LOG_POS=1;
Important – when you lock the tables in the master – do the dump (the next step) in a different terminal, DO NOT exit mysql to do it, as that seems to release the lock so your dump will be out of date when you restart the slave.
– can someone verify this?
I think it is correct to first “flush tables with read lock;” and then “reset master;”.
Using this article I was able to quickly restore my slave to working status. This is a great article – please do not take it down.
Vasili: You said that your master server went down, and that your slave took over, right?
If this is true, there is only one difference:
use mysqldump on the slave, then load that data into the master.
Everything else is the same.
Note that MySQL 5.6 provides global transaction identifiers (GTIDs). With GTIDs you do not need to know the MASTER_LOG_FILE or the MASTER_LOG_POS. You do need to know the ‘gtid_executed’ value on the master.
A great writeup can be found here:
http://www.mysqlperformanceblog.com/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/
This works, but it just overwrites the slave DB with the master one. Is there a way to replicate only the changes made to a slave when the master is down, back to the master?