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的表正在处理事务，这个命令退出并会产生错误信息