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 name | IP | Role | Server ID | Installation Link |
| test-machine01 | 192.168.114.170 | Master | 1 | Click Here |
| test-machine012 | 192.168.114.176 | Slave | 3 | Click 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 'repluser@192.168.114.176: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 ‘repluser@192.168.114.176: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.
