zabbix清理监控历史mysql数据
问题描述：
今天同事说有个zabbix监控数据库历史数据越来越多了，
让我帮忙清一下，顺便熟悉练练手，做个笔记
zabbix监控运行一段时间以后，会留下大量的历史监控数据
zabbix数据库一直在增大可能会造成系统性能下降，查看历史数据室查询速度缓慢。
zabbix里面最大的表就是history和history_uint两个表，但是我这边查看到history_log表也是最大的。
而且zabbix里面的时间是使用的时间戳方式记录，所以可以根据时间戳来删除历史数据。
解决方法：
1、首先需要关闭zabbix和httpd服务：
我的系统是centos7系统，关闭服务是这种
[root@awza ~]# systemctl stop zabbix-server.service
[root@awza ~]# systemctl stop httpd.service
2、查看数据库目录文件的大小
[root@awza zabbix]# pwd /database/mysql/zabbix [root@awza zabbix]# ll -h |grep G total 129G -rw-rw---- 1 mysql mysql 2.9G Nov 4 15:15 history.ibd -rw-rw---- 1 mysql mysql 121G Nov 4 15:15 history_log.ibd -rw-rw---- 1 mysql mysql 4.2G Nov 4 15:15 history_uint.ibd
生成一个Unix时间戳，例如想删除2019-05-01号之前的数据（保留19年5月1号以后的监控数据）
或者使用站长时间戳工具生成：https://tool.lu/timestamp/
[root@awza zabbix]# date +%s -d "May 1, 2019 00:00:00" 1556640000
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@awza backup]# mysql -uzabbix -p Enter password: MariaDB [(none)]> use zabbix MariaDB [zabbix]> select max(itemid) from history; +-------------+ | max(itemid) | +-------------+ | 62944 | +-------------+ 1 row in set (0.00 sec) MariaDB [zabbix]> select max(itemid) from history_uint; +-------------+ | max(itemid) | +-------------+ | 62950 | +-------------+ 1 row in set (0.00 sec) MariaDB [zabbix]> select max(itemid) from history_log; +-------------+ | max(itemid) | +-------------+ | 62863 | +-------------+ 1 row in set (0.00 sec)
5、按时间戳清理监控历史数据：
第一种清理方法：
use zabbix; MariaDB [zabbix]> delete from history where clock < 1556640000; Query OK, 0 rows affected (41.70 sec) MariaDB [zabbix]> delete from history_uint where clock < 1556640000; Query OK, 0 rows affected (3 min 51.22 sec) MariaDB [zabbix]> 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_str; optimize table history_str; ------------------------------------------------------- truncate table history_log; optimize table history_log; ------------------------------------------------------- truncate table history_uint; optimize table history_uint; ------------------------------------------------------- truncate table trends; optimize table trends; ------------------------------------------------------- truncate table trends_uint; optimize table trends_uint; ------------------------------------------------------- truncate table events; optimize table events; ------------------------------------------------------- 注意：这些命令会把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)
注意提示：
当你删除数据时，mysql并不会回收已删除的数据所占据的存储空间，以及索引位。
而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。
所以对于写比较频繁的表，要定期进行optimize，看实际情况而定了，所以删除了历史数据，可以使用optimize来回收存储空间。
版权声明：本文为Sungeek原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。