Reset ( Re-Sync ) MySQL Master-Slave Replication

Rate this post

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
  • 65
    Birth Of MySQL MySQL started out with the intention of using the mSQL database system to connect to tables using fast low-level (ISAM) routines. However, after some testing, They conclude that mSQL was not fast enough or flexible enough. This resulted in a new SQL interface to our database but with…
    Tags: mysql, server
  • 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

3 thoughts on “Reset ( Re-Sync ) MySQL Master-Slave Replication

  • August 13, 2014 at 4:51 am
    Permalink

    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    START SLAVE;
    SHOW SLAVE STATUS \G;

    Reply
  • September 12, 2014 at 6:48 am
    Permalink

    gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

    Reply
  • September 12, 2014 at 6:50 am
    Permalink

    Restoring your MySQL Database

    Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

    Create an appropriately named database on the target machine
    Load the file using the mysql command:
    $ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql] Have a look how you can restore your tut_backup.sql file to the Tutorials database. $ mysql -u root -p Tutorials < tut_backup.sql To restore compressed backup files you can do the following: gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname] If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows: mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

    Reply

Leave a Reply

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

%d bloggers like this: