为什么个人站长也需要 MySQL 主从复制

很多个人站长觉得主从复制是大公司才需要的高端技术，自己的小站数据量不大，单机跑得好好的，没必要折腾。这个想法在网站刚起步时没有错，但随着数据积累，你迟早会遇到两个痛点：第一是备份恢复太慢，数据库文件动辄几个 G，用 mysqldump 全量导出要十几分钟，期间网站还不敢关；第二是单点故障，一旦服务器磁盘损坏或者误操作删了表，你只能靠上一次的备份恢复，丢失最近一天甚至更久的数据。MySQL 主从复制解决的就是这两个问题：主库实时把 binlog 同步给从库，从库始终有一份几乎最新的数据副本，主库挂掉时可以快速切换，平时也能把备份和查询压力分担到从库上。

对于个人站长来说，主从复制还有一个隐藏的好处：你可以把从库放在另一台廉价服务器甚至另一家机房，实现异地冗余。主库在 A 机房挂了，从库在 B 机房还活着，网站不至于全军覆没。本文我会从复制原理讲起，带着你一步一步在两台 CentOS 服务器上把 MySQL 主从复制搭起来，并给出常见故障的排查方法，全部命令都经过实际验证，可以直接照着操作。

主从复制的工作原理

理解原理之前，先搞清楚几个基本概念。MySQL 从 3.23 版本开始就支持复制功能，默认情况下它基于二进制日志（binary log，简称 binlog）工作。整个复制过程可以概括为三个线程的接力：主库上的 dump 线程、从库上的 IO 线程和 SQL 线程。

具体流程是这样的：主库把所有会改变数据的操作（INSERT、UPDATE、DELETE、DDL）按顺序写进 binlog；从库的 IO 线程连接主库，请求从某个 binlog 位置开始的数据，主库的 dump 线程负责把 binlog 内容发给从库；从库的 IO 线程收到后，先写进自己的中继日志（relay log）；最后从库的 SQL 线程按顺序重放 relay log 里的 SQL，把数据变更应用到从库自己的数据库上。整个过程是异步的，也就是说主库不会等从库确认完成才提交事务，所以正常情况下从库会有极小的延迟，一般在一秒以内。

理解了这三个线程，你以后看 SHOW SLAVE STATUS 的输出就不会一头雾水了。Slave_IO_Running 表示 IO 线程是否正常连接主库并拉取 binlog，Slave_SQL_Running 表示 SQL 线程是否正常重放中继日志，Seconds_Behind_Master 则表示从库落后主库多少秒。这三个字段是排障时最先要看的。

搭建前的环境准备

动手之前，先确认三件事。第一，主从两台服务器的 MySQL 版本最好一致，至少大版本一致，比如都是 5.7 或都是 8.0，跨大版本复制容易出现兼容问题。第二，两台服务器之间要能互相访问，数据库端口 3306 要在防火墙里放行，如果用了云厂商的安全组，也要在控制台里配置好规则。第三，主库的 server-id 和从库的 server-id 必须不同，这是复制拓扑识别的关键，很多人搭不起来就是因为两台机器 server-id 一样。

假设我们的环境是：主库 IP 为 192.168.1.10，从库 IP 为 192.168.1.20，两台都是 CentOS 7 系统，MySQL 5.7，使用默认的数据目录 /var/lib/mysql。下面所有的配置都在这个基础上进行。生产环境请把 IP 换成你的真实地址，并且不要在主库的配置里写死从库 IP，这样以后加从库会更灵活。

主库配置：开启 binlog

主库要做的第一件事是开启 binlog 并设置唯一的 server-id。编辑主库的配置文件 /etc/my.cnf，在 [mysqld] 段下添加以下内容：

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=15
max_binlog_size=512M

参数含义说明：server-id 设置为 1，只要和从库不同即可；log-bin 指定 binlog 文件前缀名，默认写在数据目录下；binlog_format 建议使用 ROW 格式，相比 STATEMENT 格式，行级复制在数据一致性上更可靠，主从数据不一致的问题会少很多，代价是 binlog 体积稍大；expire_logs_days 控制 binlog 保留天数，防止磁盘被日志占满；max_binlog_size 控制单个 binlog 文件大小，到点自动滚动。配置完成后重启 MySQL 使配置生效：

systemctl restart mysqld

重启后登录 MySQL 验证 binlog 是否开启：

mysql -uroot -p
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

如果 log_bin 的值为 ON，并且 SHOW MASTER STATUS 返回了 File 和 Position 两列，说明 binlog 已经正常工作。请记下 File 和 Position 的值，后面配置从库时要用到。

主库创建复制专用账号

复制账号不能直接用 root，安全起见要创建一个权限最小的专用账号。在主库上执行下面的 SQL，账号名为 repl，密码为 Repl@2026，注意把密码换成你自己的强密码：

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@2026';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

这里只授予了 REPLICATION SLAVE 权限，这是从库连接主库拉取 binlog 所需的最小权限，千万不要授予 ALL PRIVILEGES。账号主机范围建议写成从库所在网段，不要写成 %，减少被外部扫描爆破的风险。创建好后可以用下面的命令测试复制账号能否正常连接主库：

