MySQL高可用方案之多级复制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

在大部分场景中我们都是用MySQL主从复制来实现数据库的冗余,这里是用多级复制来处理,多级复制可以快速简单的处理数据库的故障,数据库有A、B、C服务器,正常情况下A为主、B为A的从、C为B的从。

A-->B-->C

当A出现问题时,将B设为主,C为B的从,A正常后就为C的从

B-->C-->A

当B出问题后,C为主,A为C的从,B为A的从,如此反复可以快速解决问题


角色 IP 主机名 数据库版本
192.168.2.241 db1 5.6.29
192.168.2.242 db2 5.6.29
192.168.2.243 db3 5.6.29

注意在这样的场景下数据库的版本必须为一致,否则会因为版本之间不兼容导致出现问题


  1. 创建复制账户

  2. 配置数据库配置

  3. 备份主库,然后导入到备库

  4. 配置主从



1.创建复制账户

1
2
mysql> grant  repication slave  on  *.*  to  'repl' @ '192.168.2.%'  identified  by  'repl' ;
mysql>flush  privileges ;


2.开启数据库binlog,设置server-id和启用log_slave_updates

说明:log_slave_updates是将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中

如果没有开启log_slave_updates则在A-->B-->C场景中,C将无法从B中获取到数据

在MySQL配置文件/etc/my.cnf中的[mysqld]下添加如下语句

1
2
3
4
log-bin=mysqlbin
server- id =241       #这里每台服务器都必须不一致,最好是IP的末段
log_slave_updates=1
expire_logs_days=7

记得重启下数据库


3.备份主库,然后导入到备库中

锁表

1
2
3
4
5
6
7
8
mysql> flush tables  with  read  lock;
mysql> show master status;
+ ---------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |    409 |         |             |             |
+ ---------------+----------+--------------+------------------+-------------------+
1 row  in  set  (0.00 sec)


注意:这里不能退出mysql命令行会话,另外再开一个窗口将数据库导出,因为锁表的时候,只要退出会话锁表自动解除

1
[root@db1 ~] # mysqldump -uroot -p --all-database --add-drop-table >all_database.sql

将上面导出的all_database.sql导入到其他的db2、db3中

1
[root@db2 ~] # mysql -uroot -p <all_database.sql
1
[root@db3 ~] # mysql -uroot -p <all_database.sql

4.开启主从复制

在db2上:

1
2
3
4
5
6
7
8
mysql> change master  to  master_host= '192.168.2.241' ,master_user= 'repl' ,master_password= 'repl' ,master_log_file= 'binlog.000002' ,master_log_pos=409;
mysql> start slave;
mysql> show master status;
+ ---------------+----------+--------------+------------------+-------------------+
| File     | Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|
+ ---------------+----------+--------------+------------------+-------------------+
| binlog.000002| 647569 |       |         |          |
+ ---------------+----------+--------------+------------------+-------------------+


在db3上:

1
2
mysql> change master  to  master_host= '192.168.2.242' ,master_user= 'repl' ,master_password= 'repl' ,master_log_file= 'binlog.000002' ,master_log_pos=647569;
mysql> start slave;

然后分别在db2和db3上执行show slave status\G;查看是否有错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master  to  send event
                   Master_Host: 192.168.2.242
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: binlog.000002
           Read_Master_Log_Pos: 647569
                Relay_Log_File: db3-relay-bin.000002
                 Relay_Log_Pos: 280
         Relay_Master_Log_File: binlog.000002
              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: 647569
               Relay_Log_Space: 451
               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: 242
                   Master_UUID: 25a2315a-d9f0-11e5-9aa9-000c296e3855
              Master_Info_File: /var/lib/mysql/master.info
                     SQL_Delay: 0
           SQL_Remaining_Delay:  NULL
       Slave_SQL_Running_State: Slave has  read  all  relay log; waiting  for  the slave I/O thread  to  update  it
            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: 
                 Auto_Position: 0
1 row  in  set  (0.00 sec)

可以看到

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

为yes,说明复制正常


接着测试一下:

在数据库中插入数据,然后在db1、db2、db3上查询即可


如有问题可以show slave status\G;查看是否有错误

如果遇到类似1062的错误的话可以忽略,则可以直接

1
2
3
mysql> stop slave;
mysql>  SET  GLOBAL  SQL_SLAVE_SKIP_COUNTER = 1;
mysql> start slave;



在运行一段时间后,db1出现问题,导致无法恢复的故障,则只需要在db2上执行stop slave;

然后db1恢复后,从db3导出数据并记录点,然后change master到db3上


如果为了防止在从库意外写入,也可以在从数据库的配置文件中加入read_only = 1



本文转自 rong341233 51CTO博客,原文链接:http://blog.51cto.com/fengwan/1744304

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
256 3
Mysql高可用架构方案
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
208 1
MySQL主从复制原理和使用
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
87 5
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
62 1
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
139 0
Mysql中搭建主从复制原理和配置
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
754 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
615 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
mysql集群方案
mysql集群方案
59 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等