参考：https://blog.csdn.net/sinat_36757755/article/details/124049382
https://blog.51cto.com/feirenraoyuan/5720727
# 手动切换
1、主库锁库，禁止写入
mysql> set global super_read_only=ON;
mysql > set global read_only =1; 或者 set global read_only=ON;
mysql > flush tables with read lock;
#杀掉所有已经建立的连接
mysqladmin -uroot -p processlist -pabcd1234|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill -pabcd1234
2、从库确认主从Executed_Gtid_Set的最后事务一致：
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.102
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 600
Relay_Log_File: mariadb-relay-bin.000013
Relay_Log_Pos: 899
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 600
Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
#注意看Exce_Master_Log_Pos是否和主库同步，sencond_behind_master是否为0；
mysql > show processlist;
3、从库停掉复制进程并清空主从信息：
mysql> stop slave;
mysql> reset slave all; #清空所有relaylog（清除日志同步位置标志）并清空内存中的从库信息，并重新生成master.info
mysql> reset master; #清空所有binlog，这条命令就是原来的FLUSH MASTER
从库关闭只读并开启读写，转为新主库
mysql> set global read_only=off;
mysql> set global super_read_only=off;
4、原来主库执行新主库的复制链路，转为新备库，完成主从切换
mysql> unlock tables;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.207.132',MASTER_USER='root',MASTER_PORT=3306,MASTER_PASSWORD='abcd1234',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
操作过程：
从库：
Last login: Fri Dec 23 16:11:53 2022 from 10.168.20.66
[root@mysql2 ~]# mysql -h 192.168.207.132 -P 3306 -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show full processlist;
+----+-----------------+-----------------------+------+---------+--------+----------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+---------+--------+----------------------------------------------------------+-----------------------+
| 5 | system user | connecting host | NULL | Connect | 693066 | Waiting for source to send event | NULL |
| 6 | system user | | NULL | Query | 157437 | Replica has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Query | 157435 | Waiting for an event from Coordinator | NULL |
| 8 | event_scheduler | localhost | NULL | Daemon | 693066 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 693066 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 693066 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 693066 | Waiting for an event from Coordinator | NULL |
| 18 | root | 192.168.207.132:57300 | NULL | Query | 0 | init | show full processlist |
+----+-----------------+-----------------------+------+---------+--------+----------------------------------------------------------+-----------------------+
8 rows in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.207.131
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 373
Relay_Log_File: mysql2-relay-bin.000005
Relay_Log_Pos: 589
Relay_Master_Log_File: mysql-bin.000006
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: 373
Relay_Log_Space: 2701
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: 740dc65b-7ced-11ed-9e87-82b000a4e5ee
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
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
mysql> show REPLICA status\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.207.131
Source_User: rpl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000006
Read_Source_Log_Pos: 373
Relay_Log_File: mysql2-relay-bin.000005
Relay_Log_Pos: 589
Relay_Source_Log_File: mysql-bin.000006
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 373
Relay_Log_Space: 2701
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 740dc65b-7ced-11ed-9e87-82b000a4e5ee
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 2369
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select @@global.gtid_executed;
+------------------------------------------+
| @@global.gtid_executed |
+------------------------------------------+
| 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed);
ERROR 1054 (42S22): Unknown column 'master_gtid_executed' in 'field list'
mysql> SELECT GTID_SUBSET(740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9,740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':1-9,740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9)' at line 1
mysql> SELECT GTID_SUBSET('740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9','740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9');
+----------------------------------------------------------------------------------------------------+
| GTID_SUBSET('740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9','740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9') |
+----------------------------------------------------------------------------------------------------+
| 1 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show REPLICA status\G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> show full processlist;
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
| 8 | event_scheduler | localhost | NULL | Daemon | 693692 | Waiting on empty queue | NULL |
| 18 | root | 192.168.207.132:57300 | NULL | Query | 0 | init | show full processlist |
| 19 | root | 192.168.207.131:35096 | NULL | Binlog Dump GTID | 32 | Source has sent all binlog to replica; waiting for more updates | NULL |
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show REPLICA status\G;
Empty set (0.01 sec)
ERROR:
No query specified
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| j |
| jyc |
| jycdb |
| mysql |
| performance_schema |
| sys |
| t |
+--------------------+
8 rows in set (0.00 sec)
mysql> use j;
Database changed
mysql> create table t (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from j.t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> insert into t values(2);
Query OK, 1 row affected (0.01 sec)
mysql>
原主库操作：
Last login: Fri Dec 23 16:11:47 2022 from 10.168.20.66
[root@mysql1 ~]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show full processlist;
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 162474 | Waiting on empty queue | NULL |
| 9 | rpl | 192.168.207.132:59354 | NULL | Binlog Dump GTID | 162418 | Source has sent all binlog to replica; waiting for more updates | NULL |
| 12 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+-----------------+-----------------------+------+------------------+--------+-----------------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G;
Empty set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 373
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> set global super_read_only=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@mysql1 ~]# mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill
Enter password:
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
Enter password:
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
[root@mysql2 ~]# mysqladmin -uroot -p processlist -pabcd1234
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+-----------------+-----------------------+----+------------------+--------+-----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------------+----+------------------+--------+-----------------------------------------------------------------+------------------+
| 8 | event_scheduler | localhost | | Daemon | 694110 | Waiting on empty queue | |
| 19 | root | 192.168.207.131:35096 | | Binlog Dump GTID | 450 | Source has sent all binlog to replica; waiting for more updates | |
| 21 | root | localhost | | Query | 0 | init | show processlist |
+----+-----------------+-----------------------+----+------------------+--------+-----------------------------------------------------------------+------------------+
[root@mysql2 ~]# mysqladmin -uroot -p processlist -pabcd1234 |awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill -pabcd1234
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: kill failed on 0; error: 'Unknown thread id: 0'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: kill failed on 8; error: 'Unknown thread id: 8'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: kill failed on 22; error: 'Unknown thread id: 22'
[root@mysql1 ~]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@global.gtid_executed;
+------------------------------------------+
| @@global.gtid_executed |
+------------------------------------------+
| 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed);
ERROR 1054 (42S22): Unknown column 'master_gtid_executed' in 'field list'
mysql>
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.207.132',MASTER_USER='root',MASTER_PORT=3306,MASTER_PASSWORD='abcd1234',master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.207.132
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 157
Relay_Log_File: mysql1-relay-bin.000002
Relay_Log_Pos: 373
Relay_Master_Log_File: mysql-bin.000001
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: 157
Relay_Log_Space: 584
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: d0271cef-7782-11ed-828a-d67f35a9de29
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
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
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 373
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 740dc65b-7ced-11ed-9e87-82b000a4e5ee:1-9
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show full processlist;
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+-----------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 163089 | Waiting on empty queue | NULL |
| 19 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 20 | system user | connecting host | NULL | Connect | 25 | Waiting for source to send event | NULL |
| 21 | system user | | NULL | Query | 25 | Replica has read all relay log; waiting for more updates | NULL |
| 22 | system user | | NULL | Connect | 25 | Waiting for an event from Coordinator | NULL |
| 23 | system user | | NULL | Connect | 25 | Waiting for an event from Coordinator | NULL |
| 24 | system user | | NULL | Connect | 25 | Waiting for an event from Coordinator | NULL |
| 25 | system user | | NULL | Connect | 25 | Waiting for an event from Coordinator | NULL |
+----+-----------------+-----------------+------+---------+--------+----------------------------------------------------------+-----------------------+
8 rows in set (0.00 sec)
mysql> select * from j.t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from j.t;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql>
最后修改时间：2023-03-10 10:22:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
