企业官网建设流程全解析

MySQL最早的主从配置，我是在一台Windows开发机和一台Linux服务器上搞的。当年照着网上的教程一步步敲，结果卡在从库的IO线程一直显示connecting，排错排到半夜。后来搞清楚了才发现，主从复制本身不复杂，复杂的是你对它背后机制的理解程度。这篇文章我就把从原理到实操，再到我踩过的那些坑，一次性讲透。

1. 主从复制到底在解决什么问题：先想清楚再动手

很多人一上来就搜“mysql 主从配置”，照着文档敲一遍命令就以为完事了。但如果你不清楚主从复制解决的是什么问题，后面遇到故障根本无从下手。

1.1 你其实是在给数据库做备份+读写分离

主从复制的核心价值有两个。第一个是容灾备份，主库挂了以后，从库可以马上顶上，业务不至于全盘瘫痪。第二个是读写分离，把查询压力从主库分摊到从库，主库专心处理写事务。这两件事是主从复制的根本目的，你配置过程中的每一个参数选择，都应该是围绕这两个目的来的。

1.2 数据同步的三个线程：主库一个，从库两个

理解主从复制，你只需要记住三个线程。主库上有一个binlog dump线程，它的职责是监听binlog的变更，一旦有新的日志事件，就推送给从库。从库上有两个线程，一个叫IO线程，负责接收主库推送过来的binlog，然后原样写入从库自己的relay log（中继日志）；另一个叫SQL线程，负责读取relay log里的日志事件，逐个在从库上重放执行。

整个过程就是：主库写binlog → 从库IO线程拉取到relay log → 从库SQL线程在本地重放。理解了这个链路，再去看配置参数，每个配置项为什么存在，就一目了然了。

1.3 为什么主从之间会有延迟：一个很容易被忽视的概念

从库的数据和主库不是实时一致的，中间有一个时间差，这个时间差叫复制延迟。理论上，只要主库有写操作，SQL线程重放就需要时间，所以延迟只能缩小，无法完全消除。尤其是主库上一次性执行了大事务（比如alter一个大表），SQL线程要等这个事务在从库完整执行完，延迟就会瞬间拉高。

提示：如果你的业务对数据一致性要求极高（比如金融交易），主从复制只能作为灾备，不能完全替代主库。读写分离后，刚写入的数据立刻从从库读，极有可能读不到，这是架构设计上需要提前想清楚的问题。

2. 一步步搭起主从环境：从零到复制的完整流程

接下来是大家最关心的实操环节。我以MySQL 8.0为例，因为8.0是目前的绝对主流版本，5.7的配置方式也基本通用。整个流程我拆成六个部分，每一步我都会告诉你为什么这么做，而不只是命令怎么敲。

2.1 修改配置文件：两个关键参数决定复制命脉

配置文件在Windows的MySQL里叫my.ini，在Linux里叫my.cnf，通常在/etc/my.cnf或者/etc/mysql/my.cnf。这个文件是主从复制的起点，里面有两个参数是必须配置的，缺一个都不行。

第一个是server-id，这是MySQL实例在整个复制拓扑里的唯一身份证。主库和从库的server-id必须不一样，否则从库会报错，拒绝连接。第二个是log-bin，打开binlog日志功能，它是主从复制的数据源头。

主库的配置我建议这样写：

[mysqld] # 服务器唯一标识，主库从库必须不同 server-id = 1 # 开启binlog，文件前缀名自定义 log-bin = mysql-bin # 选择ROW模式，下面会详细解释 binlog_format = ROW # 需要同步的数据库，不写就是全部库 # binlog-do-db = mydb # 不需要同步的库，可写可不写 # binlog-ignore-db = mysql

从库的配置则是这样：

[mysqld] # 从库的ID，只要和主库不一样就行 server-id = 2 # 从库可以不写log-bin，但建议也开启，方便以后从库再做下级复制 log-bin = mysql-bin # 记录SQL线程重放的中继日志 relay-log = mysql-relay-bin # 只读模式，从库禁止写操作，下面会细讲 read-only = 1

2.2 为什么binlog_format要选ROW模式：一个影响数据安全的细节

