Reset ( Re-Sync ) MySQL Master-Slave Replication

Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.

 

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 mysql server and execute 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 following commands.

mysql> show slave status \G

 

Related Posts

  • 76
    Here we are going to create a Master -> Master -> Slave -> Slave replications architecture. I am expecting here , you have MySQL installed and set-up as normal.       Master 1 will be known as Master 1 and Slave 2 with IP 10.1.1.1 Master 2 will be known as…
    Tags: slave, mysql, master
  • 62
    Here we will help you to setup master-slave replication between MySQL servers. Setup Details: Master Server: 10.0.10.12 Slave  Server: 10.0.10.18 Database: empmaster 1. Setup MySQL Master Server Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master. Block write statement on…
    Tags: mysql, slave, server, master, replication
  • 60
    Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
    Tags: mysql, master, server, replication, slave
  • 48
    90% of the time, this error is due either to the MySQL Server not running, or else firewall configuration on the Windows server blocking access on port 3306 (or whatever port your MySQL instance is configured to use). Error: 2003 (CR_CONN_HOST_ERROR) Message: Can't connect to MySQL server on '%s' (%d)
    Tags: mysql, server
  • 45
    Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently. Checking Replication Status: The most common task when managing a replication process is to ensure…
    Tags: slave, master, replication, mysql

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: