• Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

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

Written by Rahul, Updated on March 8, 2020

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.

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

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

10 Comments

  1. Avatar Faheem Reply
    July 26, 2020 at 11:43 am

    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 ?

  2. Avatar Robert Reply
    March 7, 2020 at 9:41 pm

    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.

  3. Avatar Magzhan Reply
    August 25, 2019 at 6:50 pm

    Thanks for post!!!

  4. Avatar Karin Shieh Reply
    April 6, 2017 at 6:24 pm

    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,

  5. Avatar GJMAN2 Reply
    July 16, 2015 at 9:16 am

    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;

  6. Avatar Derek Almond Reply
    September 30, 2014 at 1:07 am

    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?

    • Avatar Alexander Reply
      January 10, 2015 at 1:04 pm

      I think it is correct to first “flush tables with read lock;” and then “reset master;”.

  7. Avatar Chris Reply
    July 19, 2014 at 2:16 am

    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.

  8. Avatar Stefan Lasiewski Reply
    June 4, 2014 at 10:42 pm

    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/

  9. Avatar Vasili Reply
    May 21, 2014 at 1:02 am

    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?

Leave a Reply Cancel reply

Popular Posts

  • How to View or List Cron Jobs in Linux
  • How to Install PHP 8 on Ubuntu 20.04
  • How to Set Up SSH Tunnel with PuTTY
  • How to Install Tor Browser on Ubuntu 20.04
  • Issue with phpMyAdmin and PHP: Warning in ./libraries/sql.lib.php#613 count(): Parameter must be an array or an object that implements Countable”
  • How to Allow Remote Connections to MySQL
  • How to Install MySQL 8.0 on Ubuntu 20.04
  • How to Install Apache Kafka on Ubuntu 20.04
© 2013-2021 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy