zabbix清除历史监控数据
zabbix监控运行一段时间以后，会留下大量的历史监控数据，zabbix数据库一直在增大；可能会造成系统性能下降，查看历史数据室查询速度缓慢，图形出现断图现象，隔几秒出现断图，发现CPU采集数据的时候使用率都是100%，如下图
zabbix里面最大的表就是history和history_uint两个表，而且zabbix里面的时间是使用的时间戳方式记录，所以可以根据时间戳来删除历史数据
1、先生成unix时间戳，时间设定为2024年7月23日之前的数据全部删除，先记下 1721664000 这个时间戳，后面执行删除时要用
date +%s -d "2024-07-23"
2、停止zabbix-server、Apache2进程
sudo systemctl stop zabbix-server.service apache2.service
3、查询文件占用空间
cd /
sudo du -sh * | grep G
4、备份数据库（可选，备份时间较长）
sudo mysqldump -uroot -p zabbix>./zabbix.sql
5、登录数据库，删除操作，时间可能较长请耐心等待，中间不要终止，否则容易数据丢失
sudo mysql -uroot -p #登录mysql数据库
mysql> use zabbix; #切换到zabbix数据库
mysql> delete from history where clock < 1721664000; #删除2024年7月23日之前的历史数据
Query OK, 0 rows affected (0.61 sec)
mysql> 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 (0.83 sec)
mysql> delete from history_uint where clock < 1721664000; #删除2024年7月23日之前的历史数据
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql> optimize table history_uint; #优化表空间结构
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status | OK |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3 hours 27 min 58.55 sec)
mysql> delete from trends where clock < 1721664000; #删除2024年7月23日之前的历史数据
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id: 12
Current database: zabbix
Query OK, 8172 rows affected (2.74 sec)
mysql> optimize table trends; #优化表空间结构
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.trends | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.trends | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.29 sec)
mysql> delete from trends_uint where clock < 1721664000; #删除2024年7月23日之前的历史数据
Query OK, 354220 rows affected (5.71 sec)
mysql> optimize table trends_uint; #优化表空间结构
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.trends_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.trends_uint | optimize | status | OK |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.39 sec)
mysql> delete from events where clock < 1721664000; #删除2024年7月23日之前的历史数据
Query OK, 3106 rows affected (0.86 sec)
mysql> optimize table events; #优化表空间结构
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| zabbix.events | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.events | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.43 sec)
mysql> delete from auditlog where clock < 1721664000; #删除2024年7月23日之前的历史数据
Query OK, 144055 rows affected (13.33 sec)
mysql> optimize table auditlog; #优化表空间结构
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.auditlog | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.auditlog | optimize | status | OK |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.34 sec)
6、启动服务
sudo systemctl start zabbix-server.service apache2.service
# 使用truncate命令清空zabbix 所有监控数据 (未尝试)
-------------------------------------------------------
truncate table history;
optimize table history;
-------------------------------------------------------
truncate table history_str;
optimize table history_str;
-------------------------------------------------------
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,如果我们执行truncat的表正在处理事务，这个命令退出并会产生错误信息