binlog有三种格式：STATEMENT、ROW、MIXED。早期MySQL默认是STATEMENT，它记录的是SQL语句本身。你执行一条update语句，binlog里就记一条update语句，从库原样再执行一次。

听起来没问题，但STATEMENT有个致命缺陷。如果SQL语句里用了now()函数，或者update表时依赖了当前的表数据，从库执行时得到的结果就可能不一样。最典型的就是：

UPDATE user SET create_time = NOW() WHERE id = 100;

主库执行后，binlog里记录的是“执行这条SQL”，从库重放时，NOW()会重新取一个时间。哪怕只差一毫秒，数据就已经不完全一致了。

ROW模式记录的不是SQL，而是每一行数据的变化结果。主库上需要改动哪些行，binlog就记哪些行的最终值，从库直接改对应的行即可。这样无论SQL语句多复杂，从库得到的数据和主库永远一致。

注意：ROW模式在批量update大量数据时，binlog的体积会比STATEMENT模式大很多，因为每条受影响的行都要记录。但为了数据一致性，这个代价是值得的。现在MySQL 8.0的默认格式已经是ROW了，你只需要在配置里显式声明，防止某些自定义编译版本或历史配置把它改成别的。

2.3 创建复制专用账号：权限越小越安全

这里有一个很容易踩的坑——直接用root账号做复制。千万不要这么做。root权限太大，万一从库被攻破，主库也跟着遭殃。而且root账号的密码一般涉及业务核心资产，不应该暴露给从库。

复制账号只需要一个权限：REPLICATION SLAVE。在MySQL里，这个词汇是固定的，不要写成REPLICATION SLAVE加复数，也不要加别的权限，最小权限原则在这里同样适用。

在主库上执行：

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

关于host的部分，我一般不建议用%，而是指定从库的IP，更安全，也可以有效减少网络广播带来的账号暴露风险。

2.4 备份主库数据并恢复到从库：复制启动前的数据一致性

这是新手最容易漏掉的一步。很多人配置完主从，change master也执行了，start slave也执行了，结果从库的SQL线程一直在报错。一查，原来主从之间的数据本来就不一致，主库已有的数据根本没有同步到从库。

主从复制不是“把主库全区块复制到从库”，它只是从开启复制的那一刻开始，把新的变更同步过去。如果从库上原本是空的，初始数据就是缺失的。所以开始复制之前，必须把主库的数据先备份并恢复到从库。

推荐用mysqldump，命令如下：

mysqldump -u root -p --single-transaction --master-data=2 --all-databases > backup.sql

这个命令有讲究。--single-transaction是InnoDB引擎下使用一致的快照备份，可以避免备份期间对主库写入产生太大影响。--master-data=2会在备份文件里自动记录当前主库的binlog文件名和位置，这是启动复制时必须用到的信息，它将直接显示在备份文件的注释中。

然后把备份文件传到从库，执行：

mysql -u root -p < backup.sql

提示：恢复的过程中，如果从库上有正在运行的复制线程，建议先停掉，即执行STOP SLAVE，否则可能出现数据冲突。

2.5 change master并启动复制：一行命令里面藏着大乾坤

在从库上执行change master，这句SQL长得吓人，其实每个参数都有明确含义：

CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='repl', MASTER_PASSWORD='YourStrongPassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

MASTER_HOST是主库的IP，MASTER_USER和MASTER_PASSWORD是刚才创建复制账号。MASTER_LOG_FILE和MASTER_LOG_POS则是从主库的备份里得到的，如果使用的是mysqldump的--master-data=2，他们会在备份文件头部显示。

启动复制：

START SLAVE;

然后用这条命令查看状态：

SHOW SLAVE STATUS\G

看到两个超关键的指标：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

两个都是Yes，代表复制正常运行。如果有一个是No，就要往下看Last_IO_Error或者Last_SQL_Error的具体报错信息。

2.6 为什么不建议把从库设为可写：一个只需要改一行配置的防呆设计

从库默认是可写的，但正常业务中不应该向从库写入。如果直接往从库插入一条数据而主库没有的，会导致两个结果：要么复制冲突，SQL线程直接停止，要么从库写了一份主库没有的数据，主从数据永久不一致。

解决很简单，在从库配置文件中加一行：

read-only = 1

加上之后，从库会拒绝所有应用层的写操作。注意，这只会限制有权限的用户，root用户的SUPER权限还是可以写的，所以从库的root密码也要保管好，这不是一个绝对安全的机制，只是防呆防误操作。

3. GTID方案：新时代的复制推荐姿势，也是搜索引擎里最常出现的同步方案

前文讲的是基于binlog文件名和POS位置的复制方式，也就是基于位点的复制，它有一处软肋，就是如果从库的relay log损坏了，或者你换了一台从库，需要手工定位到正确的binlog坐标，定位不准确就可能导致数据重复或丢失。

3.1 GTID让每个事务都有全球唯一ID

GTID的全称是Global Transaction Identifier，全局事务标识符。每当事务在主库上提交，MySQL会为它生成一个唯一ID，格式是：

服务器UUID:事务序号

比如3b6d4b7a-2d34-11ee-8b6f-005056a0a06d:1，这个ID在整个复制拓扑里都是唯一的。从库执行事务时，也会记录这个ID，不会重复执行。

基于GTID的复制，不再需要关心binlog文件是哪个、位置在哪，只要主从都开启了GTID，从库会自动识别主库已经执行到哪了，然后接着往下同步。这个过程极大地简化了复制切换和从库重建的运维成本。

3.2 基于GTID的配置差异

主库配置：

[mysqld] server-id = 1 gtid_mode = ON enforce_gtid_consistency = ON log-bin = mysql-bin binlog_format = ROW

从库配置：

[mysqld] server-id = 2 gtid_mode = ON enforce_gtid_consistency = ON log-bin = mysql-bin relay-log = mysql-relay-bin read-only = 1

启动复制时，命令可以直接简化：

CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='repl', MASTER_PASSWORD='YourStrongPassword', MASTER_AUTO_POSITION=1;

MASTER_AUTO_POSITION=1的意思是让从库自动获取主库的GTID执行进度，不需要你手动指定文件和POS了。

3.3 用xtrabackup的物理备份做从库，配合GTID同步

用mysqldump逻辑备份，在数据量小的情况下没问题。但数据库变到几十个GB甚至TB级别时，逻辑备份恢复时间太长，而且备份期间负载很高。这时候就应该考虑Percona出品的xtrabackup，做物理备份。

xtrabackup备份的是数据文件，相当于把InnoDB的数据页面直接拷贝出来，恢复时也不需要一条条执行SQL，速度能够有量级上的提升。配合GTID同步，是大中型MySQL从库搭建时比较稳妥的组合拳。

主库上用xtrabackup创建备份并流式传到从库，详细命令可以写成脚本：

# 主库上备份，生成压缩文件 xtrabackup --backup --user=root --password=yourpass --target-dir=/data/backup xtrabackup --prepare --target-dir=/data/backup # 传到从库 tar -czf backup.tar.gz /data/backup scp backup.tar.gz root@从库IP:/data/

从库上解压并恢复到数据目录后，启动MySQL。xtrabackup恢复出来的数据，在备份信息里会带有GTID信息。从库启动之后，直接change master并打开MASTER_AUTO_POSITION，从库会自己判断从哪个GTID开始追，大大缩短从库追上主库进度的时间，也降低了手工计算binlog坐标出错的概率。

4. 复制状态检查与故障排查：那些年我踩过的坑

配置主从完成不代表万事大吉，线上环境随时可能踩坑。下面的内容是我实操中真实遇到的情况，不是从文档里搬运来的理论。

4.1 主从复制状态巡检清单

第一步，确认IO线程和SQL线程都是Yes。

SHOW SLAVE STATUS\G

重点关注这几个字段：

• Master_Log_File / Read_Master_Log_Pos：IO线程已经拉到哪个binlog的哪个位置。
• Relay_Master_Log_File / Exec_Master_Log_Pos：SQL线程已经执行到哪个binlog的哪个位置。

如果Read_Master_Log_Pos和Exec_Master_Log_Pos一致，且一直在增长，说明延迟很小，复制正常运行。

第二步，用这条命令计算复制延迟：

SHOW SLAVE STATUS\G

看 Seconds_Behind_Master 字段。它是从库当前时间与SQL线程所在binlog事件主库时间之间的秒数差。这个值等于0最理想，大于0说明有延迟，如果持续增长说明从库处理不过来。

4.2 从库的SQL线程报错了怎么办：完整排查链路

SQL线程报错是最常见的故障，报错格式大概是：

Last_SQL_Error: Error 'Duplicate entry '1001' for key 'PRIMARY'' on query

通过这个报错可以判断，从库试图插入一条跟主库不一致的主键记录，导致冲突。产生原因一般是在主从搭建初期，从库手动插过或修改过数据，或者备份恢复时没有完全对齐主库。

我的排查思路是：

1. 先看一下报错事务涉及的表，搞清楚哪条数据冲突了。可以通过SHOW SLAVE STATUS里的 Last_SQL_Error 和 Last_Errno（错误码1062）定位到具体SQL。
2. 判断冲突数据的重要性。如果是测试环境，最简单快捷的办法是打开跳过错误事务的功能，让SQL线程把出错的这个事务略过。
3. 在生产环境，绝不能盲目跳过错误。我们的目标是让从库完全追上主库，而不是带着错误继续跑。

实践中，如果错误数据本身没有业务价值，可以用下面的方式临时恢复：

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

sql_slave_skip_counter = 1代表跳过SQL线程的下一个事务，注意是一个事务，不是一条SQL。一个事务可能涉及多条SQL，都要跳过。

跳过之后观察Seconds_Behind_Master是否能逐渐变成0。如果能追上，那从库可以继续在线服务。如果跳过之后又立刻报新的Duplicate键，那说明从库的数据和主库差异太大，与其修修补补，不如直接从主库重新备份恢复，更干净。

注意：sql_slave_skip_counter的作用已经越来越不推荐了，因为跳过事务有一定概率破坏数据一致性。如果你开启了GTID，替代方法是注入一个空事务来跳过冲突，这个操作需要谨慎，建议在专业人士指导下进行。

4.3 IO线程卡在connecting，先去查三件事

IO线程状态如果一直显示 Connecting，我的排查顺序是固定的三步：

第一步，检查网络连通性。从从库那台机器上ping主库IP，如果ping不通，要么是防火墙拦截，要么是主库宕机。端口层面可以再测试一下，MySQL默认是3306端口。

telnet 192.168.1.10 3306

第二步，检查复制账号权限。用change master里的MASTER_USER账号手动登录主库，看能不能登录上去，复制权限是否还在：

SELECT user, host FROM mysql.user WHERE user = 'repl'; SHOW GRANTS FOR 'repl'@'%';

第三步，检查server-id冲突。主库和从库server-id如果设置成了同一个值，IO线程也会一直connecting，因为MySQL会觉得这是在连接自己。

4.4 主从不同步了，如何最少影响业务地修复

线上有业务在跑，主从不同步时，最忌直接停库。我建议的操作路径是：

1. 记录下当前从库已经执行到的GTID位置或binlog位置，用于后续追平。
2. 从库停止复制，只读状态保持不变，避免应用继续读旧数据。
3. 用xtrabackup对主库做增量备份，恢复到从库。
4. 重新启动复制，观察是否追平。

如果你的数据量不大，也可以用pt-table-checksum工具找出主从差异的表数据，再用pt-table-sync修复。这两个工具是Percona Toolkit里的明星产品，专门解决主从数据不一致问题，不用整库重搞。

其中pt-table-sync直接执行后就能把从库的数据修正为和主库一致，操作前建议先备份。

5. 进阶思路：从单主单从到一主多从的运维变化

单主单从是入门，很多企业实际环境里用到的是一主多从，甚至多级复制。同一个主库挂着多台从库，每台从库都有不同的用途。这时候，主从配置的思路就要调整了。

5.1 给不同从库安排不同职责

常见的一主多从场景，一般是一台从库做实时灾备，另一台从库做报表查询或数据仓库的取数源。报表查询往往有慢查询，会占用大量CPU和IO，如果不分开，灾备从库的性能就会受到影响。我一般建议为这样的从库单独设置参数：

