MYSQL DB SWITCHOVER

INTRODUCTION:

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

Step 1. Check replication in MySQL Slave:

Use below command to check show slave status & show master status to check for the status of lag and for any error in replication.

ON PRIMARY

============

mysql -u root -pTEST_MAST1$

show master status \G;

check log position both Master and Slave. (If Both are same then proceed with below)

systemctl stop mysqld

ON STANDBY

============

mysql -u root -pTEST_SLAVE$

show slave status \G;

check log position both Master and Slave. (If Both are same then proceed)

systemctl stop mysqld

STEP 2: Make Master Read only and change below mentioned entry in .conf file

take the backup (my config file)

mysql> set persist read_only=ON;

Query OK, 0 rows affected (0.07 sec)

--disable log_file. (#log_file) in .conf file

systemctl start mysqld

reset master;

reset slave;

STEP 3: Make Slave Read and Write and change below mentioned entry in .conf file

take the backup (my config file)

--disable read_only file (#read_only=1).

--enable log_file.

systemctl start mysqld

Reset master;

reset slave;

stop slave;

STEP 4: Perform the below steps in old-master and update it with new-master information and start slave.

stop slave;

CHANGE MASTER TO MASTER_HOST='current master (TEST_SLAVE) host’, MASTER_USER='replication',MASTER_PASSWORD='replication',MASTER_PORT=xxxx, MASTER_LOG_FILE='current master(STANDBY01) log file name',MASTER_LOG_POS=123;

Start slave;

CHANGE MASTER TO MASTER_HOST='xxx.xxx.xx.xx',MASTER_USER='replication',MASTER_PASSWORD='xxxxxx',MASTER_PORT=xxx, MASTER_LOG_FILE='xxxxxxxx',MASTER_LOG_POS=123;

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.

Biju Muthusamy