Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Databases»MySQL»How to Reset ( Re-Sync ) MySQL Master-Slave Replication

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

    RahulBy RahulSeptember 12, 20132 Mins ReadUpdated: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
    
    MySQL mysql replication replication resync mysql replication
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleInstall AjaXplorer ( File sharing platform ) on Linux
    Next Article How to Find File Creation Time (crtime) in Linux

    Related Posts

    How To Install Linux, Nginx, MySQL, & PHP (LEMP Stack) on Ubuntu 22.04

    Updated:April 7, 20227 Mins Read

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How To Install LAMP Stack on Ubuntu 22.04 LTS

    Updated:April 20, 20225 Mins Read

    How To Install MariaDB on Debian 11

    4 Mins Read

    How to Rename MySQL Database

    Updated:July 26, 20213 Mins Read

    How To Change MySQL User Password

    2 Mins Read

    10 Comments

    1. Faheem on July 26, 2020 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 ?

      Reply
    2. Robert on March 7, 2020 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.

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

      Thanks for post!!!

      Reply
    4. Karin Shieh on April 6, 2017 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,

      Reply
    5. GJMAN2 on July 16, 2015 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;

      Reply
    6. Derek Almond on September 30, 2014 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?

      Reply
      • Alexander on January 10, 2015 1:04 pm

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

        Reply
    7. Chris on July 19, 2014 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.

      Reply
    8. Stefan Lasiewski on June 4, 2014 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/

      Reply
    9. Vasili on May 21, 2014 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?

      Reply

    Leave A Reply Cancel Reply

    Recent Posts
    • How to Enable / disable Firewall in Windows
    • How to Install JAVA on Ubuntu 22.04
    • Switching Display Manager in Ubuntu – GDM, LightDM & SDDM
    • Changing the Login Screen Background in Ubuntu 22.04 & 20.04
    • How To Install PHP (8.1, 7.4 or 5.6) on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.