慢sql导致xtrabackup备份关闭表等待从而造成主从延迟

主从数据5点10分钟左右延迟问题分析

1. 问题原因分析：

登录数据库服务器查看主从数据确实存在延迟

登录数据库show processlist查看

可以看出是从内部的一个ip上用dev_read 用户连接到备库上执行的 查询导致数据库备份拷贝完数据文件后FLUSH NO_WRITE_TO_BINLOG TABLES加锁处等待状态（waiting for table flush）

而由于上述慢sql查询，导致flush table一直无法关闭该表而一直处于等待状态

（FLUSH NO_WRITE_TO_BINLOG TABLES 关闭所有打开的表，强制关闭所有正在使用的表）

数据备份日志如下：

210123 04:32:04 >> log scanned up to (34781249431476)

210123 04:32:05 [01] ...done

210123 04:32:05 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

210123 04:32:05 >> log scanned up to (34781249448552)

210123 04:32:06 >> log scanned up to (34781249465337)

之后业务的所有DML操作都处于等待状态，进而导致数据库主从延迟。

2. 分析慢sql查询来源：

show processlist看到ip地址是数据查询平台的ip地址，而dev_read这个用户就是为这个平台开放的查询用户，所以可以确认这个语句是从这个平台发起的

从数据库查询平台查看日志，可以确认是开发人员发起的查询

问题解决：

第一时间获取到信息并进行保留（用户问题分析），然后立即通过kill点慢sql语句的线程，kill备份线程问题解决

主从开始同步

后续连带问题：

第二天检查备份发现备份失败：

210125 01:00:02 Connecting to MySQL server host: localhost, user: dbauser, password: set, port: 3312, socket: /tmp/mysql3312.sock

xtrabackup: Redo Log Archiving is not set up.

Starting to parse redo log at lsn = 34849857389211

Recovery parsing buffer extended to 4194304.

Recovery parsing buffer extended to 8388608.

Recovery parsing buffer extended to 16777216.

210125 01:00:16 >> log scanned up to (34850360108928)

xtrabackup: Generating a list of tablespaces

Directories to scan '.;undolog;.'

Scanning './'

Completed space ID check of 2 files.

Allocated tablespace ID 44413 for guiyu_notice/notice_info, old maximum was 0

210125 01:00:17 >> log scanned up to (34850361716914)

Undo tablespace number 1 was being truncated when mysqld quit.

Cannot recover a truncated undo tablespace in read-only mode

xtrabackup: error: xb_load_tablespaces() failed with error code 57

考虑到上述错误应该是和上面处理问题时kill掉备份的进程有关

解决：

试着重启数据库报错：

删除undo再次重启解决问题

关闭数据库，删除undo 然后启动数据库

备份恢复正常

预防：

建议开发人员执行sql之前要进行查看执行计划，对sql进行审核，以防止语句长时间运行

从数据库层面对数据库进行过载保护

通过pt-kill 或者参数max_execution_time 来kill长时间运行的查询语句