清理Zabbix下MySQL中的zabbix数据库历史数据记录

问题描述：

今天同事说有个zabbix监控数据库历史数据越来越多了，让我帮忙清一下，顺便熟悉练练手，做个笔记

zabbix监控运行一段时间以后，会留下大量的历史监控数据

zabbix数据库一直在增大可能会造成系统性能下降，查看历史数据室查询速度缓慢。

zabbix里面最大的表就是history和history_uint两个表，但是我这边查看到history_log表也是最大的。

而且zabbix里面的时间是使用的时间戳方式记录，所以可以根据时间戳来删除历史数据。

 

解决方法：

1、首先需要关闭zabbix和httpd服务：

我的系统是centos7系统，关闭服务是这种

[root@Zabbix604 ~]# systemctl stop zabbix-server
[root@Zabbix604 ~]# systemctl stop httpd

2、查看数据库目录文件的大小

 

[root@Mysql Zabbix604]# pwd
/database/mysql/zabbix

[root@Mysql Zabbix604]# ll -h |grep G

 

生成一个Unix时间戳，例如想删除2019-12-15号之前的数据（保留19年5月1号以后的监控数据）

或者使用站长时间戳工具生成：https://tool.lu/timestamp/

3、数据库备份（重要的话最好做一下备份）

我这边备份磁盘空间不足，说不用备份，出现了mysqldump: Got errno 28 on write就是磁盘空间不足

[root@awza ~]# /usr/bin/mysqldump -uzabbix -p --triggers --routines --events zabbix > /database/backup/zabbix-mysql-20191104.sql
Enter password: 
mysqldump: Got errno 28 on write

4、查询历史表数据数：

 

[root@zabbix604]# mysql -uzabbix -p
Enter password: 

Mysql[(none)]> use zabbix

Mysql[zabbix604]> select max(itemid) from history;
+-------------+
| max(itemid) |
+-------------+
|       62944 |
+-------------+
1 row in set (0.00 sec)

Mysql[zabbix604]> select max(itemid) from history_uint;
+-------------+
| max(itemid) |
+-------------+
|       62950 |
+-------------+
1 row in set (0.00 sec)

Mysql [zabbix604]> select max(itemid) from history_log;
+-------------+
| max(itemid) |
+-------------+
| 62863 |
+-------------+
1 row in set (0.00 sec)

 

5、按时间戳清理监控历史数据：

这边清理相关表按照历史表的大小来清理，根据（2、查看数据库目录文件的大小）

第一种清理方法：

use zabbix;
Mysql [zabbix604]> delete from history where clock < 1556640000;
Query OK, 0 rows affected (41.70 sec)

Mysql [zabbix604]> delete from history_uint where clock < 1556640000;
Query OK, 0 rows affected (3 min 51.22 sec)

Mysql [zabbix604]> delete from history_log where clock < 1556640000;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size


##注意：这边时间戳按照自己的条件写，1556640000这个是我前面按照2019-05-01计算出来的时间戳

这边删除第三个表的时候，因为数据量太大，导致报错：
错误1206（HY000）：锁的总数超过了锁表的大小
这个表也不重要，所以就执行下面的truncate命令来清理

 

第二种清理方法：

 

truncate table history;
optimize table history;-------------------------------------------------------
truncate table history_log;
optimize table history_log;
-------------------------------------------------------
truncate table history_uint;
optimize table history_uint;

注意：这些命令会把zabbix所有的监控数据清空，操作前注意备份数据库
truncate是清空整张表，然后根据表结构重新建立，delete删除的是记录的数据没有修改表
truncate执行删除比较快，但是在事务处理安全性方面不如delete
如果我们执行truncate的表正在处理事务，这个命令退出并会产生错误信息

MariaDB [zabbix]> optimize table history;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7 min 4.03 sec)

6、开启服务

[root@Mysql  database]# systemctl start zabbix-server
[root@Mysql database]# systemctl start httpd

注意提示：

当你删除数据时，mysql并不会回收已删除的数据所占据的存储空间，以及索引位。

而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。

所以对于写比较频繁的表，要定期进行optimize，看实际情况而定了，所以删除了历史数据，可以使用optimize来回收存储空间。