# 从库A，承担实时灾备 read-only = 1 # 从库B，承担报表分析，可以对外提供查询 read-only = 1 # 如果从库B性能较弱，可以适当丢弃部分不重要的慢日志

这里有一个很实用的功能，就是在从库上配置不同步某些表或库。比如报表从库不需要同步日志库，就可以用replicate-wild-ignore-table参数：

replicate-wild-ignore-table = mydb.log_%

这个参数在搭建多从库时很实用，可以减少从库的存储压力。

5.2 主库宕机后，如何快速把从库提升为主库

假定从库数据已经和主库追平，主库突然宕机了。此时业务不可写，你已经决定把从库提升成新的主库。需要注意的操作顺序是：

1. 确认从库已经执行完relay log中所有事务。查看SHOW SLAVE STATUS，确保Retrieved_Gtid_Set（或Relay_Master_Log_File/Exec_Master_Log_Pos）已经执行到最新。
2. 停止从库的复制线程。
3. 在从库上解除只读状态。
4. 修改从库的配置文件，让它以主库身份对外提供服务，比如将read-only=1注释掉，开启log-bin，并配置一个新的server-id（如果不打算继续沿用旧的从库ID）。
5. 如果有其他从库，需要把这些从库的复制源指向新主库。

操作过程中，提升为新的主库后，原有上下游复制关系会发生变化，新从库需要重新change master到新主库，这正是GTID方案的优势所在，自动定位到正确的GTID，无需关心文件位置。

6. 日常保命技巧：主从复制运维的几条肺腑之言

最后这部分，是平时不写在配置文档里的经验之谈，但真到线上出问题时，这些细节比命令本身更值钱。

6.1 用半同步复制弥补数据丢失风险

MySQL原生的复制是异步的，主库提交事务后，不需要等从库确认，直接返回给客户端成功。如果主库这时候突然宕机，还没送达到从库的数据就丢了，主从切换后这部分数据永远找不到。

如果业务对数据安全要求高，可以打开半同步复制（semisync replication）。半同步的核心逻辑在于：主库提交事务时，至少要等一个从库确认已经收到binlog，然后才返回客户端成功。虽然网络多了一次往返，性能有一些损耗，但主从切换时不会丢数据。

MySQL 8.0里启用半同步比较顺，直接加载插件并设置global变量：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_slave_enabled = 1;

如果半同步确认超时，系统会自动降级回异步，不影响主库的可用性，这点设计得很安全。

6.2 复制账号的密码一定要定期更换，且要用强密码

主从复制账号只在从库和主库之间互相连接，通常会把它暴露在配置文件和脚本里。时间久了，如果这个密码泄露，攻击者就能直接拉取主库的binlog，进而窥探所有业务的写入数据。

我建议把复制账号密码纳入公司的密码管理系统，每季度更换一次。顺便不要用123456这样的弱密码，所有线上的MySQL账号都不该用weak password。

6.3 别忽略监控，尤其是延迟和错误日志

主从复制有没有问题，预警最有效的方式不是人工去看，而是监控系统。只要把几个关键指标加进监控：

• Slave_IO_Running 是否等于 2（Yes的状态值）
• Slave_SQL_Running 是否等于 2
• Seconds_Behind_Master 是否大于阈值（一般30秒之内算正常，超过30秒就需要告警）

一旦触发告警，先看从库机器的CPU、磁盘IO，再看主库是否有大批量写入操作。如果延迟是慢SQL引起，可以顺便检查主库上的慢查询日志，找出最耗时的事务，考虑拆分成小批量执行。

6.4 从库数据的周期性校验，这是很多人不做的

你以为从库和主库只是差了延迟，实际上运行久了，主从数据不一致的例子比比皆是。要么是跳过事务埋下的隐患，要么是人为修改数据留下的问题，要么是bug导致binlog解析错误。

我建议每月做一次数据校验。Percona Toolkit里的pt-table-checksum工具专门做这件事：

pt-table-checksum --databases=mydb --tables=user --nocheck-binlog-format

它会对比主从上的数据，输出差异，再配合pt-table-sync修复。这一步操作请务必先在测试环境演练，因为修复数据的SQL可能涉及大量行，生产环境直接执行有风险。