最近有朋友问我一个很实际的需求远程机器上有一张业务表想实时同步到本地库不要整库就要这一张表。问了一圈有人推荐用定时任务跑mysqldump增量有人说用binlog解析工具还有人直接说“干脆两边都连同一个中间件”。其实这个场景最正统、最省事的方案就是MySQL主从复制而且只配置一张表的过滤规则就能满足“只要这一张表”的要求。主从复制不是只在机房容灾、读写分离里才有用像这种远程单表实时同步的需求它一样是首选。这篇文章我会把“怎么使用MySQL主从复制把远程库的这张表同步到本地”这个需求完整拆开先讲原理和选型再说环境准备与参数配置然后给出从主库到从库的详细操作步骤最后把运行中我实际踩过的坑和排查命令整理出来。适合刚接触复制的DBA、后端开发也适合自己手上有几台机器想快速搭数据同步的同学。1. 先搞清楚你要的到底是“备份”还是“实时同步”1.1 常见同步方案怎么选很多人一听到“同步”这两个字第一反应是写个脚本定时把数据导过来这确实是最容易想到的办法。但在远程单表同步这个场景下定时任务有很明显的问题数据是“批量滞后”的不是实时的而且每次全量导出对主库的压力都不小跑在业务高峰期容易把线上拖垮。用定时任务做增量又得自己去维护binlog位点、处理重复数据做几次就知道有多痛苦。下面这张表是我在实际方案选型时习惯做的对比方案实时性主库影响运维复杂度是否适合远程单表mysqldump定时全量分钟级甚至小时级高全量扫描低不适合数据滞后严重binlog增量解析工具秒级较低高需要自己管理位点可以但要额外部署工具链MySQL主从复制秒级低只接收binlog低MySQL自带机制非常适合天然支持这里的逻辑很简单MySQL本身就内置了复制能力把主库的binlog实时传输到从库并执行底层机制成熟稳定不需要额外引入中间件也不用自己处理位点推进和断点续传问题。如果你只需要一张表那我就在从库上加过滤规则其他表一概不收完全满足需求。1.2 主从复制的核心运行原理主从复制之所以能实现“远程库的表实时同步到本地”本质是主库把每一次数据变更记录在binlog里从库的IO线程远程拉取这些日志写入自己的relay log中继日志再由SQL线程把中继日志里的变更重放到本地表上。整个过程是单向的从库不会反过来影响主库。我习惯把这套流程类比成“寄快递”主库是发货方binlog就是发货单IO线程是快递员——它负责把发货单从主库拉到从库仓库SQL线程是分拣员——按单据把货物重新摆放到货架上。任何一个环节中断比如快递员网络断了或者分拣员遇到无法处理的单据卡住了数据同步就会停在那里。理解了这两个线程的分工后面排查问题就很好定位了。这里还有一个关键点要记住从库默认会开启relay log的自动清理但binlog是否记录从库自己的操作取决于log_slave_updates参数。单层复制场景下它不影响主链路但如果你以后还想把从库继续作为下一层主库这个参数就必须打开。2. 搭建前的准备与参数设计2.1 版本差异与复制账号准备动手之前先确认主从两边的MySQL版本。官方推荐从库版本不低于主库版本比如主库是5.7从库最好也是5.7或更高如果主库是8.0从库用5.7就会出现兼容问题。另一个容易踩的坑是8.0默认的身份认证插件是caching_sha2_password老版本客户端和部分连接方式不认。我建议为复制专门创建一个账号并用mysql_native_password作为8.0下的认证插件避免复制链路建立时提示认证失败。创建账号和授权的SQL如下CREATE USER repl% IDENTIFIED WITH mysql_native_password BY YourStrongPass123; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;有人会问为什么需要REPLICATION CLIENT权限因为SHOW MASTER STATUS、SHOW SLAVE STATUS这类监控命令需要它。如果不加你在从库上查不了主库状态排障时少了一只眼睛。还有一点复制账号的host不要只给localhost因为从库在远程要用%或者主库能解析到的具体地址。当然生产环境建议收窄到从库IP。2.2 主库参数binlog是复制的地基主从复制的地基是binlog所以主库必须先确认binlog已经开启。最常见的检查方式SHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE binlog_format;如果log_bin是OFF需要修改主库配置文件并重启MySQL。同时我强烈建议把binlog_format设置为ROW。原因很简单在表级复制过滤场景下ROW格式记录的是“哪一行发生了变更”能精确到具体表STATEMENT格式记录的是SQL语句本身执行时如果不小心跨库操作容易让过滤规则失效。我们把server_id也在这里确认一下主从两台机器必须用不同的值。主库配置示例[mysqld] server_id 100 log_bin mysql-bin binlog_format ROW expire_logs_days 7expire_logs_days是日志保留策略。同步任务如果中断超过7天从库可能因为binlog已被清理而无法续传。你要根据同步的重要程度调整这个值最好设置为至少保留72小时以上留足排查和修复的时间。如果用的是MySQL 8.0expire_logs_days已经废弃改用binlog_expire_logs_seconds按秒设置。2.3 从库参数只同步一张表过滤规则怎么配从库这边除了设置独立的server_id还要配置复制过滤规则。常用的过滤参数有三个参数作用适用场景replicate-do-db只复制指定数据库按库过滤规则最简单replicate-do-table只复制指定表精确到表但有一个跨库坑replicate-wild-do-table按通配符复制表可以匹配多个表支持%通配符既然需求是“把远程库的这张表同步到本地”那直接使用replicate-do-tableremote_db.target_table是一般人会想到的方案。但这里我要重点提醒一个坑replicate-do-table在ROW格式下如果主库执行更新前没有USE目标数据库从库可能会判定这条事件不属于指定表导致不同步。更多时候我会直接推荐用replicate-wild-do-table并在主库侧固定USE目标库双保险。从库配置示例[mysqld] server_id 200 read_only ON replicate-wild-do-table remote_db.target_tableread_only ON是为了防止本地误写入数据导致复制和本地修改产生主键冲突。注意如果从库还要承担别的写入任务这个参数不能直接打开需要配合专门的管理账号。3. 详细操作步骤把远程库的这张表同步到本地3.1 步骤一主库开启binlog并确认当前状态如果主库已经开启了binlog直接跳到下一步如果没开先修改配置文件重启MySQL再继续。注意重启前先确认没有长时间运行的大事务否则重启过程会等事务回滚或提交业务会受影响。在操作主机上执行mysql -h remote_master_ip -u root -p然后确认主库状态SHOW MASTER STATUS;看到类似下面的结果说明binlog文件已经存在而且当前有坐标------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000007 | 154 | | | | -------------------------------------------------------------------------------这个坐标是后面CHANGE MASTER TO的关键。记下File和Position如果开了GTID还要留意Executed_Gtid_Set。3.2 步骤二在主库创建复制账号用前面第一节的SQL创建账号和授权。强调一下不要在从库上执行这条SQL去主库执行。很多新手把主从的职责搞反了复制账号必须在主库创建因为从库要主动连主库来拉日志。3.3 步骤三做单表初始数据同步主从复制只会复制“从某个时间点之后”的增量数据但远程库的这张表里大概率已经有存量数据了。如果不先同步存量从库复制启动后会发现目标表不存在或者数据对不上。最稳妥的单表初始化命令mysqldump -h remote_master_ip -u root -p \ --single-transaction \ --routinesfalse \ --triggersfalse \ --set-gtid-purgedOFF \ --databases remote_db \ --tables target_table \ target_table.sql--single-transaction的作用是导出过程中不加表锁利用InnoDB的一致性读保证导出的是一个快照不会锁住主库的业务写入。--set-gtid-purgedOFF是为了避免导出的SQL里带上GTID信息否则导入到从库时容易干扰复制链路的GTID匹配。然后把SQL导入本地从库mysql -h local_slave_ip -u root -p target_table.sql如果从库上本来就有这张表建议先确认表结构定义一致再决定是清空导入还是直接覆盖。3.4 步骤四配置从库过滤规则并执行CHANGE MASTER在从库配置文件里加上replicate-wild-do-table重启MySQL让参数生效。如果你不想重启数据库8.0可以用CHANGE REPLICATION FILTER动态设置5.7配置起来稍麻烦。我建议直接用配置文件规则清晰重启后也不会失效。然后执行复制链路的指定。两种方式任选其一方式A传统的binlog文件名和位点CHANGE MASTER TO MASTER_HOSTremote_master_ip, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPass123, MASTER_LOG_FILEmysql-bin.000007, MASTER_LOG_POS154;方式BGTID自动定位模式CHANGE MASTER TO MASTER_HOSTremote_master_ip, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPass123, MASTER_AUTO_POSITION1;GTID模式是从MySQL 5.6开始支持的如果两边都开启了GTID强烈建议用方式B。它的好处是位点不用人工维护复制断开了重连会自动跳转到正确位置不会因为手动填错坐标而重复报错。注意GTID模式下主从两边都必须开启gtid_mode和enforce_gtid_consistency。3.5 步骤五启动从库复制并验证启动复制START SLAVE;在8.0里命令可以写成START REPLICA;两者兼容。启动后马上检查状态SHOW SLAVE STATUS\G重点看这几项Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Last_IO_Error: Last_SQL_Error:只要IO和SQL线程都是YesLast的错误为空说明链路已经跑起来了。此时在主库往这张表插入一条测试数据等一两秒再从库查询如果能查到就说明“远程库的这张表同步到本地”已经完成。实操时我会再验证一件事主库往“非目标表”里写一条数据确认从库不会同步。这样能确认过滤规则真的生效。4. 运行中的问题排查与避坑清单4.1 最常见错误对照表主从复制跑起来不难真正麻烦的是跑起来之后出现了异常。我把实际运维中最常遇到的错误整理成一张速查表方便直接对照错误码现象常见原因处理思路1236IO线程报错拉取binlog失败主库binlog已被清理或位点超出范围重新做一次全量初始化再CHANGE MASTER1062SQL线程报主键冲突从库已有相同主键或重复初始化定位冲突行在从库删除后让复制跳过冲突重新执行1594relay log损坏从库异常断电或磁盘故障重新初始化复制链路1872从库回放失败找不到临时表使用临时表操作且复制中断后在从库重建同名临时表或跳过该事务1208从库内存不足无法建立连接主库连接数打满检查主库max_connections调整从库重连策略遇到1062这种主键冲突时我的处理方式是先看主库端对应行的数据确认从库冲突数据没有保留价值然后让SQL线程跳过冲突。命令如下STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;注意这只能跳过一条错误不适合连续报错。如果错误很多大概率是初始数据没对齐最好重新从头初始化。4.2 实操中我踩过的三个坑第一个坑是过滤规则没按预期生效。我帮一个朋友排查时发现主库执行更新时用了USE 另一个库; UPDATE remote_db.target_table SET ...在ROW格式下replicate-do-table直接就不同步了。后来我改成replicate-wild-do-table并且约定主库业务连接固定USE remote_db问题才彻底解决。千万不要小看 “SQL语句在哪个库下执行” 这件事。第二个坑是mysqldump导出时默认带了GTID信息。之前我用mysqldump默认参数把单表导入从库结果START SLAVE后SQL线程一直处于异常状态报错说GTID不连续。用SHOW VARIABLES LIKE gtid_mode一查才发现从库的GTID集合和主库不一致。加了--set-gtid-purgedOFF之后复制才正常这个参数不值钱但很多新手会漏掉。第三个坑是表结构不一致导致的复制中断。主库那张表有个字段是varchar(255)从库因为建表时疏忽建成了varchar(100)主库插入一条超长字符串后从库直接报“Data too long for column”。排查半天怎么都没想到是这个低级问题。所以初始化前务必对比SHOW CREATE TABLE结构不一致的同步链路迟早要出问题。4.3 延迟与性能问题怎么处理Seconds_Behind_Master如果持续增大说明从库回放速度跟不上主库写入速度。最典型的原因是主库出现大事务比如一次性更新几百万行binlog总量巨大从库只能串行回放。解决办法是优化主库写入逻辑把大事务拆成小批次如果实在拆不掉考虑主库业务低谷期再执行。还有一个经常被忽略的因素目标表没有主键或唯一索引。从库回放ROW格式的binlog时每条变更都要通过索引定位那行没有索引就只能全表扫性能差距是数量级的。所以建表一定给主键这不仅是业务规范问题直接决定复制能不能跟上。参数层面从库可以适当调大relay_log_space_limit和 IO线程的缓冲区。多数情况下把slave_parallel_workers打开让SQL线程并行回放也能缓解延迟。不过并行复制依赖主库的binlog格式和事务粒度不是所有版本都默认支持5.7以上一般没问题。5. 收尾前的几点体会做了这么多年MySQL运维主从复制在我手里解决的问题非常多远程单表同步、读写分离、临时分析库的数据抽取、甚至做容灾演练。但我不建议你把生产环境当成第一次试验场先在测试环境完整跑一遍这套流程确认过滤规则、权限、端口、表结构都没问题再应用到线上。如果条件允许给从库的复制链路加一个监控脚本定时检查Slave_IO_Running和Slave_SQL_Running一旦不是Yes就告警。很多复制故障都是晚上静默发生的撑到第二天早上发现时数据已经差了一大截。监控脚本不复杂用Python或者Shell定时执行SHOW SLAVE STATUS解析结果就行这比临时抱佛脚要靠谱得多。最后再分享一个小技巧初始化完成后保留好当时的SHOW MASTER STATUS输出和mysqldump生成的文件。以后万一出现1236这种断老日志的问题你至少知道当前从库是基于哪个位点搭出来的能大幅缩短排查时间。
MySQL主从复制实战：远程库单表实时同步到本地方案
最近有朋友问我一个很实际的需求远程机器上有一张业务表想实时同步到本地库不要整库就要这一张表。问了一圈有人推荐用定时任务跑mysqldump增量有人说用binlog解析工具还有人直接说“干脆两边都连同一个中间件”。其实这个场景最正统、最省事的方案就是MySQL主从复制而且只配置一张表的过滤规则就能满足“只要这一张表”的要求。主从复制不是只在机房容灾、读写分离里才有用像这种远程单表实时同步的需求它一样是首选。这篇文章我会把“怎么使用MySQL主从复制把远程库的这张表同步到本地”这个需求完整拆开先讲原理和选型再说环境准备与参数配置然后给出从主库到从库的详细操作步骤最后把运行中我实际踩过的坑和排查命令整理出来。适合刚接触复制的DBA、后端开发也适合自己手上有几台机器想快速搭数据同步的同学。1. 先搞清楚你要的到底是“备份”还是“实时同步”1.1 常见同步方案怎么选很多人一听到“同步”这两个字第一反应是写个脚本定时把数据导过来这确实是最容易想到的办法。但在远程单表同步这个场景下定时任务有很明显的问题数据是“批量滞后”的不是实时的而且每次全量导出对主库的压力都不小跑在业务高峰期容易把线上拖垮。用定时任务做增量又得自己去维护binlog位点、处理重复数据做几次就知道有多痛苦。下面这张表是我在实际方案选型时习惯做的对比方案实时性主库影响运维复杂度是否适合远程单表mysqldump定时全量分钟级甚至小时级高全量扫描低不适合数据滞后严重binlog增量解析工具秒级较低高需要自己管理位点可以但要额外部署工具链MySQL主从复制秒级低只接收binlog低MySQL自带机制非常适合天然支持这里的逻辑很简单MySQL本身就内置了复制能力把主库的binlog实时传输到从库并执行底层机制成熟稳定不需要额外引入中间件也不用自己处理位点推进和断点续传问题。如果你只需要一张表那我就在从库上加过滤规则其他表一概不收完全满足需求。1.2 主从复制的核心运行原理主从复制之所以能实现“远程库的表实时同步到本地”本质是主库把每一次数据变更记录在binlog里从库的IO线程远程拉取这些日志写入自己的relay log中继日志再由SQL线程把中继日志里的变更重放到本地表上。整个过程是单向的从库不会反过来影响主库。我习惯把这套流程类比成“寄快递”主库是发货方binlog就是发货单IO线程是快递员——它负责把发货单从主库拉到从库仓库SQL线程是分拣员——按单据把货物重新摆放到货架上。任何一个环节中断比如快递员网络断了或者分拣员遇到无法处理的单据卡住了数据同步就会停在那里。理解了这两个线程的分工后面排查问题就很好定位了。这里还有一个关键点要记住从库默认会开启relay log的自动清理但binlog是否记录从库自己的操作取决于log_slave_updates参数。单层复制场景下它不影响主链路但如果你以后还想把从库继续作为下一层主库这个参数就必须打开。2. 搭建前的准备与参数设计2.1 版本差异与复制账号准备动手之前先确认主从两边的MySQL版本。官方推荐从库版本不低于主库版本比如主库是5.7从库最好也是5.7或更高如果主库是8.0从库用5.7就会出现兼容问题。另一个容易踩的坑是8.0默认的身份认证插件是caching_sha2_password老版本客户端和部分连接方式不认。我建议为复制专门创建一个账号并用mysql_native_password作为8.0下的认证插件避免复制链路建立时提示认证失败。创建账号和授权的SQL如下CREATE USER repl% IDENTIFIED WITH mysql_native_password BY YourStrongPass123; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;有人会问为什么需要REPLICATION CLIENT权限因为SHOW MASTER STATUS、SHOW SLAVE STATUS这类监控命令需要它。如果不加你在从库上查不了主库状态排障时少了一只眼睛。还有一点复制账号的host不要只给localhost因为从库在远程要用%或者主库能解析到的具体地址。当然生产环境建议收窄到从库IP。2.2 主库参数binlog是复制的地基主从复制的地基是binlog所以主库必须先确认binlog已经开启。最常见的检查方式SHOW VARIABLES LIKE log_bin; SHOW VARIABLES LIKE binlog_format;如果log_bin是OFF需要修改主库配置文件并重启MySQL。同时我强烈建议把binlog_format设置为ROW。原因很简单在表级复制过滤场景下ROW格式记录的是“哪一行发生了变更”能精确到具体表STATEMENT格式记录的是SQL语句本身执行时如果不小心跨库操作容易让过滤规则失效。我们把server_id也在这里确认一下主从两台机器必须用不同的值。主库配置示例[mysqld] server_id 100 log_bin mysql-bin binlog_format ROW expire_logs_days 7expire_logs_days是日志保留策略。同步任务如果中断超过7天从库可能因为binlog已被清理而无法续传。你要根据同步的重要程度调整这个值最好设置为至少保留72小时以上留足排查和修复的时间。如果用的是MySQL 8.0expire_logs_days已经废弃改用binlog_expire_logs_seconds按秒设置。2.3 从库参数只同步一张表过滤规则怎么配从库这边除了设置独立的server_id还要配置复制过滤规则。常用的过滤参数有三个参数作用适用场景replicate-do-db只复制指定数据库按库过滤规则最简单replicate-do-table只复制指定表精确到表但有一个跨库坑replicate-wild-do-table按通配符复制表可以匹配多个表支持%通配符既然需求是“把远程库的这张表同步到本地”那直接使用replicate-do-tableremote_db.target_table是一般人会想到的方案。但这里我要重点提醒一个坑replicate-do-table在ROW格式下如果主库执行更新前没有USE目标数据库从库可能会判定这条事件不属于指定表导致不同步。更多时候我会直接推荐用replicate-wild-do-table并在主库侧固定USE目标库双保险。从库配置示例[mysqld] server_id 200 read_only ON replicate-wild-do-table remote_db.target_tableread_only ON是为了防止本地误写入数据导致复制和本地修改产生主键冲突。注意如果从库还要承担别的写入任务这个参数不能直接打开需要配合专门的管理账号。3. 详细操作步骤把远程库的这张表同步到本地3.1 步骤一主库开启binlog并确认当前状态如果主库已经开启了binlog直接跳到下一步如果没开先修改配置文件重启MySQL再继续。注意重启前先确认没有长时间运行的大事务否则重启过程会等事务回滚或提交业务会受影响。在操作主机上执行mysql -h remote_master_ip -u root -p然后确认主库状态SHOW MASTER STATUS;看到类似下面的结果说明binlog文件已经存在而且当前有坐标------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000007 | 154 | | | | -------------------------------------------------------------------------------这个坐标是后面CHANGE MASTER TO的关键。记下File和Position如果开了GTID还要留意Executed_Gtid_Set。3.2 步骤二在主库创建复制账号用前面第一节的SQL创建账号和授权。强调一下不要在从库上执行这条SQL去主库执行。很多新手把主从的职责搞反了复制账号必须在主库创建因为从库要主动连主库来拉日志。3.3 步骤三做单表初始数据同步主从复制只会复制“从某个时间点之后”的增量数据但远程库的这张表里大概率已经有存量数据了。如果不先同步存量从库复制启动后会发现目标表不存在或者数据对不上。最稳妥的单表初始化命令mysqldump -h remote_master_ip -u root -p \ --single-transaction \ --routinesfalse \ --triggersfalse \ --set-gtid-purgedOFF \ --databases remote_db \ --tables target_table \ target_table.sql--single-transaction的作用是导出过程中不加表锁利用InnoDB的一致性读保证导出的是一个快照不会锁住主库的业务写入。--set-gtid-purgedOFF是为了避免导出的SQL里带上GTID信息否则导入到从库时容易干扰复制链路的GTID匹配。然后把SQL导入本地从库mysql -h local_slave_ip -u root -p target_table.sql如果从库上本来就有这张表建议先确认表结构定义一致再决定是清空导入还是直接覆盖。3.4 步骤四配置从库过滤规则并执行CHANGE MASTER在从库配置文件里加上replicate-wild-do-table重启MySQL让参数生效。如果你不想重启数据库8.0可以用CHANGE REPLICATION FILTER动态设置5.7配置起来稍麻烦。我建议直接用配置文件规则清晰重启后也不会失效。然后执行复制链路的指定。两种方式任选其一方式A传统的binlog文件名和位点CHANGE MASTER TO MASTER_HOSTremote_master_ip, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPass123, MASTER_LOG_FILEmysql-bin.000007, MASTER_LOG_POS154;方式BGTID自动定位模式CHANGE MASTER TO MASTER_HOSTremote_master_ip, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPass123, MASTER_AUTO_POSITION1;GTID模式是从MySQL 5.6开始支持的如果两边都开启了GTID强烈建议用方式B。它的好处是位点不用人工维护复制断开了重连会自动跳转到正确位置不会因为手动填错坐标而重复报错。注意GTID模式下主从两边都必须开启gtid_mode和enforce_gtid_consistency。3.5 步骤五启动从库复制并验证启动复制START SLAVE;在8.0里命令可以写成START REPLICA;两者兼容。启动后马上检查状态SHOW SLAVE STATUS\G重点看这几项Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Last_IO_Error: Last_SQL_Error:只要IO和SQL线程都是YesLast的错误为空说明链路已经跑起来了。此时在主库往这张表插入一条测试数据等一两秒再从库查询如果能查到就说明“远程库的这张表同步到本地”已经完成。实操时我会再验证一件事主库往“非目标表”里写一条数据确认从库不会同步。这样能确认过滤规则真的生效。4. 运行中的问题排查与避坑清单4.1 最常见错误对照表主从复制跑起来不难真正麻烦的是跑起来之后出现了异常。我把实际运维中最常遇到的错误整理成一张速查表方便直接对照错误码现象常见原因处理思路1236IO线程报错拉取binlog失败主库binlog已被清理或位点超出范围重新做一次全量初始化再CHANGE MASTER1062SQL线程报主键冲突从库已有相同主键或重复初始化定位冲突行在从库删除后让复制跳过冲突重新执行1594relay log损坏从库异常断电或磁盘故障重新初始化复制链路1872从库回放失败找不到临时表使用临时表操作且复制中断后在从库重建同名临时表或跳过该事务1208从库内存不足无法建立连接主库连接数打满检查主库max_connections调整从库重连策略遇到1062这种主键冲突时我的处理方式是先看主库端对应行的数据确认从库冲突数据没有保留价值然后让SQL线程跳过冲突。命令如下STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;注意这只能跳过一条错误不适合连续报错。如果错误很多大概率是初始数据没对齐最好重新从头初始化。4.2 实操中我踩过的三个坑第一个坑是过滤规则没按预期生效。我帮一个朋友排查时发现主库执行更新时用了USE 另一个库; UPDATE remote_db.target_table SET ...在ROW格式下replicate-do-table直接就不同步了。后来我改成replicate-wild-do-table并且约定主库业务连接固定USE remote_db问题才彻底解决。千万不要小看 “SQL语句在哪个库下执行” 这件事。第二个坑是mysqldump导出时默认带了GTID信息。之前我用mysqldump默认参数把单表导入从库结果START SLAVE后SQL线程一直处于异常状态报错说GTID不连续。用SHOW VARIABLES LIKE gtid_mode一查才发现从库的GTID集合和主库不一致。加了--set-gtid-purgedOFF之后复制才正常这个参数不值钱但很多新手会漏掉。第三个坑是表结构不一致导致的复制中断。主库那张表有个字段是varchar(255)从库因为建表时疏忽建成了varchar(100)主库插入一条超长字符串后从库直接报“Data too long for column”。排查半天怎么都没想到是这个低级问题。所以初始化前务必对比SHOW CREATE TABLE结构不一致的同步链路迟早要出问题。4.3 延迟与性能问题怎么处理Seconds_Behind_Master如果持续增大说明从库回放速度跟不上主库写入速度。最典型的原因是主库出现大事务比如一次性更新几百万行binlog总量巨大从库只能串行回放。解决办法是优化主库写入逻辑把大事务拆成小批次如果实在拆不掉考虑主库业务低谷期再执行。还有一个经常被忽略的因素目标表没有主键或唯一索引。从库回放ROW格式的binlog时每条变更都要通过索引定位那行没有索引就只能全表扫性能差距是数量级的。所以建表一定给主键这不仅是业务规范问题直接决定复制能不能跟上。参数层面从库可以适当调大relay_log_space_limit和 IO线程的缓冲区。多数情况下把slave_parallel_workers打开让SQL线程并行回放也能缓解延迟。不过并行复制依赖主库的binlog格式和事务粒度不是所有版本都默认支持5.7以上一般没问题。5. 收尾前的几点体会做了这么多年MySQL运维主从复制在我手里解决的问题非常多远程单表同步、读写分离、临时分析库的数据抽取、甚至做容灾演练。但我不建议你把生产环境当成第一次试验场先在测试环境完整跑一遍这套流程确认过滤规则、权限、端口、表结构都没问题再应用到线上。如果条件允许给从库的复制链路加一个监控脚本定时检查Slave_IO_Running和Slave_SQL_Running一旦不是Yes就告警。很多复制故障都是晚上静默发生的撑到第二天早上发现时数据已经差了一大截。监控脚本不复杂用Python或者Shell定时执行SHOW SLAVE STATUS解析结果就行这比临时抱佛脚要靠谱得多。最后再分享一个小技巧初始化完成后保留好当时的SHOW MASTER STATUS输出和mysqldump生成的文件。以后万一出现1236这种断老日志的问题你至少知道当前从库是基于哪个位点搭出来的能大幅缩短排查时间。
