大家好我是数据库小学妹 上周凌晨3点被一通电话吵醒。运维说从库CPU飙了应用在超时让我赶紧看看。我爬起来打开电脑迷迷糊糊登录服务器top一看——mysqldump。当时心里就咯噔一下备份怎么会搞出这么大动静排查下来才发现事情一环扣一环备份锁表主从延迟飙升应用跟着超时。从3点05分告警到3点52分恢复整整47分钟我那天基本没怎么睡。今天把这次排查过程整理出来。故障现场告警群在3点05分炸的消息一条接一条。从库CPU 100%主从延迟从0飙到300秒应用开始报超时。我登录从库服务器top一看mysqldump进程CPU占用80%多IO也在高位。再看从库状态SHOWSLAVESTATUSSeconds_Behind_Master已经300多了而且还在涨。备份为什么会锁表mysqldump备份时会执行FLUSH TABLES WITH READ LOCKFTWRL获取全局读锁。这个锁会阻塞所有写操作。为什么mysqldump要加这个锁因为它需要拿到一个一致性位点consistent snapshot。备份过程中如果有写操作备份出来的数据就不一致了——比如你备份到一半某个表被truncate了备份出来的数据就缺了一块。FTWRL的执行分三步Close tables— 关闭所有已打开的表把脏页刷到磁盘。这一步会等所有正在执行的语句结束Acquire global read lock— 拿到全局读锁之后所有写操作被阻塞Get binlog position— 记录当前binlog位点作为一致性快照的参照问题就出在第一步。如果有大查询在跑比如一个跑了20分钟的报表JOINClose tables会一直等它结束。关键是这期间FTWRL已经开始阻塞新的写操作了——锁已经在排队但还没拿到新的写请求全卡在那。更坑的是如果你用的是MyISAM引擎--single-transaction参数根本没用。这个参数只对InnoDB生效通过开启一个一致性读事务来避免FTWRL。MyISAM没有事务机制只能老老实实加全局锁。我那个场景从库上有个报表查询在跑执行了20多分钟。FTWRL等这个查询结束期间所有写操作都卡住了。备份锁表为什么会影响主库备份锁表影响的是从库但为什么会波及主库这个问题当时我也想了好一会。主从复制的基本流程主库执行写操作 → 写入binlog → 从库IO线程拉取binlog → 从库SQL线程回放。从库锁表后回放被阻塞binlog在从库这边就堆积起来了。半同步复制下问题更严重。如果你配置了半同步复制semi-sync replication主库提交事务后要等至少一个从库确认收到binlog才返回客户端。从库锁表导致回放卡住binlog确认也卡住主库的写操作直接被拖慢。我遇到的场景就是半同步主库的TPS从3000掉到了个位数。binlog堆积会撑爆磁盘。主库的binlog要等从库确认接收后才能清理。从库一直不确认主库的binlog就一直涨。我遇到过一次binlog涨了50多G磁盘直接告警。如果你配了expire_logs_daysMySQL会尝试清理过期binlog但如果还有从库没读完它不敢删磁盘空间就这么被撑满。并行复制场景下影响更大。如果从库开了MTSMulti-Threaded Slave多个worker线程并行回放binlog。FTWRL一锁所有worker线程全卡住延迟蹭蹭往上涨。我见过延迟从0飙到3000秒的就是因为MTS的协调线程在等全局锁。还有个容易忽略的点从库延迟变大如果有读写分离配置应用读到的是旧数据。用户下单成功刷新页面发现订单没出来投诉就来了。应用为什么会超时从库锁表、主从延迟应用为什么会超时如果应用配置了读写分离读请求会发到从库。从库锁表后读请求被阻塞等待锁释放。应用有超时设置比如30秒。从库锁表时间超过30秒应用就报超时。超时后应用可能会重试重试又打到从库又被阻塞。大量请求堆积连接池被耗尽应用开始报连接池已满。我那个场景应用配置了3次重试。每次重试都等30秒一个请求就等90秒。并发一上来连接池很快就满了。排查过程排查这个故障用了大概20分钟。说实话刚看到CPU 100%的时候我第一反应是磁盘满了或者内存泄漏完全没想到是备份的锅。第一步看系统资源排除硬件问题。top-ciostat-x13df-htop一看mysqldump进程CPU占用80%多。iostat的%util也在高位说明IO也在打满。磁盘空间还有余量排除磁盘满的可能。到这一步我基本确认是mysqldump的问题了但还得往下查影响面。第二步看从库复制状态确认延迟程度。SHOWSLAVESTATUS\G重点看这几个字段Seconds_Behind_Master: 347 Slave_SQL_Running: Yes Slave_IO_Running: Yes Last_Error: (空)SQL线程和IO线程都在跑没有报错说明不是复制本身出了问题而是回放被阻塞了。Seconds_Behind_Master 347秒而且还在涨这个延迟量级不是小问题。当时心里就凉了半截。第三步看从库进程找阻塞原因。SHOWPROCESSLIST;输出里看到大量这样的状态State: Waiting for table flush我愣了一下——等了几秒才反应过来这是FTWRL的特征。说明有全局读锁在排队。第四步找备份进程确认根因。psaux|grepmysqldump果然凌晨3点的定时备份任务在跑。命令大概是mysqldump --all-databases --master-data2 /data/backup/full.sql注意这里没加--single-transaction所以会走FTWRL。第五步确认锁的范围。SHOWOPENTABLESWHEREIn_use0;SELECT*FROMperformance_schema.metadata_locksWHEREOBJECT_TYPEGLOBAL;SHOW OPEN TABLES可以看到哪些表被锁了。如果你用的是MySQL 5.7performance_schema.metadata_locks能更精确地看到锁的类型和持有者。第六步评估主库影响。SHOWBINARYLOGS;binlog数量在涨说明从库确认跟不上主库的binlog清理不了。SHOWGLOBALSTATUSLIKERpl_semi_sync_master_no_tx;这个值在涨说明半同步复制已经在退化成异步了——主库等不到从库确认开始降级。第七步看应用日志评估业务影响。应用日志里大量Connection pool exhausted和Query timeout。这里我走了一小段弯路——一开始以为是应用代码bug查了半天才发现是从库锁表传导过来的。定位到问题后KILL了备份进程。主从延迟大概5分钟开始下降应用在10分钟左右恢复正常。解决方案这次故障后我做了几个改进。用xtrabackup替代mysqldumpxtrabackup是Percona的物理备份工具最大的优势是不需要FTWRL。它的工作原理分两个阶段备份阶段直接拷贝InnoDB的数据文件.ibd同时持续监听并拷贝redo log。因为拷贝的是物理文件不需要锁表来保证一致性——后续的修改都记录在redo log里。整个过程只加一把轻量级的backup lockMySQL 8.0是LOCK INSTANCE FOR BACKUP不会阻塞DML操作。Prepare阶段备份完成后用redo log对数据文件做crash recovery把备份期间的修改回放进去得到一个一致性快照。这一步在备份结束后离线做不影响线上业务。# 备份xtrabackup--backup--target-dir/data/backup/\--userbackup_user--passwordxxx# Prepare应用redo log保证一致性xtrabackup--prepare--target-dir/data/backup/还有一个容易被忽略的好处xtrabackup支持增量备份。第一次全量备份后后续只拷贝变化的数据页备份速度快很多对IO的影响也小。# 增量备份基于上次全量xtrabackup--backup--target-dir/data/backup/inc1\--incremental-basedir/data/backup/full如果只能用mysqldump怎么办有些场景你可能没法装xtrabackup那mysqldump也不是不能用关键是要加对参数mysqldump --single-transaction--routines--triggers\--master-data2--flush-logs --all-databases/data/backup/full.sql--single-transaction会开启一个一致性读事务对InnoDB表不需要FTWRL。但有两个前提条件备份期间不能有DDL操作ALTER TABLE、CREATE INDEX等DDL会隐式提交事务破坏一致性MyISAM表还是会锁如果你还有MyISAM表的话备份窗口规划备份时间避开业务高峰晚上10点到凌晨2点是备份窗口其他时间不备份。这个看起来简单但很多团队的定时任务是随便写的凌晨3点恰好是某些报表任务的高峰期。建议在cron里用nice和ionice限流避免备份打满IOnice-n19ionice-c2-n7xtrabackup--backup--target-dir/data/backup/这样备份进程的CPU和IO优先级都是最低的不会抢占业务资源。监控前置在主从延迟超过10秒时就告警而不是等到300秒。我后来用PMMPercona Monitoring and Management搭了一套监控设了三级告警延迟 10秒钉钉通知延度 60秒电话告警延迟 300秒自动触发应急预案另外备份进程也要监控。写个简单的脚本备份开始和结束都打个时间戳到监控系统如果备份超过预设时间比如2小时还没结束也要告警。长期架构改进如果业务量继续增长从库的压力会越来越大。几个方向可以考虑专用备份从库— 单独起一个从库专门做备份不承担业务读流量。备份延迟不影响业务延迟从库— 配置CHANGE MASTER TO MASTER_DELAYN故意延迟N小时。万一主库误删数据可以从延迟从库捞回来逻辑备份物理备份结合— 物理备份做全量恢复用逻辑备份做跨版本迁移和单表恢复用两种工具各有各的场景别只备一种避坑清单能用xtrabackup就别用mysqldump物理备份走redo log保证一致性不需要全局锁。如果只能用mysqldump一定加--single-transaction但注意这个参数对MyISAM无效备份前先SHOW PROCESSLIST看一眼从库有没有大查询在跑我那次就是没检查FTWRL卡了20多分钟才拿到锁。备份窗口也别随便定凌晨3点看起来冷门可能是报表高峰期从库锁表会波及主库尤其是半同步复制场景主库TPS可能直接掉到个位数。主从架构一定要提前评估备份的影响面业务量大的话建议搭专用备份从库把备份压力和业务读流量隔离开你们线上备份用的什么方案有没有踩过类似的坑评论区聊聊 我是数据库小学妹咱们下篇见
