How to Reset ( Re-Sync ) MySQL Master-Slave Replication

By 2 Mins Read

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?

Advertisement

This article will guide you to how to reset MySQL replication and it will start again from scratch.

Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup of bin-log files first and then follow the instructions.

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;

[ Note: Adding a read lock with production sites. Read more about table locking ]

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 

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
Share.

Related Posts

10 Comments

  1. Faheem on

    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 ?

    Reply
  2. Robert on

    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.

    Reply
  4. Karin Shieh on

    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,

    Reply
  5. GJMAN2 on

    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;

    Reply
  6. Derek Almond on

    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?

    Reply
  7. Chris on

    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.

    Reply
  9. Vasili on

    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?

    Reply

Exit mobile version