环境:
mysql8.0.18 一主一从 开启GTID
主从实例切换的场景有:
数据库版本的升级
主机操作系统出现故障,需要停机修复(切换后进行修复)
主库性能降低(如磁盘不及备库)
切换步骤:
在主库开启sysbench压测:
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.1.1.201 --mysql-port=3320 --mysql-user=root --mysql-password='xxx@2021' --mysql-db=ww_test --tables=10 --table_size=100000 --mysql_storage_engine=Innodb --threads=2 --time=3000 --report-interval=10 --rand-type=uniform run
1.设置主库为只读模式,防止切换时数据写入
SET GLOBAL super_read_only=1;
SET GLOBAL read_only=1;
mysql> SET GLOBAL super_read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 4254 | Waiting on empty queue | NULL |
| 12 | repl | 10.1.1.201:46366 | NULL | Binlog Dump GTID | 3079 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 13 | root | localhost | opensips | Query | 0 | starting | show processlist |
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
2.查看主库连接,杀掉相关链接(最好把业务停掉)
如果有中间件,可以把中间件停掉防止业务通过中间件连接数据库
这里也可以通过删除连接用户,或者修改连接用户密码,来防止新的连接进来(比较暴力)
mysql> show processlist;
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 4254 | Waiting on empty queue | NULL |
| 12 | repl | 10.1.1.201:46366 | NULL | Binlog Dump GTID | 3079 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 13 | root | localhost | opensips | Query | 0 | starting | show processlist |
+----+-----------------+------------------+----------+------------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql>
mysql> kill 13;
ERROR 1317 (70100): Query execution was interrupted
3.在源主库加锁 FLUSH TABLES WITH READ LOCK
通过上面步骤就可以保证主库不能进行任何写入操作,只能进行查询了
4.备库查看 SHOW SLAVE STATUS,确保备库数据都同步完成
[root@ck1 home]# /usr/local/mysql/bin/mysql -S /tmp/mysql3321.sock -uroot -pGuijidba@2021
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.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.201
Master_User: repl
Master_Port: 3320
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1053271418
Relay_Log_File: ck1-relay-bin.000030
Relay_Log_Pos: 1053271632
Relay_Master_Log_File: mysql-bin.000010
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: 1053271418
Relay_Log_Space: 1053271925
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: 2223306
Master_UUID: 6d19e271-d7ee-11eb-8b74-56c8a95977d1
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: 6d19e271-d7ee-11eb-8b74-56c8a95977d1:1-80422
Executed_Gtid_Set: 6d19e271-d7ee-11eb-8b74-56c8a95977d1:1-80422
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 (0.00 sec)
ERROR:
No query specified
确保接收的事务,被应用完毕
5.停止复制STOP SLAVE,RESET SLAVE ALL,设置SET GLOBAL read_only=0,SET GLOBAL super_read_only=0
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.23 sec)
mysql> SET GLOBAL super_read_only=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL read_only=0;
Query OK, 0 rows affected (0.00 sec)
6.在原来主库上执行change master连接新的主库上
mysql> change master to master_host='10.1.1.201',MASTER_PORT=3321,master_user='repl',master_password='guiji_repl',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
7.启动新的备库START SLAVE
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 event
Master_Host: 10.1.1.201
Master_User: repl
Master_Port: 3321
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 1052808372
Relay_Log_File: ck1-relay-bin.000002
Relay_Log_Pos: 416
Relay_Master_Log_File: mysql-bin.000011
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: 1052808372
Relay_Log_Space: 622
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: 2223321
Master_UUID: d67c7c8f-d7ee-11eb-bff0-56c8a95977d1
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: 6d19e271-d7ee-11eb-8b74-56c8a95977d1:1-80422
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 (0.00 sec)
8.原来主库释放锁 unlock tables
9.检查数据同步以及校验数据一致性
插入数据进行验证
10.修改应用程序连接,或者启动中间件,使应用程序连接到新的主库上,然后进行程序基础流程测试验证
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。