Reset replica/slave 5-step

from the Artful MySQL Tips List

Replication needs to be rest when When source/master and replica/slave have got out of sync. Execute steps 1 and 2 on the source/master server; 3, 4 and 5 on the replica/slave. Note that since 8.0, the preferred reference word for a "master" server is "source", and the preferred word for a "slave" server is "replica".

1 Reset source/master: On the source/master server, in the mysql client program, execute ...

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G

Note the File, Position values, save them somewhere.

2 Dump databases: If the databases are not too large, the traditional mysqldump utility should suffice. If all master databases are being replicated, from the master terminal prompt execute ...

mysqldump -uUSR -pPWD --add-drop-database -K -E -R --all-databases >BACKUPPATH

... or if only some databases are being replicated then ...

mysqldump -uUSR -pPWD --add-drop-database -K -E -R --databases DBLIST >BACKUPPATH

... in each case substituting your values for USR, PWD, DBLIST, BACKUPPATH.

For larger databases, consider using one of the tools described here. All these dump tools are more than an order of magnitude faster.

After source/master data has been dumped and saved, on source/master in the mysql client program or in mysql shell, execute ...

UNLOCK TABLES;

3 Stop replica/slave, import the dump file:

On the replica/slave ...

STOP SLAVE;

Import the synchronising dump file on the replica/slave, if mysqldump or mysqlpump was used then in the mysql client program with the source command, otherwise with the appropriate recovery method for the dump tool you used.

4 Reset the replica/slave: In the replica's client program execute ...

RESET SLAVE;
CHANGE MASTER TO 
  MASTER_LOG_FILE='FILE', 
  MASTER_LOG_POS=POSITION;

... substituting for FILE, POSITION the values from step 1.

5 Restart the replica/slave:

START SLAVE;
SHOW SLAVE STATUS\G

Interruptions for maintenance

If source and replicas are in sync but replication needs to be suspended, e.g., for maintenance, consider this less time-consuming sequence ...

1 Take all servers offline: On the source, execute ...

FLUSH TABLES WITH READ LOCK;

2 Stop replication on replicas: Ensure that SHOW SLAVE STATUS shows all slaves up-to-date, then on each replica execute ...

STOP SLAVE;

3 Reconfigure and restart the source: Restart source with skip-networking=ON and session sql_log_bin=OFF, then do the maintenance on source and replicas.

4 Restart source: Restart source with code>skip-networking=OFF and session sql_log_bin=ON.

5 Restart replicas: On each replica execute ...

START SLAVE;
SHOW SLAVE STATUS;

Last updated 16 Jun 2022

Return to the Artful MySQL Tips page