MySQL Replication Switchover: Step-by-Step Guide

MySQL replication is a powerful feature that allows you to create redundant copies of your database for improved availability and scalability. In certain scenarios, it becomes necessary to switch the roles of the master and slave servers in a replication setup. This article will guide you through the process of performing a MySQL replication switchover, ensuring a smooth transition without any data loss. Let’s dive into the step-by-step instructions.

Current Master: 192.168.221.131
Current Slave : 192.168.221.135

Step 1: Verifying the Replication

Before initiating the switchover, it’s important to verify that replication is functioning correctly on the current Slave server (192.168.221.135). Execute the following queries to ensure the replication status:

SHOW SLAVE STATUS\G;

This command displays the slave’s status, including information such as replication lag, error messages, and the current position in the replication log. Verify that there are no errors and the slave is synchronized with the master.

If there are any issues or errors reported, resolve them before proceeding with the switchover.

Once you have confirmed that replication is working properly, proceed to the next steps.

Step 2: Preparing the Current Master

On the current master server (192.168.221.131), execute the following queries to initiate the switchover process:

-- Make the current master read-only
SET GLOBAL read_only=ON;
-- Confirm that it is set to read-only mode
SHOW VARIABLES LIKE '%read_only%';
-- Flush tables and logs to ensure all changes are written to disk
FLUSH TABLES;
FLUSH LOGS;
-- Retrieve the master's status, including the log file and position
SHOW MASTER STATUS;

Step 3: Preparing the Current Slave

On the current slave server (192.168.221.135), execute the following queries:

STOP SLAVE; -- Stops the slave replication process
SET GLOBAL read_only=OFF; -- Allows write operations on the current slave
SHOW VARIABLES LIKE '%read_only%'; -- Confirm that it is set to read-write mode
SHOW MASTER STATUS; -- Displays the current slave's replication status

Stopping the slave replication process ensures that it doesn’t interfere with the switchover. We also set the read_only variable to OFF on the slave server, allowing it to accept write operations.

Additionally, take note of the master’s status using the SHOW MASTER STATUS; command. We will need this information later.

Step 4: Creating a user for the slave:

To facilitate replication, we need to create a user on the current master that the new slave server can use to connect. Execute the following queries on the New Master Server (192.168.221.135):

-- Creating a user for the slave
CREATE USER slaveuser@192.168.221.131 IDENTIFIED WITH mysql_native_password BY 'password'; -- Creates a user for replication
GRANT REPLICATION SLAVE ON *.* TO slaveuser@192.168.221.131; -- Grants replication privileges
GRANT REPLICATION CLIENT ON *.* TO slaveuser@192.168.221.131; -- Grants client privileges
FLUSH PRIVILEGES; -- Reloads the privileges to apply the changes

Replace password with a strong password of your choice. These commands create a user named slaveuser and grant the necessary replication privileges.

Step 5: Configuring the New Master

On the new slave server (192.168.221.131), execute the following queries to configure it as the new slave:

CHANGE MASTER TO MASTER_HOST = '192.168.221.135',
    MASTER_USER = 'slaveuser',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000004',
    MASTER_LOG_POS = 695;

START SLAVE;

Make sure to replace the values with the appropriate IP address, username, password, and the master’s log file and position obtained from the previous steps. Starting the slave replication process on the new master server establishes it as the slave in the replication setup.

By starting the slave, it will begin replicating changes from the new master server. This step is crucial to ensure the proper functioning of the replication setup.

Once you have started the slave, proceed to next step to verify the replication.

Step 4: Verifying the Replication

After the switchover is complete, it is essential to check if the replication is functioning correctly. You can perform various tests, such as creating and dropping test databases or executing other write operations on the new master server. Monitor the logs and ensure that the changes propagate to the new slave server (previously the master).

Conclusion:
Performing a MySQL replication switchover involves careful coordination between the current master and slave servers. By following the step-by-step instructions provided in this guide, you can ensure a smooth transition without losing any data. MySQL replication switchover is a valuable technique to maintain the availability and reliability of your database infrastructure.

Note: If you have any suggestions or if there is a better way to perform any of the steps mentioned in this guide, please feel free to explore alternative methods. The instructions provided here are meant to serve as a general guideline for performing a MySQL replication switchover. Each environment may have specific configurations and requirements that could be optimized for your particular use case. Don’t hesitate to adapt the steps according to your specific needs or seek professional advice if necessary. Continuous improvement is always encouraged to ensure the best performance and reliability of your MySQL replication setup.