Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»How to Reset ( Re-Sync ) MySQL Master-Slave Replication

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

    By RahulMarch 8, 20202 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 < 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

    Related Posts

    Securing MySQL Database with Limited User Permissions

    How to Install LAMP Stack on RHEL & CentOS Stream 9

    (Resolved) MySQL connection error: certificate verify failed

    View 10 Comments

    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

    Advertisement
    Recent Posts
    • How to List Manually Installed Packages in Ubuntu & Debian
    • 10 Bash Tricks Every Developer Should Know
    • How to Validate Email Address in JavaScript
    • Firewalld: Common Firewall Rules and Commands
    • 12 Apk Commands in Alpine Linux Package Management
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

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