MySQL – Rebuilding Replication on Windows

When MySQL replication breaks or becomes corrupted, sometimes it is necessary to rebuild it from scratch. Follow the steps below on your Windows servers running MySQL to restore replication to a healthy state.

1. Login to the server.

2. Open MySQL Command Line Client and login with the root password, then enter the following command:

STOP SLAVE;

1. Login to the server.

2. Open MySQL Command Line Client and login with the root password, then enter the following commands:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

3. IMPORTANT! Record the values shown for use later on the slave server (take a screenshot).

4. Backup the MySQL databases that you want to replicate. This can be done from Command Prompt using the following command (run for each database that needs to be replicated):

• Replace ROOTPWD with the actual Root password for the MySQL server.
• Replace DBNAME with the actual database name that you want to backup.

“C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe” -u root -pROOTPWD DBNAME > %UserProfile%\Desktop\DBNAME_backup.sql

5. Once all the databases are backed up, copy them from the desktop of the master server to the slave server’s desktop.

6. Unlock the SQL tables on the master server using the following command:

UNLOCK TABLES;

1. Restore the MySQL databases that you backed up on the master server. This can be done from Command Prompt using the following command (run for each database that needs to be replicated):

• Replace ROOTPWD with the actual Root password for the MySQL server.
• Replace DBNAME with the actual database name that you want to backup.

“C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe” -u root -pROOTPWD DBNAME < %UserProfile%\Desktop\DBNAME_backup.sql

2. Open MySQL Command Line Client and login as root.

3. Enter the following command to set the master (use the values you recorded from the master server):

• Replace SERVERNAME with the actual NETBIOS name of the master server.
• Replace replication with the MySQL user account used for replication.
• Replace XXXXXXX with the password of the MySQL replication user account.
• Replace NNNNNN with the log bin file number from the master server.
• Replace #### with the log position from the master server.

CHANGE MASTER TO MASTER_HOST='SERVERNAME', MASTER_USER='replication', MASTER_PASSWORD='XXXXXXX', MASTER_LOG_FILE='SERVERNAME-bin.NNNNNN', MASTER_LOG_POS=####;

4. Start the slave server:

START SLAVE;

5. Ensure replication is working as expected:

SHOW SLAVE STATUS\G;

You may also want to test that it is working by making a change on the master server and then checking the database on the slave to see if it picked up the change (ie. create a table, then delete it).