MySQL Replication Switchover: Step-by-Step Guide

 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.

I have tow node one is master node and another is slave node.Given below details:-

Current Master: 192.168.70.10

Current Slave : 192.168.70.11

Verifying the Replication:-

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

mysql> 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.

Preparing the Current Master:-

On the current master server (192.168.70.10), 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;

Preparing the Current Slave:-

On the current slave server (192.168.70.11), 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.

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.70.11):

-- 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.

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.70.11',

    MASTER_USER = 'slaveuser',

    MASTER_PASSWORD = 'password',

    MASTER_LOG_FILE = 'mysql-bin.000004',

    MASTER_LOG_POS = 695;

START SLAVE;

mysql> show slave status\G

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.

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).