专栏首页mysql-dba主从mysql实例手动在线切换步骤
原创

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

环境:

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 故障分析 | MHA 切换的一个“坑”

    遇到这个报错内心是懵的,明明切换前检查集群状态、masterha_check_repl都是正常的。嗯……还是对 MHA 的原理了解不够深入。

    爱可生开源社区
  • 云上MySQL你应该知道的几点

    使用云上的MySQL时,会遇到很多人询问CDB的 为了更好的了解云上的MySQL,本文将介绍一些重要的知识点。

    苏欣
  • MHA 日常管理

        MHA是众多使用MySQL数据库企业高可用的不二选择,它简单易用,功能强大,实现了基于MySQL replication架构的自动主从故障转移。本文主要...

    Leshami
  • 中小型企业IDC机房数据库迁移上云的最佳实践

    本文主要介绍中小型互联网企业,从本地机房迁移数据库到腾讯云的实践方法。其中包含了详细数据库迁移的方法和步骤,并且增加了实践演练和验证。实践与验证部分内容以常见的...

    邵聪 SHAO CONG
  • MySQL5.5到5.7版本之间的迁移

    这两天在做MySQL方向上的版本升级和高可用改造,在这个过程当中,还是遇到了不少的问题。从资源问题到级连的复制关系,虽然没有肉眼可见的困难环节,但是小...

    AsiaYe
  • 小议MySQL主从复制、读写分离及高可用方案

    随着数据量的增大,读写并发的增加,系统可用性要求的提升,单机 MySQL 出现危机:

    DB之路
  • 大厂都在用的MySQL主从复制、读写分离及高可用方案

    随着数据量的增大,读写并发的增加,系统可用性要求的提升,单机 MySQL 出现危机:

    JavaEdge
  • 一文搞懂MySQL主从复制方案、读写分离及高可用

    随着数据量的增大,读写并发的增加,系统可用性要求的提升,单机 MySQL 出现危机:

    JavaEdge
  • 【玩转腾讯云】基础网络迁移VPC方案的“千层姿势”

    基础网络是腾讯云上所有用户的公共网络资源池(如下图右所示)。所有云服务器的内网 IP 地址都由腾讯云统一分配,无法自定义网段划分、IP 地址。

    elontian田凌翔
  • MySQL 高可用性—keepalived+mysql双主(有详细步骤和全部配置项解释)

    前言:生产环境中一台mysql主机存在单点故障,所以我们要确保mysql的高可用性,即两台MySQL服务器如果其中有一台MySQL服务器挂掉后,另外一台能立马接...

    庞小明
  • 基于MHA搭建MySQL Replication集群高可用架构

    MHA是Master High Availability的缩写,它是目前MySQL高可用方面的一个相对成熟的解决方案,其核心是使用perl语言编写的一组脚本,是...

    端碗吹水
  • MySQL半同步复制

      何为半同步复制模式呢?在此我们先了解异步复制模式,这是MySQL的默认复制选项。异步复制即是master数据库把binlog日志发送给slave数据库,然后...

    那一叶随风
  • 听说Mysql你很豪横?-------------搭建MySQL MHA实现数据库高可用( MySQL MHA概述、 搭建 MySQL MHA、 MySQL MHA 故障切换)

    MHA目前在MySQL高可用方面是一个相对成熟的解决方案 但是在搭建的过程中会经常报错,且MHA的构建综合了主从复制,所以MHA的安装要思路清晰才可

    不吃小白菜
  • 组复制升级 | 全方位认识 MySQL 8.0 Group Replication

    本节介绍如何对组复制进行升级的设置。升级组成员的基本步骤与升级独单实例的步骤相同,关于升级方式,具体选择就地升级(基于原来的数据文件直接使用mysql_upgr...

    老叶茶馆
  • 组复制常规操作-使用xtrabackup备份恢复或添加组成员 | 全方位认识MySQL8.0 Group Replication

    xtrabackup 8.0版本支持备份时不加全局读锁(不执行FLUSH TABLE WITH READ LOCK语句),这就避免了在组复制中启用多线程回放的组...

    老叶茶馆
  • MySQL 主从架构原理

    上图展示的是 MySQL 的主从切换流程。在 State-1 中,客户端的读写都直接访问节点 A,而节点 B 是 A 的备库,只是将 A 的更新都同步过来,到本...

    张申傲
  • Mysql 监控 performance_schema 拿得起,放不下(2)

    接上期说,在MYSQL 5.7 后performance_schema 以及后来的sys库的重要性越来越高,各种系统的性能以及系统资源的分配信息都会在这里体现。

    AustinDatabases
  • 基于MMM搭建MySQL Replication集群高可用架构

    MMM是Multi-Master Replication Manager for MySQL的缩写,它是MySQL提供的一个多主复制管理器,其核心是使用perl...

    端碗吹水
  • 运维系统数据库升级到MGR小结

    今天对运维系统的MySQL架构做了下升级,从单点实例升级到了MGR跨机房集群。当然目前也是一个迭代的方案,后续的架构升级还需要持续的补充,算是一个开始吧。

    jeanron100

扫码关注云+社区

领取腾讯云代金券