MySQL异步复制实战：主从搭建、验证与排错全解析

说实话MySQL复制这一块儿同步、半同步、异步三个词经常把新手绕晕。今天我就用最直白的方式以“异步复制”为例带你把主从复制的搭建、验证和排错整个走一遍。这篇文章既不是教科书也不是官方文档翻译是我自己折腾各种项目时攒下来的实操路子重点放在“示例”上——从一条空库到主从链路真正跑起来每一步都有明确目的和验证手段。不管你是刚接触数据库的运维新手还是写业务代码时被主从延迟坑过的后端这篇都能给你一个可以照着抄的作业。1. 为什么单独把“异步复制”拿出来细讲1.1 同步、半同步、异步的本质区别先解决一个最基础的问题什么叫异步复制MySQL主库提交事务之后并不等待从库确认收到二进制日志就直接返回客户端“成功了”。从库在后台异步地拉取主库的binlog然后回放到自己本地。这就是“异步”二字的核心语义主库和从库之间存在时间差这个时间差就是我们常说的复制延迟。与之相对的同步复制MySQL里实际是“半同步复制”要求主库在提交事务时至少等待一个从库确认已经接收并写入relay log主库才向客户端返回成功。注意MySQL并没有真正意义上的“全同步复制”官方提供的半同步插件已经是最接近同步的方案。为什么MySQL默认用异步而不是半同步答案很简单性能。异步复制下主库完全不需要管从库的状态写事务的响应时间几乎等于单机写入的响应时间吞吐量最高。而半同步要付出网络往返的代价主库性能必然被拉低。1.2 异步复制适合什么场景又有什么坑异步复制最大的价值在于三个场景。一是读写分离主库承接写入从库承接读流量业务上天然能接受“读到的数据可能比主库晚几百毫秒”二是灾备主库宕机时从库可以顶上即使丢失少量事务也比全员停摆强三是数据分析与报表跑大查询、生成报表这类重IO任务直接扔到从库执行不影响主库业务。但异步复制的坑也必须说透。最典型的就是主从延迟。主库高并发写入时从库的单线程回放历史上默认是单线程8.0对此做了不少优化后面会细说跟不上延迟会持续累积另一个是数据一致性问题异步模式下主库崩溃后从库可能缺少最后一批事务强行提升从库为主库会造成数据丢失还有一个是逻辑冲突问题如果从库上有写入操作或者主从表结构不一致复制随时可能中断。这些坑我会在后面的排查部分结合实例展开。2. 动手前的准备与关键参数选择2.1 版本选型8.0还是5.7热词里藏着的答案很多人在网上搜“mysql安装教程”“mysql下载地址”装的时候图省事直接点上最新版结果后面复制配置时到处踩坑。结合这些年我实际接触的环境提醒一句新项目无脑选8.0老项目或者兼容性要求极高的场景才选5.7。如果你自己搭实验环境做异步复制示例非常建议直接用MySQL 8.0因为8.0默认使用caching_sha2_password认证插件gtid_mode默认也是开启的后面配置主从会少很多麻烦。5.7和8.0在复制功能上有一处明显差异值得单独说。8.0把复制回放线程改成了基于writeset的并行回放从库的并行度大幅提升异步复制的延迟问题被缓解了很多。而5.7虽然也有并行复制MTS但默认配置相对保守需要手动调整slave_parallel_workers等参数。所以如果你做性能对比测试5.7和8.0的异步复制延迟表现会差一个量级这不是你配置错了是版本机制本身决定的。我建议实验环境用MySQL 8.0.4x以上的稳定版本我在热词里看到有人用8.0.46这个版本做复制示例完全没问题。2.2 主库参数与业务账号规划开始配置之前先规划好主库的关键参数。以下这些参数在my.cnf或者my.ini里必须出现[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON binlog_expire_logs_seconds 2592000server_id是主从身份的唯一标识整个复制集群里不能重复这是新手最容易忽略的。log_bin开启二进制日志这是异步复制的数据源不开这个主从根本无从谈起。binlog_format用ROW还是STATEMENT异步复制建议直接用ROW虽然日志量会大一些但数据的准确性最有保障回放时的确定性也最好。gtid_mode和enforce_gtid_consistency是8.0默认开启的但如果你是从5.7升级过来务必手动确认。8.0里GTID几乎已经成了复制的主流方式后面示例我也会基于GTID来讲。业务账号也要提前规划好。复制链路的账号只需要REPLICATION SLAVE和REPLICATION CLIENT两个权限千万不要图省事直接给ALL PRIVILEGES。账号创建语句如下CREATE USER repl% IDENTIFIED BY Repl123456; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;为什么强调权限最小化因为复制账号是长期需要网络访问的账号如果给了过高权限一旦被拖库或者账号泄露后果不堪设想。复制账号只需要拉取binlog的能力这个能力由REPLICATION SLAVE控制REPLICATION CLIENT则是用于执行SHOW MASTER STATUS等命令查询复制状态。2.3 从库参数与历史遗留坑从库参数相对简单但有几个容易出问题的点。[mysqld] server_id 2 log_bin mysql-bin relay_log mysql-relay-bin read_only ON gtid_mode ON enforce_gtid_consistency ONread_only ON是保护从库不被业务写入的关键开关这个参数对超级管理员账号SUPER权限不生效所以平时连接从库执行操作的账号要克制不要用超级管理员账号去写业务表。relay_log指定中继日志文件名前缀它在从库复制链路里承上启下从库IO线程从主库拉取binlog到本地relay logSQL线程再把relay log里的SQL语句回放到从库数据。还有一个历史遗留坑如果你在旧版本MySQL上做过主从复制然后直接原地升级到8.0从库的relay log可能会残留旧格式数据导致复制直接报错。这种问题在升级场景下非常常见解决办法是升级后执行STOP SLAVE; RESET SLAVE ALL;重新搭建复制链路。3. 主从复制的搭建完整示例3.1 确认主库状态与同步点在正式搭建复制链路之前先把两台MySQL实例都启动好参数配置正确。第一步是登录主库查看当前二进制日志状态。SHOW MASTER STATUS;8.0版本下开启GTID后这条命令输出类似这样------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 123 | | | 5f8a1c2e-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1-5 | -------------------------------------------------------------------------------在老版本非GTID模式下你需要记录下File和Position两个值后面CHANGE MASTER TO要用。在GTID模式下主库自己会跟踪事务的GTID集合日志位置反而不需要手动指定只需要确保从库的GTID集合和主库对齐即可。所以用GTID模式搭建复制本质上比老方式更简单你不需要去“对齐日志位置”只需要让从库知道“我要从主库的这个GTID集合之后开始拉取”。如果你现在维护的是一个已经有业务数据的主库需要在搭建复制前把存量数据导出并导入到从库。这个操作务必在导出前锁定主库写入或者使用mysqldump的--single-transaction配合binlog位置信息。下面这条命令是标准做法mysqldump -uroot -p --single-transaction --set-gtid-purgedON --master-data2 --all-databases /data/backup/full.sql--master-data2会在导出文件里自动记录当时的binlog文件名和位置老版本或者记录GTID集合GTID模式这样导入到从库后复制链路可以从导出时刻无缝衔接。用这个文件导入从库之后从库的GTID集合就已经和主库的某个历史点对齐了后面的CHANGE MASTER TO自然就不用考虑从哪个位置开始拉的问题。3.2 从库初始化导入拿到全量备份文件之后把文件传到从库服务器执行导入mysql -uroot -p /data/backup/full.sql如果是从空实例开始做实验导入这一步可以省略但最好是走一遍这个流程因为真实项目中业务库基本不可能是空的。导入时有一个细节要特别小心如果mysqldump导出命令里没有带--set-gtid-purgedON那么导入的文件里不会包含GTID信息这种情况下从库的GTID集合是空的复制链路可能会重复导入已经存在的数据导致主键冲突。所以上面的导出命令里这个参数一定要带。导入完成后登录从库先确认一下GTID的同步状态SHOW GLOBAL VARIABLES LIKE gtid_purged; SHOW GLOBAL VARIABLES LIKE gtid_executed;正常情况下执行过全量导入后gtid_executed应该包含主库导出时的GTID集合gtid_purged一般为空或者包含一个初始值。这个细节不是特别重要只要后面CHANGE MASTER TO能成功执行就行但知道了这些变量的含义排查问题时会快很多。3.3 从库发出复制指令在从库上执行CHANGE MASTER TO语句指定主库的连接信息。以8.0为例语法如下CHANGE MASTER TO MASTER_HOST 192.168.1.10, MASTER_PORT 3306, MASTER_USER repl, MASTER_PASSWORD Repl123456, MASTER_AUTO_POSITION 1;MASTER_AUTO_POSITION 1是GTID模式的关键标志。这告诉从库别管什么日志文件位置了我直接用GTID集合自动对齐主库的复制点。主库接收到从库的请求后会比对从库当前已有的GTID集合和自身binlog里的GTID集合找出差额开始推送。这种方式比手动指定日志位置靠谱得多就算从库中间断过重新连接时也能自动找到继续点而不是因为日志位置不对导致漏数据或者报错。启动复制START SLAVE;查看复制状态SHOW SLAVE STATUS\G关注以下几个关键字段Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 123 Relay_Master_Log_File: mysql-bin.000003 Exec_Master_Log_Pos: 123IO线程负责连接主库拉取日志SQL线程负责回放日志这两个状态必须是Yes。Seconds_Behind_Master为0表示从库已经追平主库没有延迟。Master_Log_File和Read_Master_Log_Pos表示从库IO线程已经拉取到主库的哪个日志文件和位置Relay_Master_Log_File和Exec_Master_Log_Pos表示SQL线程已经回放到哪个点位。两个位置一样说明从库完全追平如果Exec落后于Read说明SQL线程在回放上跟不上。4. 示例落地一个最小业务场景的验证过程4.1 建库建表与数据写入复制链路建立之后光看状态是Yes还不能说明一切必须用实际数据验证。我习惯的做法是这样在主库上建一个测试库和测试表然后插入几行数据。CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE demo; CREATE TABLE IF NOT EXISTS user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, email VARCHAR(128) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO user (name, email) VALUES (张三, zhangsanexample.com); INSERT INTO user (name, email) VALUES (李四, lisiexample.com);为什么用utf8mb4而不是utf8utf8mb4才是真正的四字节UTF-8编码可以完整支持emoji和生僻字。如果建库用了老式utf8utf8mb3插入一个emoji就会报错或者乱码这个坑在日常业务里出现频率极高趁示例直接养成好习惯。数据插入完成后在从库上查询SELECT * FROM demo.user;如果能看到同样的两条记录说明复制链路全通。这个验证看起来很简单但它是整个异步复制示例的核心闭环主库产生binlog - 从库IO线程拉取 - 从库SQL线程回放 - 从库数据可见。4.2 验证DDL操作与结构同步插入数据只能验证DML复制DDL的复制同样重要。比如在从库上查询表结构SHOW CREATE TABLE demo.user\G主库上执行一个ALTER TABLEALTER TABLE demo.user ADD COLUMN phone VARCHAR(20) DEFAULT NULL;稍等片刻再从库上看SHOW CREATE TABLE demo.user\G你会看到从库的建表语句里也出现了phone字段。MySQL的异步复制机制对DDL的支持是比较完备的但要注意一点DDL在从库回放时是原样执行不区分事务边界而且一旦DDL在从库执行失败SQL线程可能会直接停止。例如主库表里有一列是8.0的新数据类型而从库是5.7版本回放时就会报错。所以主从版本尽量保持一致即使是大版本一致、小版本不同也建议尽量贴近避免这类兼容性差异。4.3 状态命令与延迟监控的日常用法搭建完成后日常运维最常用的命令要形成肌肉记忆。一条是SHOW SLAVE STATUS前面已经用了一条是SHOW PROCESSLIST看主从两端的连接状态还有一条是查看主从的GTID集合差异。-- 从库查看IO线程连接主库的状态 SHOW PROCESSLIST;正常运行时从库的PROCESSLIST里应该有两条和复制相关的线程一条是system userState是“Waiting for master to send event”IO线程在等待主库发送事件另一条也是system userState是“Applying batch of row changes”或者“Reading event from the relay log”SQL线程在应用relay log里的变更。另外可以定期在主库执行SHOW MASTER STATUS在从库执行SHOW SLAVE STATUS把Exec_Master_Log_Pos和主库的Position进行比较。如果两边的值长期一致且Seconds_Behind_Master为0说明复制链路健康如果从库的Exec_Master_Log_Pos长期落后说明有延迟需要进一步排查。5. 异步复制常见故障与排查笔记5.1 最常见的1062主键冲突SQL线程停止、报错“Duplicate entry 1 for key PRIMARY”这是我从入行到现在见过最多次的复制故障。原因很简单主库插入一条id1的记录从库上恰好已经有一条id1的记录回放时从库执行相同插入主键冲突SQL线程中止。这个故障的高发场景有两种。第一种是主从搭建时没有做数据对齐主库已有数据从库是空库直接开启复制导致全量数据回放时大量冲突第二种是从库上被误写入过数据read_only没开或者用了超级管理员业务逻辑绕过保护在从库插入了和主库相同的记录。排查思路很直接先SHOW SLAVE STATUS查看报错信息确认是不是1062然后找到从库冲突的那条记录是什么。处理方式看业务容忍度如果冲突记录在从库上是垃圾数据直接DELETE掉然后START SLAVE恢复如果冲突记录是有价值的需要评估从主库导入该记录还是跳过该事务。使用SQL_SLAVE_SKIP_COUNTER跳过事务这个方法现在不建议随便用因为GTID模式下跳过事务会导致GTID集合不一致后续可能出现更麻烦的错位最好是精准处理冲突行后再继续。8.0里可以借助binlog工具或直接手工修正数据放弃简单粗暴的全量跳过。5.2 复制延迟突然飙升怎么办异步复制最让人头疼的不是中断而是延迟。从库Seconds_Behind_Master从0变成几百秒主库已经写入大量数据从库却迟迟追不上。优先排查这几个方向第一主库是不是有大事务。例如一次UPDATE影响百万行binlog里这个事务体量巨大从库必须回放完这个大事务才继续延迟必然飙升。这种场景只能从业务层避免把大事务拆小。第二从库的并行复制参数有没有优化。8.0下默认的并行复制配置已经不错但还可以调大从库的并行worker数量STOP SLAVE SQL_THREAD; SET GLOBAL slave_parallel_workers 8; START SLAVE SQL_THREAD;注意slave_parallel_workers这个参数在8.0里已经更名为replica_parallel_workers但老名字仍然兼容。调整后从库回放并行度变高延迟通常会快速回落。第三从库硬件是不是太差。真遇到过从库硬盘是机械盘、主库是SSD的情况回放的IO性能跟不上写入再优化参数也白搭。异步复制场景下从库的硬件配置不能和主库差距太大至少要能扛住主库的写入峰值。5.3 主库重启后复制链路异常的处理思路主库因为维护或者故障重启后从库的IO线程可能会报错连接不上或者出现“Got fatal error 1236”这类binlog读取错误。1236的原因通常是主库的binlog已经清理从库要读取的位置超出了保留范围。这个问题要从两个方向解决。第一个方向是主库的binlog保留策略。我建议把binlog_expire_logs_seconds设置成至少7天而不是默认的较短时间。这样即使故障发生从库中断几个小时主库的binlog还有足够余量让从库补上。第二个方向是从库的复制起点。如果主库的binlog已经清得太多从库无论如何追不上了只能重新对齐在主库上重新做一次全量备份导入从库重新CHANGE MASTER TO这比挖空心思去抢救一个残废的复制链路快得多。另外主库重启后务必先检查从库的IO线程是否自动重连。MySQL的IO线程在连接断开后会按master_connect_retry参数默认60秒自动重试语法上无需手动干预。但如果网络故障持续时间太长超过了主库的wait_timeoutIO线程会自动重连只要binlog未清复制就能自己恢复。观察几分钟再决定要不要手动处理这是运维的基本素质。6. 实操心得异步复制不是终点而是起点6.1 我踩过的一个延迟坑有一次搭建读写分离架构从库配置看起来一切正常复制状态也是Yes但每天跑报表时总会读到旧数据。后来排查发现主库每隔十分钟跑一次批处理任务批量更新几十万行记录生成超大binlog事务从库回放时被这个事务卡住延迟就波动在十几秒到几十秒。解决办法不是去调复制参数而是从业务侧把批量更新拆成小事务每批5000行延迟就稳住了。这个案例说明异步复制的延迟很多时候不在数据库本身而在业务的写入模式排查时要跳出数据库看整体链路。6.2 后续可以继续扩展的方向异步复制搭好之后很多玩法可以继续扩展。最常见的进阶方向是级联复制从库A再作为主库挂一个从库B形成A-B-C的链式结构适合跨机房、多地域的读扩展。另一个方向是半同步复制改造把异步复制升级为半同步在性能和一致性之间找平衡点。还有就是对主从角色切换的演练主库故障时从库提升为主库业务切换到新主库这套流程最好在测试环境多演练几次真正故障时才能从容应对。我个人始终觉得复制链路本身不是核心核心是你对这套机制的深刻理解理解到位了任何架构演进都能接得住。