双主复制这四个字听着像是要把两台MySQL都变成能写、能扛故障的超级节点但实际上搭建它的人更多是为了解决一件事当其中一台挂掉时另一台能立刻顶上且业务不用因为改数据库地址而断掉。这是我在生产环境维护了多套MySQL 5双主复制架构后最真实的感受。所谓双主核心就是让两台数据库节点互为主从——A是B的主库B同时也是A的主库数据双向流动但业务写入点仍然要收敛。这篇文章主要面向正在评估或准备搭建MySQL 5双主复制的运维、DBA和后端开发我会从复制底层原理、参数选择、完整搭建、切换演练到常见故障排查一条线讲完把我踩过的坑和验证过的结论都留在下面。1. 双主复制架构的本质与应用场景1.1 先搞懂MySQL的复制到底在复制什么MySQL的复制不是物理复制整个数据文件而是基于binlog的逻辑复制。主库上每一个产生数据变更的语句都会被记录到二进制日志binlog中从库开启一个IO线程把主库binlog拉取过来写入本地relay log中继日志再由SQL线程把relay log中的事件逐条回放应用到自己的数据文件上。整个链条里binlog是快递单relay log是本地中转仓库SQL线程是负责签收拆包的人。在MySQL 5系列中binlog_format可以设置STATEMENT、ROW、MIXED三种格式。双主复制环境下我建议直接使用ROW格式因为两台库都可能产生写入基于行的复制在冲突发生时更容易定位也不会因为存储函数、触发器、不确定语句导致两边数据不一致。这一点在后面的参数配置里会再次强调。1.2 双主复制不是什么不要把双主复制理解为两台MySQL都能随便写。它能做的是让两边的数据实时保持一致任何一边写入的变更都会通过复制流到另一边。但MySQL本身不提供写入冲突仲裁。如果两个应用进程同时在两台库上往同一行数据UPDATE后提交的会直接把前一个覆盖掉如果同时往同一张自增表INSERT更会直接撞主键。我通常建议团队把双主复制定位成双节点互为冗余业务写入仍通过VIP或固定配置指向一个主执行仅在故障切换时切换写节点。只有个别场景比如按库、按业务模块拆分两个节点分别负责不同业务库的写入双写才算安全。搭建双主之前一定要先把这个原则刻在脑子里面否则后面所有运维都是走钢丝。1.3 双主、主从、MHA、MMM的定位差异动手配置之前团队需要明确自己到底要什么。这里放一个对比方便你判断双主复制在整个高可用体系里的位置方案写入点自动切换数据一致性保障运维复杂度一主一从固定主库无普通异步复制可能丢数据低双主复制通常固定一侧切换后移到另一侧依赖VIP或外部工具异步或半同步切换仍有极小数据丢失窗口中MHA固定主库自动尽力保证relay log不丢仍有风险较高MMM双主VIP自动依赖双主复制写冲突风险需应用层规避高从表格能看出来双主复制并不是最完备的高可用方案它是高可用的基础设施。它解决的主要是切换时两个节点间数据已经对齐的问题。至于切换决策、脑裂预防还是要交给上层VIP漂移、keepalived或者专业的HA编排工具。不要指望MySQL双主自己会做故障转移它从设计上就没有这个能力。2. 搭建前的准备与核心参数选择2.1 环境规划与版本选择如果版本还没定我推荐直接选MySQL 5.7。原因很实际5.7的GTID复制已经很成熟半同步复制更稳参数默认值更合理遇到问题也好查资料。MySQL 5.6也能用但部分参数和行为跟5.7有差异MySQL 5.5虽然能做双主但半同步性能和数据安全保障不足不推荐新项目使用。硬件方面两台机器的规格尽量一致特别是内存和磁盘IO能力。复制对IO有天然消耗binlog写入、relay log回放、网络传输都在抢资源SSD和足够的内存是关键。网络方面两台机器之间的内网延迟要低至少千兆网卡复制流量是非常容易忽略的带宽消费者。系统层面做好hosts解析、统一时区、开启chrony或NTP定时同步。数据库服务器时间不一致复制超时、日志判读、监控告警都会出问题我见过好几个奇怪故障最后发现是系统时间不对。这里还要强调一个基础项主机名。双主复制中CHANGE MASTER TO通常会写主机名主机名解析不正确会导致无法连接或者连到错误机器。所以/etc/hosts里把两个节点都写清楚比直接用IP可读性好后续维护也方便。2.2 一份能落地的my.cnf配置参数配置前先明确两个节点的配置文件绝大部分一致必须不同的参数其实只有少量几个。下面是一套线上环境的模板。node1的/etc/my.cnf中[mysqld]段配置[mysqld] server-id 1 log-bin /data/mysql/logs/mysql-bin log-bin-index /data/mysql/logs/mysql-bin.index log_slave_updates 1 expire_logs_days 7 binlog_format ROW sync_binlog 1 innodb_flush_log_at_trx_commit 1 auto_increment_increment 2 auto_increment_offset 1 gtid_mode ON enforce_gtid_consistency ON relay_log /data/mysql/logs/mysql-relay-bin relay_log_index /data/mysql/logs/mysql-relay-bin.index master_info_repository TABLE relay_log_info_repository TABLE skip_name_resolve 0 character-set-server utf8mb4 collation-server utf8mb4_general_cinode2的配置只需要改动两处server-id 2auto_increment_offset 2。其余参数与node1保持一致。这里的auto_increment_increment和auto_increment_offset是双主避免自增主键冲突的关键后面会单独展开讲。还有几个经验层面的补充。binlog_format建议ROW的原因前面提过双主两台库都可能产生写入ROW模式在回放时直接应用行变更某个事件因为数据不匹配失败时错误信息也更明确。sync_binlog1和innodb_flush_log_at_trx_commit1组合能确保每次事务提交都把binlog和redo落盘这是异步复制下最大程度减少丢数据的标准配置。代价是每次提交都增加一次fsync写入性能会有明显下降。如果业务写量很大可以适当调整为sync_binlog0或N但你必须清楚这意味着主库宕机时可能丢失最近N个事务的binlog。在双主环境里我更倾向保留双1模式。2.3 基于GTID还是binlog position做复制MySQL 5.6开始引入GTID。只有两个节点的情况下GTID和传统position方式都能用。怎么选我给出一个对照表复制方式适用版本搭建难度故障切换注意点基于binlog文件名Position所有5.x低手动记录日志位置容易出错兼容性最好什么时候都能用基于GTID5.6及以上低自动定位CHANGE MASTER一行搞定受GTID一致性约束导入备份时需处理GTID_PURGED如果确定使用MySQL 5.7直接上GTID。GTID模式下同一个GTID事务不会重复执行所以两边各自写入相同数据时要特别小心。另外开启GTID后mysqldump导入数据时需要用--set-gtid-purged方式做正确处理否则会报GTID_PURGED错误。如果是5.5或5.6早期版本老老实实用binlog file position。下面搭建环节里两种方式都会给出命令大家按版本选即可。3. 双主复制搭建全流程3.1 初始化环境与导入初始数据这里假定两台服务器都装好了MySQL 5.7root密码也已初始化。如果是新装5.7会生成一个临时root密码记录在/var/log/mysqld.log里注意看temporary password那一行。环境层面的三个步骤建议都做一遍在/etc/hosts里配置两个节点的主机名解析。放行防火墙3306端口确保两台机器能互相telnet 3306。配置chrony并确认chronyc sources显示时间已同步。完成之后在node1上准备初始数据备份。如果是一个全新空库这步可以省略如果已经有一些库表必须把node1的现有数据先全量搬到node2否则一开启复制就会遇到找不到行、主键冲突等问题。mysqldump导出时建议加上--single-transaction --master-data2 --all-databasesmysqldump -uroot -p --single-transaction --master-data2 --all-databases --set-gtid-purgedOFF /tmp/node1_dump.sql再把备份文件复制到node2并导入mysql -uroot -p /tmp/node1_dump.sql如果使用GTID模式导入时需要特别注意不要保留备份文件里的GTID_EXECUTED信息。我一般导出时用--set-gtid-purgedOFF导入后在两边手动确认GTID集合再执行CHANGE MASTER TO MASTER_AUTO_POSITION1。这一步之所以关键是因为双主开始复制后两边数据基线必须一致任何差异都会在复制中被放大成冲突。3.2 创建双向复制账号复制需要专用账号不建议直接用root。在node1上创建账号并授权CREATE USER repl10.0.0.% IDENTIFIED BY StrongPasswd_2024; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl10.0.0.%; FLUSH PRIVILEGES;这里REPLICATION SLAVE权限是从库去主库拉binlog的基础权限REPLICATION CLIENT是为了让监控工具能查询主从状态。授权网段建议限定为两台服务器所在的内网网段不要用%减少不必要的暴露面。node2上也要创建同样账号因为双主意味着两边都会连接对方拉日志。权限和密码最好保持一致后续切换时应用配置不需要改只需要改主从方向。3.3 建立双向复制链路以GTID方式为例。先查看当前主库状态SHOW MASTER STATUS;在node2上执行CHANGE MASTERSTOP SLAVE; CHANGE MASTER TO MASTER_HOSTnode1, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDStrongPasswd_2024, MASTER_AUTO_POSITION1; START SLAVE;再在node1上执行反向配置STOP SLAVE; CHANGE MASTER TO MASTER_HOSTnode2, MASTER_PORT3306, MASTER_USERrepl, MASTER_PASSWORDStrongPasswd_2024, MASTER_AUTO_POSITION1; START SLAVE;如果是基于position的方式需要先在两台机器上执行SHOW MASTER STATUS获取当前binlog文件名和Position然后把MASTER_LOG_FILE、MASTER_LOG_POS分别填入CHANGE MASTER语句。GTID方式最大的好处就是省掉这一长串日志定位只要数据基线一致复制会自动从双方已经EXECUTED的GTID集合继续跑。3.4 验证复制链路配置完成后快速验证mysql -uroot -p -e SHOW SLAVE STATUS\G | grep -E Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master正常输出应该看到Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0。接下来做一次双向写入验证。在node1建库建表并插入几条记录CREATE DATABASE dbtest; USE dbtest; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t1(name) VALUES(from_node1);回到node2查询能查到数据说明node1到node2方向通了。再在node2插入一条回到node1查询能查到说明反向也通了。我第一次搭双主时只测了A到B忘了测B到A结果切换演练时才发现反向复制根本没生效。后来养成了习惯每次配完双向各写一条做验证绝不含糊。这里还有一个细节。双向都通了以后两边SHOW MASTER STATUS的File和Position都在变化而且两边binlog里都可能出现来自对方的写入记录。你可能会担心A写入BB又写回AA再写回B是不是死循环放心MySQL通过server-id过滤不会循环应用。这是复制协议内置的行为后面讲原理时会说明。3.5 做一次切换演练搭建完成不是终点切换演练才是真正暴露问题的地方。我会在业务低峰期做一次模拟先停掉业务写入把VIP从node1飘到node2再把node2的read_only参数关掉确认应用写入node2一切正常。接着把node1恢复上线确认node1作为从库追平了node2的所有数据并且Slave_SQL_Running是Yes。最后再把写流量切回node1。这里有个容易被忽略的点双主复制中故障恢复后的节点必须检查是否还持有主的身份。如果切换后node2已经产生大量新数据node1恢复却迟迟没能追平日志旧应用又尝试写node1就会产生严重不一致。所以在切换过程中先把应用配置里指向node1的连接全部断开再启动node1的复制等Seconds_Behind_Master0后再小心恢复流量。4. 写入冲突、脑裂风险与高可用扩展4.1 双主最典型的坑自增主键冲突双主复制最经典的问题就是自增ID冲突。两台主库如果都往同一张自增表插入数据都从1开始生成ID很快就会出现主键重复。解决办法就是前面提到的auto_increment_increment和auto_increment_offset参数。MySQL的自增序列生成规则是这样的当前自增值由offset决定起始点increment决定递增间隔。我们配置increment2offset一个为1一个为2两台库生成的就分别是奇数和偶数天然不碰撞。假设node1生成ID1、3、5、7node2生成ID2、4、6、8。只要业务不手动插入具体ID值就不会撞。但这个方案有一个隐藏问题如果未来要扩展成3台、4台双主节点increment需要从2改成3或更大的数字而已经存在的自增序列不能被重排只能通过修改表结构或数据迁移来适配代价不小。所以自增错开方案适合确定长期只有两个节点的场景。如果业务预期会有多写多节点扩容不如一开始就用分布式ID方案或者让每个节点负责不同的业务库避免共用同一张自增表。4.2 数据冲突只是表象写入路由才是关键即使自增ID错开了双写同一行数据依然存在丢失更新问题。举个例子两条UPDATE语句一条把商品库存减1一条把商品价格改成99。它们分别在node1和node2上执行成功再通过复制流到对方节点。由于复制执行顺序有先后最后应用的是哪一条库里就可能少一次库存修改或者价格被旧值覆盖。MySQL的复制不提供冲突仲裁只会按binlog顺序执行。因此双主复制在生产环境中最稳妥的用法就是只有一个主写点另一个节点平时只读或者只承担部分只读流量。应用层可以通过VIP访问数据库也可以把读流量均匀分布到两个节点。只有发生故障切换时才把写权限切到备用节点。更准确的说法是同一时间只有一个写主节点另一个等待接替。如果确实希望两个节点分别承担不同业务库的写入一定要按库拆分写入确保没有两个节点同时修改同一行数据。这需要应用层做严格的路由控制把不同业务的写请求固定到不同节点。4.3 双主 VIP 的高可用套路双主复制本身不做故障转移生产环境通常在上面加一层虚拟IP。最常用的是keepalived加双主复制。keepalived通过VRRP协议绑定一个VIP正常时VIP落在node1所有读写都通过VIP走node1检测到node1的MySQL进程不可用VIP漂移到node2node2接管写入。keepalived需要写一个健康检查脚本不能只看进程在不在要真实连接MySQL#!/bin/bash MYSQL_PORT3306 mysql -h127.0.0.1 -P$MYSQL_PORT -uroot -pxxx -e select 1 /dev/null 21 if [ $? -ne 0 ]; then exit 1 fi脚本返回非0时keepalived会降低本机优先级并释放VIP。应用连接的是VIP所以数据库节点切换对应用层基本透明。但这里有个绕不开的坑脑裂。如果两台机器之间网络抖动keepalived的VRRP心跳互相看不到对方两个节点可能同时持有VIP两边都在对外提供服务写流量被拆到两台库引发严重数据冲突。规避脑裂的办法是引入仲裁。例如在第三台机器上部署仲裁服务或者用复制状态辅助判断本机是否应该持有VIP。不要信任黑盒VIP要在脚本里加更多条件约束比如检测到对方在线而且复制正常时本机不主动抢占VIP。4.4 半同步复制让数据更不容易丢异步复制下主库提交后不关心从库是否收到binlog主库宕机时那些还没来得及传到从库的binlog事件就丢了。双主架构虽然有两台节点丢数据的风险并不会因为多一台机器自动消失。MySQL 5.5之后提供的半同步复制插件能缓解这个问题。打开半同步后主库在提交事务后必须等待至少一个从库确认我已经把binlog写进relay log事务才算真正提交成功。也就是说如果node1是主node2必须ACKnode1才返回客户端成功。这样一来主库崩溃时已提交事务的binlog基本都在从库手里切换后数据不丢的概率大幅提升。配置半同步时双主模式下的两节点必须同时加载master和slave插件因为每一台既可能当主也可能当从。参数如下plugin-load rpl_semi_sync_mastersemisync_master.so;rpl_semi_sync_slavesemisync_slave.so rpl_semi_sync_master_enabled 1 rpl_semi_sync_slave_enabled 1 rpl_semi_sync_master_timeout 1000rpl_semi_sync_master_timeout1000表示等待从库ACK最多1秒超时后自动降级为普通异步复制不让业务长时间阻塞。需要特别说明半同步并不是绝对可靠。在5.7的增强半同步里主库是在特定阶段等待ACK极端情况下ACK还没到主库就宕机数据依然可能丢失只是窗口大幅缩小。如果业务对数据丢失零容忍就需要考虑MySQL Group Replication或更复杂的分布式事务方案那已经超出普通双主复制的范围。5. 常见问题排查与避坑实录5.1 健康状态怎么看才准确配置完成后很多人习惯只盯Slave_IO_Running和Slave_SQL_Running是不是Yes。这两个字段只能说明IO线程和SQL线程还活着不说明日志已经追平。判断复制是否健康至少要看这些字段Slave_IO_RunningIO线程是否连接主库并拉取日志Slave_SQL_RunningSQL线程是否正常回放Seconds_Behind_Master从库落后主库多少秒Read_Master_Log_Pos与Exec_Master_Log_Pos拉取到的日志位置与已执行位置的差值Last_IO_Error和Last_SQL_Error最近一次错误的详细信息在双主环境中主库持续写入时Exec_Master_Log_Pos会一直增长。Seconds_Behind_Master如果是0说明从库已执行完所有relay log。如果是正数且不断增大要考虑主库写入压力大或者从库回放能力不足比如从库还在同时跑报表查询。注意Seconds_Behind_Master在某些情况下不可靠比如主从系统时间不一致或者复制刚启动的瞬间这个值可能不准。所以要结合binlog位置的差值一起判断。5.2 双主搭建中频率最高的几个问题我把这些年遇到的故障整理成一个速查表问题现象常见原因处理方法Slave_IO_Running: Connecting网络不通、账号密码错误、权限不对telnet 3306重新授权检查server-idSlave_SQL_Running: No复制回放时主键冲突、缺行、缺列查看Last_SQL_Error修复不一致记录Error 1062 Duplicate entry自增ID冲突或两边重复INSERT检查auto_increment参数必要时重置自增序列Error 1236 binlog找不到主库binlog被purge或position不匹配重新做全量备份重建复制关系Error 1594 relay log损坏磁盘异常或非正常关闭清空relay log重新START SLAVESeconds_Behind_Master持续增大回放慢、主库并发高、从库有锁优化SQL检查慢日志降低从库负载其中有个很隐蔽的场景双主两边执行过同样的DDL后续DML复制会报失败。比如在node1执行了ALTER TABLE增加列这个事件复制到node2后如果node2又执行了一次相同的ALTER TABLE就会因为列已存在而报错。因此双主下DDL操作必须在业务低峰期严格单边执行执行后确认复制状态正常再操作另一边。通常我更推荐pt-online-schema-change这类工具但仍然要保证同一时间只有一边在改结构。5.3 数据一致性校验与日常保养双主复制不能保证运行几个月后数据仍然完全一致尤其是中途出现过跳过错误、手动修数据、恢复备份等情况。建议定期做一致性校验。环境允许的话使用percona-toolkit的pt-table-checksum比较成熟。它能通过在校验表上计算checksum并与对端对比找出哪些表、哪些行不一致。双主环境下执行校验要注意校验过程会往库里写临时表和checksum记录要在业务低峰期执行并且不要两边同时运行校验否则校验操作本身会互相复制造成干扰。一旦发现不一致优先从数据更完整的一侧重新导出该表再导入另一侧然后确认复制状态恢复。日常保养还有几项定期备份binlog至少保留7天以上设置合适的expire_logs_days检查磁盘空间防止binlog暴涨打爆磁盘每天过一遍错误日志里有没有复制相关的警告。复制槽点、告警这些建议都接入现有监控体系。5.4 几个可能救命的细节最后补几个我在实际维护中觉得特别重要的细节。master_info_repository和relay_log_info_repository务必设置为TABLE这样主从的元数据存在数据库表中而不是文件里异常崩溃后不容易出现文件损坏、位置信息丢失的问题。双主环境下建议把备用节点的read_onlyON写上避免运维误操作直接在备用节点写入制造数据分叉。同时两个节点都开启skip_name_resolve时账号授权网段里不要用域名否则会出现连接认证失败的问题。还有一个几乎每次切换都会遇到的坑应用侧数据库连接池有缓存。VIP漂移后连接池里的老连接不会立刻失效需要设置合理的连接超时和探活参数否则切换期间应用会持续报连接错误看起来就像切换失败一样。连接池参数这件事一定要在切换演练里反复验证别等到真正的故障来临时才第一次测试。这些年跟复制打交道我越来越觉得双主复制不是搭完就结束的基础设施。它真正考验人的地方是在故障切换这种高压时刻你还能不能冷静判断数据流向、确认复制状态、安抚业务方。好在这些能力都可以通过日常演练获得。每套双主环境我都会固定做三件事定期看错误日志定期做一致性校验定期在业务低峰期做一次全流程切换演练。数据同步慢一点可以优化但冲突和脑裂这类问题等到线上出了事故再想补救代价就太大了。