mysql -urepl -p -h192.168.1.10 -P3306

初始化从库数据

从库要复制主库的数据，前提是它自己先有一份和主库一致的数据快照。最常用的方式是用 mysqldump 在主库导出，再导入从库。注意导出时加上 --master-data=2 参数，它会在 dump 文件里自动记录导出时刻的 binlog 文件名和位置，省得你手动去查。执行导出命令：

mysqldump -uroot -p --all-databases --single-transaction --master-data=2 --routines --triggers > /tmp/master_dump.sql

参数解释：--single-transaction 在 InnoDB 引擎下通过事务快照实现一致性导出，整个过程不需要锁表，不会影响主库线上写入；--routines 和 --triggers 把存储过程、函数和触发器也一并导出，防止从库缺少这些对象。如果你的数据库特别大，导出的 SQL 文件会很大，可以用 gzip 压缩传输：

gzip /tmp/master_dump.sql
scp /tmp/master_dump.sql.gz root@192.168.1.20:/tmp/

然后在从库上解压并导入。导入前先确认从库 MySQL 是刚初始化或者空的，避免数据冲突：

gunzip /tmp/master_dump.sql.gz
mysql -uroot -p < /tmp/master_dump.sql

导入完成后，打开 dump 文件头部，找到类似 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154 的行，记下这个文件名和位置，后面 CHANGE MASTER TO 语句要用。

从库配置与启动复制

现在配置从库。编辑从库的 /etc/my.cnf，在 [mysqld] 段下添加：

[mysqld]
server-id=2
relay-log=mysql-relay-bin
read_only=1

server-id 必须和主库不同，这里设置为 2；relay-log 指定中继日志前缀；read_only=1 让从库拒绝除复制线程和超级用户以外的写操作，防止人为误写导致主从数据不一致。重启从库 MySQL 后，登录从库执行 CHANGE MASTER TO 语句，把 dump 文件里记录的 binlog 位置填进去：

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@2026',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;

然后启动复制线程并查看状态：

START SLAVE;
SHOW SLAVE STATUS\G

重点看两个字段：Slave_IO_Running 和 Slave_SQL_Running 都应该显示 Yes。如果都是 Yes，说明复制已经正常跑起来了。你可以在主库上随便插入一条数据，再到从库查询，确认数据实时同步。再强调一次，复制状态里这两个 Running 字段是 Yes 只是基础，还要结合 Seconds_Behind_Master 观察延迟，如果这个值持续增长，说明从库重放速度跟不上主库写入速度。

GTID 复制简介

传统基于文件和位置的复制有个麻烦：每次要从头记录 binlog 文件名和位置，一旦出错很难定位。MySQL 5.6 开始引入 GTID（全局事务标识符）复制，每个事务都有唯一的标识，从库靠 GTID 自动判断自己已经执行到哪个事务，不需要手动指定文件和位置，故障切换和加新从库都方便很多。启用方式是在主从两边的 my.cnf 里都加上 gtid_mode=ON 和 enforce_gtid_consistency=1，然后重新初始化复制。对于刚开始搭建复制的新手，我建议直接学 GTID 模式，一步到位，等熟悉之后再回头看传统模式会觉得豁然开朗。

常见故障排查

复制搭好只是开始，运行中遇到的问题才是常态。第一个高频问题是 SQL 线程报错停止，最常见的是 1062 主键冲突：从库上已经存在相同主键的数据，重放主库的插入语句时失败。解决办法是先确认两边数据差异，把从库多出来的那条数据删掉或者补齐，然后跳过这一个错误事务：

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

第二个问题是 IO 线程连不上主库，状态一直显示 Connecting。排查顺序是：先确认网络通不通（ping 和 telnet 3306），再确认防火墙和安全组是否放行，然后确认复制账号密码是否正确，最后看主库的 max_connections 是否被打满。第三个问题是复制延迟越来越大，通常是因为从库硬件太差、主库有大事务、或者从库上跑了大量的分析查询占用了资源。解决方法包括升级从库配置、把大事务拆小、以及给从库的查询单独优化索引。

还有一个新手容易踩的坑：主库执行了 DROP DATABASE 之类的危险操作，从库会忠实地跟着删。所以在从库上建议定期做全量备份，同时把 binlog 保留时间设置合理，这样即使误操作，也能用 binlog 做时间点恢复。

主从复制后的运维建议

复制跑起来之后，有几个习惯值得养成。第一，每天检查一次 SHOW SLAVE STATUS，把检查命令写进 crontab，输出异常时发邮件或者推送告警，很多站长都是从库挂了半个月才发现，到时候再追数据就来不及了。第二，定期在主库和从库分别做一次数据校验，最简单的办法是比对几张核心表的行数和 checksum，防止静默的数据不一致。第三，从库不要只当备份放着，可以把慢查询分析、报表统计这类只读业务迁移到从库，减轻主库压力，这才是主从复制的价值所在。

主从复制不是终点，但它是个人站长走向高可用架构的第一步。先把复制搭稳、看熟状态、摸清排障套路，以后再上 MHA、Orchestrator 这类自动切换工具，甚至分库分表，就有了扎实的基础。希望这篇文章能帮你把主从复制这个技能点点亮，让你的数据多一道保险。