1.Mysql 版本

5.7.35

2.场景

主备正常切换，此场景主要是针对在主备同步复制正常的情况下进行的主备切换，例如：灾备演练，计划性的主备切换。

3.环境

主库：10.xx.220.40 3306
从库：10.xx.220.41 3306

4.切换步骤

4.1 切断应用对主库的流量

？

4.2 主库备库设置只读

set global read_only=ON;
set global super_read_only=ON;

设为只读后的效果

4.3 查看备库复制进程状态

show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.10.220.41Master_User: dt_syncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1316Relay_Log_File: relay.000002Relay_Log_Pos: 1529Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1316Relay_Log_Space: 1726Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 41Master_UUID: 35d26faf-38bb-11ed-9929-0cda411df25cMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 35d26faf-38bb-11ed-9929-0cda411df25c:1-4Executed_Gtid_Set: 35d26faf-38bb-11ed-9929-0cda411df25c:1-4,
3a077b03-38bb-11ed-9a96-0cda411d1419:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)

4.4 比对主备两边的GTID是否一致

select @@global.gtid_executed;

主库

从库

4.5 确认是否真正同完

主库

从库

4.6 从库停掉复制进程并清空主从信息

stop slave;
reset slave all;

4.7 从库关闭只读开启读写，转为新主库

set global read_only=off;
set global super_read_only=off;

4.8 主库设置执行新主库的复制链路，转为新备库，完成主从切换

mysql> CHANGE MASTER TO MASTER_HOST='10.xx.220.40',MASTER_USER='dt_sync',MASTER_PORT=3306,MASTER_PASSWORD='Xy12345678',master_auto_position=1; 
Query OK, 0 rows affected, 1 warning (0.10 sec)mysql> 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.10.220.40Master_User: dt_syncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1333Relay_Log_File: relay.000002Relay_Log_Pos: 414Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1333Relay_Log_Space: 611Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 40Master_UUID: 3a077b03-38bb-11ed-9a96-0cda411d1419Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 35d26faf-38bb-11ed-9929-0cda411df25c:1-4,
3a077b03-38bb-11ed-9a96-0cda411d1419:1-4Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)

4.9 应用流量切向新主库

5.参考

MySQL 主从切换步骤
https://blog.csdn.net/sinat_36757755/article/details/124049382
MySQL主从复制【基于GTID复制】
https://blog.csdn.net/JohnnyG2000/article/details/124701214