1. 项目概述一主一从与双主双从分别解决什么问题先说说这次动手搭建的背景。很多团队在数据库从单机走向高可用的过程中第一步想到的就是“主从复制”。MySQL主从复制本身并不难但不少人把一主一从配通之后就开始纠结还要不要做双主双从这两个架构之间的取舍经常讲不清楚。我自己也经历过这个阶段所以这次把一主一从和双主双从的搭建过程完整梳理一遍把每一步的配置、验证方法、踩坑点都写出来希望能帮正准备做复制方案的人少走弯路。先说结论一主一从适合解决“读压力大”和“备份窗口紧张”的问题核心是让从库分担查询流量并在主库出问题时提供一份实时可用副本。双主双从则是在一主一从基础上把写入侧的高可用也纳入考虑两个主库互相复制任意一个主库故障时另一个仍能继续接收写入另外两个从库继续分摊读请求。也就是说一主一从是“读有冗余”双主双从是“写也有冗余”。这套方案适合谁来参考如果你是刚接触MySQL复制、想搭一套测试环境练手的人里面的一主一从部分可以一步步照做如果你已经能跑通主从正在思考如何升级成双主双从可以直接看后半部分。文中默认环境是 Linux MySQL 8.0但除个别参数外同样适用于 MySQL 5.7。下面我按实际搭建顺序来写从原理到配置再到问题排查尽量把当初自己遇到的疑问都讲透。2. 动手之前必须理解的复制机制和几个关键概念不夸张地说主从复制配不顺畅80%的原因是对复制链路认识不清。很多配置文件抄过来却不知道为什么这么写一旦报错就完全没法下手。所以在写配置之前先把复制链条里的几个角色讲清楚。2.1 binlog、relay log 与复制线程的协作关系主从复制的本质是“主库把变更记录成二进制日志从库把这些日志回放一遍”。整个链路里有两个核心日志文件主库的 binlog以及从库的 relay log。binlog 记录的是主库上已经提交的、可能导致数据变化的操作relay log 则是从库把主库拉取过来的 binlog 内容先落到本地磁盘后再一条条回放。背后的执行者是三个线程。主库上有一个 dump 线程负责把 binlog 推给从库从库上有一个 IO 线程负责接收主库推来的日志并写入 relay log从库上还有一个 SQL 线程负责读取 relay log 并依次执行。理解这三个线程非常重要因为很多排查工作其实就是看这三个线程卡在了哪一步。比如 IO 线程报错通常是网络、认证、日志位置有问题SQL 线程报错通常是执行到了主库没有、或者主键冲突的语句。用个生活化的类比主库像是在写日记binlog 就是记好的日记内容从库的 IO 线程相当于一个快递员每天把新写的日记页搬回自己家SQL 线程则是负责把搬回来的日记内容重新誊写一遍。只要快递员或者誊写环节任何一个出问题整个复制链路就会停下来。2.2 binlog position 与 GTID 两种复制方式的选型配置主从复制的核心就是告诉从库“从主库的哪个位置开始同步”。这个位置有两种表达方式一种是传统的 binlog 文件名 日志偏移量比如 MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS154另一种是 GTID全局事务标识符它给每个事务分配一个全局唯一编号从库直接按 GTID 集合去请求缺失事务即可。我的建议是新环境优先考虑 GTID理由很直接。GTID 方式下从库不需要手动记住 binlog 文件和偏移量如果从库挂了重搭、或者要做主从切换它自己会根据 GTID 集合判断从哪里继续大大降低了手工操作出错的风险。传统 position 方式虽然更直观但“从哪个文件哪个位置继续”完全依赖人工记录一旦记错复制就会从错误位置开始导致数据不一致。不过 GTID 也不是没有限制。如果你用的是很老版本的 MySQL或者从库要跟第三方工具做特殊联动传统方式可能兼容性更好。一般 5.7 之后我建议直接 GTID教程里我会把两种方式的关键步骤都写出来方便对照。2.3 server_id 为什么不能重复这是新手配置时最容易犯的错。MySQL 要求集群中每个实例的 server_id 必须唯一复制链路里从库会通过 server_id 判断一条日志是不是自己产生的。如果两台机器的 server_id 相同从库拿到 binlog 后可能不知道该不该执行轻则复制中断重则出现无穷无尽的“丢事务”现象。双主架构里 server_id 更是生死线。因为两个主库互相复制A 上产生的事务传给 BB 执行后又会写自己的 binlog这个 binlog 再传回 A。MySQL 内部有一个机制如果日志事件里的 server_id 和当前实例的 server_id 相同就跳过不回放。正是靠这个机制才避免了两边互相复制导致无限循环。可一旦 server_id 配重了这个机制就失灵了轻则循环复制重则关键数据被覆盖。所以每一台实例的 server_id 必须单独规划这个规划要在动手前写在纸上而不是临时猜。3. 一主一从搭建完整实操步骤下面进入正题先搭一套最基础的一主一从。这套环境我假设是两台 CentOS 服务器MySQL 8.0分别叫主库A和从库B。既然是演示我直接用 192.168.1.10 和 192.168.1.11 这种地址来写你换成自己环境里的实际IP即可。3.1 主库配置开启 binlog 并创建复制账号主库需要改 my.cnf通常是 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/ 下的配置文件加入下面几项[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW expire_logs_days 7 max_binlog_size 256M这些参数中server_id 是实例唯一标识log_bin 开启二进制日志文件前缀叫 mysql-binbinlog_formatROW 表示按行记录变更相比 statement 格式它在数据一致性上更可靠也是 MySQL 8.0 默认推荐的方式expire_logs_days 控制二进制日志保留天数避免磁盘被日志写满。注意如果做主从的同时还要做基于时间点的恢复expire_logs_days 不能设太短至少要比你的备份周期长。日志是恢复的原材料删早了真要恢复时就傻眼了。配置改好后重启 MySQLsystemctl restart mysqld然后登录主库创建专用的复制账号。复制账号不需要太大权限最小化原则CREATE USER repl192.168.1.% IDENTIFIED BY Repl123456; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%;REPLICATION SLAVE 是从库拉取 binlog 所需的权限REPLICATION CLIENT 是让复制账号能执行 SHOW MASTER STATUS 等检查命令。很多新手只授权了 REPLICATION SLAVE后面排障时想看看主库状态都要换别的账号登录很麻烦所以每次我习惯顺手把 CLIENT 也加上。创建完账号后用这条命令查看当前 binlog 位置SHOW MASTER STATUS;记录下返回的 File 和 Position 两个值。等下配置从库时这两个值就是复制的起点。如果是空库或刚配置完 binlog一般情况下 File 是刚生成的第一个 binlogPosition 是 154 附近的初始值。3.2 从库配置relay log、只读账号与复制启动从库 B 的 my.cnf 配置如下[mysqld] server_id 2 relay_log relay-bin log_bin mysql-bin read_only 1relay_log 是从库回放日志的落盘文件前缀log_bin 建议从库也开启以后如果要从从库再拉一个级联从库或者把从库提升为主库它自己才有完整的 binlog。read_only1 让普通账号在从库上无法执行写操作但对拥有 SUPER 权限的账号无效这能防止应用误连从库写数据。提醒read_only 只挡普通 DML不挡 SUPER 权限。如果要彻底禁止写操作需要配置 super_read_only1MySQL 5.7.8 之后支持。我一般在从库上两个参数同时开。接下来在从库上执行 CHANGE MASTER以 position 方式为例CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154;注意MASTER_LOG_FILE 和 MASTER_LOG_POS 必须和刚才主库 SHOW MASTER STATUS 的结果一致。填错任何一项从库 IO 线程都会立刻报错。然后启动复制START SLAVE;用下面这条命令确认复制状态SHOW SLAVE STATUS\G重点看三行Slave_IO_Running 和 Slave_SQL_Running 是否都为 YesLast_Errno 是否为 0Seconds_Behind_Master 是否为 0。如果 IO 线程和 SQL 线程都是 Yes说明复制链路已经通了。这时候可以回到主库随便建一个库、建一张表、插几条数据再到从库看看是否同步整个一主一从就完成了。如果你是搭 GTID 模式主从两边的 my.cnf 都要加上gtid_mode ON enforce_gtid_consistency ON执行 CHANGE MASTER 时改成CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_AUTO_POSITION1;这里唯一要注意的是GTID 模式下从库不能再手动指定 binlog 文件和偏移量全部由 MASTER_AUTO_POSITION1 自动协商。两条路都能走通建议新环境直接 GTID。4. 双主双从的扩展思路与完整搭建流程一主一从跑顺后有人会问能不能再加一个主库让两个主库都能写这就是双主双从。双主双从的常见拓扑是两个主库互为主从主库 A 带从库 A1主库 B 带从库 B1。A 故障时业务可以把写请求切到 BB 上的从库继续服务读请求。这样一来读和写都有了备份节点。4.1 双主环境下最核心的问题写冲突与自增主键先泼一盆冷水双主不是简单地把两个一主一从拼在一起最大难点在“两边同时写”时如何避免主键冲突。假设一张表的主键是自增的主库 A 有一条 id1 的记录主库 B 同时也生成一条 id1 的记录两边互相复制后A 上会有两条 id1 的记录直接主键冲突。解决思路是让两侧生成的 id 错开。MySQL 提供了两个关键参数auto_increment_increment 和 auto_increment_offset。前者规定自增值每次增加多少后者规定从哪个数字开始。比如两个主库各设置主库 Aauto_increment_increment 2, auto_increment_offset 1主库 Bauto_increment_increment 2, auto_increment_offset 2这样 A 生成的 id 是 1、3、5、7……B 生成的 id 是 2、4、6、8……两者永远不撞车。注意两个参数必须同时设置只在一边设置会导致另一边的 id 也发生偏移反而更容易冲突。如果以后要扩展到三主increment 设成 3offset 分别设 1、2、3以此类推。提醒自增错开解决了新写入的冲突但对于历史数据如果两边原本都自己插入过 id1 的数据复制过程中依旧会报 1062 主键重复错误。双主架构要求两边初始数据是同一份且写请求做好分片规划不能同一个业务在两边同时写同一张表。另一个需要提前处理的是循环复制问题。A 的事务同步到 B 后B 执行这条事务又会生成新的 binlog 事件。由于 server_id 不同这个事件再同步回 A 时MySQL 通过比对 server_id 能识别出这是自己产生的事务会自动跳过。但前提是 server_id 严格唯一且配置正确。这再次说明 server_id 规划是双主的地基。4.2 双主双从的完整配置实例继续用四台机器的例子来说明规划如下机器角色server_id自增设置192.168.1.10主库 A1increment2, offset1192.168.1.11主库 B2increment2, offset2192.168.1.12从库 A13read_only192.168.1.13从库 B14read_only第一步四台机器的 my.cnf 都开启 binlog 和 relay log其中主库 A、主库 B 的配置类似[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW expire_logs_days 7 relay_log relay-bin auto_increment_increment 2 auto_increment_offset 1主库 B 的 server_id 改成 2auto_increment_offset 改成 2其他相同。第二步在两台主库上分别创建复制账号命令和 3.1 中一样host 要覆盖到对方主库和从库的 IP 段。第三步初始化数据。双主复制要求两边从同一份数据开始否则复制过程中会因为数据不一致报错。通常做法是先把主库 A 的数据备份出来恢复到主库 B 和两个从库。备份时推荐 mysqldump 加 --single-transaction --master-data2这样不会锁表而且备份文件里会记录当时主库 A 的 binlog 位置。对于大库可以用 xtrabackup原理类似。这一步不能省不要试图直接启动复制再让它自己追平两边初始数据不一致时start slave 会立刻报错。第四步在两台主库上互相配置复制链路。在主库 B 上执行CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;在主库 A 上执行CHANGE MASTER TO MASTER_HOST192.168.1.11, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;如果使用 GTID则两边都改成 MASTER_AUTO_POSITION1。第五步配置两台从库。从库 A1 以主库 A 为源从库 B1 以主库 B 为源-- 从库 A1 上执行 CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE; -- 从库 B1 上执行 CHANGE MASTER TO MASTER_HOST192.168.1.11, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;全部执行完后四台机器的 Slave_SQL_Running 都应该为 Yes。可以在主库 A 插入数据观察是否会同步到主库 B、从库 A1、从库 B1再反过来从主库 B 写入观察是否同步到主库 A。整个双向复制链路就算跑通了。4.3 双主中间件选型应用层切换还是VIP漂移双主双从搭好后还有一个绕不开的问题应用到底怎么连两个主库都能写但应用不可能同时连接两个主库否则会出现“一端写 A、一端写 B、两边互相覆盖同一行”的尴尬。常见的做法有两种一种是应用层做读写分离和数据源路由根据业务标识把写请求固定路由到其中一个主库。这个方案灵活但应用改造量大而且一旦主库故障路由规则要能快速切换。另一种是加一层 VIP虚拟IP漂移平时 VIP 绑定在主库 A 上A 故障时把 VIP 切到主库 B。应用始终连接同一个虚拟IP感觉不到后端切换。这个方案对应用透明但需要额外引入高可用组件来监控主库健康状态并执行漂移比如常见的高可用脚本或集群管理工具。我在实际项目中更推荐第二种原因很简单它对业务入侵最小而且和“双主双从提供写可用性”的目标最一致。不过需要注意的是VIP 漂移只能解决连接切换不能解决数据一致性的时间差。A 刚写了一批数据还没同步完就宕机切到 B 时最新提交的少量数据可能暂时丢了。这是异步复制的固有代价如果要缩小丢失窗口需要开半同步复制这部分下面单独讲。4.4 半同步复制的作用与配置方法默认的异步复制中主库提交事务时不会等待从库确认所以主库宕机后从库上很可能缺少最后几个事务。半同步复制的思路是主库在提交事务时至少要等待一个从库确认收到 binlog 后再向客户端返回成功这样主库宕机时至少有一个从库完整拥有最近的事务。MySQL 8.0 自带半同步插件安装配置步骤如下INSTALL PLUGIN rpl_semi_sync_source SONAME semisync_source.so;从库上则安装INSTALL PLUGIN rpl_semi_sync_replica SONAME semisync_replica.so;主库的 my.cnf 加rpl_semi_sync_source_enabled 1 rpl_semi_sync_source_timeout 3000从库的 my.cnf 加rpl_semi_sync_replica_enabled 1注意半同步复制虽然有“至少一个从库确认”的保障但从库确认的只是“收到了 binlog 并写入 relay log”并不代表 SQL 线程已经执行完。所以它降低的是“日志丢失”风险而不是“数据完全一致”风险。另外如果从库确认超时上面配置的 3000 毫秒内没回应主库会自动退化为异步复制继续提供服务。这是设计上的保护机制不然一个从库抖动就会拖垮整个主库写入。5. 常见问题与排查实录复制链路涉及网络、账号权限、日志位置、MySQL 版本等多个层面报错是常态关键是能快速定位。下面把最常遇到的问题整理出来基本都是搭建和运维过程中真实踩过的坑。5.1 复制线程起不来或频繁中断的典型报错先看一张速查表错误场景典型表现常见原因处理方向无法连接主库Last_IO_Error: error connecting to master网络不通、账号密码错、主库 MySQL 未监听外网ping 测试、查看 bind_address、检查 repl 账号 host 授权认证失败Access denied for user repl账号未创建、密码错、host 范围不匹配用主库上授权语句核对日志位置错误Got fatal error 1236MASTER_LOG_FILE/POS 和主库不一致重新 CHANGE MASTER用 SHOW MASTER STATUS 的准确值SQL 线程报错Error 1062: Duplicate entry从库已有同主键数据确认重复原因必要时跳过该事务或重建从库缺少数据Error 1032: Cant find record从库本来就没有这一行或该行已被修改大概率是初期数据不一致需要重新同步延迟Seconds_Behind_Master 持续增长大事务、慢SQL、主从硬件差异排查大事务分析慢查询优化从库硬件以 1236 错误为例它是最容易让新手迷惑的。只说一句“Got fatal error 1236, master may be empty or too old”很多人就懵了。这句话的意思是从库请求的 binlog 文件或偏移量在主库上已经不存在了。可能是因为 binlog 被 expire_logs_days 清理了也可能是因为从库记录的起点比主库当前最早的 binlog 还早。解决方法是找到主库当前能提供的最早 binlog 位置重新 CHANGE MASTER如果数据已经差得太多直接重新做一次全量备份再初始化从库反而更快。5.2 双主复制时主键冲突与循环复制的实战处理双主环境里最常见的是 1062 主键重复。比如两边各有一张业务表自增参数没配置好两边都生成了 id2 的记录复制一启动SQL 线程直接报错停下。这种时候不建议暴力跳过错误因为跳过的可能正是别人在主库上已经提交过的关键数据。正确做法是先在两边分别查询冲突记录确认哪边的数据是业务的最终版本然后删除或覆盖另一边的数据再重启 SQL 线程。如果这条错误本质上是因为双主方案本身规划不合理比如应用真的在同时写两边那么就算跳过这次下次还会冲突必须从源头调整路由。循环复制的问题通常表现为两个主库的 binlog 文件增长速度特别快但实际业务写入并不多。原因基本是 server_id 重复。排查方法是在两边分别执行 SHOW MASTER STATUS对比 binlog 文件增长情况再检查每个实例的 server_id 配置。MySQL 内部对同 server_id 的日志是跳过执行的如果 server_id 配错成一样这个保护就失效了就会出现事务在两个主库之间反复传递。发现后立刻修改其中一侧的 server_id 并重启再重新 CHANGE MASTER从干净的起点恢复。5.3 主从切换和故障恢复的关键注意点双主双从的高可用价值最终体现在主库故障后的切换是否顺畅。这里有一个我自己踩过很多次的坑主库 A 宕机后直接强制把应用切到主库 B结果 A 恢复后重新加入集群时两边数据已经不一致A 的 binlog 和 B 的 binlog 互相冲突。所以切换前不能头脑发热。正确的流程应该是先确认 A 上最后提交的事务是否已经同步到 B。如果是半同步复制这个确认比较有底气如果是异步复制尽量等 A 恢复后把它剩余的 binlog 手动补到 B 上或者干脆用 B 当前的数据重新初始化 A。如果 A 恢复时 B 已经接了新写入数据冲突几乎不可避免这时候唯一的选择就是让 A 以 B 的数据为准重新做全量同步把 A 降级为从库而不是抱着 A 的历史数据去覆盖 B。另外从库在切换后一定要记得关闭 read_only 和 super_read_only否则应用切到新主库后所有写操作都会失败。很多团队在演练时都栽在这一步配置半天才发现新主库只读参数没有去掉。6. 双主双从的延伸经验与个人实操体会配置完全部复制链路后有几个经验值得单独说一说。第一个是监控。复制一旦中断是不会自动报警的主库继续接收写入从库是不是已经落后几个小时完全没人发现。我的习惯是写一个简单的巡检脚本每隔 30 秒检查一次所有从库的 SHOW SLAVE STATUS发现 Slave_IO_Running 或 Slave_SQL_Running 不为 Yes立刻告警。脚本核心逻辑很直接但非常管用。第二个经验是复制链路的网络时延。主库和从库如果不在同一个机房或者中间经过防火墙限制每秒处理的 binlog 量会直接体现在 Seconds_Behind_Master 上。双主场景尤其明显因为两个主库之间互相传输日志稍有网络抖动两边都会表现为复制延迟然后应用可能误判主库故障触发不必要的切换。所以双主双从对网络的稳定性和延迟要求比一主一从高得多。第三个经验是不要把双主当成负载均衡来用。很多团队搭完双主双从后就想着让应用一半写 A、一半写 B美其名曰“分担写入压力”。但 MySQL 复制是逻辑日志回放字节数往往比主库直接执行要高好几倍两边互相复制的开销并不小。双主的定位应该是“高可用”而不是“写入扩展”。如果写入量真的很大应该考虑分库分表而不是加第二个主库。最后再说一点操作细节。我在每次执行 CHANGE MASTER 之前都会在从库上先执行一条 STOP SLAVE清掉可能残留的复制状态再执行 CHANGE MASTER。配置完成后不要急着看 IO 线程是否连上先看 Last_IO_Error 和 Last_SQL_Error。这两个字段几乎不撒谎任何问题都会直接写在里面比猜靠谱得多。这套一主一从加双主双从的链路我从配置到运维已经反复折腾过很多次。如果你也是刚开始接触建议先找几台虚拟机把一主一从完全跑通再在上面加第二个主库体会一下双主之间相互复制的行为差异。真正理解了 server_id、binlog position、中继回放这三件事以后遇到任何复制问题你都会比看文档更容易定位。
1. 项目概述一主一从与双主双从分别解决什么问题先说说这次动手搭建的背景。很多团队在数据库从单机走向高可用的过程中第一步想到的就是“主从复制”。MySQL主从复制本身并不难但不少人把一主一从配通之后就开始纠结还要不要做双主双从这两个架构之间的取舍经常讲不清楚。我自己也经历过这个阶段所以这次把一主一从和双主双从的搭建过程完整梳理一遍把每一步的配置、验证方法、踩坑点都写出来希望能帮正准备做复制方案的人少走弯路。先说结论一主一从适合解决“读压力大”和“备份窗口紧张”的问题核心是让从库分担查询流量并在主库出问题时提供一份实时可用副本。双主双从则是在一主一从基础上把写入侧的高可用也纳入考虑两个主库互相复制任意一个主库故障时另一个仍能继续接收写入另外两个从库继续分摊读请求。也就是说一主一从是“读有冗余”双主双从是“写也有冗余”。这套方案适合谁来参考如果你是刚接触MySQL复制、想搭一套测试环境练手的人里面的一主一从部分可以一步步照做如果你已经能跑通主从正在思考如何升级成双主双从可以直接看后半部分。文中默认环境是 Linux MySQL 8.0但除个别参数外同样适用于 MySQL 5.7。下面我按实际搭建顺序来写从原理到配置再到问题排查尽量把当初自己遇到的疑问都讲透。2. 动手之前必须理解的复制机制和几个关键概念不夸张地说主从复制配不顺畅80%的原因是对复制链路认识不清。很多配置文件抄过来却不知道为什么这么写一旦报错就完全没法下手。所以在写配置之前先把复制链条里的几个角色讲清楚。2.1 binlog、relay log 与复制线程的协作关系主从复制的本质是“主库把变更记录成二进制日志从库把这些日志回放一遍”。整个链路里有两个核心日志文件主库的 binlog以及从库的 relay log。binlog 记录的是主库上已经提交的、可能导致数据变化的操作relay log 则是从库把主库拉取过来的 binlog 内容先落到本地磁盘后再一条条回放。背后的执行者是三个线程。主库上有一个 dump 线程负责把 binlog 推给从库从库上有一个 IO 线程负责接收主库推来的日志并写入 relay log从库上还有一个 SQL 线程负责读取 relay log 并依次执行。理解这三个线程非常重要因为很多排查工作其实就是看这三个线程卡在了哪一步。比如 IO 线程报错通常是网络、认证、日志位置有问题SQL 线程报错通常是执行到了主库没有、或者主键冲突的语句。用个生活化的类比主库像是在写日记binlog 就是记好的日记内容从库的 IO 线程相当于一个快递员每天把新写的日记页搬回自己家SQL 线程则是负责把搬回来的日记内容重新誊写一遍。只要快递员或者誊写环节任何一个出问题整个复制链路就会停下来。2.2 binlog position 与 GTID 两种复制方式的选型配置主从复制的核心就是告诉从库“从主库的哪个位置开始同步”。这个位置有两种表达方式一种是传统的 binlog 文件名 日志偏移量比如 MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS154另一种是 GTID全局事务标识符它给每个事务分配一个全局唯一编号从库直接按 GTID 集合去请求缺失事务即可。我的建议是新环境优先考虑 GTID理由很直接。GTID 方式下从库不需要手动记住 binlog 文件和偏移量如果从库挂了重搭、或者要做主从切换它自己会根据 GTID 集合判断从哪里继续大大降低了手工操作出错的风险。传统 position 方式虽然更直观但“从哪个文件哪个位置继续”完全依赖人工记录一旦记错复制就会从错误位置开始导致数据不一致。不过 GTID 也不是没有限制。如果你用的是很老版本的 MySQL或者从库要跟第三方工具做特殊联动传统方式可能兼容性更好。一般 5.7 之后我建议直接 GTID教程里我会把两种方式的关键步骤都写出来方便对照。2.3 server_id 为什么不能重复这是新手配置时最容易犯的错。MySQL 要求集群中每个实例的 server_id 必须唯一复制链路里从库会通过 server_id 判断一条日志是不是自己产生的。如果两台机器的 server_id 相同从库拿到 binlog 后可能不知道该不该执行轻则复制中断重则出现无穷无尽的“丢事务”现象。双主架构里 server_id 更是生死线。因为两个主库互相复制A 上产生的事务传给 BB 执行后又会写自己的 binlog这个 binlog 再传回 A。MySQL 内部有一个机制如果日志事件里的 server_id 和当前实例的 server_id 相同就跳过不回放。正是靠这个机制才避免了两边互相复制导致无限循环。可一旦 server_id 配重了这个机制就失灵了轻则循环复制重则关键数据被覆盖。所以每一台实例的 server_id 必须单独规划这个规划要在动手前写在纸上而不是临时猜。3. 一主一从搭建完整实操步骤下面进入正题先搭一套最基础的一主一从。这套环境我假设是两台 CentOS 服务器MySQL 8.0分别叫主库A和从库B。既然是演示我直接用 192.168.1.10 和 192.168.1.11 这种地址来写你换成自己环境里的实际IP即可。3.1 主库配置开启 binlog 并创建复制账号主库需要改 my.cnf通常是 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/ 下的配置文件加入下面几项[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW expire_logs_days 7 max_binlog_size 256M这些参数中server_id 是实例唯一标识log_bin 开启二进制日志文件前缀叫 mysql-binbinlog_formatROW 表示按行记录变更相比 statement 格式它在数据一致性上更可靠也是 MySQL 8.0 默认推荐的方式expire_logs_days 控制二进制日志保留天数避免磁盘被日志写满。注意如果做主从的同时还要做基于时间点的恢复expire_logs_days 不能设太短至少要比你的备份周期长。日志是恢复的原材料删早了真要恢复时就傻眼了。配置改好后重启 MySQLsystemctl restart mysqld然后登录主库创建专用的复制账号。复制账号不需要太大权限最小化原则CREATE USER repl192.168.1.% IDENTIFIED BY Repl123456; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.1.%;REPLICATION SLAVE 是从库拉取 binlog 所需的权限REPLICATION CLIENT 是让复制账号能执行 SHOW MASTER STATUS 等检查命令。很多新手只授权了 REPLICATION SLAVE后面排障时想看看主库状态都要换别的账号登录很麻烦所以每次我习惯顺手把 CLIENT 也加上。创建完账号后用这条命令查看当前 binlog 位置SHOW MASTER STATUS;记录下返回的 File 和 Position 两个值。等下配置从库时这两个值就是复制的起点。如果是空库或刚配置完 binlog一般情况下 File 是刚生成的第一个 binlogPosition 是 154 附近的初始值。3.2 从库配置relay log、只读账号与复制启动从库 B 的 my.cnf 配置如下[mysqld] server_id 2 relay_log relay-bin log_bin mysql-bin read_only 1relay_log 是从库回放日志的落盘文件前缀log_bin 建议从库也开启以后如果要从从库再拉一个级联从库或者把从库提升为主库它自己才有完整的 binlog。read_only1 让普通账号在从库上无法执行写操作但对拥有 SUPER 权限的账号无效这能防止应用误连从库写数据。提醒read_only 只挡普通 DML不挡 SUPER 权限。如果要彻底禁止写操作需要配置 super_read_only1MySQL 5.7.8 之后支持。我一般在从库上两个参数同时开。接下来在从库上执行 CHANGE MASTER以 position 方式为例CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154;注意MASTER_LOG_FILE 和 MASTER_LOG_POS 必须和刚才主库 SHOW MASTER STATUS 的结果一致。填错任何一项从库 IO 线程都会立刻报错。然后启动复制START SLAVE;用下面这条命令确认复制状态SHOW SLAVE STATUS\G重点看三行Slave_IO_Running 和 Slave_SQL_Running 是否都为 YesLast_Errno 是否为 0Seconds_Behind_Master 是否为 0。如果 IO 线程和 SQL 线程都是 Yes说明复制链路已经通了。这时候可以回到主库随便建一个库、建一张表、插几条数据再到从库看看是否同步整个一主一从就完成了。如果你是搭 GTID 模式主从两边的 my.cnf 都要加上gtid_mode ON enforce_gtid_consistency ON执行 CHANGE MASTER 时改成CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_AUTO_POSITION1;这里唯一要注意的是GTID 模式下从库不能再手动指定 binlog 文件和偏移量全部由 MASTER_AUTO_POSITION1 自动协商。两条路都能走通建议新环境直接 GTID。4. 双主双从的扩展思路与完整搭建流程一主一从跑顺后有人会问能不能再加一个主库让两个主库都能写这就是双主双从。双主双从的常见拓扑是两个主库互为主从主库 A 带从库 A1主库 B 带从库 B1。A 故障时业务可以把写请求切到 BB 上的从库继续服务读请求。这样一来读和写都有了备份节点。4.1 双主环境下最核心的问题写冲突与自增主键先泼一盆冷水双主不是简单地把两个一主一从拼在一起最大难点在“两边同时写”时如何避免主键冲突。假设一张表的主键是自增的主库 A 有一条 id1 的记录主库 B 同时也生成一条 id1 的记录两边互相复制后A 上会有两条 id1 的记录直接主键冲突。解决思路是让两侧生成的 id 错开。MySQL 提供了两个关键参数auto_increment_increment 和 auto_increment_offset。前者规定自增值每次增加多少后者规定从哪个数字开始。比如两个主库各设置主库 Aauto_increment_increment 2, auto_increment_offset 1主库 Bauto_increment_increment 2, auto_increment_offset 2这样 A 生成的 id 是 1、3、5、7……B 生成的 id 是 2、4、6、8……两者永远不撞车。注意两个参数必须同时设置只在一边设置会导致另一边的 id 也发生偏移反而更容易冲突。如果以后要扩展到三主increment 设成 3offset 分别设 1、2、3以此类推。提醒自增错开解决了新写入的冲突但对于历史数据如果两边原本都自己插入过 id1 的数据复制过程中依旧会报 1062 主键重复错误。双主架构要求两边初始数据是同一份且写请求做好分片规划不能同一个业务在两边同时写同一张表。另一个需要提前处理的是循环复制问题。A 的事务同步到 B 后B 执行这条事务又会生成新的 binlog 事件。由于 server_id 不同这个事件再同步回 A 时MySQL 通过比对 server_id 能识别出这是自己产生的事务会自动跳过。但前提是 server_id 严格唯一且配置正确。这再次说明 server_id 规划是双主的地基。4.2 双主双从的完整配置实例继续用四台机器的例子来说明规划如下机器角色server_id自增设置192.168.1.10主库 A1increment2, offset1192.168.1.11主库 B2increment2, offset2192.168.1.12从库 A13read_only192.168.1.13从库 B14read_only第一步四台机器的 my.cnf 都开启 binlog 和 relay log其中主库 A、主库 B 的配置类似[mysqld] server_id 1 log_bin mysql-bin binlog_format ROW expire_logs_days 7 relay_log relay-bin auto_increment_increment 2 auto_increment_offset 1主库 B 的 server_id 改成 2auto_increment_offset 改成 2其他相同。第二步在两台主库上分别创建复制账号命令和 3.1 中一样host 要覆盖到对方主库和从库的 IP 段。第三步初始化数据。双主复制要求两边从同一份数据开始否则复制过程中会因为数据不一致报错。通常做法是先把主库 A 的数据备份出来恢复到主库 B 和两个从库。备份时推荐 mysqldump 加 --single-transaction --master-data2这样不会锁表而且备份文件里会记录当时主库 A 的 binlog 位置。对于大库可以用 xtrabackup原理类似。这一步不能省不要试图直接启动复制再让它自己追平两边初始数据不一致时start slave 会立刻报错。第四步在两台主库上互相配置复制链路。在主库 B 上执行CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;在主库 A 上执行CHANGE MASTER TO MASTER_HOST192.168.1.11, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;如果使用 GTID则两边都改成 MASTER_AUTO_POSITION1。第五步配置两台从库。从库 A1 以主库 A 为源从库 B1 以主库 B 为源-- 从库 A1 上执行 CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE; -- 从库 B1 上执行 CHANGE MASTER TO MASTER_HOST192.168.1.11, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;全部执行完后四台机器的 Slave_SQL_Running 都应该为 Yes。可以在主库 A 插入数据观察是否会同步到主库 B、从库 A1、从库 B1再反过来从主库 B 写入观察是否同步到主库 A。整个双向复制链路就算跑通了。4.3 双主中间件选型应用层切换还是VIP漂移双主双从搭好后还有一个绕不开的问题应用到底怎么连两个主库都能写但应用不可能同时连接两个主库否则会出现“一端写 A、一端写 B、两边互相覆盖同一行”的尴尬。常见的做法有两种一种是应用层做读写分离和数据源路由根据业务标识把写请求固定路由到其中一个主库。这个方案灵活但应用改造量大而且一旦主库故障路由规则要能快速切换。另一种是加一层 VIP虚拟IP漂移平时 VIP 绑定在主库 A 上A 故障时把 VIP 切到主库 B。应用始终连接同一个虚拟IP感觉不到后端切换。这个方案对应用透明但需要额外引入高可用组件来监控主库健康状态并执行漂移比如常见的高可用脚本或集群管理工具。我在实际项目中更推荐第二种原因很简单它对业务入侵最小而且和“双主双从提供写可用性”的目标最一致。不过需要注意的是VIP 漂移只能解决连接切换不能解决数据一致性的时间差。A 刚写了一批数据还没同步完就宕机切到 B 时最新提交的少量数据可能暂时丢了。这是异步复制的固有代价如果要缩小丢失窗口需要开半同步复制这部分下面单独讲。4.4 半同步复制的作用与配置方法默认的异步复制中主库提交事务时不会等待从库确认所以主库宕机后从库上很可能缺少最后几个事务。半同步复制的思路是主库在提交事务时至少要等待一个从库确认收到 binlog 后再向客户端返回成功这样主库宕机时至少有一个从库完整拥有最近的事务。MySQL 8.0 自带半同步插件安装配置步骤如下INSTALL PLUGIN rpl_semi_sync_source SONAME semisync_source.so;从库上则安装INSTALL PLUGIN rpl_semi_sync_replica SONAME semisync_replica.so;主库的 my.cnf 加rpl_semi_sync_source_enabled 1 rpl_semi_sync_source_timeout 3000从库的 my.cnf 加rpl_semi_sync_replica_enabled 1注意半同步复制虽然有“至少一个从库确认”的保障但从库确认的只是“收到了 binlog 并写入 relay log”并不代表 SQL 线程已经执行完。所以它降低的是“日志丢失”风险而不是“数据完全一致”风险。另外如果从库确认超时上面配置的 3000 毫秒内没回应主库会自动退化为异步复制继续提供服务。这是设计上的保护机制不然一个从库抖动就会拖垮整个主库写入。5. 常见问题与排查实录复制链路涉及网络、账号权限、日志位置、MySQL 版本等多个层面报错是常态关键是能快速定位。下面把最常遇到的问题整理出来基本都是搭建和运维过程中真实踩过的坑。5.1 复制线程起不来或频繁中断的典型报错先看一张速查表错误场景典型表现常见原因处理方向无法连接主库Last_IO_Error: error connecting to master网络不通、账号密码错、主库 MySQL 未监听外网ping 测试、查看 bind_address、检查 repl 账号 host 授权认证失败Access denied for user repl账号未创建、密码错、host 范围不匹配用主库上授权语句核对日志位置错误Got fatal error 1236MASTER_LOG_FILE/POS 和主库不一致重新 CHANGE MASTER用 SHOW MASTER STATUS 的准确值SQL 线程报错Error 1062: Duplicate entry从库已有同主键数据确认重复原因必要时跳过该事务或重建从库缺少数据Error 1032: Cant find record从库本来就没有这一行或该行已被修改大概率是初期数据不一致需要重新同步延迟Seconds_Behind_Master 持续增长大事务、慢SQL、主从硬件差异排查大事务分析慢查询优化从库硬件以 1236 错误为例它是最容易让新手迷惑的。只说一句“Got fatal error 1236, master may be empty or too old”很多人就懵了。这句话的意思是从库请求的 binlog 文件或偏移量在主库上已经不存在了。可能是因为 binlog 被 expire_logs_days 清理了也可能是因为从库记录的起点比主库当前最早的 binlog 还早。解决方法是找到主库当前能提供的最早 binlog 位置重新 CHANGE MASTER如果数据已经差得太多直接重新做一次全量备份再初始化从库反而更快。5.2 双主复制时主键冲突与循环复制的实战处理双主环境里最常见的是 1062 主键重复。比如两边各有一张业务表自增参数没配置好两边都生成了 id2 的记录复制一启动SQL 线程直接报错停下。这种时候不建议暴力跳过错误因为跳过的可能正是别人在主库上已经提交过的关键数据。正确做法是先在两边分别查询冲突记录确认哪边的数据是业务的最终版本然后删除或覆盖另一边的数据再重启 SQL 线程。如果这条错误本质上是因为双主方案本身规划不合理比如应用真的在同时写两边那么就算跳过这次下次还会冲突必须从源头调整路由。循环复制的问题通常表现为两个主库的 binlog 文件增长速度特别快但实际业务写入并不多。原因基本是 server_id 重复。排查方法是在两边分别执行 SHOW MASTER STATUS对比 binlog 文件增长情况再检查每个实例的 server_id 配置。MySQL 内部对同 server_id 的日志是跳过执行的如果 server_id 配错成一样这个保护就失效了就会出现事务在两个主库之间反复传递。发现后立刻修改其中一侧的 server_id 并重启再重新 CHANGE MASTER从干净的起点恢复。5.3 主从切换和故障恢复的关键注意点双主双从的高可用价值最终体现在主库故障后的切换是否顺畅。这里有一个我自己踩过很多次的坑主库 A 宕机后直接强制把应用切到主库 B结果 A 恢复后重新加入集群时两边数据已经不一致A 的 binlog 和 B 的 binlog 互相冲突。所以切换前不能头脑发热。正确的流程应该是先确认 A 上最后提交的事务是否已经同步到 B。如果是半同步复制这个确认比较有底气如果是异步复制尽量等 A 恢复后把它剩余的 binlog 手动补到 B 上或者干脆用 B 当前的数据重新初始化 A。如果 A 恢复时 B 已经接了新写入数据冲突几乎不可避免这时候唯一的选择就是让 A 以 B 的数据为准重新做全量同步把 A 降级为从库而不是抱着 A 的历史数据去覆盖 B。另外从库在切换后一定要记得关闭 read_only 和 super_read_only否则应用切到新主库后所有写操作都会失败。很多团队在演练时都栽在这一步配置半天才发现新主库只读参数没有去掉。6. 双主双从的延伸经验与个人实操体会配置完全部复制链路后有几个经验值得单独说一说。第一个是监控。复制一旦中断是不会自动报警的主库继续接收写入从库是不是已经落后几个小时完全没人发现。我的习惯是写一个简单的巡检脚本每隔 30 秒检查一次所有从库的 SHOW SLAVE STATUS发现 Slave_IO_Running 或 Slave_SQL_Running 不为 Yes立刻告警。脚本核心逻辑很直接但非常管用。第二个经验是复制链路的网络时延。主库和从库如果不在同一个机房或者中间经过防火墙限制每秒处理的 binlog 量会直接体现在 Seconds_Behind_Master 上。双主场景尤其明显因为两个主库之间互相传输日志稍有网络抖动两边都会表现为复制延迟然后应用可能误判主库故障触发不必要的切换。所以双主双从对网络的稳定性和延迟要求比一主一从高得多。第三个经验是不要把双主当成负载均衡来用。很多团队搭完双主双从后就想着让应用一半写 A、一半写 B美其名曰“分担写入压力”。但 MySQL 复制是逻辑日志回放字节数往往比主库直接执行要高好几倍两边互相复制的开销并不小。双主的定位应该是“高可用”而不是“写入扩展”。如果写入量真的很大应该考虑分库分表而不是加第二个主库。最后再说一点操作细节。我在每次执行 CHANGE MASTER 之前都会在从库上先执行一条 STOP SLAVE清掉可能残留的复制状态再执行 CHANGE MASTER。配置完成后不要急着看 IO 线程是否连上先看 Last_IO_Error 和 Last_SQL_Error。这两个字段几乎不撒谎任何问题都会直接写在里面比猜靠谱得多。这套一主一从加双主双从的链路我从配置到运维已经反复折腾过很多次。如果你也是刚开始接触建议先找几台虚拟机把一主一从完全跑通再在上面加第二个主库体会一下双主之间相互复制的行为差异。真正理解了 server_id、binlog position、中继回放这三件事以后遇到任何复制问题你都会比看文档更容易定位。
绕过AOP代理的深层原理与解决方案)
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
