做了几年数据库运维MySQL主从复制这套东西几乎每天都在碰。从早期某个项目只有一台库撑读写到后来需要容灾备份再到写压力分散到两台主库一主一从和双主双从这两种架构我前后搭了不下十次。回头看这两种模式本质上并不复杂核心就一句话——把主库的binlog同步到从库去重放。但真到操作环节参数配置、账号权限、数据一致性检查、故障切换每一个细节都能把人绊住。这篇内容适合两类读者一类是刚接触MySQL复制、想搞清楚一主一从怎么搭的新手另一类是已经搭过基本主从、想了解双主双从如何规划、有哪些坑的运维或后端同学。我会结合自己实际搭建过程中的操作记录把两种架构的方案选型、原理、具体步骤和踩坑经验都过一遍。1. 先想清楚要哪种架构一主一从和双主双从分别治什么病很多人一上来就问怎么搭双主双从但实际上连一主一从都还没想明白。数据库架构最怕跟风先搞清楚业务要解决什么问题再选架构顺序不能反。1.1 一主一从成本最低的读写分离高可用入门形态一主一从的定位很明确——一台主库负责读写一台从库实时同步主库数据。它解决两件事第一把读流量从主库分流出去比如报表查询、统计分析、后台管理系统这类重读轻写的业务挂到从库上能明显减轻主库压力第二当主库宕机时从库可以作为临时只读库顶上去或者手动提升为新主库缩短业务不可用时间。它的适用场景也很清晰中小规模业务、单机写入压力不大、读多写少、团队还在起步阶段、没有专职DBA。这类场景下一主一从在成本和收益之间是最划算的。一台普通服务器就能当从库配置几条同步参数跑起来之后日常只需要看一下复制状态有没有中断。但它的局限也要说透从库只有一个读流量大了它自己也会变成瓶颈主库如果彻底挂了从库手动提升需要一定操作时间这期间业务写不可用更重要的是如果主库已经写入了一些从库还没来得及同步的数据切换时这些数据会面临丢失风险。1.2 双主双从写给写压力不集中但又丢不起数据的场景双主双从字面理解就是两台主库互相把对方当从库每台主库下面再挂一台从库。这套架构通常出现在写流量已经分散到两个节点、或者机房容灾有双活诉求的项目里。它解决的核心问题是单点写入瓶颈和高可用切换速度。两台主库都可以接收写请求从A库写入的数据实时同步到B库从B库写入的数据也实时同步到A库两边数据保持一致。当其中一台主库出现故障时另一台主库立刻接管全部写请求对外几乎没有感知。每台主库背后再各挂一台从库用于分担读压力同时也为故障时提供更多冗余。需要强调的是双主双从不是用来扛两台主库同时写同一张表的同一行这种并发冲突的。它更适合通过业务分流把不同业务模块或不同用户群体的写请求分配到不同主库。比如A库承载订单写入B库承载用户写入两边数据通过复制实现最终一致。如果两个主库同时对同一行数据做更新就会出现主键冲突或数据覆盖的问题后面我会专门讲到这个坑。选型建议就一条绝大多数项目从一主一从开始就够了等确实出现单库写压力瓶颈、或机房容灾有双活要求再考虑升级到双主双从。架构每复杂一层运维成本和故障排查难度都翻一倍。1.3 双主双从的最终拓扑长什么样动工之前先把图画清楚。我常用的规划是主库A (server_id1) ----互相同步---- 主库B (server_id2) | | 从库A1 (server_id101) 从库B1 (server_id102)主库A和主库B之间建立双向复制从库A1只挂在主库A下面从库B1只挂在主库B下面。这样任何一个主库挂掉另一台主库马上接管写从库挂掉只需要重新搭建那一台不影响其他节点。IP和端口建议也提前约定好比如主库A用192.168.56.10:3306主库B用192.168.56.11:3306从库分别用192.168.56.101和192.168.56.102避免后面配置CHANGE MASTER时到处找IP。2. 复制机制核心binlog、relay log和那三个线程都在忙什么不管一主一从还是双主双从底层都是同一套复制机制。搞清楚它配置的时候就不是照着抄而是真正理解每一步在干什么了。2.1 binlog与relay log的角色分工MySQL的复制依赖两个日志——主库的binlog二进制日志和从库的relay log中继日志。主库上所有会改变数据的操作INSERT、UPDATE、DELETE、DDL等都会按顺序记录到binlog。从库的IO线程把主库binlog拉过来写到本地的relay log里然后SQL线程按顺序读取relay log在从库本地重放这些操作。所以从库数据等于主库binlog的回放结果。这里有个容易误解的点relay log不是从库自己的binlog。从库也可以同时开启binlog那是为了给从库的下游节点比如它的从库提供复制源或者将来把从库提升为主库时能继续向外同步。一主一从的从库其实可以不开binlog但双主双从里的每台主库必须开binlog因为它们的互为对方提供同步源。binlog有两种常用格式STATEMENT记录SQL语句和ROW记录每一行数据的变化。MySQL 5.7开始默认就是ROW格式也最推荐使用ROW——主从两边执行同样的语句可能因为索引、函数、临时表等产生不同结果ROW格式直接从行级复制数据变化一致性最好。某些特殊操作还需要MIXED格式但日常业务直接选ROW没毛病。2.2 异步复制、半同步复制与全同步复制的取舍复制还有个重要参数主库写入binlog后要不要等从库确认收到再向客户端返回提交成功默认是异步复制——主库提交事务写入binlog后立即返回成功不管从库有没有拿到这份binlog。性能最好但主库突然宕机时从库可能缺一小段数据这就是复制延迟导致的数据丢失窗口。于是有了半同步复制semisync。它的逻辑是主库提交事务后至少等一个从库确认已经收到并写入了relay log才给客户端返回成功。这样主库宕机时从库手里至少有你返回成功之前的所有数据丢失窗口大幅缩小。代价是写入RT升高因为多了一次主从之间的网络往返。全同步复制则要等所有从库都确认才返回性能代价太大生产环境基本不用。实际工作中能不能接受异步复制丢那几百毫秒的数据直接决定了你要不要开启半同步。金融、订单、支付类项目强烈建议开半同步日志、报表类业务可以接受异步。2.3 位点复制与GTID复制新老方案的差别在哪里MySQL 5.6之前从库定位同步进度靠的是文件名位点即主库的binlog文件名和文件中的偏移位置position。比如CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154。这种方式比较脆弱因为一旦主库binlog被清理、或手工指定错位点复制就会错乱。MySQL 5.6开始引入GTID全局事务标识符每个事务在全局有一个唯一编号。主从之间只要都开启了GTID模式从库通过MASTER_AUTO_POSITION1自动跟主库对齐事务位置不需要手工找位点。这条配置我从5.7一路用到8.0体验就是省心太多——重建从库、切换主从都简单了。如果从现在开始搭建新环境直接上GTID模式别犹豫。如果你接手的是老环境还在用位点复制升级到GTID也值得做一次后面故障切换和扩容会轻松很多。3. 一主一从搭建全流程从空服务器到跑通复制先聊我实际搭过的那套环境两台虚拟机都是CentOS 7.9装的MySQL社区版8.0.40主库IP 192.168.56.10从库IP 192.168.56.20。下面把每个步骤原原本本过一遍。3.1 环境准备与基础参数配置两台机器装好MySQL之后先改配置文件。主库上/etc/my.cnf增加[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON binlog_expire_logs_seconds 604800 max_binlog_size 256Mserver_id整个复制拓扑里每个节点的唯一标识绝不能重复。log_bin开启binlog文件名的前缀是mysql-bin。gtid_mode和enforce_gtid_consistency开启GTID模式的两把钥匙建议一起设置。从库上增加[mysqld] server_id 2 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON relay_log relay-bin relay_log_recovery 1注意从库这里也开了binlog我的习惯是给从库也留恢复冗余将来它提升为新主库时不用再次改配置再重启一遍。relay_log_recovery1是让从库崩溃重启后自动丢弃损坏的relay log并从主库重新拉取这个参数很重要。改完后两台分别重启MySQL然后确认GTID已经开启SHOW VARIABLES LIKE gtid_mode;返回值应该是ON。这一步检查常常被跳过但一旦没开后面CHANGE MASTER直接报错还得回来改配置重启浪费时间。3.2 在主库创建复制专用账号并锁定数据复制账号的权限和业务账号要分离只给REPLICATION SLAVE权限不要给业务库的读写权限。我在主库上执行CREATE USER repl192.168.56.% IDENTIFIED BY Repl_2024!Pass; GRANT REPLICATION SLAVE ON *.* TO repl192.168.56.%; FLUSH PRIVILEGES;这里的192.168.56.%是允许从库所在网段连接比直接写%安全得多。如果你从库IP不是固定的这里可以用具体的从库IP。然后给全库加全局读锁保证备份期间数据不变化取得一个一致的备份位点FLUSH TABLES WITH READ LOCK;注意执行完这条主库上所有写操作都会被阻塞所以这个锁要尽快释放不要让业务长时间卡住。如果你的业务表全是InnoDB可以用另一种方式mysqldump带上--single-transaction --master-data2利用InnoDB一致性快照备份不需要长时间锁表。但为了保险一些老库还是建议FLUSH一把。3.3 备份导入与CHANGE MASTER配置主库上做备份mysqldump -uroot -p --single-transaction --master-data2 --default-character-setutf8mb4 -A /tmp/backup.sql--master-data2会把主库当前的binlog文件名和位点信息以注释形式写进备份文件头。备份完成后立刻释放读锁UNLOCK TABLES;把backup.sql传到从库导入mysql -uroot -p /tmp/backup.sql如果你想跳过备份直接搭建前提是两组副本之间已经通过其他方式初始化了完全一致的数据否则后续同步必然报错。从库导入完成后查看备份文件头的主库位点信息head -30 /tmp/backup.sql找到类似这一行-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS874;在从库上执行CHANGE MASTER。用位点方式的话CHANGE MASTER TO MASTER_HOST192.168.56.10, MASTER_USERrepl, MASTER_PASSWORDRepl_2024!Pass, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS874, MASTER_PORT3306;如果两台MySQL都开了GTID我们前面已经开了可以直接用GTID自动定位CHANGE MASTER TO MASTER_HOST192.168.56.10, MASTER_USERrepl, MASTER_PASSWORDRepl_2024!Pass, MASTER_PORT3306, MASTER_AUTO_POSITION1;用这条省心很多之后再也不用关心binlog文件名和位点对不对。MySQL 8.0.23开始官方推荐把MASTER换成SOURCE比如CHANGE REPLICATION SOURCE TO ...但老写法仍然兼容新手碰到老教程也不用慌。3.4 启动复制并验证从库上启动START SLAVE;然后检查状态这条命令是我每次搭完主从必看的SHOW SLAVE STATUS\G重点看这几项Slave_IO_Running: Yes —— IO线程连主库拉日志是否正常Slave_SQL_Running: Yes —— SQL线程回放日志是否正常Seconds_Behind_Master: 0 —— 复制延迟秒数刚搭完应该是0MySQL 8.0.22之后SHOW SLAVE STATUS仍然可用但SHOW REPLICA STATUS是更标准的写法。两个看到的核心字段含义一样不过新命令里字段名略有变化。一个更彻底的验证方法是在主库建一张测试表、插几条数据到从库看能不能查到-- 主库执行 CREATE DATABASE if NOT EXISTS testdb; USE testdb; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t1(name) VALUES(repl_test); -- 从库执行 SELECT * FROM testdb.t1;能查到就说明这条复制链路已经通了。顺便在主库删掉这张测试表免得留垃圾数据。4. 双主双从搭建双向复制其实是两台主库互相把对方当从库一主一从跑顺之后双主双从的搭建思路就很清晰了——它就是一主一从做两遍方向相反主A做主B的从主B做主A的从从库A1挂主A从库B1挂主B。我把关键差异点单独拎出来说。4.1 双主双从的架构关系与server-id规划四台机器的server_id规划主机角色server_id说明192.168.56.10主库A1接收业务写流量向上游无复制关系192.168.56.11主库B2接收业务写流量向上游无复制关系192.168.56.101从库A1101挂主库A分担读压力192.168.56.102从库B1102挂主库B分担读压力server_id划分规律我统一用主库个位数、从库百位数的方式好辨认好维护。如果以后扩容再加从库直接从102往后排。4.2 两台主库的双向复制配置主库A和主库B的my.cnf除了server_id不同还有两个参数是双主场景独有的——自增偏移量设置主库A[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON auto_increment_offset 1 auto_increment_increment 2主库B[mysqld] server_id 2 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON auto_increment_offset 2 auto_increment_increment 2auto_increment_increment2表示自增主键每次步进2auto_increment_offset决定从哪个数字开始。主A生成1、3、5、7……主B生成2、4、6、8……这样两台主库同时插入数据时主键不会撞到一起。这个细节是双主双从不踩主键冲突雷的关键。两台主库互相指定对方为主库。在主库B上配置指向ACHANGE MASTER TO MASTER_HOST192.168.56.10, MASTER_USERrepl, MASTER_PASSWORDRepl_2024!Pass, MASTER_PORT3306, MASTER_AUTO_POSITION1; START SLAVE;在主库A上配置指向BCHANGE MASTER TO MASTER_HOST192.168.56.11, MASTER_USERrepl, MASTER_PASSWORDRepl_2024!Pass, MASTER_PORT3306, MASTER_AUTO_POSITION1; START SLAVE;这里有个细节主库A和主库B互相复制时A本地的写事务产生的binlog也会被B拉取回放而A同时作为B的从库拉到的数据里可能包含从A同步过去的数据又被B同步回来的情况形成循环复制。在GTID模式下MySQL自带逻辑判断重复事务并跳过所以不用太担心在老位点模式下要额外小心配错导致循环复制风暴。4.3 两台主的从库挂载与互为主备的细节从库A1和B1的配置跟一主一从里的从库基本一样只有server_id不同[mysqld] server_id 101 -- A1 server_id 102 -- B1 log_bin mysql-bin binlog_format ROW gtid_mode ON enforce_gtid_consistency ON relay_log relay-bin relay_log_recovery 1然后A1指向主A执行CHANGE MASTERB1指向主B执行CHANGE MASTER步骤跟一主一从完全一致。互为主备还有一个细节——如果将来主A宕机把主B提升为唯一写库之后从库A1还需要改挂到主B。所以在搭建的时候就要预留好 repl 账号在主A、主B上的双向授权别等故障发生了才发现权限不够。4.4 自增冲突问题为什么必须设置offset和increment这块值得再展开说一下。双主双向复制下两台主库若同时往同一张表的自增列插数据MySQL默认的自增主键生成规则会导致两边都从1开始于是B库复制A库的插入时主键就冲突了SQL线程报错停住。解决办法就是我上面配置的两个参数auto_increment_increment2让两台主库生成的主键错开auto_increment_offset分别指定A从奇数开始、B从偶数开始。这样即便两个主库同时插入相同业务表它们生成的ID不会重叠。如果你的双主之间明确做了业务拆分比如A只写订单表B只写用户表两张表互不相干理论上可以不用这个配置。但运维时很难保证以后不会踩线而且这个配置代价极低我建议不管什么业务拆分都把这两个参数配上省得以后某天突然出现复制中断事故。5. 搭建完不是终点复制状态检查、延迟判断和切换思路搭建完成只是开始真正的日常工作在于维护复制链路。这部分我总结几个高频排查场景。5.1 常用状态命令日常巡检我一般跑这四条SHOW REPLICA STATUS\G -- 或 SHOW SLAVE STATUS\G SHOW MASTER STATUS; SHOW PROCESSLIST; -- 看有没有长时间运行的复制IO/SQL线程SHOW REPLICA STATUS的返回结果里除了那两个Running字段我还会盯这几项Retrieved_Gtid_Set和Executed_Gtid_Set —— GTID模式下从库拉取和执行的GTID集两者差集就是还没回放完的事务。Last_IO_Errno和Last_SQL_Errno —— 错误码等于0说明无错误。Relay_Log_File和Relay_Log_Pos —— 当前relay log定位可用于人工干预时参考。建议写个简单的定时脚本每分钟跑一次SHOW REPLICA STATUS把Slave_IO_Running和Slave_SQL_Running抓取出来不是Yes就告警。数据平台类项目对复制中断很敏感我见过因为没人盯主从断了两天才发现补数补到怀疑人生。5.2 Seconds_Behind_Master真的靠谱吗这个指标是很多运维判断延迟的第一选择它表示SQL线程当前回放位置与IO线程拉取位置的差值秒数。但它有几个明显的坑如果从库IO线程和SQL线程都正常它基本能反映回放延迟。可一旦网络本身慢从库拉日志的速度跟不上主库产生日志的速度IO线程堆积在本地磁盘整个库的压力上来了这个字段会直接变成一个大数甚至NULL误报几率很大。更常见的是它在赶进度时明显变小看起来马上追上实际堆积的事务量还很大。真正要判断延迟我建议直接比较主库和从库的binlog位点或GTID集合差或者看从库上最新插入的一行数据时间戳与当前时间的差。比如你有张带create_time的业务表在从库执行SELECT NOW() - MAX(create_time)如果差值是分钟级说明回放确实慢需要排查大事务或回放并发配置。5.3 故障切换思路一主一从场景下主库宕机最简单的提升从库流程是STOP SLAVE; RESET SLAVE ALL;然后在从库执行只读关闭SET GLOBAL read_only OFF;再把应用连接切过来就行。注意RESET SLAVE ALL之后这台从库和原主库的复制关系彻底解除不能再希望通过START SLAVE恢复。双主场景的切换就省事多了。主A宕机后业务写流量直接切到主B主B本来就是独立可写的主库不需要做什么提升动作。唯一要处理的是把从库A1改挂到主B等主A恢复后再把主A重新挂回主B做同步——这时主A需要用CHANGE MASTER配置指向主B先赶齐数据再恢复双向复制。这些切换流程建议提前写成脚本、定时做一次演练别等真出故障了再现场敲命令。我后来把所有切换步骤固化成文档新同事照着走也能完成切换这比每次都由我一个人上阵靠谱得多。6. 实战中躲不开的六个坑从UUID冲突到半同步静默降级搭建和运维过程中我踩过的坑不少挑六个最有代表性的分享给大家。6.1 server-uuid冲突现象是从库的IO线程反复报错错误日志里写着Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs原因很常见——用虚拟机克隆镜像、或直接把数据目录拷贝到另一台机器时auto.cnf文件里的server-uuid也被带过去了两台MySQL的uuid完全相同从库不认自己主机拒绝复制。解决办法在从库上删除auto.cnf重启MySQL让它重新生成一个uuid。rm -f /var/lib/mysql/auto.cnf systemctl restart mysqld重启后确认SHOW REPLICA STATUS里的Last_IO_Error消失。这个问题在较早的5.7版本上很典型8.0里也会遇到任何克隆环境都要先检查这一步。6.2 双主双向复制时的数据写入冲突这个我在前文提到过再展开讲一个真实例子。某次我帮某项目搭双主因为业务方说两边的写已经按用户ID哈希分散不会写同一行所以暂时没配auto_increment_offset。结果上线两周后从库SQL线程突然报主键重复错误一查才知道有一条数据在两边几乎同时插入了同一张表恰好自增主键都生成到了同一个值。解决过程分两段先把冲突行修复好再手工在两边补齐offset和increment参数重启后重建复制关系。这个案例给我的教训是架构层面的保险措施不能靠口头承诺来豁免业务说的不会冲突随时会被真实流量打脸。双主双向复制下配套的自增错位参数必须从一开始就配上。6.3 binlog格式不一致带来的复制崩溃某一次搭主从主库是5.7从库还是老版本的5.6主库的binlog_formatROW从库老版本解析ROW格式的事件时报错SQL线程直接停了。MySQL各版本的复制对binlog格式和事件类型兼容性不同。要避免这种坑最稳妥的做法是保证主从MySQL版本一致或大版本接近至少在搭建之前确认从库版本能够解析主库的binlog事件。如果一定要跨版本建议统一用ROW格式、避免STATEMENT格式中的函数在两边产生歧义并且先在测试环境跑一轮提前验证。6.4 防火墙和账号网段权限导致的连不上症状是IO线程起不来Last_IO_Errno报2003连接失败。排查时我先看端口通不通telnet 192.168.56.10 3306端口通了再看账号权限repl192.168.56.%这种授权要覆盖从库的真实IP网段。如果用了容器或云主机还要检查安全组规则。2003错误大部分是网络原因但有时也会因为账号IP白名单太窄被拒。6.5 半同步复制超时降级后没有告警开启半同步复制之后如果主库迟迟等不到任何一个从库的确认超过rpl_semi_sync_master_timeout毫秒半同步会自动降级为异步复制。这时候主库不会报错业务照常写入但你以为的半同步保护已经悄悄失效了——主库如果此时宕机照样可能丢数据。我遇到过的情况是某从库因为磁盘满IO线程断了主库等不到确认超时降到异步期间主库又发生过一次重启导致主从和半同步链路彻底乱套。从那以后我把rpl_semi_sync_master_timeout设成能接受的阈值并在监控里专门看Rpl_semi_sync_master_status这个变量一旦它不是ON就触发告警。6.6 回放大事务导致的复制延迟假象曾经有个项目在从库上查出几分钟的复制延迟排查半天发现主库执行了一个百万行的大UPDATE这个事务在binlog里占了很大体积从库的并行回放线程处理它耗时很久之后才恢复到0。真正要紧的是不要长时间出现复制追不上主库产生速度这时可以考虑调整并行回放参数replica_parallel_workers 4 replica_preserve_commit_order 1MySQL 8.0里这两条配合起来可以让从库在保证提交顺序的前提下并行执行多个事务大幅提高回放吞吐。但要注意如果从库本身CPU核数少开太多并行线程反而会抢占资源。设到4~8之间、观察延迟变化再调是最稳的路子。我在实际操作中最大的体会是搭建一主一从和双主双从难点从来不在敲那几条命令而在于对复制机制的理解——理解了binlog、relay log、GTID、半同步这些概念之后遇到问题能自己推算出哪里出了问题反之只会抄命令的人换个环境、报个新错就卡住了。这个系列的两种架构我的建议是老老实实从一主一从开始跑熟悉了再上双主双从每一步都动手验证、记录输出踩过的坑才会真正变成你的经验。