Resolving MySQL Error 1198 (ER_SLAVE_MUST_STOP): Proper Steps for Slave Operations

by

When managing a MySQL replication setup, encountering Error 1198 – SQLSTATE: HY000 (ER_SLAVE_MUST_STOP) indicates an attempt to perform an operation that requires the replication process to be stopped first. This error is a safeguard to prevent conflicts and potential data corruption. This guide will provide you with the necessary steps to diagnose and fix this error, ensuring that your replication operations are carried out safely and correctly.

Understanding Error 1198

Error 1198 occurs when an operation that requires exclusive access to the slave’s data or configuration is attempted while the slave’s replication threads are running. MySQL requires that you stop the slave’s replication process to ensure data integrity and consistency.

Diagnosing the Error

This error is straightforward to diagnose. It occurs when you try to perform certain operations such as changing replication parameters, updating the master configuration, or performing a backup that requires a consistent state.

Fixing the Error

To fix this error, you must stop the slave’s replication process before proceeding with the operation. Here are multiple scenarios where you might encounter Error 1198 and how to resolve them:

1. Changing Replication Parameters

When changing replication parameters, you must stop the slave:

STOP SLAVE;
-- Make your changes, for example:
CHANGE MASTER TO MASTER_HOST='new_master_host';
-- Once done, restart the slave:
START SLAVE;

2. Performing a Backup

To perform a backup, especially when using mysqldump, you need to stop the slave to ensure data consistency:

STOP SLAVE;
-- Perform your backup
mysqldump -u root -p --all-databases > backup.sql
-- After the backup, restart the slave:
START SLAVE;

3. Resetting the Slave

If you need to reset the replication process completely:

STOP SLAVE;
RESET SLAVE; -- or RESET SLAVE ALL; for MySQL 5.5.16 or later
-- Reconfigure the slave as necessary and start the replication:
START SLAVE;

4. Upgrading the Slave Server

Before upgrading the MySQL version on the slave:

STOP SLAVE;
-- Proceed with the upgrade process
-- Once the upgrade is complete, start the slave:
START SLAVE;

5. Relaying Log Operations

When performing operations that involve relay logs:

STOP SLAVE;
-- Perform the relay log operation, such as purging relay logs:
PURGE RELAY LOGS;
-- Restart the slave:
START SLAVE;

Preventive Measures

To prevent disruptions:

  • Always check the slave status before performing sensitive operations (SHOW SLAVE STATUS\G).
  • Schedule maintenance during off-peak hours to minimize the impact on replication.
  • Document and standardize procedures for operations that require stopping the slave.

Conclusion

Error 1198 is a protective measure by MySQL to prevent actions that could harm replication integrity. By stopping the slave before performing certain operations, you can ensure that your replication setup remains stable and consistent. Always remember to start the slave after completing the operation to resume replication. For more details on replication management and best practices, refer to the MySQL Replication documentation. If you face complex issues or are unsure about any step, seeking assistance from a seasoned database administrator is advisable.

Leave a Comment