When a MySQL replica instance is no longer to be associated with a primary database instance in a replication design, the replica not only needs to be stopped, but the connection information pointing to the primary must be cleared as well.

In older versions of MySQL (5.0 and 5.1) STOP SLAVE simply followed by RESET SLAVE commands were often used. RESET SLAVE causes any existing relay log files to be deleted as well as the relay log info repositories. It also clears the current binary log position where the replica had been retrieving transactions.

This older method of stopping and clearing replica deletes the master.info file but does not clear the master host, user, password and other connection information which is stored in memory. If a slave automatically restarts after a restart of the server or instance slave_skip_start is not included in the replica’s configuration file, this may lead to the replica reading an improper binary log causing the replica to either generate errors or become inconsistent with the primary. This can be avoided by carrying out one other step after the STOP SLAVE command is issued; use CHANGE MASTER TO with master_host=’ ‘ followed by the RESET SLAVE command.

Newer versions of MySQL (5.5, 5.6, 5.7) have a simpler method to disable a replica. After stopping the replica (with STOP SLAVE) the newer RESET SLAVE ALL command will remove the master connection parameters so any START SLAVE commands will not succeed and the replica database instance will remain consistent with the primary up to the point in which the replica was stopped.

Summary: For newer versions of MySQL (5.5, 5.6, 5.7) use RESET SLAVE ALL after stopping a replica to ensure that the replica cannot inadvertently be restarted at an incorrect binary log position. For older versions of MySQL (5.0, 5.1) after stopping the slave, clear the master info configuration settings by issuing a CHANGE MASTER TO statement where master_host=’ ‘, then issue RESET SLAVE.

Share This