MySQL主从复制原理与实践：从配置到故障监控

怼烎@ 2024-04-25 08:33 82阅读 0赞

#### 文章目录 ####

*  前言
 *  主从复制原理
 *   *  复制源
     *  主节点的工作
     *  从节点的工作
     *  复制流程的设计
 *  主从复制环境搭建
 *   *  一、主从节点配置
     *  二、从节点开启复制步骤
     *   *  1、备份主节点的数据
         *  2、将数据同步到从节点
         *  3、从节点复制参数配置
     *  三、验证复制环境
 *  主从复制故障监控
 *   *  监控主从复制状态
     *  监控主从复制延迟
 *  总结

## 前言 ##

上文[《MySQL数据被误删怎么办？》][MySQL]介绍了MySQL在故障或者误删数据后，可以通过备份+binlog的方式进行数据恢复。但是，当备份文件和binlog都丢失了呢？所以单节点是不可靠的，为了避免单节点故障带来的数据丢失以及MySQL服务的可用性，生产环境通常都是采用高可用或者集群模式。而在这背后则离不开主从复制技术，所以本文对主从复制的原理和操作展开介绍，从而全面了解这一技术。

## 主从复制原理 ##

### 复制源 ###

MySQL的主从复制主要是将主节点的数据同步到从节点，这个数据的来源就是binlog（之前的文章也有提到）。

binlog文件中的格式是这样的：

| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info    
    
    | mysql-bin.000004 | 3016 | Anonymous_Gtid |         1 |        3081 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                 |
    | mysql-bin.000004 | 3081 | Query          |         1 |        3160 | BEGIN                                                                                                                                                                                                                                                |
    | mysql-bin.000004 | 3160 | Query          |         1 |        3296 | use `test`; INSERT INTO account (id, user,balance) VALUES (30, 'CCV',5000)                                                                                                                                                                           |
    | mysql-bin.000004 | 3296 | Xid            |         1 |        3327 | COMMIT /* xid=1661 */

> 那么，MySQL是如何将主节点的binlog同步到从节点的？

### 主节点的工作 ###

通过binlog文件可以看到，每个事务提交后都会将相应的SQL语句清晰的记录在binlog中，所以在binlog新增日志的这个时机，将这些日志传输到从节点即可。

这个工作是由主节点中的「**binlog dump**」线程处理的。

### 从节点的工作 ###

在接收到主节点的日志后，剩下的工作就由从节点来完成了。

在主从复制这件事上，从节点主要由两个线程和一个日志文件来完成该工作。

两个线程分别是「**IO线程**」和「**SQL线程**」，「**IO线程**」用来**连接主节点**、\*\*接收日志（一些SQL语句）\*\*并且记录到「**relaylog**」，「**SQL线程**」是用来执行「**relaylog**」进行SQL执行，从而达到复制目的。

### 复制流程的设计 ###

> 直接执行SQL不行吗？为什么要两个线程+一个日志才能完成主从复制？

试想一下，如果直接执行SQL，在这过程中出现故障怎么办？执行时间过长，阻塞积压导致复制延时怎么办？所以，MySQL这样设计有两个好处：

1.  使用两个线程可以在出现错误时起到故障隔离作用。例如，如果SQL线程遇到错误时，可以停止并等待IO线程发送下一个日志。而如果IO线程遇到错误，它可以从最后一个已知的、成功的位置重新开始拉取日志。
2.  IO 操作和 SQL 操作可以并行进行，提高复制的效率。

还有一个重要的原因就是：为了满足高一致性的需求，MySQL还提供了半同步和组复制模式，简单来讲就是主节点需要确认从节点收到数据之后才算同步成功，也就是日志落地到「**relaylog**」中。如果直接执行SQL再响应主节点，可想而知，复制的效率是极其低下的。

这里贴一张主从复制的工作流程图方便理解

![在这里插入图片描述][1f97c80b926d442d8d824a781298fb8e.png]

## 主从复制环境搭建 ##

接下来演示一下如何搭建一个主从复制环境。

### 一、主从节点配置 ###

在搭建主从复制环境前需要确保主从实例可以通信，此外，还需要注意两个事项：

1.  需要确保主从实例的serverid不一致，如果一致需要在配置文件`/etc/my.cnf`进行修改。
2.  主节点需要开启binlog，也是在`/etc/my.cnf`中配置。

![在这里插入图片描述][5f4b6fb644f34eb3a43b299014862812.png]

除此之外，主节点需要创建两个角色供从节点使用，创建角色命令如下

-- 创建'repl'用户并授权从节点复制权限
    CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl123456';
    GRANT replication slave on *.* to 'repl'@'%';
    
    -- 创建'replroot'用户并授权从节点所有权限，使得从节点可以远程备份数据
    CREATE USER 'replroot'@'%' IDENTIFIED WITH mysql_native_password BY 'root123456';
    GRANT ALL on *.* to 'replroot'@'%';

### 二、从节点开启复制步骤 ###

配置完成后，从节点就可以开启复制了，复制步骤如下：

#### 1、备份主节点的数据 ####

远程将主节点的数据备份下来。命令如下：

mysqldump -ureplroot -proot123456 -h 192.168.0.39 -P 3306 -A --master-data=2 --single-transaction -R -E --triggers >/root/full.sql

![在这里插入图片描述][b755edd9da654d07a6bd3cc769d71d7b.png]

#### 2、将数据同步到从节点 ####

在从节点上执行恢复操作，使得从节点的状态最接近主节点的状态，这样的话从节点就可以基于最新的状态开始复制了。恢复命令如下：

set sql_log_bin=0;
    source /root/full.sql 
    set sql_log_bin=1;

