Resync MySQL Slave with Master

For various kinds of reasons, it is not unusual to find MySQL Slave instance in a broken state during the replication. The quick fix requires a perfect database backup of Master instance. From the backup file, two parameters must be extracted for restoration of data onto Slave Server:

bin-log filename: MASTER_LOG_FILE
bin-log position: MASTER_LOG_POS

Of course, the overall system performance may be affected during the backup operation. However, MySQL Master server can be kept running as normal without any interruption.

You may find it difficult to find all the switches required for a successful online backup operation. Fortunately, an example is here.

Before anything happens, you'll find it easier to manage all those by opening two separate terminal consoles for both Master and Slave instances.

Terminal console for Master server:

The following command (recommended to run under “sudo –s” environment) will dump all databases on Master instance:

$ mysqldump -uroot -p \-S /etc/mysql/mysql.sock \
--master-data --hex-blob --opt --single-transaction \
--comments --dump-date --no-autocommit \
--all-databases > target_backup.sql

Please make sure the path of socket file of MySQL Master server is correct.

With “--master-data” switch, Mysqldump program will make a comment for the bin-log filename and position which can be really useful for resynchronization afterwards.

With “--single-transaction” switch, a global lock is acquired for a very short time at the beginning of the dump while any action on logs happens at the exact moment of the dump.

With “--hex-blob” switch, image data with BLOB type can be well preserved inside the backup without any disruption.

Once finished, you'll get a perfect backup file from MySQL Master server named "target_backup.sql".

Terminal console for Slave server:

Now, it is time to put the data onto MySQL Slave server. Please beware that the socket file used here becomes “/etc/mysql2/mysql2.sock”. Don’t it mix up with that of Master instance.

$ mysql -uroot -p -S /etc/mysql2/mysql2.sock < target_backup.sql

Now, login to MySQL Slave server:

$ mysql –root –p –S /etc/mysql2/mysql2.sock

Please check if MySQL user role named “slaveuser” with “replication” privileges does exist on Slave server.

Then, execute the following commands to resynchronize Slave server:

mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST=’localhost’,
>MASTER_PORT=3306,
>MASTER_USER=’slaveuser’,
>MASTER_PASSWORD=’XXXXXXXX’,
>MASTER_LOG_FILE=’mysql-bin.XXXXXXXX’,
>MASTER_LOG_POS=XXXXX
>;
mysql> START SLAVE;

For the corresponding values of MASTER_LOG_FILE and MASTER_LOG_POS, please read through the heading comments inside “target_backup.sql” file.

To check the status, please issue the following command:

mysql> SHOW SLAVE STATUS;

It is useful to check the error log of MySQL Slave server to confirm whether everything is back on track.