MySQL Master Slave Switchover

March 6, 2021
()

MySQL Master Slave Switchover

In the previous blog, we saw How to create MySql GTID-based Master Slave Replication – Single DB Click here to read more. We will use the same configuration to perform Switchover between Master & Slave.


Current Configuration.

Host nameIPRoleServer IDInstallation Link
test-machine01
192.168.114.170
Master1
Click Here
test-machine012
192.168.114.176
Slave3Click Here


Step 1. Check replication in MySQL Slave: Use command show slave status & show master status command to check for the status of lag and for any error in replication. If you notice here Executed_Gtid_Set is the same in slave & master that means all changes from Master are applied in Slave or Replica.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.170
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000025
          Read_Master_Log_Pos: 8969
               Relay_Log_File: relay_bin01.000004
                Relay_Log_Pos: 1144
        Relay_Master_Log_File: binlog.000025
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: repl_test
          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: 8969
              Relay_Log_Space: 1349
              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: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             Master_Info_File: mysql.slave_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: 13ecba9c-444a-11eb-a397-000c29f9d9e6:34-35
            Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-35
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000024 |      940 |              |                  | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-35 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)


Step 2. Make Master Readonly: Make master readonly to avoid any update in master before switchover and create replication user in master.

mysql> set persist read_only=ON;
Query OK, 0 rows affected (0.07 sec)

mysql>  show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)

4. Create replication user in Master

mysql> CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>


Step3. Flush in Slave: Flush table and Flush binary log to get a clean database.

mysql> flush tables; flush logs;
Query OK, 0 rows affected (0.40 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>


Step 4. Stop Slave and Convert it to read/write: Perform the below steps in Slave instance to reset master information as it will be new Master now and take note of Executed_Gtid_Set as we will use to initialize slave.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> reset master;

mysql> set persist read_only=OFF;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.07 sec)

mysql>
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000024 |      196 |              |                  | 13c84508-5014-11eb-af41-000c2997dedd:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)


Step 5. Update old-master instance with new-master information: Perform the below steps in old-master and update it with new-master information and start slave.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.114.176',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> set global GTID_PURGED="13c84508-5014-11eb-af41-000c2997dedd:1";
Query OK, 0 rows affected (0.01 sec)

mysql>   show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 |      156 |              |                  | 13c84508-5014-11eb-af41-000c2997dedd:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.42 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.114.176
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay_bin01.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 0
              Relay_Log_Space: 156
              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: 2003
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.114.176' (113)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_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: 210303 15:36:57
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 13c84508-5014-11eb-af41-000c2997dedd:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified


If you notice I received the below error in Slave/Replica (Old-Master) which means Slave cannot communicate with Master : Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries: 1 message: Can’t connect to MySQL server on ‘192.168.114.176’ (113). I notice the Firewall on the new-master was blocking Port 3306, Execute the below commands in the new-master to allow connection in Port 3306.

[root@test-machine02 data]# firewall-cmd --permanent --add-service=mysql
success
[root@test-machine02 data]# firewall-cmd --reload
success


Step 6. Check the replication status: Check again replication status in Old-Master, Everything should look fine now.

mysql>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.176
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000024
          Read_Master_Log_Pos: 196
               Relay_Log_File: relay_bin01.000003
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 196
              Relay_Log_Space: 779
              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: 2
                  Master_UUID: 13c84508-5014-11eb-af41-000c2997dedd
             Master_Info_File: mysql.slave_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:
            Executed_Gtid_Set: 13c84508-5014-11eb-af41-000c2997dedd:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified


Step 7. Test Replication: Perform some transaction in New-Master instance and check in Slave if its replicated.


New Master

mysql> create table repl_test7 select * from repl_test6;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test1          |
| repl_test2          |
| repl_test3          |
| repl_test4          |
| repl_test5          |
| repl_test6          |
| repl_test7          |
+---------------------+
7 rows in set (0.00 sec)

mysql> insert into repl_test7 select * from repl_test2;
Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.03 sec)

mysql>



New Slave

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test1          |
| repl_test2          |
| repl_test3          |
| repl_test4          |
| repl_test5          |
| repl_test6          |
| repl_test7          |
+---------------------+
7 rows in set (0.00 sec)


mysql> select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.02 sec)


This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *