三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

MySQL主从复制实战：从零搭建到生产环境高可用架构

发布时间：2026/8/13 3:37:27 三亩地 编程学习日记
MySQL主从复制实战：从零搭建到生产环境高可用架构

1. 项目概述：为什么我们需要MySQL主从复制？

在任何一个处理线上流量的系统里，数据库都是最核心也最脆弱的环节。想象一下，你的电商网站正在做秒杀活动，所有用户都在疯狂点击“立即购买”，请求像潮水一样涌向数据库。如果只有一个数据库实例在苦苦支撑，一旦它因为CPU打满、磁盘IO瓶颈或者一个慢查询而“趴下”，整个网站就会瞬间崩溃，用户看到的将是冰冷的“502 Bad Gateway”。这不仅仅是技术故障，更是直接的业务损失和口碑崩塌。

MySQL主从复制，就是为了解决这个“单点故障”和“性能瓶颈”而生的经典架构。它的核心思想很简单：让一个数据库（主库）负责处理所有的写操作（增、删、改），然后把这些写操作产生的数据变更，实时地、异步地同步到另一个或多个数据库（从库）上。从库则专门负责处理读操作（查）。这样一来，读的压力被分散到了多个从库上，主库可以更专注于写，整个数据库集群的吞吐能力得到了质的提升。更重要的是，当主库发生故障时，我们可以快速地将一个从库提升为新的主库，实现业务的高可用，将停机时间从小时级降到分钟甚至秒级。

我经历过不止一次因为数据库单点问题导致的深夜紧急上线。自从稳当地部署了主从复制架构后，晚上睡觉都踏实了不少。它不是什么高深莫测的新技术，但却是构建可靠后端服务的基石。接下来，我就从一个老运维的角度，带你从零开始，手把手搭建一套MySQL主从复制环境，并分享在实际生产中使用和维护它的核心要点与避坑指南。

2. 环境准备与规划：兵马未动，粮草先行

在动手敲命令之前，合理的规划能避免后期大量的调整和折腾。主从复制对系统环境有一些基本要求，我们需要提前准备好。

2.1 服务器与网络规划

首先，你需要至少两台服务器。在生产环境中，强烈建议主库和从库部署在不同的物理机或云主机上，以实现真正的故障隔离。如果它们在同一台机器的不同Docker容器里，那机器宕机时两者会一起挂掉，失去了高可用的意义。

服务器配置：主库的配置通常需要更高，因为它要处理所有写请求和Binlog日志的生成。从库的配置可以视读压力而定，如果读请求非常重，从库的配置甚至可能需要高于主库。一个常见的起步配置是：2核4GB内存，SSD磁盘。确保磁盘有足够的空间存放Binlog日志和数据库文件。

网络要求：主从服务器之间的网络必须稳定且延迟要低。跨机房、跨地域的主从同步会因网络延迟导致数据不一致的风险急剧增加。内网互通是必须的，同时要确保防火墙规则开放了MySQL服务的端口（默认3306）。你可以用ping和telnet命令测试双向的网络连通性。

注意：很多云服务商的安全组策略是默认禁止所有端口的，务必检查并放行3306端口，以及主从服务器间用于复制的SSH或特定端口（如果使用）。

2.2 MySQL版本选择与安装

版本一致性：理想情况下，主库和从库的MySQL大版本应该保持一致。比如主库是MySQL 8.0.33，从库也最好是8.0.x系列。虽然5.7到8.0的主从复制在很多时候也能工作，但可能会遇到一些数据类型或SQL_MODE不兼容的坑。对于新项目，直接选择MySQL 8.0是更明智的，它在性能、安全性和功能上都有显著提升。

安装方式：我个人的习惯是使用官方仓库安装，这样便于后续的版本管理和安全更新。以CentOS 8为例，安装MySQL 8.0社区版的步骤大致如下：

  1. 添加MySQL官方Yum仓库。
  2. 安装MySQL服务器社区版：sudo yum install mysql-community-server。
  3. 启动MySQL服务并设置开机自启：sudo systemctl start mysqld和sudo systemctl enable mysqld。

安装完成后，MySQL会为root用户生成一个临时密码，通常记录在日志文件/var/log/mysqld.log中。使用sudo grep ‘temporary password’ /var/log/mysqld.log可以找到它。用这个密码登录后，必须立即修改密码。

# 登录MySQL mysql -uroot -p # 输入临时密码 # 修改root密码，请将‘YourNewStrongPassword!123’替换成你自己的强密码 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘YourNewStrongPassword!123’;

在两台服务器上重复以上步骤，完成MySQL的基础安装。确保服务都能正常启动和登录。

3. 主库（Master）配置详解

主库是数据变更的源头，它的核心任务是记录下所有修改数据的操作。这是通过二进制日志（Binary Log，简称Binlog）来实现的。我们需要对主库进行一些关键配置。

3.1 核心配置文件修改

找到MySQL的配置文件my.cnf，通常位于/etc/my.cnf或/etc/mysql/my.cnf.d/目录下。我们需要在主库的配置文件中添加或修改以下参数：

[mysqld] # 服务器唯一ID，这是主从复制的标识，每台必须不同 server-id = 1 # 启用二进制日志，这是主从复制的基石 log-bin = mysql-bin # 设置二进制日志格式，推荐使用ROW模式，它基于数据行变化，最为安全可靠 binlog_format = ROW # 指定需要复制的数据库，如果不指定则默认复制所有库 # binlog-do-db = your_database_name # 指定不需要复制的数据库（与上一个参数二选一） # binlog-ignore-db = mysql # 设置二进制日志过期时间，避免磁盘被占满（单位：天） expire_logs_days = 7 # 每个二进制日志文件的最大大小（单位：字节） max_binlog_size = 100M

参数解读与避坑：

  • server-id：这是全局唯一的标识符。如果主从的server-id设置相同，复制将会失败。通常主库设为1，从库依次设为2、3、4...
  • binlog_format：这是最重要的参数之一。有三种模式：STATEMENT（基于SQL语句）、ROW（基于数据行）、MIXED（混合模式）。STATEMENT模式日志量小，但对于不确定性的函数（如NOW()，RAND()）可能导致主从数据不一致。ROW模式记录每一行数据的变化，是最安全、最可靠的，也是MySQL 8.0的默认推荐。虽然日志体积会大一些，但在数据一致性面前，这点开销是值得的。
  • expire_logs_days：务必设置！我曾经遇到过开发机磁盘被Binlog塞满导致数据库挂掉的情况。根据你的数据变更频率和磁盘空间来设定，生产环境通常保留7-14天。

修改完配置后，重启MySQL服务使配置生效：sudo systemctl restart mysqld。

3.2 创建复制专用账户

为了让从库能够连接主库并拉取日志，我们需要在主库上创建一个专门用于复制的用户。这个用户的权限不需要很大，只需要有REPLICATION SLAVE权限即可。

-- 登录主库MySQL mysql -uroot -p -- 创建复制用户，将‘repl_user’和‘YourReplPassword’替换为你自己的用户名和强密码 CREATE USER ‘repl_user’@‘%’ IDENTIFIED BY ‘YourReplPassword’; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘%’; -- 刷新权限 FLUSH PRIVILEGES;

安全提示：在生产环境中，@‘%’表示允许从任何主机连接，这存在安全风险。最佳实践是将其限制为从库的具体IP地址，例如‘repl_user’@‘192.168.1.100’。

3.3 获取主库状态信息

在配置从库之前，我们需要知道主库当前二进制日志的位置。这个位置信息（由File和Position组成）相当于一个“坐标”，从库需要从这个坐标开始同步数据。

-- 在主库执行 FLUSH TABLES WITH READ LOCK; -- 这条命令会锁定所有表为只读，确保在备份瞬间没有新的数据写入，保证一致性。 -- 注意：在繁忙的生产环境，这个操作要谨慎，最好在业务低峰期进行。 SHOW MASTER STATUS;

执行SHOW MASTER STATUS;后，你会看到类似下面的输出：

+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 785 | | | | +------------------+----------+--------------+------------------+-------------------+

请务必记录下File(mysql-bin.000003) 和Position(785) 这两个值，配置从库时会用到。

记录完毕后，不要立即退出当前MySQL会话。我们需要在这个会话中保持全局读锁，然后新开一个终端窗口，使用mysqldump工具对现有数据库进行全量备份。备份完成后，再回到这个窗口解锁。

# 在新的终端窗口，备份数据库（假设数据库名为app_db） mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events app_db > master_backup.sql

mysqldump关键参数解释：

  • --master-data=2：这个参数会在备份文件中以注释的形式记录下执行SHOW MASTER STATUS时得到的File和Position信息。这对于从库初始化极其方便。
  • --single-transaction：对于InnoDB存储引擎，这个参数可以确保在备份过程中得到一个一致性的数据快照，而不需要像之前那样用FLUSH TABLES WITH READ LOCK锁住所有表。它通过开启一个独立的事务来实现。这是推荐的方式，对线上业务影响更小。我们前面执行锁表命令是为了演示最传统的流程，实际生产中，如果所有表都是InnoDB，直接使用--single-transaction参数备份即可，无需手动锁表。
  • --routines --triggers --events：同时备份存储过程、触发器和事件调度器。

备份完成后，回到第一个MySQL会话窗口，解锁表：

UNLOCK TABLES;

现在，将备份文件master_backup.sql拷贝到从库服务器上，准备初始化从库数据。

4. 从库（Slave）配置与初始化

从库的角色是订阅者，它需要知道主库在哪里，并从哪个位置开始同步数据。

4.1 从库基础配置

同样，编辑从库的my.cnf配置文件，添加以下参数：

[mysqld] # 服务器唯一ID，必须与主库不同 server-id = 2 # 可选：启用中继日志，从库从主库拉取的Binlog会先存到这里 relay-log = mysql-relay-bin # 可选：防止从库写操作，确保它只作为只读副本（除非你需要在从库上进行特定报告查询） read_only = 1

设置read_only = 1可以防止应用意外地在从库上执行写操作，导致主从数据不一致。但请注意，具有SUPER权限的用户（如root）依然可以写。重启从库MySQL服务。

4.2 恢复主库备份数据

将之前从主库备份的master_backup.sql文件在从库上恢复，这相当于给从库一个与主库在某个时间点完全一致的数据基础。

# 在从库服务器上执行 mysql -uroot -p < master_backup.sql

这个过程可能会比较长，取决于数据库的大小。恢复完成后，从库就拥有了和主库在备份时刻完全一致的数据。

4.3 配置复制链路

这是最关键的一步：告诉从库它的主库是谁，以及从哪里开始同步。

登录从库的MySQL：

mysql -uroot -p

执行以下命令，配置主库连接信息：

CHANGE MASTER TO MASTER_HOST=‘主库的IP地址’, MASTER_USER=‘repl_user’, MASTER_PASSWORD=‘YourReplPassword’, MASTER_LOG_FILE=‘mysql-bin.000003’, -- 替换为之前记录的File MASTER_LOG_POS=785; -- 替换为之前记录的Position

如果你在mysqldump时使用了--master-data=2，并且备份文件里记录了正确的位置，你甚至可以直接从备份文件里获取这些信息，而无需手动记录：

# 在从库服务器上，查看备份文件头部的注释 head -n 50 master_backup.sql | grep “CHANGE MASTER TO”

你会看到一行被注释掉的CHANGE MASTER TO命令，里面的MASTER_LOG_FILE和MASTER_LOG_POS就是你需要的信息。

配置完成后，启动从库的复制进程：

START SLAVE; -- 在MySQL 8.0.22及以后，推荐使用 START REPLICA;

4.4 检查复制状态

启动复制后，我们需要检查从库的复制进程是否正常运行。

SHOW SLAVE STATUS\G; -- 使用\G让结果以垂直格式显示，更易读

在输出的众多信息中，重点关注以下两个字段：

  • Slave_IO_Running: 显示为Yes，表示从库的IO线程（负责从主库拉取Binlog）运行正常。
  • Slave_SQL_Running: 显示为Yes，表示从库的SQL线程（负责执行拉取到的Binlog中的事件）运行正常。

如果这两个字段都是Yes，那么恭喜你，主从复制链路已经成功建立！从库现在已经开始实时同步主库的数据变更了。

你还可以查看Seconds_Behind_Master字段，它表示从库落后于主库的秒数。在初始同步或有大事务时，这个值可能会比较大。当复制正常进行时，这个值应该稳定在一个很小的数字（如0或1），表示近乎实时同步。

5. 主从复制的进阶使用与生产实践

搭建成功只是第一步，要让主从复制在生产环境中稳定、高效地运行，还需要了解一些进阶知识和实践技巧。

5.1 监控与告警：让问题无所遁形

不能等到业务报障了才发现复制中断。我们必须建立有效的监控。

核心监控指标：

  1. 复制状态：定期（如每分钟）检查SHOW SLAVE STATUS中的Slave_IO_Running和Slave_SQL_Running。任何一个不为Yes都需要立即告警。
  2. 复制延迟：监控Seconds_Behind_Master。可以根据业务容忍度设置阈值，比如延迟超过30秒触发警告，超过5分钟触发严重告警。
  3. 错误日志：MySQL的错误日志（/var/log/mysqld.log）是排查问题的宝库。需要监控其中是否有复制相关的错误信息。

你可以编写一个简单的Shell脚本，定期检查这些指标并通过邮件、钉钉、企业微信等渠道发送告警。也可以使用成熟的监控系统，如Prometheus + Grafana。社区有现成的mysqld_exporter可以采集MySQL的各类指标，包括主从复制状态，然后在Grafana中配置漂亮的仪表盘和告警规则。

5.2 常见故障排查与修复

即使配置正确，复制也可能因为各种原因中断。以下是一些常见场景及处理思路：

场景一：主键冲突或数据不存在

  • 错误信息：在SHOW SLAVE STATUS\G的Last_SQL_Error字段中看到Duplicate entry ‘XXX’ for key ‘PRIMARY’或Could not execute Update_rows event on table xxx; Can’t find record in ‘xxx’。
  • 原因：这可能是因为有人在从库上手动修改了数据，或者之前复制出错后跳过错误导致主从不一致。
  • 处理：
    • 临时恢复：如果确定从库数据可以丢弃，或者冲突数据不重要，可以跳过这个错误事件。先停止复制STOP SLAVE;，然后让SQL线程跳过1个事件SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;，再启动复制START SLAVE;。这是一种“救火”措施，会加剧数据不一致，需谨慎使用。
    • 根本解决：重建从库。当主从不一致严重时，最可靠的办法是锁住主库（或在低峰期），重新做一次全量备份并恢复到从库，然后重新配置复制点。可以使用Percona XtraBackup这类物理备份工具，比mysqldump更快，对主库影响更小。

场景二：网络中断导致IO线程错误

  • 错误信息：Slave_IO_Running: Connecting或Last_IO_Error: error reconnecting to master…。
  • 原因：主从库之间网络不通，或者主库重启后Binlog文件被清理（expire_logs_days设置过短）。
  • 处理：
    1. 检查网络：ping主库IP，telnet主库3306端口。
    2. 检查主库复制用户权限是否正常。
    3. 如果是因为主库的Binlog文件被删除，从库请求的MASTER_LOG_FILE已经不存在，那就只能通过重建从库来恢复了。这凸显了合理设置expire_logs_days和监控磁盘空间的重要性。

场景三：大事务导致延迟激增

  • 现象：Seconds_Behind_Master突然变得很大，并且从库服务器负载（CPU、IO）很高。
  • 原因：主库执行了一个需要修改大量数据的事务（例如，不带条件的全表更新、删除百万级数据）。这个事务在Binlog里是一个巨大的事件，从库的SQL线程需要很长时间才能执行完。
  • 处理：
    • 优化应用：避免在业务高峰期执行大批量数据操作。这类操作应拆分成小批次进行。
    • 监控与告警：对Seconds_Behind_Master设置告警，及时发现延迟问题。
    • 硬件升级：如果从库的硬件（特别是磁盘IO）明显弱于主库，考虑升级从库配置。

5.3 读写分离的应用集成

搭建主从不是为了摆设，最终目的是为了让应用用起来。这就需要我们在应用程序中实现读写分离：将写请求（INSERT, UPDATE, DELETE）发给主库，将读请求（SELECT）发给从库。

实现方式主要有两种：

  1. 应用层实现：在业务代码中，根据SQL类型动态选择数据源。很多现代框架（如Spring Boot）都提供了简单的多数据源配置。你需要定义两个数据源（DataSource），一个指向主库，一个指向从库（或多个从库，可配合负载均衡）。然后通过AOP（面向切面编程）或注解，在Service层方法上标记是读操作还是写操作，从而路由到不同的数据源。

    • 优点：灵活，可控性强。
    • 缺点：侵入业务代码，增加了代码复杂度。

  2. 中间件实现：使用独立的数据库中间件代理。应用连接中间件，中间件根据SQL语句自动进行读写分离和负载均衡。常见的中间件有：

    • MySQL Router (官方)：轻量级，配置简单。
    • ProxySQL：功能非常强大，支持查询路由、缓存、故障转移、负载均衡等，是生产环境的热门选择。
    • MyCat/ShardingSphere-Proxy：更偏向于分库分表，但也具备读写分离功能。
    • 优点：对应用透明，无需修改代码。功能集中，便于管理和监控。
    • 缺点：引入了新的组件，增加了架构复杂度，需要保证中间件本身的高可用。

读写分离的核心挑战——复制延迟：这是架构师必须面对的问题。当应用刚在主库写入一条数据，紧接着一个读请求被路由到从库，此时从库可能还没来得及同步这条新数据，导致用户“看不到”刚才的修改。对于一致性要求不高的场景（如查看新闻列表、商品详情），可以接受。但对于“用户发表评论后立刻要显示”这类场景，就需要采用“写后读主”的策略，即让这个用户的后续读请求在一定时间内强制走主库。这通常需要在应用层或中间件层通过会话粘滞等机制来实现。

6. 高可用架构演进：从主从到主备自动切换

基础的主从复制解决了读扩展和备份问题，但还没有实现完全的自动化高可用。当主库宕机时，我们仍然需要人工介入，将一个从库提升（Promote）为新的主库，并修改应用的配置。这个过程可能耗时数分钟，对于核心业务是不可接受的。

因此，我们需要引入高可用（HA）解决方案，实现故障的自动检测与切换。这里介绍两个主流方向：

6.1 基于Keepalived + VIP的简单方案

这个方案的核心是虚拟IP（VIP）。Keepalived会在主库和从库上运行，它们通过心跳检测彼此的健康状态。VIP最初绑定在主库上。应用始终连接这个VIP。

  • 正常情况：主库健康，VIP在主库，所有请求到达主库。
  • 主库故障：Keepalived检测到主库宕机，会自动将VIP漂移到备选的从库上。同时，该从库需要被提升为新的主库（这个过程通常需要配合自定义脚本完成，比如执行STOP SLAVE;RESET SLAVE ALL;等命令）。
  • 优点：架构简单，切换速度快（秒级）。
  • 缺点：脑裂（Split-brain）风险。如果心跳网络出现问题，两个节点可能都认为对方挂了，都去抢占VIP，导致数据写入两个“主库”，造成数据混乱。需要精心配置心跳检测和仲裁机制来避免。

6.2 使用专业的数据库高可用套件

对于生产环境，更推荐使用经过充分测试的成熟方案。

  • MHA (Master High Availability)：一款经典的、用Perl编写的MySQL高可用工具。它由管理节点（Manager）和多个数据节点（Node）组成。Manager会定期探测所有Node，当主库故障时，它能自动将数据最新的从库提升为新主，并让其他从库指向新主。它还能在故障切换前后提供虚拟IP切换、发送告警等功能。MHA在中小规模场景中应用广泛。
  • Orchestrator：一款用Go编写的、Raft协议管理的高可用管理工具。它提供了Web UI，可以可视化地管理复制拓扑，并支持自动或手动的故障恢复。功能比MHA更强大和现代。
  • Galera Cluster / Group Replication：这是“共享一切”的同步多主集群方案。所有节点都能读写，数据通过组通信协议同步，保证强一致性。它提供了真正意义上的多主高可用，但架构复杂，对网络要求极高，性能开销也较大。适用于对写高可用有极端要求的场景。

选择建议：对于大多数互联网应用，基于主从复制，搭配MHA或Orchestrator来实现自动故障切换，是一个在可靠性、复杂度和成本之间取得很好平衡的方案。它保留了主从架构的清晰性，又通过自动化工具弥补了其在故障切换上的不足。

主从复制是MySQL世界里历久弥坚的基础设施。从简单的搭建到深入的生产实践，再到高可用架构的演进，每一步都凝结着对数据可靠性、服务可用性的不懈追求。我个人的体会是，越是基础的技术，越需要扎实的理解和细致的运维。很多看似复杂的故障，根源往往是一些基础的配置疏忽或理解偏差。希望这篇从搭建到实战的详细梳理，能帮你建立起稳固的数据库架构基石，让你的系统在流量洪峰和硬件故障面前，依然从容不迫。

编程学习 学习日记 实战经验
← 返回列表