Switchover in MySQL Replication(Master-Slave) Setup

Switchover in MySQL Replication(Master-Slave) Setup 

Performing DR Switch is regular activity for DBA for DR Testing. In this blog , We will show/discuss about how to switchover MySQL Replication (Master-Slave) Setup.

Below are the steps followed for the MySQL Replication Switchover:

1) Stop All application servers/application.

2) Check Slave and Master status on the Slave(mysqlslave01) and Master server(mysqmaste01):

 
Log in to Master server (mysqmaste01):
mysql> Flush logs;
mysql> SHOW MASTER Status\G

*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 28624712
     Binlog_Do_DB: mysqldb
 Binlog_Ignore_DB:
Executed_Gtid_Set: 61fa835b-2c75-11e6-987c-005056b7fda8:1-4104
1 row in set (0.00 sec)

Log into Slave server (mysqlslave01):

Make sure slave running without log-slave-updates.

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.21.38.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 28624712
               Relay_Log_File: mysqlslave01-relay-bin.000004 
                Relay_Log_Pos: 26020889
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mysqldb
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 28624712
              Relay_Log_Space: 28624683
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 61fa835b-2c75-11e6-987c-005056b7fda8
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 61fa835b-2c75-11e6-987c-005056b7fda8:4-4104
            Executed_Gtid_Set: 61fa835b-2c75-11e6-987c-005056b7fda8:1-4104
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

NOTE: Compare Executed_Gtid_Set GTID on Master output with the Slave Retrieved_Gtid_Set & Executed_Gtid_Set. All these must be same.

3) Check process list on the Slave (mysqlslave01) and Master server (mysqmaste01):

On Master server :

mysql> SHOW PROCESSLIST;
+----+------------+--------------------+----------+-------------+--------+---------------------------------------------------------------+------------------+
| Id | User       | Host               | db       | Command     | Time   | State                                                         | Info             |
+----+------------+--------------------+----------+-------------+--------+---------------------------------------------------------------+------------------+
| 14 | appdbuser | 172.21.38.4:52856  | mysqldb | Sleep       |     11 |                                                               | NULL             |
| 18 | appdbuser | 172.21.38.4:52972  | mysqldb | Sleep       |     95 |                                                               | NULL             |
| 19 | appdbuser | 172.21.38.4:52974  | mysqldb | Sleep       |     67 |                                                               | NULL             |
| 50 | repl       | 172.21.45.67:52318 | NULL     | Binlog Dump | 470802 | Master has sent all binlog to slave; waiting for more updates | NULL             |  ==> Note
| 53 | appdbuser | 172.21.38.4:54448  | mysqldb | Sleep       |     42 |                                                               | NULL             |
| 58 | appdbuser | 172.21.38.4:54752  | mysqldb | Sleep       |     38 |                                                               | NULL             |
| 61 | root       | localhost          | mysqldb | Query       |      0 | starting                                                      | SHOW PROCESSLIST |
+----+------------+--------------------+----------+-------------+--------+---------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)

mysql>

On Slave Server:

mysql> SHOW PROCESSLIST;
+-----+-------------+---------------------+-------------+---------+---------+--------------------------------------------------------+------------------+
| Id  | User        | Host                | db          | Command | Time    | State                                                  | Info             |
+-----+-------------+---------------------+-------------+---------+---------+--------------------------------------------------------+------------------+
|   1 | system user |                     | NULL        | Connect | 1917641 | Waiting for master to send event                       | NULL             | ==>NOTE
|   2 | system user |                     | NULL        | Connect |   16109 | Slave has read all relay log; waiting for more updates | NULL             | ==>NOTE
|   8 | appuser    | 172.21.40.192:60269 | lams_logger | Sleep   |     312 |                                                        | NULL             |
| 353 | appuser    | 172.21.40.192:63689 | lams_logger | Sleep   |     312 |                                                        | NULL             |
| 473 | root        | localhost           | mysqldb    | Query   |       0 | starting                                               | SHOW PROCESSLIST |
+-----+-------------+---------------------+-------------+---------+---------+--------------------------------------------------------+------------------+
5 rows in set (0.01 sec)

NOTE: There should not be any Binary log to be shipped to slave server. And there should not be any relay log to be processed by the slave.

4) Switchover to Slave.

On Slave Server:

mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;

On Master server: 

mysql> Reset Slave;

mysql>
CHANGE MASTER TO
MASTER_HOST='198.168.0.2', 
MASTER_USER='repl',
MASTER_PASSWORD='**********',
MASTER_LOG_FILE='mysql-bin.000002', ==> use the information from above step.
MASTER_LOG_POS=769;

mysql>start slave;
mysql>show warnings;
mysql>show slave status;
mysql>SHOW SLAVE STATUS\G ==> Check for Slave_IO_Running,Slave_SQL_Running,Last_errno,Last_error

5) Point application to new master.

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

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

You can also , Visit our Blogs for related topic on MySQL:
1)MySQL InnoDB Cluster node Addition Issue: - Cluster.addInstance: : MySQL server has gone away (RuntimeError)
2)Install & Configure MySQL Router -  MySQL InnoDB Cluster

3)Restoring a Failed member in InnoDB Cluster