MySQL: How to Re-Sync (Refresh) Replication Properly

The following steps illustrate how to refresh MySQL replication in a single master-replica (slave) scenario:

1) Connect to MySQL on the master server

2) Issue the following commands:

RESET MASTER;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Take note of the values reported in the row returned by SHOW MASTER STATUS. In my case they look like this:

File	        Position	Binlog_Do_DB	Binlog_Ignore_DB
bin.000001	107		

3) Use mysqldump to perform a full backup of all databases on the master. For example:

mysqldump -h[master-hostname-or-ip] --routines -uroot -pxxxxxxx --verbose --all-databases > all-databases.sql

If you add the –master-data command-line option to mysqldump, the CHANGE MASTER TO command (described in step 6) is written into the dump file, thus saving execution of that statement after importing the dump file into the replica.

Execute the following command, to allow users to begin using the master again:

UNLOCK TABLES;

4) Execute the dump file you just created (all-databases.sql) against the replica (slave) server. Copy the file to the replica server (for faster execution), or execute it locally (whichever is appropriate for your situation).

For example:

mysql -h[replica-hostname-or-ip] --comments -uroot -pxxxxxxx < all-databases.sql

5) Connect to MySQL on the replica (slave), and execute the following commands, replacing the values bin.000001 and 107 with the values output by the SHOW MASTER STATUS command you executed in step 2 (above):

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=107;

6) Next, execute the following commands on the replica:

START SLAVE;

7) Finally, test that the replica’s IO and SQL replication threads are both working again, by executing (on the replica):

SHOW SLAVE STATUS;

Successful completion of the task will be indicated by the following output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Author: Alex T. Silverstein

A developer who wants to help you and himself by writing down good tidbits for free

One thought on “MySQL: How to Re-Sync (Refresh) Replication Properly”

Leave a Reply

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