命令执行后就可以在从节点看到主节点的数据了

![在这里插入图片描述][c5516c95711b4371b27899ec35ebef19.png]

#### 3、从节点复制参数配置 ####

数据同步过来后，可以执行以下命令查看从节点开始在哪里进行复制。

grep  "\--\ CHANGE MASTER" /root/full.sql

![在这里插入图片描述][256a6c84437147cebd37a5bb70fa959d.png]

是否感觉这些操作很熟悉？没错，截止至此，以上的操作都是上文介绍的备份恢复的操作。

最后在从节点执行以下命令配置复制参数，就开启主从复制了。

--修改复制相关参数
    CHANGE MASTER TO MASTER_HOST='主节点的ip', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154,MASTER_CONNECT_RETRY=60;
    
    --启动复制进程
    start slave;

其中`MASTER_LOG_FILE`和`MASTER_LOG_POS`就是从节点应该开始复制主节点的二进制日志和位置。

至此，主从复制就搭建好了，妈妈再也不用担心节点故障或数据误删了。

### 三、验证复制环境 ###

> 怎么验证复制环境搭建好了呢？

我们可以在从节点上执行`show slave status \G;`命令进行查看复制进度、错误信息和复制状态等。如果有结果输出，那说明环境搭建成功了。类似下图  
![在这里插入图片描述][415b568e13a34bf09b7cb25178e7e252.png]

或者在主节点执行show slave hosts;命令查看从节点信息，如果有输出也说明搭建成功。类似下图

![在这里插入图片描述][f052e25bd59844239093a6901bfb8c34.png]

环境搭建好就可以看一下复制效果了。

在开启复制之前，从节点的数据状态是这样的：

![在这里插入图片描述][8d15ee9f449a4cbeb12b516ec38b4ab0.png]

此时，我在主节点执行两条DML命令，然后在从节点看数据是否同步过来。

INSERT INTO `account` VALUES(80,'LKJ',5000.00)
    UPDATE `account`  SET balance=1000 WHERE id=1;

通过下图可以看到从节点的数据已经同步过来

![在这里插入图片描述][9732ca9c6dc446f78a0ef6e9ca22fcb5.png]

## 主从复制故障监控 ##

> 主从复制会不会出现故障？出现故障怎么办？

当然会出现故障，通常可以通过`show slave status \G;`监控复制进度、错误信息和复制状态，如果出现问题对症下药。

### 监控主从复制状态 ###

重点关注`Slave_IO_Running` 和 `Slave_SQL_Running`状态。这两个状态都应该显示为 “Yes”，表示复制正常运行。

`Last_Errno`和`Last_Error` 字段表示错误码或错误信息，可以说明复制过程中出现了问题。比如下面这个错误：

Last_Error: Error 'ER_BAD_SLAVE: Slave I/O thread killed while reading event from master' has occurred in query: 'SELECT * FROM my_table'

这个错误意味着从节点在尝试读取事件时遇到了问题，这个时候就要看是网络问题还是权限问题。

### 监控主从复制延迟 ###

通过比较主从节点上的时间戳、数据版本等信息，可以检测到复制延迟。

比如，`Seconds_Behind_Master`字段表示从节点落后主节点的秒数，如果这个值持续较高，可能存在复制延迟问题。

这个时候就要排查是不是 主从节点有网络延时 \\color\{blue\}\{主从节点有网络延时\} 主从节点有网络延时，需不需要增加网络带宽？ 是不是主库并发事务高 \\color\{blue\}\{是不是主库并发事务高\} 是不是主库并发事务高，需不需要修改成复制模式组复制？ 还是从库刷盘慢 \\color\{blue\}\{还是从库刷盘慢\} 还是从库刷盘慢，需不需要增加从节点的复制线程数？具体情况具体分析。

当然，还有很多故障情况，可以使用一些开源的监控工具，如Zabbix、Prometheus、pt-slave-delay等实时监控主从复制的状态和性能指标。这些工具通常都会提供告警功能，当出现异常时可以及时发现并处理。

## 总结 ##

不管是备份恢复还是主从复制，其目的都是为了提高MySQL的可靠性、可用性等。两者本质上就是对数据的copy+传输，前者是为了故障恢复，后者更多是为了高可用、故障转移、读写分离等需求。

在复制基础上可以使用MHA、MMM、ProxySQL、MyCAT等中间件实现高可用、读写分离等需求。

当然，现在大多数公司更倾向于现成的云上高可用、集群数据库。即便这样，还是要知其然，知其所以然，以备不时之需。

[MySQL]: https://wangbinguang.blog.csdn.net/article/details/135556178
[1f97c80b926d442d8d824a781298fb8e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/09882f03479b4697a8b316f2bf6333c3.png
[5f4b6fb644f34eb3a43b299014862812.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3b519dc210b94c189a2451fee717b972.png
[b755edd9da654d07a6bd3cc769d71d7b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3a4c621454ef4588bccc58afcf5c4064.png
[c5516c95711b4371b27899ec35ebef19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3b4fe1d165e14ae98f58a30fde617a1d.png
[256a6c84437147cebd37a5bb70fa959d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/0cd6ba9f481343949b548bc1662e0dd0.png
[415b568e13a34bf09b7cb25178e7e252.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/1101d98def794c24983fe86938b7ee88.png
[f052e25bd59844239093a6901bfb8c34.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/95034c9ea81942b3b08588ab2137ad78.png
[8d15ee9f449a4cbeb12b516ec38b4ab0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/07cc512ec05d43ca948b58e7a3240893.png
[9732ca9c6dc446f78a0ef6e9ca22fcb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/d8cbedcefbba48279e93327a1a858ee9.png