526互联

主从mysql实例手动在线切换步骤

发布时间 2023-09-05 15:16:16作者: dbstack

一、环境：

mysql8.0.34 一主一从 开启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.34 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)

确保接收的事务，被应用完毕.
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.修改应用程序连接，或者启动中间件，使应用程序连接到新的主库上，然后进行程序基础流程测试验证