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再响应主节点，可想而知，复制的效率是极其低下的。

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

在这里插入图片描述

主从复制环境搭建

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

一、主从节点配置

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

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

在这里插入图片描述

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

-- 创建'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

在这里插入图片描述

2、将数据同步到从节点

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

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

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

在这里插入图片描述

3、从节点复制参数配置

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

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

在这里插入图片描述

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

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

--修改复制相关参数
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;命令进行查看复制进度、错误信息和复制状态等。如果有结果输出，那说明环境搭建成功了。类似下图

在这里插入图片描述

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

在这里插入图片描述

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

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

在这里插入图片描述

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

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

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

在这里插入图片描述

主从复制故障监控

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

当然会出现故障，通常可以通过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等中间件实现高可用、读写分离等需求。

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

我正在参与2024腾讯技术创作特训营第五期有奖征文，快来和我瓜分大奖！