MySQL异步复制实战：从主从配置到高可用架构详解

1. 项目概述与核心需求1.1 什么是MySQL异步复制先说说我为什么折腾这个事儿。去年公司有个业务系统每天要出好几份报表但主库扛着在线交易查询稍微多点就慢得让人血压升高。当时团队里有人提议直接在主库上做读写分离但DBA老哥坚决不同意理由很简单报表查询全是聚合扫描一跑起来能把主库的IOPS吃光业务直接跟着遭殃。后来我们决定上MySQL主从复制把报表查询全部打到从库上。而这个方案最核心的机制就是异步复制。所谓异步复制指的是主库提交事务和从库应用日志之间不是同步等待的关系。主库在提交一个事务之后会把这个事务写进自己的二进制日志binlog然后立刻返回给客户端“提交成功”至于从库什么时候来拉取binlog、什么时候把变更应用到自己的数据文件里主库完全不关心。整个过程有三个线程在配合主库上的Binlog Dump线程、从库上的I/O线程和SQL线程。I/O线程负责去主库拉binlog日志把它存到本地的中继日志relay log里SQL线程负责读取relay log并顺序执行最终把变更落到从库的数据上。这种“主库不等从库”的设计换来的是主库几乎零额外开销写入性能基本不受复制影响。但代价也很直接从库的数据可能落后主库如果在主库提交后、从库应用前主库发生了故障从库可能丢数据。这一点在做异步复制之前必须想清楚——它不是强一致方案而是最终一致方案。1.2 异步复制解决了什么场景问题我见过不少朋友一上来就问“MySQL复制是不是就是备份”这话对了一半。异步复制确实能起到备份的部分作用但它的核心价值在于分流和隔离。以我们当时的处境为例主库负责交易写入从库负责报表查询和数据分析。通过复制从库拿到了主库近乎实时的数据快照报表延迟基本控制在几百毫秒到几秒内对于日常的运营报表完全够用。更重要的是我们把那些慢查询、大查询、临时统计全部甩给了从库主库的压力瞬间降了一大截业务的写入响应时间也稳定了不少。异步复制还有一类典型场景是“异地容灾的弱一致版本”——比如在另一个机房放一台从库每天定期拉取主库的数据变更。虽然严格来说异步复制在故障切换时可能丢事务但配合binlog的保留策略和周期性全量备份至少能做到“丢少量数据但整体可用”比没有强太多。另一类常见用法是做“只读从库”给开发人员和数据分析师查询避免他们手一抖执行个全表update把生产库搞挂。当然如果你需要强一致比如金融类支付系统异步复制就不合适了那得考虑半同步复制或组复制。但如果你只是做读写分离、报表分析、轻量级容灾异步复制是性价比最高的选择。1.3 示例的整体架构设计这篇博文我用一个具体例子来讲一台主库Master和一台从库SlaveMySQL版本用8.0操作系统用Linux。主库上有业务库app_db里面有订单表orders和用户表users我们要把这两个库复制到从库。主库的IP是192.168.10.10从库的IP是192.168.10.20。在开始动手之前我建议先把整个复制链路画一遍主库开启binlog指定server-id创建复制专用账号对主库做一次全量备份把这个备份恢复到从库从库配置server-id和relay log路径用CHANGE MASTER TO指定主库地址、账号、binlog文件名和位置启动从库的I/O线程和SQL线程用SHOW SLAVE STATUS检查状态。这个流程看起来简单但实际踩坑的地方不少。比如binlog格式选什么、sync_binlog怎么配、从库的read_only要不要开、GTID和坐标复制选哪种——这些细节如果不提前定好后面排查问题会很痛苦。2. 环境准备与基础配置2.1 主库配置my.cnf先看主库的/etc/my.cnf需要修改哪些参数。我直接给出一份可以使用的配置片段并解释每个参数的意义[mysqld] server-id 1 log-bin mysql-bin binlog-format ROW sync_binlog 1 binlog_row_image FULL expire_logs_days 7 max_binlog_size 256Mserver-id整个复制集群里唯一标识主从必须不同。这里主库设1。log-bin开启二进制日志文件名前缀为mysql-bin。binlog-format强烈建议用ROW。早期MySQL有STATEMENT和MIXED但STATEMENT在出现非确定性函数比如NOW()、UUID()时主从数据可能不一致。ROW格式记录的是“哪一行变成了什么”复制结果更可靠虽然日志体积会大一些但值得。sync_binlog 1每次事务提交都把binlog刷到磁盘。如果设为0MySQL依赖操作系统刷盘宕机时可能丢binlog事件进而导致从库丢数据。这个参数在复制场景里建议设为1。binlog_row_image FULL记录整行变更。MySQL 8.0默认就是FULL显式写出来是为了明确行为。expire_logs_days 7binlog保留7天。这个要看实际需求如果你要搭新的从库旧binlog不够用就得先做一个全量备份。max_binlog_size 256M每个binlog文件的最大体积超过会滚动生成新文件。配置完之后记得重启MySQL。另外我要特别提醒一个容易忽略的点如果主库上之前就有数据而你开了binlog那么复制开始的位置应该从“全量备份时刻”开始而不是从明显最早的binlog位置开始。后面我会在备份环节细说。2.2 从库配置从库也需要配置/etc/my.cnf但和主库有差异[mysqld] server-id 2 log-bin mysql-bin relay-log relay-bin relay-log-index relay-bin.index read_only ON skip_replica_start ONserver-id2不能和主库或其他从库冲突。从库也可以开log-bin这通常是因为你要做“级联复制”或者从库还需要继续往下游分发数据。如果从库只是纯查询不开binlog也可以节省磁盘。relay-log指定中继日志路径和文件名前缀。默认情况下MySQL会用主机名生成文件名但建议显式指定否则主机名变更后容易出问题。read_only ON从库只读。这个参数会禁止普通账号执行写操作但不会禁止超级管理员账号。这算是一道保险防止有人误连从库更新数据导致复制链路中断。skip_replica_start ONMySQL 8.0中这个参数让从库实例启动后不自动开始复制线程。对DBA来说这意味着你可以先把数据恢复好、检查一下主从配置然后再手动启动复制避免启动即报错。这里要注意MySQL 8.0中废除了skip-slave-start变成了skip_replica_start。如果你还在用5.7记得参数名不同。2.3 创建复制账号并授权在主库上创建一个专门用于复制的账号。不要用业务账号也不要给过大的权限只给REPLICATION SLAVE和REPLICATION CLIENT即可CREATE USER repl_user192.168.10.% IDENTIFIED BY YourPassword123; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl_user192.168.10.%; FLUSH PRIVILEGES;这里192.168.10.%限定了来源网段降低账号滥用风险。REPLICATION SLAVE权限用于让从库拉取binlog。REPLICATION CLIENT权限用于执行SHOW REPLICA STATUS等复制监控命令。我见过有人图省事直接GRANT ALL这完全没必要。复制账号只需要拉日志和看状态权限越大风险越大尤其当从库被攻破时权限越界的影响面会更大。3. 异步复制核心原理与配置示例3.1 binlog与relay log的角色先简单理一理binlog和relay log的关系。主库上的binlog是复制的源头MySQL会把所有数据变更按照提交顺序追加到binlog中。从库的I/O线程连上主库后会请求“从某个binlog文件的某个位置开始之后的所有事件”拿到事件后先写到本地relay log。SQL线程再顺序执行relay log里的SQL或行变更更新从库数据。整个过程用了“日志接力”好处是I/O线程只需要负责网络传输和写文件速度可以很快SQL线程则负责本地应用速度取决于从库硬件和SQL复杂度。I/O线程和SQL线程是异步的所以可能出现relay log堆积但这也是复制延迟的主要来源之一。在ROW格式下binlog事件里的不是原始SQL而是“表名每行的前后镜像”。比如主库执行UPDATE orders SET status2 WHERE id100binlog里记录的是这一行的完整before image和after image。SQL线程拿到之后会在从库上执行“在id100这一行上把status从1改成2”的操作。这种基于行事件的复制天然避免了很多SQL语句层面的不确定性问题也是我推荐ROW格式的原因。3.2 经典异步复制配置步骤先说基于binlog坐标的复制这是最经典也最透明的方式。第一步在主库上执行FLUSH TABLES WITH READ LOCK;让主库短暂只读。然后执行SHOW MASTER STATUS;拿到当前binlog文件名和位置。举例输出File: mysql-bin.000031 Position: 134845第二步另开一个终端用mysqldump做全量备份mysqldump -uroot -p --single-transaction --master-data2 --databases app_db app_db.sql这里用--single-transaction是因为InnoDB支持一致性快照不需要锁表在不影响业务的情况下拿到一个一致性的数据快照。--master-data2会在dump文件中自动写入当时主库的binlog文件名和位置以注释形式存在第24行左右即使你刚才漏看了SHOW MASTER STATUS也能从dump文件里找到。第三步把dump文件传到从库并执行恢复mysql -uroot -p app_db.sql恢复完成后从库的数据应该和主库在备份时刻的数据一致。第四步解锁主库UNLOCK TABLES;第五步在从库上配置复制源CHANGE MASTER TO MASTER_HOST192.168.10.10, MASTER_PORT3306, MASTER_USERrepl_user, MASTER_PASSWORDYourPassword123, MASTER_LOG_FILEmysql-bin.000031, MASTER_LOG_POS134845;新手很容易把MASTER_LOG_FILE和MASTER_LOG_POS写成SHOW MASTER STATUS看到的当前值但要注意这两个值必须来源于“dump备份时刻”。如果你dump的时候指定了--master-data2就去看dump文件头部的注释如果你刚才已经FLUSH TABLES WITH READ LOCK并记录下来了就用那个。第六步启动复制START SLAVE;在MySQL 8.0中START SLAVE虽然还能用但官方更推荐START REPLICA语义一致。如果你在8.0.22之后使用控制台可能会提示你改用START REPLICA。3.3 验证复制状态启动之后必须检查状态最关键的几个字段SHOW REPLICA STATUS\G重点看这几项Slave_IO_Running: 应为Yes表示I/O线程连接主库正常。Slave_SQL_Running: 应为Yes表示SQL线程应用relay log正常。Seconds_Behind_Master: 从库落后主库的秒数0表示基本追平。Last_IO_Errno/Last_IO_Error: I/O线程的错误码和错误信息。Last_SQL_Errno/Last_SQL_Error: SQL线程的错误码和错误信息。Master_Log_File和Read_Master_Log_Pos: 表示I/O线程已读取到主库binlog的哪个位置。Relay_Master_Log_File和Exec_Master_Log_Pos: 表示SQL线程已应用了哪个binlog位置。如果两个Running都是YesSeconds_Behind_Master一直下降说明复制构建成功。然后回到主库做一笔简单的测试写入USE app_db; INSERT INTO orders(order_id, user_id, status, amount) VALUES(1, 100, 0, 99.00);稍等一两秒在从库上查询SELECT * FROM orders WHERE order_id1;如果能看到刚才插入的记录说明异步复制已经跑通整个过程从业务角度看就是“主库写完从库最终也能看到”。3.4 从“坐标复制”到“GTID复制”的升级建议如果你是从零开始搭建我其实更推荐直接用GTID复制。GTIDGlobal Transaction Identifier是每个事务在全局范围内唯一的ID由source_id:transaction_id组成。使用GTID后你不需要再关心binlog文件名和位置从库会自动根据GTID集合来确认自己该从哪里继续拉取。配置GTID复制主从的my.cnf都要额外加gtid_mode ON enforce_gtid_consistency ON然后从库上使用CHANGE MASTER TO ... MASTER_AUTO_POSITION 1;不再需要指定MASTER_LOG_FILE和MASTER_LOG_POS。这种方式对人为配置错误更宽容主从切换时也更方便。异步复制和GTID并不冲突GTID只是替代了“文件名位置”的定位方式底层依然是异步复制机制。4. 常见问题与排查技巧实录4.1 主从数据不一致问题最常见的问题就是主从数据不一致。发生不一致的原因很多但在我踩过的坑里有几种典型情况。一种是主库的binlog格式用了STATEMENT。比如你在存储过程里用了UUID()或者用了不稳定的LIMIT更新这类语句在从库执行时会因为数据环境不同产生不同结果。解决方法是改成ROW格式并重跑一次数据校验。另一种是手动在从库上执行了写操作。虽然设置了read_only但如果你用了超级管理员账号照样能写进去。曾经有同事用root在从库执行了一条UPDATE结果SQL线程应用同一条变更时发现行不存在复制直接停了。这种情况只能通过重新对齐主从数据来解决。还有一种是升级或迁移过程中的版本差异。比如主库是5.7从库是8.0有些数据类型默认值变了行格式变了复制就可能出岔子。我建议尽量减少跨大版本的复制组合实在避不开时先做详细测试。数据不一致的排查思路是先对比主从关键表的CHECKSUM TABLE找到差异表再对比具体行的数据最后分析是哪一类操作导致差异。如果差异难以手工修复最简单的办法是重建从库——用mysqldump或者物理备份重新恢复再次配置复制。短期修复可以用pt-table-checksum和pt-table-sync这类工具但注意这类工具在高版本MySQL上兼容性需要验证。4.2 网络延迟与复制延迟异步复制的主库不会等待从库所以网络抖动不会影响主库写入但会导致Seconds_Behind_Master飙升。我遇到过从库机房网络带宽被打满relay log堆积了几百MB复制延迟变成了几分钟。排查延迟要看几个维度I/O线程是否正常Read_Master_Log_Pos是否一直在增长。如果增长缓慢说明网络传输是瓶颈可以从主库执行SHOW PROCESSLIST看Binlog Dump线程的状态。SQL线程是否正常Exec_Master_Log_Pos是否落后于Read_Master_Log_Pos。如果I/O已经追平但SQL线程应用慢那就是从库执行效率的问题。从库的磁盘写入速度。relay log的写入是连续写如果磁盘IO饱和也同样会拖慢复制。一个容易忽略的问题是大事务对异步复制的延迟冲击非常明显。比如你在主库执行了一个批量DELETE删掉几百万行这个操作在事务提交时才会写入binlog但从库上要一条一条执行执行时间可能比主库还长。要避免这种问题最好把大事务拆成小批次每批几千行这样主从延迟会被摊平而不是一次性炸开。4.3 锁与并发冲突从库上如果有长查询比如一个报表要跑20分钟它会在某个表上持有共享读锁。此时SQL线程需要应用一个针对该表的UPDATE或DELETE行变更时发现拿不到锁就会等待。这种情况下Seconds_Behind_Master会不断增加从库监控界面上看起来像是“复制停了”。排查方法是登录从库执行SHOW PROCESSLIST看SQL线程的状态。如果它的状态是Waiting for table metadata lock或者Waiting for table level lock那就说明被长查询阻塞了。解决方案有三种杀掉长查询进程让复制先追上来优化查询减少锁持有时间如果报表可以接受延迟那就等它跑完。在生产环境我倾向于用第三种但需要业务方确认数据延迟在允许范围内。另外从库上建议把innodb_flush_log_at_trx_commit调到2这样SQL线程应用事务时不用每次都刷盘可以减少IO压力代价是异常断电时可能丢一点relay log里已应用的事务但复制链路本身有重试机制影响有限。4.4 复制中断与错误跳过SQL线程报错是家常便饭。最常见的错误类似Cannot execute statement: impossible to write to readonly ...或者Error Duplicate entry或者Could not execute Update_rows event ...。这些通常都是因为从库出现了人为写入、初始数据不一致、重复执行等原因。手动跳过错误的方式是STOP REPLICA; SET GLOBAL sql_slave_skip_counter 1; START REPLICA;sql_slave_skip_counter1表示让SQL线程跳过当前正在执行的下一个事务。这里要注意“事务”粒度不是一个事件或一条SQL。如果你遇到的是一个事务内部多条事件跳过整个事务可能会让更多变更丢失所以只能在确认这个错误事务无足轻重时使用。更稳妥的做法是在CHANGE MASTER TO时指定MASTER_IGNORE_SERVER_IDS或者给复制过滤规则replicate-ignore-db。但本质上来讲跳过错误只是“续命”根因必须查清楚。我建议每跳过一个错误都要记录时间、原因、跳过的事务边界后续做一次主从数据校验。在MySQL 8.0中还支持基于GTID的gtid_executed集合查询你可以通过对比主从的gtid_executed来判断两端到底差多少事务定位更精准。4.5 常见问题速查表现象可能原因排查与处理方法Slave_IO_RunningNo网络不通、账号权限不足、server-id重复检查Last_IO_Error测试主从连通性确认账号权限Slave_SQL_RunningNo从库错误数据、主从初始数据不一致查看Last_SQL_Error修复数据或重建从库Seconds_Behind_Master持续增长大事务、从库慢查询、磁盘性能差分批执行大事务优化从库查询检查IO复制中断后无法继续relay log损坏通过SHOW RELAYLOG EVENTS检查必要时重置复制再重连主从数据不一致但无报错ROW格式未生效或手动从库写入开启ROW格式重建从库限制从库写权限5. 从异步复制到生产级高可用的扩展5.1 半同步复制真的比异步更强吗很多人在做完异步复制后会追求更高级的可靠性于是开始研究半同步复制。半同步复制Semisynchronous Replication的机制是主库提交事务时需要等待至少一个从库确认“我已经收到了binlog事务”并写入relay log然后才给客户端返回提交成功。注意这里只要求“收到”不要求“应用完成”所以从库依然可以落后于主库但至少能保证事务不会在传输过程中丢失。半同步复制和异步复制相比损失了一部分主库的写入延迟因为多了一次网络往返。在跨机房场景下这个延迟可能被放大。但如果你的业务对“最多丢一条事务”都无法容忍半同步比较合适。实际项目中我通常建议主从同机房、对一致性要求高的核心链路可以开半同步跨机房、业务允许最终一致用异步就行。5.2 基于GTID复制的高可用切换异步复制加上GTID之后主从切换的复杂度会大幅下降。传统坐标复制在切换时需要从库知道“新主库”的binlog位置这个位置很难精确获取容易导致数据丢失或重复。而GTID模式下新主库的gtid_executed集合明确记录了自己已经执行过哪些事务其他从库连接过来时会自动请求缺失的增量事务。搭建GTID复制并支持切换通常配合MHAMaster High Availability或者MySQL Shell的InnoDB Cluster。MHA本身不支持GTID的自动切换所以现在更多人直接用mysqlsh搭InnoDB Cluster或者用Orchestrator。异步复制是这些高可用方案的基础所以把异步复制跑稳是第一步。我在一次演练中还发现切换后旧的从库如果还连着旧主库会产生脑裂风险。所以生产环境必须配一套监控和仲裁机制比如用Consul或脚本定时检测主从状态。这个扩展话题比较大这里不展开但你要知道异步复制绝不是终点而是迈向高可用的起跑线。5.3 复制监控与告警建议最后说几个监控指标我会在Zabbix或Prometheus里对它们持续采集Slave_IO_Running和Slave_SQL_Running这两个是0/1状态量只要变成0就要告警。Seconds_Behind_Master建议设阈值比如超过30秒触发告警。但这个值在从库追上主库后可能会变成0如果从库刚好没有业务流量时它是比较准的如果持续在高位就要排查大事务或慢查询。Master_Log_File和Exec_Master_Log_Pos用这两个值计算延迟日志量比Seconds_Behind_Master更稳定因为它不受墙钟时间影响。relay log的总大小监控目录下的relay-bin.*文件大小总和超过一定值说明I/O线程拉取速度远高于SQL线程应用速度。此外每个从库都要配置一个“复制心跳检查”脚本最简单的是在从库执行SELECT NOW() - INTERVAL seconds_behind_master SECOND;或者直接读取SHOW REPLICA STATUS里的Seconds_Behind_Master字段然后通过自定义脚本推送告警。这比用外部工具轮询更直接因为MySQL元数据都在这里。6. 实操过程中的踩坑复盘6.1 备份位置对不上的常见问题我第一次搭异步复制时犯过最蠢的错误就是忘了在mysqldump之前记录主库位置。当时直接在业务高峰期跑备份备份完成后再去看SHOW MASTER STATUS以为这就是起始位置结果从库从错误的位置开始拉取导致部分事务重复执行复制链路上报了大量主键冲突最后花了两个小时重建从库。现在我的做法是永远在dump命令里加--master-data2然后从dump文件头部读取位置。这个位置是mysqldump在开始时刻自动获取的它和备份数据是一致性快照的产物不会错。即使我手动FLUSH TABLES WITH READ LOCK也不需要再人工看SHOW MASTER STATUS了。还有一个细节dump阶段如果使用了--single-transaction --master-data2那么在备份过程中主库的DDL操作可能会影响一致性。所以高并发业务下建议在低峰期做备份或者在备份前确认没有数据库结构变更。6.2 从库开启binlog导致磁盘爆满从库如果开了log-bin又不注意清理策略磁盘很容易满。我见过一台从库的binlog在每天高写入下增长几十GB结果数据库直接宕掉连带着复制也停了。如果从库不是级联复制的上游只是纯查询从库建议把log-bin关掉。如果确实需要开记得同样要设置expire_logs_days或者binlog_expire_logs_seconds并且定期检查磁盘空间。6.3 账号建了但权限不足REPLICATION SLAVE和REPLICATION CLIENT都要给不然从库能连上但是拉不了binlog或者在SHOW REPLICA STATUS时看不到信息。我给权限时通常会顺手验证一下SHOW GRANTS FOR repl_user192.168.10.%;避免出现少了权限还要回头看半天日志的情况。6.4 复制延迟与从库查询冲突的平衡从库上如果跑报表和分析一定要评估查询对复制的影响。我在一次运营大促前给从库加了一个索引结果DDL操作导致复制SQL线程阻塞了差不多10分钟。原因是MySQL的在线DDL在从库上不能完全在线执行它需要获取元数据锁。从那以后我对从库的任何DDL都格外谨慎要么在低峰期执行要么先取消复制线程再执行执行完再启动复制。当然这步操作要配合监控否则漏掉一段时间复制恢复会积累大量延迟。7. 写在最后的一些体会每次搭完一套异步复制我都会反复给团队讲一个道理复制不是备份复制只是数据分发工具。你可以在从库上做查询、做分析、做阶段性容灾但不能把它当成“主库数据的绝对镜像”。异步复制天然存在延迟和潜在丢失所以真正可靠的架构必须搭配定期的全量备份、binlog保留策略、监控告警和定期的数据校验。我自己在实际操作中的体会是MySQL异步复制最难的环节不是配置而是“稳定运行”。配置一天能完成但稳定运行需要应对业务变化、硬件故障、版本升级、网络抖动等一连串问题。所以最终留下来的经验就是先保证主库binlog安全落地再从库快速拉取再到从库可靠应用每一环都要有监控和应急手段。最后再分享一个小技巧如果你需要做多从库建议每个从库都从同一个全量备份来恢复初始数据而不是从另一台从库去“克隆”因为从库的relay log状态可能不一致容易留下隐患。基于同一个备份搭建所有从库再各自通过GTID自动同步到最新是既可靠又省事的做法。