数据库备份导致的zabbix_server大量告警的问题排查

问题:

每天在9点15分左右，运维人员会收到大量的zabbix_server报警邮件，提示 "PROBLEM: Zabbix agent on XXX is unreachable for 5 minutes"

排查过程：

从9点之前的系统运行状态开始查起。

1、查看9点左右的zabbix_server.log。没有发现异常。

2、在zabbix server上写了一个计划任务 56 8 * * * /bin/ping one_agent_IP>> /tmp/net_status.log  

通过ping agent端的ip，判断是否网络抖动导致agent unreachable的。

但是，第二天/tmp/net_status_log的日志显示ping没有丢包问题。排除了网络抖动问题。

3、查看9点左右的计划任务，发现9点时候在执行了一个backup.sh，看到里面Mysqldump的备份参数如下：

mysqldump --force --opt -uxxx -pxxx  zabbix | gzip > xxxx.sql.gz

至此，可以基本判断是这个备份数据库操作导致的zabbixserver告警。

原因：

这里先看下上面mysqldump的几个参数，

  --opt        Same as --add-drop-table,--add-locks, --create-options,

               --quick,--extended-insert, --lock-tables,--set-charset,

               and --disable-keys.Enabled by default, disable with

               --skip-opt.

  -f, --force      Continue even if we get an SQL error.

其中，--opt里面包含了一堆的参数，这里最关键的是 --lock-tables，mysql5.6官方手册上的解释如下：

--lock-tables, -l

For eachdumped database, lock all tables to be dumped before dumping them. The tablesare

lockedwith READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional

tables such as InnoDB,--single-transaction is a much better option than --lock-tables

because it does not need tolock the tables at all.

Because--lock-tables locks tables for each database separately, this option does notguarantee

that thetables in the dump file are logically consistent between databases. Tables indifferent

databasesmay be dumped in completely different states.

Someoptions, such as --opt, automatically enable --lock-tables. If you want tooverride this,

use--skip-lock-tables at the end of the option list.

官方建议在InnoDB存储引擎时候，使用 --single-transaction，而不要用 --lock-tables，因为--lock-tables会造成锁表的问题。

在搭配--single-transaction 参数后，会在FLUSH TABLES WITH READ LOCK 后添加START TRANSACTION 语句，将事务的隔离级别设置为REPEATABLE READ ，这个时候的加锁，仅仅是为了确定master-data中的binlog的具体位置和开启事务，开启事务后，就已经把读锁释放了，而且在由日志可以看出，在日志的回滚过程中，是回滚到单一的TRANSACTION，也是sp点，每次进行对表和参数的改动后，都会对事务进行回滚。通过对备份log的分析，可以发现，所有的的备份阶段完成后，都是rollback到sp点的。也就是返回到SAVEPOINT sp时间点，也就是备份完成后，实际上备份库仍然是在sp点，而这个所谓的sp回滚，其实是调用的undo中的数据快照来实现的。（就是说9点钟执行的备份，最终我们备份出的数据就是9点的数据，备份过程中数据的变化不会写入到备份文件里）。

如下图是执行 mysqldump -q --single-transaction -B zabbix > test.sql 时候的mysql的general log日志。

备份时候，使用了--opt参数会暂时锁表，zabbix_agent收集到的数据无法及时写入数据库，zabbix_server在长时间没有发现收集到agent的数据，就会触发告警。

我们只要改下备份的参数即可，mysqldump -uxx -pxx -f -q--single-transaction -B zabbix | gzip > xxxx.sql.gz

另外，对于Innodb存储引擎的表，建议在使用mysqldump备份时候都要加上 -q --single-transaction 参数。