前阵子有个朋友在群里问我有一个远程的 MySQL 库里面有一张表天天被业务写入我想把它实时同步到本地做报表查询不想每天手动导出导入有没有什么稳妥的办法。这个问题在数据库运维圈子里太经典了回答基本就四个字主从复制。只不过程度上有区别有些人只需要同步某一张表有些人要同步整个库还有些人要求双向同步或者多节点同步但骨架都是一样的。这篇文章我就把自己从零配置 MySQL 主从复制的完整过程和踩过的坑整理出来涵盖主库准备、从库初始化、复制链路搭建、状态监控、常见故障排查。照着做基本能搞定而且能让你不光知道怎么配还知道为什么这么配。1. 先把方案想清楚主从复制为什么能“同步到本地”1.1 对比几种常见同步方案在动手改配置之前应该先花两分钟想一想远程库的数据同步到本地到底有哪几条路可以走我见过不少人上来就问“怎么同步”其实背后的需求差别很大选错方案后面会非常难受。方案实时性对主库影响复杂度适合场景mysqldump 定时导出导入取决于定时频率通常是分钟级小配合单事务低数据量小、容忍延迟、临时同步MySQL 主从复制秒级几乎实时小额外开 binlog 日志中长期稳定的实时同步、读写分离、异地容灾Canal/Debezium 等 CDC 工具秒级小但需要解析 binlog 并维护组件较高需要把数据转发给消息队列、大数据平台或者做异构同步业务双写实时大侵入性强取决于业务只有极少数特殊场景才建议平时别碰如果你的需求就是“远程库的表实时同步到本地”而且源和目标都是 MySQL那主从复制就是性价比最高的方案。它不需要额外安装中间件MySQL 本身自带这个能力配置也不算复杂一条复制链路建立之后主库的写入会以 binlog 日志的形式持续流向从库。1.2 主从复制的核心原理很多人配主从复制的时候只记住了步骤没搞懂原理出了问题就抓瞎。我习惯用一个比方来解释主库像一个总账房先生每做一笔业务都会在自己的账本binlog上记一笔从库则是一个学徒连到账房先生那里把账本上的记录一条一条抄回自己家里然后在自己本子上重新演算一遍。拆开来看MySQL 复制涉及三个角色主库的 Binlog Dump 线程负责把 binlog 事件发送给从库。它不会主动推送给所有连接而是等从库来“要”日志。从库的 IO 线程负责连接主库接收 binlog 日志写入从本地的 relay log中继日志。从库的 SQL 线程负责读取 relay log并把里面的 SQL 事件按顺序重放到从库的数据文件中。这三个线程缺一个都不行。IO 线程出问题从库就收不到新日志SQL 线程出问题日志收到了但重放不了从库数据会一直停在某个时间点。我们后面排查问题基本上就是围绕这两个线程的状态展开的。2. 主库端配置打开日志、留好同步位点2.1 修改主库配置并重启打开主库的配置文件通常位于/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf在[mysqld]段下至少修改下面三项[mysqld] server-id 1 log-bin mysql-bin binlog_format ROWserver-id每个节点必须唯一。它是 MySQL 实例在复制链路中的身份证不能和从库重复否则两个实例会互相认错。主库写 1从库写 2依此类推。log-bin开启 binlog 并定义日志文件前缀。这一步是复制的根本没有 binlog后续一切免谈。binlog_format建议直接用 ROW。STATEMENT 模式记录的是 SQL 语句本身复制时遇到NOW()、UUID()这类非确定性函数容易产生主从数据不一致ROW 模式记录的是每行数据变更前后的值最稳妥代价是日志量稍大。现在磁盘不值钱一致性远重要于省那点空间。修改完配置后重启 MySQLsystemctl restart mysqld或者用service mysql restart取决于你的系统。2.2 创建专用的复制账号不要用 root 账号去跑复制链路。主从复制账号需要长期连接主库权限应该最小化。在主库上执行CREATE USER repl% IDENTIFIED BY YourStrongPassword; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;这里有两个权限解释一下REPLICATION SLAVE从库用这个权限来请求 binlog必须有。REPLICATION CLIENT允许查看主库状态方便后期用监控工具检查复制健康度。不加也不影响复制但我建议加上。账号的 Host 写%还是具体 IP取决于你是否想限制来源。如果从库 IP 固定写具体 IP 更安全例如repl192.168.1.10。2.3 获取同步位点这一步是新手最容易懵的地方。从库第一次连接主库时必须知道自己该从哪一条 binlog 日志的哪个位置开始拉取。先在主库上执行FLUSH TABLES WITH READ LOCK;这个命令会让所有表变成只读状态保证你在备份数据或记录位点期间主库没有新写入从而保证主从数据一致。注意这个锁必须尽快释放否则业务会写不进去。然后查看位点SHOW MASTER STATUS;输出大概长这样------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000003 | 1234 | | | | -------------------------------------------------------------------------------要记下File和Position这两个值后面从库配置MASTER_LOG_FILE和MASTER_LOG_POS必须用它。如果你用的是 GTID 复制要记录的是Executed_Gtid_Set。记完之后立刻释放锁UNLOCK TABLES;注意FLUSH TABLES WITH READ LOCK只锁表不锁元数据操作。执行时要确保当前没有长事务在运行否则要么锁不成功要么锁的时间特别长。这一步最好放在业务低峰期做。3. 从库端配置初始化数据并建立复制链路3.1 从库配置文件要点从库的配置文件同样在[mysqld]下修改[mysqld] server-id 2 read_only 1 relay-log mysql-relay-binserver-id必须和主库不同刚才已经强调过。read_only建议开启。它能让从库拒绝非超级权限账号的写操作防止有人在从库上手动改数据导致主从不一致。注意它对root这种有 SUPER 权限的账号无效。relay-log指定中继日志文件名。不设置也能跑但显式设置便于管理。3.2 用 mysqldump 拉取初始数据主从复制只能同步链路建立之后产生的新数据链路的起点之前的数据得先自己搬过去。这个“搬过去”的动作叫初始化。如果你的库全是 InnoDB 表推荐用这条命令期间基本不锁表mysqldump -hREMOTE_HOST -urepl -pYourPassword --single-transaction --master-data2 --databases your_db backup.sql参数解释--single-transaction在 InnoDB 引擎下通过一致性快照实现无锁备份。MyISAM 表不支持事务这条参数对它们没作用这种情况下还是建议在主库锁表后再导出。--master-data2自动在备份文件的头部写入当时的 binlog 文件和位置信息注释形式保存。这样你不需要手动记录位点导入后从文件里就能找到。注意--master-data本身会请求FLUSH TABLES WITH READ LOCK权限如果你用普通账号导出需要RELOAD权限。如果数据量特别大比如几百 GB 甚至上 TBmysqldump 这种逻辑备份方式就有点吃力了。更专业的手段是用XtraBackup或MySQL Enterprise Backup直接物理备份。普通场景下 mysqldump 足够。3.3 导入数据并建立复制链路把备份文件传送到从库所在机器然后导入mysql -uroot -p backup.sql注意导入的时候带上--one-database参数只导入指定库或者直接把createdatabase语句放进文件里管理。最简单的做法是文件里已经包含了建库语句直接导即可。导入完成后在从库上执行CHANGE MASTER TO语句。假设刚才SHOW MASTER STATUS得到的位点是mysql-bin.000003的1234那么CHANGE MASTER TO MASTER_HOST主库IP, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPassword, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS1234;然后启动复制START SLAVE;这里我额外说一句MySQL 8.0 官方文档里的术语已经改为SOURCE和REPLICA语法上也是CHANGE REPLICATION SOURCE TO、START REPLICA。但MASTER/SLAVE这套老语法在 8.0 里仍然兼容很多老运维还是习惯这么写。你根据自己的 MySQL 版本选择即可效果一致。如果你希望以后能自动找位点、不怕日志轮转导致位点失效可以考虑用 GTID 复制。主从两边都需要开启gtid_mode ON enforce_gtid_consistency ON开启之后CHANGE MASTER TO就不需要指定文件位点了直接写CHANGE MASTER TO MASTER_HOST主库IP, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDYourStrongPassword, MASTER_AUTO_POSITION1;GTID 复制的最大好处是切换节点、添加新从库时免去了手工找位点的麻烦主从身份转换也灵活很多。3.4 验证复制状态启动复制后第一件事就是看状态SHOW SLAVE STATUS\G重点看两个字段Slave_IO_Running是否为Yes。如果不是说明从库连不上主库或者认证失败。Slave_SQL_Running是否为Yes。如果不是说明中继日志重放出错。出现两个Yes基本就通了。然后可以在主库上随便写一条测试数据比如新建一张测试表并插入几行再去从库查能查到就是复制链路真正跑通了。4. 日常维护与进阶调优4.1 复制延迟为什么会出现很多人的主从复制“能用”但延迟动不动几十秒甚至几分钟。复制延迟的原因通常有这几个主库有大事务比如一次性UPDATE几十万行生成一条巨大的 binlog 事件从库 SQL 线程重放这条事件就得花很长时间。从库硬件差主库是高性能 SSD从库是老机械盘写压力一大SQL 线程追不上很正常。从库上有额外负载不少业务把报表查询、汇总分析压到从库上如果慢查询多会抢占 IO 资源直接拖慢 SQL 线程。最简单的检查命令SHOW SLAVE STATUS\G看Seconds_Behind_Master字段。这个值是估算值没有那么精准但足以反映趋势。如果它持续增长说明从库追不上主库。另外还要区分它显示NULL时表示 SQL 线程当前没有在跑可能是出错了也可能是刚启动还没开始重放。优化手段首选是开启并行复制。MySQL 5.7 及以后版本支持基于库级或事务级的并行回放slave_parallel_type LOGICAL_CLOCK slave_parallel_workers 4改完之后重启从库。这个配置能让 SQL 线程并行重放不同事务对写入密集场景的效果立竿见影。4.2 binlog 格式选择再聊聊我在主库配置里直接推荐了ROW这里再展开讲讲为什么。STATEMENT格式的 binlog 记录的是 SQL 语句本身比如UPDATE users SET age age 1 WHERE id 1。这类语句依赖环境变量、随机函数时从库重放出的结果可能和主库不一样比如NOW()、SYSDATE()、UUID()。一旦出现不一致数据就“脏”了。ROW格式记录的是每一行数据变更前后的具体值不依赖上下文重放结果唯一。缺点是日志体积大尤其是大批量更新时每条记录都要写全字段值。但从一致性角度讲这个代价值得掏。MIXED格式则是在两者之间自动选择简单场景用语句格式需要精确时切到行格式也可以接受。4.3 半同步复制与主库崩溃风险默认情况下的异步复制有一个隐患主库崩溃时已经提交的事务不一定已经传送到从库。也就是说你做了主从切换从库可能少了一部分数据。如果系统对数据安全要求很高可以把复制模式从异步改成半同步。半同步复制的核心逻辑是主库提交事务后必须等至少一个从库确认收到 binlog然后才返回客户端成功。这样就保证了主库崩溃时日志一定在从库手里。启用半同步需要在主库安装插件INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; SET GLOBAL rpl_semi_sync_master_enabled 1; SET GLOBAL rpl_semi_sync_master_timeout 1000;从库同样安装rpl_semi_sync_slave插件并启用INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; SET GLOBAL rpl_semi_sync_slave_enabled 1;插件安装后要记得写进配置文件否则重启后失效。还要注意rpl_semi_sync_master_timeout这个参数默认是 10000 毫秒如果从库确认迟迟不来主库会等待或超时退化回异步模式超时时间按业务容忍度设置。4.4 只复制指定表或指定库回到文章开头那个需求只要同步远程库的“这一张表”。虽然整库复制也能用但为了效率和资源利用可以在从库配置里加过滤规则。比如只想要business_db库里的orders表replicate-wild-do-table business_db.% replicate-ignore-table business_db.logs或者只复制指定库而不复制其他库replicate-do-db business_db这里有一个非常容易踩的坑replicate-do-db的过滤逻辑是依据当前默认数据库来判断的如果一条 SQL 语句跨库操作比如在other_db下执行UPDATE business_db.orders SET ...过滤规则可能不会生效结果让你惊讶。所以我个人更推荐replicate-wild-do-table这种表级通配方式逻辑更直观也不容易漏同步或误同步。5. 常见问题与排查技巧实录5.1 状态异常速查表我把自己这些年在各种主从复制事故中遇到的典型问题整理成了一张速查表遇到问题可以直接对号入座。现象可能原因快速排查方向Slave_IO_Running 显示 No主库地址、端口不通防火墙拦截账号密码错误从库上执行mysql -h主库IP -urepl -p手动测试连接Slave_SQL_Running 显示 No主从数据不一致重放时主键冲突或记录不存在SHOW SLAVE STATUS\G看 Last_SQL_Error 字段具体错误从库启动报 server-id 重复主从配置了相同的 server-id检查两台机器 my.cnfIO 线程报 1236 错误指定的 binlog 文件或位置不存在日志被清理或主动跳过确认MASTER_LOG_FILE与MASTER_LOG_POS是否正确IO 线程报 uuid 冲突从库通过复制主库数据目录方式初始化导致 auto.cnf 相同删除从库的auto.cnf重启 MySQL从库写入报 read-onlyread_only 已开启且无超级权限需要临时写入时用SUPER权限账号或临时关闭只读5.2 复制中断后的恢复思路复制中断是常态关键是不要慌。第一步永远是SHOW SLAVE STATUS\G把Last_SQL_Error字段完整读出来。大多数错误是数据不一致导致的比如1062表示主键重复1032表示记录不存在。恢复的通用思路在从库上先执行STOP SLAVE;让中断状态固定下来。分析错误信息判断是业务误操作导致的还是复制链路本身出问题。如果确认是某一条 SQL 数据不一致可以手工在从库上补齐或删除对应数据然后继续复制。如果错误可以容忍并且确实想快速恢复考虑使用SQL_SLAVE_SKIP_COUNTER跳过指定数量的错误事件。假设已经定位到错误来自一张表的数据缺失正确做法是先在主库上查这条数据去从库比对修复差异再执行START SLAVE;最忌讳的做法是直接无脑跳过错误。跳过一条容易但如果错误对应的是一个还未执行完的中间事务后面可能还有几十条关联错误等着你。5.3 跳过错误时要注意什么确实有一些场景下你可以接受跳过错误比如测试环境或者某条 DELETE 语句在从库上本来就没有对应记录。此时可以这样操作先停掉复制STOP SLAVE;跳过一条错误SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1;重新启动复制START SLAVE;跳过之后立刻看状态如果Slave_SQL_Running还是 No继续看是否报相同的错误然后重复操作。但我建议最多连续跳过三五条如果一直报错说明主从数据差异已经不是个别记录必须停下来做全量数据比对甚至重新初始化从库。这里分享一个我自己的做法从库出错后我会先把主从两边的数据分别算出 checksum比如对每张表做CHECKSUM TABLE快速判断差异范围。然后把出错那条记录前后的数据拉出来对比搞清楚是“从库缺数据”还是“从库多了数据”再有针对性地修复。这样比盲跳可靠得多。5.4 几个容易被忽略的细节主库防火墙很多 MySQL 主从配置本身没问题但主库的防火墙只放开了3306的本地访问外网访问被限制。测试连接时一定用从库去连主库而不是在主库本地localhost测试。主库bind-address如果配置里写了bind-address 127.0.0.1从库无论如何也连不上主库。需要改成0.0.0.0或具体网卡 IP。从库的sql_mode最好和主库保持一致。不一致可能导致STRICT_TRANS_TABLES之类的模式主库允许写入从库却报错拒绝。MySQL 版本差异从库版本可以比主库高但不建议比主库低。比如主库 8.0、从库 5.7这种组合很容易遇到 binlog 格式或字符集支持方面的兼容问题。时区差异如果主从机器时区不同TIMESTAMP类型的字段复制过来可能显示不一样。直接统一设置为UTC或同一个时区最省心。最后分享两个心得第一主从复制配通不难难的是后续的运维习惯。我现在的例行工作很简单每周看一次所有从库的Seconds_Behind_Master和磁盘占用每次业务发布涉及大表变更时提前确认 binlog 保留时长是否够用。养成这个习惯之后真正遇到复制中断时你手里有足够的信息去判断而不是临时翻日志。第二如果一开始只需求“把某一张表同步到本地”我建议你仍然先把整个库的复制链路搭起来再考虑加过滤规则。原因很简单过滤规则可以减少从库压力但也会让你在排查问题时少了很多参考数据。全库同步跑稳以后再按需缩小复制范围是一个更平滑的路径。复制能做的不只数据备份这一点读写分离、异地容灾、报表分流全都是同一套链路延伸出来的能力。