MySQL 8.0 双主互为热备配置实战笔记不少做运维和DBA的朋友应该都有过这种经历业务量不大不小上一套主从架构吧主库一挂从库要手动提主还得改应用连接串中间那几分钟业务直接不可用上Keepalived加VIP那套吧又怕脑裂脚本写得不好反而弄出双写。我前阵子刚好给一套内部系统做完MySQL 8.0的双主互为热备跑了大半年期间还真实演练过两次主库宕机切换感触挺深。先说清楚一个概念MySQL的双主Master-Master不是让你同时往两个库疯狂写业务的那是自寻死路。双主互为热备的核心思路是“一主一备、互为主备”平时只有一边承担写入另一边作为热备节点同步数据主库出问题时备库能在秒级内顶上来。这套方案好处很明显不需要额外引入第三方高可用组件纯靠MySQL自带的复制机制就能实现两个节点都能当主库切换时不用改架构配合Keepalived之类的虚拟IP方案应用端几乎无感知。本文面向的是有一定MySQL基础、想在生产环境搭建双主热备的运维、DBA、后端开发同学。我会把从环境准备、参数配置、复制搭建、切换到故障演练的完整过程都写出来顺便把我踩过的坑也一并交代清楚希望能帮你少走点弯路。1. 方案选型与整体思路1.1 为什么选双主而不是主从在决定做双主之前我先把市面上常见的几种方案过了一遍传统主从复制一个主库一个从库从库只读。主库挂掉后需要手动执行STOP SLAVE; RESET MASTER;之类操作把从库提升为新主库整个过程依赖人工判断业务中断时间不可控。MHAMaster High Availability能在数十秒内完成主从切换但部署较复杂需要额外节点做管理端而且MySQL 8.0的兼容性需要仔细踩坑。MGRMySQL Group Replication官方推荐的高可用方案但要求所有节点都开启GTID且对网络延迟比较敏感很多老业务改造成本高。双主Keepalived两节点互为主备通过虚拟IP对外提供服务切换逻辑清晰实现简单运维成本低。最终我选了双主方案理由很简单这套内部系统的数据量不算大单库几十GB级别并发写量也不高主要诉求是“别因为单点故障让业务停摆”。双主架构在满足需求的前提下比MHA和MGR都简单直接排查问题也更方便。1.2 双主方案的工作机制双主互为热备本质上是两个节点都开启binlog并且互相把对方当作自己的主库来复制。平时正常运行时Node APrimary------ Node BStandby ----------------------Node A承担读写Node B通过复制保持与Node A同步。当Node A宕机后Keepalived检测到故障虚拟IP漂移到Node BNode B接管读写。业务恢复后Node A经过修复重新上线此时Node A反而变成了Node B的从库数据追平后可以随时再次切换回去。这里面有个非常关键的点既然两个节点互为主备那就会存在“两边同时写入”的可能性。一旦双写发生binlog里的数据冲突轻则复制报错中断重则导致数据错乱。所以必须从架构层面避免双写最常用的手段就是Keepalived的虚拟IP——同一时间只有持有VIP的节点能对外提供服务另一节点的MySQL虽然开着但应用根本连不上它。另外还可以在数据库账号层面做限定比如专门创建一个只允许从VIP来源访问的账号最大程度上防止跳过高可用层面的直连写入。1.3 这套方案的适用边界把丑话说在前面双主方案不是银弹。架构上双主更适合“读写分离不明显、单点写入为主、但需要高可用”的场景。如果你的业务有非常大的并发写入、需要水平扩展或者两个库处于不同数据中心需要跨地域容灾那双主方案就不合适了。跨机房做双主延迟会显著影响复制链路的实时性一旦网络抖动备库延迟可能飙到几秒甚至几十秒这时候“热备”其实已经变成“温备”了。所以做之前先想清楚自己的业务边界。我这次部署的范围在同一个机房的同一网段内网络延迟低于1ms这才敢放心用双主。2. 环境准备与核心参数配置2.1 服务器与系统环境我这边用的两台机器配置如下项目Node ANode B主机名db-master-01db-master-02IP地址192.168.10.11192.168.10.12虚拟IP192.168.10.10Keepalived漂移192.168.10.10操作系统CentOS 7.9CentOS 7.9MySQL版本8.0.368.0.36硬件配置4核8G / SSD4核8G / SSD两台机器清一色CentOS 7.9MySQL用的是8.0.36社区版二进制包安装。之所以不用yum源直接装主要是为了统一版本和安装路径后续好维护。生产环境强烈建议两台的MySQL版本保持一致尤其是大版本一定要一致8.0和5.7之间做复制虽然可行但坑太多实在没必要给自己添堵。2.2 操作系统级优化在安装MySQL之前先对两台机器做同样的系统层面优化# 关闭防火墙和SELinux内网环境可以这么做外网环境建议放开特定端口 systemctl stop firewalld systemctl disable firewalld sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config # 调整系统文件句柄限制 cat /etc/security/limits.conf EOF mysql soft nofile 65535 mysql hard nofile 65535 mysql soft nproc 65535 mysql hard nproc 65535 EOF # 关闭透明大页减少内存分配带来的性能抖动 echo never /sys/kernel/mm/transparent_hugepage/enabled echo never /sys/kernel/mm/transparent_hugepage/defrag操作系统层面的优化其实经常被忽略但影响很大。文件句柄限制不够高并发下MySQL会报Too many open files透明大页开启时内存分配可能会让MySQL出现间歇性卡顿这在复制场景下会表现为莫名其妙的“备库延迟抖动”。先把地基打稳后面排查问题能少很多事。2.3 MySQL 8.0 配置文件详解MySQL 8.0的配置文件不像5.7那样在my.cnf里写skip-networking之类简单粗暴的参数就行8.0默认启用了一些新特性需要个性化调整的参数更多。两台机器除了server-id和server-uuid不同外核心参数保持一致。Node A的/etc/my.cnf关键配置[mysqld] # 基础设置 user mysql port 3306 basedir /usr/local/mysql datadir /data/mysql socket /tmp/mysql.sock pid-file /data/mysql/mysql.pid # 字符集与排序规则 character-set-server utf8mb4 collation-server utf8mb4_0900_ai_ci # InnoDB引擎设置 innodb_buffer_pool_size 4G innodb_log_file_size 512M innodb_flush_log_at_trx_commit 1 innodb_flush_method O_DIRECT # binlog设置 server-id 11 log_bin /data/mysql/logs/binlog binlog_format ROW binlog_row_image FULL expire_logs_days 15 max_binlog_size 512M sync_binlog 1 # 复制设置 gtid_mode ON enforce_gtid_consistency ON log_replica_updates ON skip_slave_start OFF relay_log /data/mysql/logs/relaylog relay_log_purge ONNode B只有两处不同server-id 12另外MySQL会自动生成不同的server-uuid这个不用手动干预。几个参数值得展开说说binlog_format ROW。MySQL 8.0默认就是ROW格式这也意味着复制时备库执行的是“行级变更”对于UPDATE、DELETE这类操作可以精确到具体受影响的行。ROW格式最大的好处是复制更安全不会因为SQL上下文不一致出现数据偏差。但要注意ROW格式下binlog会更大尤其是大批量UPDATE时每个受影响的行都会产生一条日志记录所以binlog大小参数max_binlog_size尽量调大些我这边设置的是512M。sync_binlog 1配合innodb_flush_log_at_trx_commit 1是数据安全要求比较高的标准配置。这意味着每次事务提交时binlog和InnoDB redo log都要同步落盘性能会有一定影响但换来的是极其重要的保障——主库在任何时刻宕机都不会丢已经提交事务的数据。对双主热备架构来说这个配置尤其关键因为备库就是靠binlog来同步的binlog丢一条就意味着备库数据不完整。gtid_mode ONenforce_gtid_consistency ON这是MySQL 5.6以后引入的GTID复制模式。以前的传统复制要手动指定MASTER_LOG_FILE和MASTER_LOG_POS一旦binlog做过清理或备库做过临时的跳过复制操作这个位点就很容易搞错。GTID模式下每个事务都有全局唯一的标识复制自动通过GTID定位位点主备切换后重新搭建复制变得异常简单。MySQL 8.0官方其实已经不太推荐传统复制方式了新搭的环境直接上GTID别犹豫。2.4 初始化MySQL实例MySQL 8.0的初始化命令和5.7不一样5.7用的mysql_install_db已经废弃8.0统一使用mysqld --initialize# 创建数据目录和日志目录 mkdir -p /data/mysql/logs chown -R mysql:mysql /data/mysql # 初始化数据库实例 /usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --initialize --usermysql --basedir/usr/local/mysql --datadir/data/mysql这里有个细节执行完初始化后root用户的临时密码会写到错误日志里需要先找出来grep temporary password /data/mysql/mysql-error.log然后用临时密码登录并立即修改密码mysql -uroot -p ALTER USER rootlocalhost IDENTIFIED BY Your-Strong-Passwd-2024;MySQL 8.0默认安装了validate_password组件对密码强度要求比较严格测试环境可以直接卸载生产环境建议保留并设置至少12位含大小写字母、数字和特殊字符的强密码。3. 复制账号创建与双主复制搭建3.1 创建复制专用账号MySQL 8.0的账号创建语句和5.7也有明显变化没有5.7里的GRANT ... IDENTIFIED BY写法了必须分两步走先建用户再授权在Node A上执行CREATE USER repl192.168.10.% IDENTIFIED WITH caching_sha2_password BY Repl-Str0ng-Passwd; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.10.%; FLUSH PRIVILEGES;在Node B上同样执行一遍账号相同。这里有个坑必须提醒你MySQL 8.0默认的认证插件是caching_sha2_password不是5.7时代的mysql_native_password。用caching_sha2_password做复制连接时首次连接需要传输明文密码这要求主库和备库之间能走加密通道或者明确开启get_public_key选项。如果搭建复制时报错Authentication plugin caching_sha2_password cannot be loaded或者连接后一直卡住多半就是这个问题。规避方式有两种二选一方案一建账号时强制使用mysql_native_passwordCREATE USER repl192.168.10.% IDENTIFIED WITH mysql_native_password BY Repl-Str0ng-Passwd;方案二保持默认的caching_sha2_password然后在备库执行CHANGE MASTER时加上公钥获取选项CHANGE MASTER TO ... , MASTER_PUBLIC_KEY_PATH /data/mysql/public_key.pem;我实际生产环境用的方案一虽然mysql_native_password是旧插件但它兼容性最好复制链路上少一个加密握手的过程内网环境安全性已经足够。3.2 主库备份数据并导入备库因为是新搭建两张库其实都是空库但这个步骤还是要走一遍因为你在生产环境操作的时候大概率不是从零开始的。标准做法是用mysqldump全量导出主库数据再导入备库。Node A上执行全量备份/usr/local/mysql/bin/mysqldump \ --single-transaction \ --master-data2 \ --all-databases \ --set-gtid-purgedON \ /tmp/full_backup_$(date %F).sql几个参数拆开讲--single-transaction利用InnoDB的MVCC机制在导出过程中拿到一个一致性的快照不会锁业务表。这个参数在5.7和8.0里都是InnoDB表全量备份的标配。--master-data2会在备份文件的头部注释里自动记录当前主库的binlog位点信息虽然我们用GTID模式但这个信息仍然有诊断参考价值。--set-gtid-purgedON这个参数在搭建GTID复制时非常关键。它会告诉备库“这份备份数据里已经包含哪些GTID事务了你从这些事务之后开始复制就行。”如果不加这个参数导入备库后GTID集合是空的备库会把导入的这堆数据也当作需要复制的事务可能导致主键冲突。然后把备份文件拷贝到Node B并导入scp /tmp/full_backup_*.sql 192.168.10.12:/tmp/ mysql -uroot -p /tmp/full_backup_*.sql导入完成后Node B的数据就和Node A一致了。3.3 双向复制链路的建立现在开始搭双主复制。先在Node B上把Node A当作主库-- 在Node B上执行 STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST 192.168.10.11, SOURCE_PORT 3306, SOURCE_USER repl, SOURCE_PASSWORD Repl-Str0ng-Passwd, SOURCE_AUTO_POSITION 1; START REPLICA;MySQL 8.0.23以后CHANGE MASTER TO正式更名为CHANGE REPLICATION SOURCE TOSTART SLAVE更名为START REPLICA老命令虽然还能用但新版本会提示deprecated。写新的自动化脚本时直接用新语法省得以后升级麻烦。然后在Node A上把Node B当作它的主库-- 在Node A上执行 STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST 192.168.10.12, SOURCE_PORT 3306, SOURCE_USER repl, SOURCE_PASSWORD Repl-Str0ng-Passwd, SOURCE_AUTO_POSITION 1; START REPLICA;注意这两条链路的SOURCE_USER用的都是同一套复制账号只不过Host方向相反。SOURCE_AUTO_POSITION 1表示开启GTID自动位点定位它比手动指定SOURCE_LOG_FILE和SOURCE_LOG_POS的好处在于即使备库本地已经执行了一部分事务它也能自动跳过不会重复执行导致报错。3.4 验证复制是否正常在Node A上执行一条测试SQLCREATE DATABASE test_repl; USE test_repl; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t1 (name) VALUES (hello), (world);然后到Node B上查一下SHOW DATABASES; SELECT * FROM test_repl.t1;如果两条复制链路都正常Node B上能看到test_repl库和t1表的数据。把名字反过来测试一遍——在Node B上建表插入数据看Node A能否同步过来。因为这是双主两个方向的复制都必须验证通过。另外别忘了用SHOW REPLICA STATUS\G查看复制状态重点看这几个字段Replica_IO_Running: Yes Replica_SQL_Running: Yes Seconds_Behind_Source: 0 Last_IO_Errno: 0 Last_SQL_Errno: 0Replica_IO_Running和Replica_SQL_Running必须同时为YesSeconds_Behind_Source为0表示已经追平主库没有延迟。Last_IO_Errno 和 Last_SQL_Errno 只要不是0就要立刻去错误日志里查原因。4. Keepalived 实现VIP自动漂移4.1 Keepalived 的安装与基础配置复制链路搭好只是第一步双主还差最后一环——让应用能自动切换到存活节点。这里我用Keepalived管理一个虚拟IPVIP哪个节点MySQL正常VIP就落在哪个节点上应用连的是VIP而不是真实IP数据库节点切换时应用完全无感知。两台机器都安装Keepalivedyum install -y keepalived主节点Node A的/etc/keepalived/keepalived.conf配置global_defs { router_id LVS_DEVEL_11 script_user root enable_script_security } vrrp_script check_mysql { script /etc/keepalived/check_mysql.sh interval 3 timeout 3 fall 2 rise 2 } vrrp_instance VI_MYSQL { state MASTER interface ens192 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1234 } virtual_ipaddress { 192.168.10.10/24 dev ens192 label ens192:vip } track_script { check_mysql } notify_master /etc/keepalived/notify.sh MASTER notify_backup /etc/keepalived/notify.sh BACKUP notify_fault /etc/keepalived/notify.sh FAULT }备节点Node B的配置基本一样只有两处不同router_id LVS_DEVEL_12 state BACKUP priority 90这里有个关键点Keepalived自身通过state和priority来决定谁是主平时Node AMASTER优先级100Node BBACKUP优先级90。当主节点故障后备节点通过VRRP协议感知不到主节点的心跳自动把VIP抢过来。如果主节点恢复它会发现自己的优先级更高又会把VIP抢回去。但因为双主架构里两个节点MySQL数据是一致的VIP来回切换并不会造成数据问题。4.2 MySQL存活检测脚本Keepalived不会天然知道MySQL是否健康它只负责检测本机IP网络是否正常。所以需要写一个脚本定时检查MySQL进程是否存活、端口是否能连通、是否能正常执行查询三重校验缺一不可。#!/bin/bash # /etc/keepalived/check_mysql.sh MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERrepl MYSQL_PASSWORDRepl-Str0ng-Passwd mysqladmin -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} ping /dev/null 21 if [ $? -eq 0 ]; then exit 0 else exit 1 fi脚本写完后记得加执行权限chmod x /etc/keepalived/check_mysql.sh在实际生产环境可能还需要考虑一种情况MySQL进程在但数据库已经僵死mysqladmin ping虽然能返回结果但查询卡住。更严格的做法是再写一个实际执行SQL查询的检测比如SELECT 1并设置超时时间。考虑到检测脚本每3秒跑一次如果里面卡住5秒反而会影响Keepalived自身的心跳所以我这边mysqladmin ping已经够用。4.3 切换时的业务恢复与收尾处理Keepalived把VIP从宕机节点漂移到存活节点后MySQL的数据一致性可能还差一点——因为宕机节点可能还有一部分binlog没来得及传给存活节点。这部分丢失的数据在“热备”场景下不可避免属于主库宕机时的最小RPO恢复点目标窗口。如果只是主机宕机MySQL文件还在但直接重新把宕机节点加回双主架构要小心。常规操作是# 在故障节点上停MySQL systemctl stop mysqld # 检查MySQL数据文件是否完好 ls -l /data/mysql/ # 将故障节点以从库角色重新加入它会自动通过GTID追平数据 # 不需要重新初始化启动后复制会自动通过GTID接着同步 systemctl start mysqld启动后故障节点会自动连接存活节点通过GTID把自己落后的数据补回来。补完之后这台节点的数据就和存活节点一致了双主架构恢复如初。如果故障节点损坏严重数据文件都坏了重建流程就是把存活节点的数据重新导出一份走一遍当初搭建双主的流程。5. 故障切换演练实录5.1 模拟主库挂掉架构搭好之后必须演练。纸上谈兵没用没真实切过几次真出问题的时候手忙脚乱反而容易二次事故。第一次演练我模拟的是Node A宕机# 模拟硬件故障直接把Node A的MySQL强制杀掉 kill -9 $(cat /data/mysql/mysql.pid)杀掉进程后立刻到Node B上看VIP是否漂移过来ip addr show ens192正常情况下192.168.10.10已经绑定到Node B的ens192网卡上。然后在Node B上验证业务可用性mysql -h192.168.10.10 -uroot -p SELECT 1;能查通就说明应用的连接已经被接管了。5.2 应用写入测试我在Node B上模拟业务写入USE test_repl; INSERT INTO t1 (name) VALUES (node-b-after-failover); SELECT * FROM t1;数据写入成功。此时整个系统的状态是VIP在Node B上业务读写正常Node A处于宕机状态复制链路自动中断应用层面没有做过任何改动连接串用的还是VIP接着把Node A恢复systemctl start mysqldNode A启动后由于${server-id}和GTID的自动追踪它会自动把自己在宕机期间落后的数据从Node B补回来。大约几秒后检查复制状态SHOW REPLICA STATUS\G此时Node A相对于Node B的角色是“备”但它的数据已经追平。如果没有人力介入VIP仍然在Node B上——因为Keepalived不会因为Node A恢复了就立刻把VIP切回去这符合高可用的预期。只有当Node B也挂掉、或者我们手动把Node B的Keepalived停掉时VIP才会再次漂移回Node A。这个机制我需要特别说明一下双主架构在故障恢复后有“主备角色互换”的现象。Node A挂了再回来它会发现自己原来的复制链路已经被GTID自动接管当前的实际主库是Node B。这时候如果想让Node A重新变回主库只需要把VIP手动停掉再重新拉起Keepalived的优先级机制就会让VIP回到Node A。MySQL层不需要做任何切换操作因为两个节点的数据已经通过复制保持一致。5.3 脑裂风险的思考使用Keepalived做高可用最怕的就是脑裂——两个节点都认为自己是主都持有VIP都在处理写入。这会导致两份数据同时变化复制链路上必然出现主键冲突。Keepalived防脑裂最有效的机制是VRRP的组播心跳。正常情况下Node A和Node B每1秒互相通信一次节点只有在超过3个心跳周期约3秒没收到对方消息后才会尝试接管VIP。如果两台机器之间的网络闪断两边都会以为自己成了孤主从而同时绑定VIP。降低脑裂风险我这边做了几件事保证两台机器在同一个二层网络Keepalived的心跳走的是组播跨三层需要特殊配置我不建议搞复杂。检测脚本里调用mysqladmin ping时其实还会隐性验证一下本机MySQL状态如果MySQL都挂了即便Keepalived认为自己是MASTERVIP绑上了业务也照样连不上相当于自动降级。在较新版本的Keepalived里可以给vrrp_instance配nopreempt或者unicast_src_ip等参数来微调行为但生产环境没特殊需求不建议动默认行为经过大量验证是最稳的。说实话双主Keepalived的方案在脑裂场景下做不到绝对无风险但对大多数中小规模业务来说这个性价比已经很高了。真要彻底解决脑裂得上分布式共识协议比如etcd、或者用MGR这类强一致方案那是另一个话题本文不展开。6. 常见问题与排查技巧6.1 复制报错主键冲突双主复制最经典的坑就是主键冲突。比如业务通过VIP写入Node A但同时有人直连Node B绕过VIP也插入了一条相同主键的数据Node B的binlog把这条变更同步给Node A时Node A发现主键已存在复制链路就会卡住。排查方式SHOW REPLICA STATUS\G看到Last_SQL_Errno: 1062同时Last_SQL_Error提示Duplicate entry。处理方法分两步第一步确认哪条SQL出错去主库写入源查看这条数据是否有必要保留如果确实是业务误操作直接在“真正的主库”删除那条数据。第二步让复制跳过这个错误事务。GTID模式下比较标准的做法是STOP REPLICA; SET GTID_NEXT xxx-xxx-xxx:123; -- 这个GTID来自Last_SQL_Error里的报错信息 BEGIN; COMMIT; SET GTID_NEXT AUTOMATIC; START REPLICA;但你必须清楚跳过事务是治标不治本。如果同一类问题反复出现说明有人/有应用绕过VIP直连备库写数据这才是根源。我的建议是除了应用账号外双主节点上的MySQL只开root本地登录复制账号严格控制网段从连接源头上杜绝备库被直连写入的可能。6.2 复制延迟突然变大双主环境下复制延迟一般都很低毫秒级。如果某天SHOW REPLICA STATUS里Seconds_Behind_Source突然变成几十秒甚至几百秒先别急着怀疑网络多数情况是以下几种原因备库有大事务在跑比如一次DELETE删了几百万行binlog在备库重放时需要时间这个属于正常现象等它跑完就会追上。备库的磁盘IO能力不足。复制线程在备库是单线程写盘主库那边的并发写入在备库上会串行化。如果备库是机械盘而主库是SSD延迟会非常明显。解决方法要么提高备库硬件要么开启多线程复制。大表DDL。MySQL 8.0的DDL虽然支持INSTANT算法但一些场景比如修改字段长度仍需要重建表在线DDL期间复制会停滞。MySQL 8.0默认开启多线程复制MTS可以通过下面SQL看一下当前的配置SHOW VARIABLES LIKE replica_parallel_workers;默认值是4。如果备库CPU核数充足可以适当调大比如改成8提升并行回放能力。6.3 GTID清理导致复制无法启动GTID模式有个隐藏问题如果主库长时间没有清理binlog但GTID_PURGED集合一直膨胀备库重新搭建复制时可能报错。反过来如果你在主库执行过RESET MASTER或者手动清理了binlog导致主库最老的GTID都大于备库当前已有的GTID备库会报The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs containing GTIDs that the slave requires.这个问题最常见的诱因是手动删过binlog# 不推荐在生产环境这样操作 rm /data/mysql/logs/binlog.0000xx正确做法是用MySQL自己的清理机制-- 推荐设置自动过期清理 SET GLOBAL binlog_expire_logs_seconds 86400 * 5; -- 或手动但安全的方式PURGE BINARY LOGS BEFORE PURGE BINARY LOGS BEFORE NOW() - INTERVAL 5 DAY;MySQL 8.0里expire_logs_days已经被binlog_expire_logs_seconds取代这是另一个容易踩坑的点。如果真遇到了GTID被清理的报错重建备库数据是唯一可靠方案mysqldump --single-transaction --set-gtid-purgedON --all-databases /tmp/full_backup.sql然后重新导入、重新CHANGE REPLICATION SOURCE TO。没有捷径可走。6.4 MySQL 8.0 认证插件兼容性前面提过caching_sha2_password的问题这里再补充一个更具体的场景。如果是MySQL 8.0和其他版本比如5.7混合作双主或者客户端工具版本很老很容易遇到ERROR 2061 (HY000): Authentication plugin caching_sha2_password reported error: Authentication requires secure connection.如果你的业务方连的还是8.0之前的老版本驱动要么升级驱动要么建用户时指定mysql_native_password。从长期看我建议升级驱动因为mysql_native_password在MySQL 8.0里虽然还能用官方已在9.0版本中移除到时候又得折腾一遍。6.5 主从数据不一致的定期校验双主架构里最怕的就是两个节点数据悄悄出现偏差但这种偏差平时不一定会立刻暴露——直到某次切换时业务数据对不上才发现损失就大了。我固定每周末凌晨跑一次数据校验-- Node A上对某张表做checksum CHECKSUM TABLE test_repl.t1;然后去Node B执行同样的命令对比。数据量大的表用CHECKSUM TABLE会比较慢可以在业务低峰期跑。更精细的方案是用pt-table-checksumPercona Toolkit它可以分批对比并生成差异报告是生产环境数据一致性校验的标配工具。双主架构建议把校验结果接入监控告警一旦发现两个节点数据不一致第一时间告警出来。7. 最后一些经验之谈这套双主热备方案上线至今跑了大概8个月中间真实故障切换过2次分别是主机硬件故障和一次误操作杀进程整个切换过程业务侧基本没有感知备库在秒级内接管了VIP。复盘来看这套方案最大的价值其实不是“技术上的高级”而是“故障发生时心里有底”。如果要给还没上车的同学三条建议第一双主热备不是拷贝一份配置、执行几条SQL就完事的。真正的功夫在故障演练和日常巡检上。建议每个月至少做一次主备切换演练把kill进程、模拟断网、模拟磁盘满这些场景都过一遍这样真出事的时候才不会手忙脚乱。第二监控必须到位。我这边把VIP状态、复制状态Seconds_Behind_Source、Replica_IO_Running、Replica_SQL_Running、binlog大小、磁盘空间都做了监控告警任何一项异常都会在5分钟内通知到人。高可用不是为了不故障而是为了故障时能快速发现、快速处置。第三回切流程要写成文档。从实际经验看MySQL本身出问题的概率很低反而是“切换过去了但忘了怎么回切”这种运维操作层面的问题更常见。把回切步骤、验证方式、失败回滚方案都白纸黑字写清楚放到团队Wiki里并让每个值班的同学都实际动手演练过这才是这套配置能够长期稳定运行的最大保障。
MySQL 8.0双主互为热备实战：Keepalived+GTID高可用架构
MySQL 8.0 双主互为热备配置实战笔记不少做运维和DBA的朋友应该都有过这种经历业务量不大不小上一套主从架构吧主库一挂从库要手动提主还得改应用连接串中间那几分钟业务直接不可用上Keepalived加VIP那套吧又怕脑裂脚本写得不好反而弄出双写。我前阵子刚好给一套内部系统做完MySQL 8.0的双主互为热备跑了大半年期间还真实演练过两次主库宕机切换感触挺深。先说清楚一个概念MySQL的双主Master-Master不是让你同时往两个库疯狂写业务的那是自寻死路。双主互为热备的核心思路是“一主一备、互为主备”平时只有一边承担写入另一边作为热备节点同步数据主库出问题时备库能在秒级内顶上来。这套方案好处很明显不需要额外引入第三方高可用组件纯靠MySQL自带的复制机制就能实现两个节点都能当主库切换时不用改架构配合Keepalived之类的虚拟IP方案应用端几乎无感知。本文面向的是有一定MySQL基础、想在生产环境搭建双主热备的运维、DBA、后端开发同学。我会把从环境准备、参数配置、复制搭建、切换到故障演练的完整过程都写出来顺便把我踩过的坑也一并交代清楚希望能帮你少走点弯路。1. 方案选型与整体思路1.1 为什么选双主而不是主从在决定做双主之前我先把市面上常见的几种方案过了一遍传统主从复制一个主库一个从库从库只读。主库挂掉后需要手动执行STOP SLAVE; RESET MASTER;之类操作把从库提升为新主库整个过程依赖人工判断业务中断时间不可控。MHAMaster High Availability能在数十秒内完成主从切换但部署较复杂需要额外节点做管理端而且MySQL 8.0的兼容性需要仔细踩坑。MGRMySQL Group Replication官方推荐的高可用方案但要求所有节点都开启GTID且对网络延迟比较敏感很多老业务改造成本高。双主Keepalived两节点互为主备通过虚拟IP对外提供服务切换逻辑清晰实现简单运维成本低。最终我选了双主方案理由很简单这套内部系统的数据量不算大单库几十GB级别并发写量也不高主要诉求是“别因为单点故障让业务停摆”。双主架构在满足需求的前提下比MHA和MGR都简单直接排查问题也更方便。1.2 双主方案的工作机制双主互为热备本质上是两个节点都开启binlog并且互相把对方当作自己的主库来复制。平时正常运行时Node APrimary------ Node BStandby ----------------------Node A承担读写Node B通过复制保持与Node A同步。当Node A宕机后Keepalived检测到故障虚拟IP漂移到Node BNode B接管读写。业务恢复后Node A经过修复重新上线此时Node A反而变成了Node B的从库数据追平后可以随时再次切换回去。这里面有个非常关键的点既然两个节点互为主备那就会存在“两边同时写入”的可能性。一旦双写发生binlog里的数据冲突轻则复制报错中断重则导致数据错乱。所以必须从架构层面避免双写最常用的手段就是Keepalived的虚拟IP——同一时间只有持有VIP的节点能对外提供服务另一节点的MySQL虽然开着但应用根本连不上它。另外还可以在数据库账号层面做限定比如专门创建一个只允许从VIP来源访问的账号最大程度上防止跳过高可用层面的直连写入。1.3 这套方案的适用边界把丑话说在前面双主方案不是银弹。架构上双主更适合“读写分离不明显、单点写入为主、但需要高可用”的场景。如果你的业务有非常大的并发写入、需要水平扩展或者两个库处于不同数据中心需要跨地域容灾那双主方案就不合适了。跨机房做双主延迟会显著影响复制链路的实时性一旦网络抖动备库延迟可能飙到几秒甚至几十秒这时候“热备”其实已经变成“温备”了。所以做之前先想清楚自己的业务边界。我这次部署的范围在同一个机房的同一网段内网络延迟低于1ms这才敢放心用双主。2. 环境准备与核心参数配置2.1 服务器与系统环境我这边用的两台机器配置如下项目Node ANode B主机名db-master-01db-master-02IP地址192.168.10.11192.168.10.12虚拟IP192.168.10.10Keepalived漂移192.168.10.10操作系统CentOS 7.9CentOS 7.9MySQL版本8.0.368.0.36硬件配置4核8G / SSD4核8G / SSD两台机器清一色CentOS 7.9MySQL用的是8.0.36社区版二进制包安装。之所以不用yum源直接装主要是为了统一版本和安装路径后续好维护。生产环境强烈建议两台的MySQL版本保持一致尤其是大版本一定要一致8.0和5.7之间做复制虽然可行但坑太多实在没必要给自己添堵。2.2 操作系统级优化在安装MySQL之前先对两台机器做同样的系统层面优化# 关闭防火墙和SELinux内网环境可以这么做外网环境建议放开特定端口 systemctl stop firewalld systemctl disable firewalld sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config # 调整系统文件句柄限制 cat /etc/security/limits.conf EOF mysql soft nofile 65535 mysql hard nofile 65535 mysql soft nproc 65535 mysql hard nproc 65535 EOF # 关闭透明大页减少内存分配带来的性能抖动 echo never /sys/kernel/mm/transparent_hugepage/enabled echo never /sys/kernel/mm/transparent_hugepage/defrag操作系统层面的优化其实经常被忽略但影响很大。文件句柄限制不够高并发下MySQL会报Too many open files透明大页开启时内存分配可能会让MySQL出现间歇性卡顿这在复制场景下会表现为莫名其妙的“备库延迟抖动”。先把地基打稳后面排查问题能少很多事。2.3 MySQL 8.0 配置文件详解MySQL 8.0的配置文件不像5.7那样在my.cnf里写skip-networking之类简单粗暴的参数就行8.0默认启用了一些新特性需要个性化调整的参数更多。两台机器除了server-id和server-uuid不同外核心参数保持一致。Node A的/etc/my.cnf关键配置[mysqld] # 基础设置 user mysql port 3306 basedir /usr/local/mysql datadir /data/mysql socket /tmp/mysql.sock pid-file /data/mysql/mysql.pid # 字符集与排序规则 character-set-server utf8mb4 collation-server utf8mb4_0900_ai_ci # InnoDB引擎设置 innodb_buffer_pool_size 4G innodb_log_file_size 512M innodb_flush_log_at_trx_commit 1 innodb_flush_method O_DIRECT # binlog设置 server-id 11 log_bin /data/mysql/logs/binlog binlog_format ROW binlog_row_image FULL expire_logs_days 15 max_binlog_size 512M sync_binlog 1 # 复制设置 gtid_mode ON enforce_gtid_consistency ON log_replica_updates ON skip_slave_start OFF relay_log /data/mysql/logs/relaylog relay_log_purge ONNode B只有两处不同server-id 12另外MySQL会自动生成不同的server-uuid这个不用手动干预。几个参数值得展开说说binlog_format ROW。MySQL 8.0默认就是ROW格式这也意味着复制时备库执行的是“行级变更”对于UPDATE、DELETE这类操作可以精确到具体受影响的行。ROW格式最大的好处是复制更安全不会因为SQL上下文不一致出现数据偏差。但要注意ROW格式下binlog会更大尤其是大批量UPDATE时每个受影响的行都会产生一条日志记录所以binlog大小参数max_binlog_size尽量调大些我这边设置的是512M。sync_binlog 1配合innodb_flush_log_at_trx_commit 1是数据安全要求比较高的标准配置。这意味着每次事务提交时binlog和InnoDB redo log都要同步落盘性能会有一定影响但换来的是极其重要的保障——主库在任何时刻宕机都不会丢已经提交事务的数据。对双主热备架构来说这个配置尤其关键因为备库就是靠binlog来同步的binlog丢一条就意味着备库数据不完整。gtid_mode ONenforce_gtid_consistency ON这是MySQL 5.6以后引入的GTID复制模式。以前的传统复制要手动指定MASTER_LOG_FILE和MASTER_LOG_POS一旦binlog做过清理或备库做过临时的跳过复制操作这个位点就很容易搞错。GTID模式下每个事务都有全局唯一的标识复制自动通过GTID定位位点主备切换后重新搭建复制变得异常简单。MySQL 8.0官方其实已经不太推荐传统复制方式了新搭的环境直接上GTID别犹豫。2.4 初始化MySQL实例MySQL 8.0的初始化命令和5.7不一样5.7用的mysql_install_db已经废弃8.0统一使用mysqld --initialize# 创建数据目录和日志目录 mkdir -p /data/mysql/logs chown -R mysql:mysql /data/mysql # 初始化数据库实例 /usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --initialize --usermysql --basedir/usr/local/mysql --datadir/data/mysql这里有个细节执行完初始化后root用户的临时密码会写到错误日志里需要先找出来grep temporary password /data/mysql/mysql-error.log然后用临时密码登录并立即修改密码mysql -uroot -p ALTER USER rootlocalhost IDENTIFIED BY Your-Strong-Passwd-2024;MySQL 8.0默认安装了validate_password组件对密码强度要求比较严格测试环境可以直接卸载生产环境建议保留并设置至少12位含大小写字母、数字和特殊字符的强密码。3. 复制账号创建与双主复制搭建3.1 创建复制专用账号MySQL 8.0的账号创建语句和5.7也有明显变化没有5.7里的GRANT ... IDENTIFIED BY写法了必须分两步走先建用户再授权在Node A上执行CREATE USER repl192.168.10.% IDENTIFIED WITH caching_sha2_password BY Repl-Str0ng-Passwd; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.10.%; FLUSH PRIVILEGES;在Node B上同样执行一遍账号相同。这里有个坑必须提醒你MySQL 8.0默认的认证插件是caching_sha2_password不是5.7时代的mysql_native_password。用caching_sha2_password做复制连接时首次连接需要传输明文密码这要求主库和备库之间能走加密通道或者明确开启get_public_key选项。如果搭建复制时报错Authentication plugin caching_sha2_password cannot be loaded或者连接后一直卡住多半就是这个问题。规避方式有两种二选一方案一建账号时强制使用mysql_native_passwordCREATE USER repl192.168.10.% IDENTIFIED WITH mysql_native_password BY Repl-Str0ng-Passwd;方案二保持默认的caching_sha2_password然后在备库执行CHANGE MASTER时加上公钥获取选项CHANGE MASTER TO ... , MASTER_PUBLIC_KEY_PATH /data/mysql/public_key.pem;我实际生产环境用的方案一虽然mysql_native_password是旧插件但它兼容性最好复制链路上少一个加密握手的过程内网环境安全性已经足够。3.2 主库备份数据并导入备库因为是新搭建两张库其实都是空库但这个步骤还是要走一遍因为你在生产环境操作的时候大概率不是从零开始的。标准做法是用mysqldump全量导出主库数据再导入备库。Node A上执行全量备份/usr/local/mysql/bin/mysqldump \ --single-transaction \ --master-data2 \ --all-databases \ --set-gtid-purgedON \ /tmp/full_backup_$(date %F).sql几个参数拆开讲--single-transaction利用InnoDB的MVCC机制在导出过程中拿到一个一致性的快照不会锁业务表。这个参数在5.7和8.0里都是InnoDB表全量备份的标配。--master-data2会在备份文件的头部注释里自动记录当前主库的binlog位点信息虽然我们用GTID模式但这个信息仍然有诊断参考价值。--set-gtid-purgedON这个参数在搭建GTID复制时非常关键。它会告诉备库“这份备份数据里已经包含哪些GTID事务了你从这些事务之后开始复制就行。”如果不加这个参数导入备库后GTID集合是空的备库会把导入的这堆数据也当作需要复制的事务可能导致主键冲突。然后把备份文件拷贝到Node B并导入scp /tmp/full_backup_*.sql 192.168.10.12:/tmp/ mysql -uroot -p /tmp/full_backup_*.sql导入完成后Node B的数据就和Node A一致了。3.3 双向复制链路的建立现在开始搭双主复制。先在Node B上把Node A当作主库-- 在Node B上执行 STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST 192.168.10.11, SOURCE_PORT 3306, SOURCE_USER repl, SOURCE_PASSWORD Repl-Str0ng-Passwd, SOURCE_AUTO_POSITION 1; START REPLICA;MySQL 8.0.23以后CHANGE MASTER TO正式更名为CHANGE REPLICATION SOURCE TOSTART SLAVE更名为START REPLICA老命令虽然还能用但新版本会提示deprecated。写新的自动化脚本时直接用新语法省得以后升级麻烦。然后在Node A上把Node B当作它的主库-- 在Node A上执行 STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST 192.168.10.12, SOURCE_PORT 3306, SOURCE_USER repl, SOURCE_PASSWORD Repl-Str0ng-Passwd, SOURCE_AUTO_POSITION 1; START REPLICA;注意这两条链路的SOURCE_USER用的都是同一套复制账号只不过Host方向相反。SOURCE_AUTO_POSITION 1表示开启GTID自动位点定位它比手动指定SOURCE_LOG_FILE和SOURCE_LOG_POS的好处在于即使备库本地已经执行了一部分事务它也能自动跳过不会重复执行导致报错。3.4 验证复制是否正常在Node A上执行一条测试SQLCREATE DATABASE test_repl; USE test_repl; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t1 (name) VALUES (hello), (world);然后到Node B上查一下SHOW DATABASES; SELECT * FROM test_repl.t1;如果两条复制链路都正常Node B上能看到test_repl库和t1表的数据。把名字反过来测试一遍——在Node B上建表插入数据看Node A能否同步过来。因为这是双主两个方向的复制都必须验证通过。另外别忘了用SHOW REPLICA STATUS\G查看复制状态重点看这几个字段Replica_IO_Running: Yes Replica_SQL_Running: Yes Seconds_Behind_Source: 0 Last_IO_Errno: 0 Last_SQL_Errno: 0Replica_IO_Running和Replica_SQL_Running必须同时为YesSeconds_Behind_Source为0表示已经追平主库没有延迟。Last_IO_Errno 和 Last_SQL_Errno 只要不是0就要立刻去错误日志里查原因。4. Keepalived 实现VIP自动漂移4.1 Keepalived 的安装与基础配置复制链路搭好只是第一步双主还差最后一环——让应用能自动切换到存活节点。这里我用Keepalived管理一个虚拟IPVIP哪个节点MySQL正常VIP就落在哪个节点上应用连的是VIP而不是真实IP数据库节点切换时应用完全无感知。两台机器都安装Keepalivedyum install -y keepalived主节点Node A的/etc/keepalived/keepalived.conf配置global_defs { router_id LVS_DEVEL_11 script_user root enable_script_security } vrrp_script check_mysql { script /etc/keepalived/check_mysql.sh interval 3 timeout 3 fall 2 rise 2 } vrrp_instance VI_MYSQL { state MASTER interface ens192 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1234 } virtual_ipaddress { 192.168.10.10/24 dev ens192 label ens192:vip } track_script { check_mysql } notify_master /etc/keepalived/notify.sh MASTER notify_backup /etc/keepalived/notify.sh BACKUP notify_fault /etc/keepalived/notify.sh FAULT }备节点Node B的配置基本一样只有两处不同router_id LVS_DEVEL_12 state BACKUP priority 90这里有个关键点Keepalived自身通过state和priority来决定谁是主平时Node AMASTER优先级100Node BBACKUP优先级90。当主节点故障后备节点通过VRRP协议感知不到主节点的心跳自动把VIP抢过来。如果主节点恢复它会发现自己的优先级更高又会把VIP抢回去。但因为双主架构里两个节点MySQL数据是一致的VIP来回切换并不会造成数据问题。4.2 MySQL存活检测脚本Keepalived不会天然知道MySQL是否健康它只负责检测本机IP网络是否正常。所以需要写一个脚本定时检查MySQL进程是否存活、端口是否能连通、是否能正常执行查询三重校验缺一不可。#!/bin/bash # /etc/keepalived/check_mysql.sh MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERrepl MYSQL_PASSWORDRepl-Str0ng-Passwd mysqladmin -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} ping /dev/null 21 if [ $? -eq 0 ]; then exit 0 else exit 1 fi脚本写完后记得加执行权限chmod x /etc/keepalived/check_mysql.sh在实际生产环境可能还需要考虑一种情况MySQL进程在但数据库已经僵死mysqladmin ping虽然能返回结果但查询卡住。更严格的做法是再写一个实际执行SQL查询的检测比如SELECT 1并设置超时时间。考虑到检测脚本每3秒跑一次如果里面卡住5秒反而会影响Keepalived自身的心跳所以我这边mysqladmin ping已经够用。4.3 切换时的业务恢复与收尾处理Keepalived把VIP从宕机节点漂移到存活节点后MySQL的数据一致性可能还差一点——因为宕机节点可能还有一部分binlog没来得及传给存活节点。这部分丢失的数据在“热备”场景下不可避免属于主库宕机时的最小RPO恢复点目标窗口。如果只是主机宕机MySQL文件还在但直接重新把宕机节点加回双主架构要小心。常规操作是# 在故障节点上停MySQL systemctl stop mysqld # 检查MySQL数据文件是否完好 ls -l /data/mysql/ # 将故障节点以从库角色重新加入它会自动通过GTID追平数据 # 不需要重新初始化启动后复制会自动通过GTID接着同步 systemctl start mysqld启动后故障节点会自动连接存活节点通过GTID把自己落后的数据补回来。补完之后这台节点的数据就和存活节点一致了双主架构恢复如初。如果故障节点损坏严重数据文件都坏了重建流程就是把存活节点的数据重新导出一份走一遍当初搭建双主的流程。5. 故障切换演练实录5.1 模拟主库挂掉架构搭好之后必须演练。纸上谈兵没用没真实切过几次真出问题的时候手忙脚乱反而容易二次事故。第一次演练我模拟的是Node A宕机# 模拟硬件故障直接把Node A的MySQL强制杀掉 kill -9 $(cat /data/mysql/mysql.pid)杀掉进程后立刻到Node B上看VIP是否漂移过来ip addr show ens192正常情况下192.168.10.10已经绑定到Node B的ens192网卡上。然后在Node B上验证业务可用性mysql -h192.168.10.10 -uroot -p SELECT 1;能查通就说明应用的连接已经被接管了。5.2 应用写入测试我在Node B上模拟业务写入USE test_repl; INSERT INTO t1 (name) VALUES (node-b-after-failover); SELECT * FROM t1;数据写入成功。此时整个系统的状态是VIP在Node B上业务读写正常Node A处于宕机状态复制链路自动中断应用层面没有做过任何改动连接串用的还是VIP接着把Node A恢复systemctl start mysqldNode A启动后由于${server-id}和GTID的自动追踪它会自动把自己在宕机期间落后的数据从Node B补回来。大约几秒后检查复制状态SHOW REPLICA STATUS\G此时Node A相对于Node B的角色是“备”但它的数据已经追平。如果没有人力介入VIP仍然在Node B上——因为Keepalived不会因为Node A恢复了就立刻把VIP切回去这符合高可用的预期。只有当Node B也挂掉、或者我们手动把Node B的Keepalived停掉时VIP才会再次漂移回Node A。这个机制我需要特别说明一下双主架构在故障恢复后有“主备角色互换”的现象。Node A挂了再回来它会发现自己原来的复制链路已经被GTID自动接管当前的实际主库是Node B。这时候如果想让Node A重新变回主库只需要把VIP手动停掉再重新拉起Keepalived的优先级机制就会让VIP回到Node A。MySQL层不需要做任何切换操作因为两个节点的数据已经通过复制保持一致。5.3 脑裂风险的思考使用Keepalived做高可用最怕的就是脑裂——两个节点都认为自己是主都持有VIP都在处理写入。这会导致两份数据同时变化复制链路上必然出现主键冲突。Keepalived防脑裂最有效的机制是VRRP的组播心跳。正常情况下Node A和Node B每1秒互相通信一次节点只有在超过3个心跳周期约3秒没收到对方消息后才会尝试接管VIP。如果两台机器之间的网络闪断两边都会以为自己成了孤主从而同时绑定VIP。降低脑裂风险我这边做了几件事保证两台机器在同一个二层网络Keepalived的心跳走的是组播跨三层需要特殊配置我不建议搞复杂。检测脚本里调用mysqladmin ping时其实还会隐性验证一下本机MySQL状态如果MySQL都挂了即便Keepalived认为自己是MASTERVIP绑上了业务也照样连不上相当于自动降级。在较新版本的Keepalived里可以给vrrp_instance配nopreempt或者unicast_src_ip等参数来微调行为但生产环境没特殊需求不建议动默认行为经过大量验证是最稳的。说实话双主Keepalived的方案在脑裂场景下做不到绝对无风险但对大多数中小规模业务来说这个性价比已经很高了。真要彻底解决脑裂得上分布式共识协议比如etcd、或者用MGR这类强一致方案那是另一个话题本文不展开。6. 常见问题与排查技巧6.1 复制报错主键冲突双主复制最经典的坑就是主键冲突。比如业务通过VIP写入Node A但同时有人直连Node B绕过VIP也插入了一条相同主键的数据Node B的binlog把这条变更同步给Node A时Node A发现主键已存在复制链路就会卡住。排查方式SHOW REPLICA STATUS\G看到Last_SQL_Errno: 1062同时Last_SQL_Error提示Duplicate entry。处理方法分两步第一步确认哪条SQL出错去主库写入源查看这条数据是否有必要保留如果确实是业务误操作直接在“真正的主库”删除那条数据。第二步让复制跳过这个错误事务。GTID模式下比较标准的做法是STOP REPLICA; SET GTID_NEXT xxx-xxx-xxx:123; -- 这个GTID来自Last_SQL_Error里的报错信息 BEGIN; COMMIT; SET GTID_NEXT AUTOMATIC; START REPLICA;但你必须清楚跳过事务是治标不治本。如果同一类问题反复出现说明有人/有应用绕过VIP直连备库写数据这才是根源。我的建议是除了应用账号外双主节点上的MySQL只开root本地登录复制账号严格控制网段从连接源头上杜绝备库被直连写入的可能。6.2 复制延迟突然变大双主环境下复制延迟一般都很低毫秒级。如果某天SHOW REPLICA STATUS里Seconds_Behind_Source突然变成几十秒甚至几百秒先别急着怀疑网络多数情况是以下几种原因备库有大事务在跑比如一次DELETE删了几百万行binlog在备库重放时需要时间这个属于正常现象等它跑完就会追上。备库的磁盘IO能力不足。复制线程在备库是单线程写盘主库那边的并发写入在备库上会串行化。如果备库是机械盘而主库是SSD延迟会非常明显。解决方法要么提高备库硬件要么开启多线程复制。大表DDL。MySQL 8.0的DDL虽然支持INSTANT算法但一些场景比如修改字段长度仍需要重建表在线DDL期间复制会停滞。MySQL 8.0默认开启多线程复制MTS可以通过下面SQL看一下当前的配置SHOW VARIABLES LIKE replica_parallel_workers;默认值是4。如果备库CPU核数充足可以适当调大比如改成8提升并行回放能力。6.3 GTID清理导致复制无法启动GTID模式有个隐藏问题如果主库长时间没有清理binlog但GTID_PURGED集合一直膨胀备库重新搭建复制时可能报错。反过来如果你在主库执行过RESET MASTER或者手动清理了binlog导致主库最老的GTID都大于备库当前已有的GTID备库会报The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION 1, but the master has purged binary logs containing GTIDs that the slave requires.这个问题最常见的诱因是手动删过binlog# 不推荐在生产环境这样操作 rm /data/mysql/logs/binlog.0000xx正确做法是用MySQL自己的清理机制-- 推荐设置自动过期清理 SET GLOBAL binlog_expire_logs_seconds 86400 * 5; -- 或手动但安全的方式PURGE BINARY LOGS BEFORE PURGE BINARY LOGS BEFORE NOW() - INTERVAL 5 DAY;MySQL 8.0里expire_logs_days已经被binlog_expire_logs_seconds取代这是另一个容易踩坑的点。如果真遇到了GTID被清理的报错重建备库数据是唯一可靠方案mysqldump --single-transaction --set-gtid-purgedON --all-databases /tmp/full_backup.sql然后重新导入、重新CHANGE REPLICATION SOURCE TO。没有捷径可走。6.4 MySQL 8.0 认证插件兼容性前面提过caching_sha2_password的问题这里再补充一个更具体的场景。如果是MySQL 8.0和其他版本比如5.7混合作双主或者客户端工具版本很老很容易遇到ERROR 2061 (HY000): Authentication plugin caching_sha2_password reported error: Authentication requires secure connection.如果你的业务方连的还是8.0之前的老版本驱动要么升级驱动要么建用户时指定mysql_native_password。从长期看我建议升级驱动因为mysql_native_password在MySQL 8.0里虽然还能用官方已在9.0版本中移除到时候又得折腾一遍。6.5 主从数据不一致的定期校验双主架构里最怕的就是两个节点数据悄悄出现偏差但这种偏差平时不一定会立刻暴露——直到某次切换时业务数据对不上才发现损失就大了。我固定每周末凌晨跑一次数据校验-- Node A上对某张表做checksum CHECKSUM TABLE test_repl.t1;然后去Node B执行同样的命令对比。数据量大的表用CHECKSUM TABLE会比较慢可以在业务低峰期跑。更精细的方案是用pt-table-checksumPercona Toolkit它可以分批对比并生成差异报告是生产环境数据一致性校验的标配工具。双主架构建议把校验结果接入监控告警一旦发现两个节点数据不一致第一时间告警出来。7. 最后一些经验之谈这套双主热备方案上线至今跑了大概8个月中间真实故障切换过2次分别是主机硬件故障和一次误操作杀进程整个切换过程业务侧基本没有感知备库在秒级内接管了VIP。复盘来看这套方案最大的价值其实不是“技术上的高级”而是“故障发生时心里有底”。如果要给还没上车的同学三条建议第一双主热备不是拷贝一份配置、执行几条SQL就完事的。真正的功夫在故障演练和日常巡检上。建议每个月至少做一次主备切换演练把kill进程、模拟断网、模拟磁盘满这些场景都过一遍这样真出事的时候才不会手忙脚乱。第二监控必须到位。我这边把VIP状态、复制状态Seconds_Behind_Source、Replica_IO_Running、Replica_SQL_Running、binlog大小、磁盘空间都做了监控告警任何一项异常都会在5分钟内通知到人。高可用不是为了不故障而是为了故障时能快速发现、快速处置。第三回切流程要写成文档。从实际经验看MySQL本身出问题的概率很低反而是“切换过去了但忘了怎么回切”这种运维操作层面的问题更常见。把回切步骤、验证方式、失败回滚方案都白纸黑字写清楚放到团队Wiki里并让每个值班的同学都实际动手演练过这才是这套配置能够长期稳定运行的最大保障。
