MySQL Backup--Xtrabackup备份异常(failed to execute query SET SESSION lock_wait_timeout=31536000)

错误场景
MySQL 版本：5.7.24
XtraBackup 版本：2.4.8
CentOS 版本：6.5

MySQL需要新增一个从库，为避免XtraBackup备份影响生产，选择在从库上进行备份，备份脚本为：

innobackupex --socket=${mysql_socket} --user=${mysql_user} --password=${mysql_pwd} --defaults-file=${mysql_config_file} --slave-info ${backup_dir}

之前一直运行正常的脚本，最近却频繁报错：

failed to execute query SET SESSION lock_wait_timeout=31536000,MySQL server has gone away.

设置lock_wait_timeout原因

在MySQL中，因为阻塞导致FLUSH TABLES WITH READ LOCK操作获取锁超时的场景有：
1、超长事务或超大DML操作
2、超大表DDL操作

innobackupex备份过程需要保证备份数据一致性，因此需要刷新表缓存并且加全局读锁(FLUSH TABLES WITH READ LOCK)获取备份位点，因此在innobackupex备份过程中会先修改会话级别的锁等待超时时间：
SET SESSION lock_wait_timeout=31536000

MySQL报gone away错误的常见因素

1、MySQL连接超时(受参数wait_timeout和interactive_timeout控制)
2、MySQL连接被KILL
3、MySQL实例重启

排查问题后发现：

1、从库上没有业务操作，不存才超大事务。
2、从库上SQL线程应用主库BINLOG，但主库没有超大事务也没有DDL操作。
3、备份账号有管理员权限，有足够权限执行SET操作。
4、SET操作失败概率最近一段时间较高。
5、MySQL实例正常，连接超时时间配置较长。
6、开启通用日志，捕捉到KILL命令，发现自动KILL的程序有BUG

问题总结

innobackupex备份过程后期，会使用长连接到MySQL执行查询，在两次执行过程中连接处于SLEEP状态，而部署的自动KILL程序为释放连接会自动KILL这些长时间SLEEP的连接，导致备份失败。

原文：https://www.cnblogs.com/gaogao67/p/10955096.html