zabbix监控运行一段时间以后，会留下大量的历史监控数据，zabbix数据库一直在增大；可能会造成系统性能下降，查看历史数据室查询速度缓慢。
zabbix里面最大的表就是history和history_uint两个表，而且zabbix里面的时间是使用的时间戳方式记录，所以可以根据时间戳来删除历史数据
清空部分历史数据
一、关闭zabbix、http服务
[root@localhost repo]# systemctl stop zabbix-server httpd
二、清理zabbix历史数据
1. 查看数据库目录文件
[root@localhost ~]# cd /var/lib/mysql/zabbix/ [root@localhost zabbix]# ls -lh | grep G -rw-rw---- 1 mysql mysql 463 7月 22 11:53 maplist.TRG -rw-rw---- 1 mysql mysql 645 7月 13 11:31 switcharp_copy.TRG -rw-rw---- 1 mysql mysql 629 7月 22 11:53 switcharp.TRG #生成Unix时间戳。时间定为2020年11月15日（暂定是保存2020年11月15日以后的监控数据） [root@localhost zabbix]# date +%s -d "Nov 15, 2020 00:00:00" #执行此命令以后会生成一个ID 1605369600 #这是生成的ID
2、数据备份
[root@localhost repo]# mysqldump -uroot -p123456 zabbix>./zabbix.sql mysql -uroot -p123456 zabbix <./zabbix.sql; # 导入
3、 登录数据库
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 76523 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> # 执行sql查看指定日期之前的数据大小： SELECT table_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLES where CREATE_TIME < '2018-02-01 00:00:00' and table_name='history.ibd'; # 根据需要修改日期和查询的表名称(如果查询出来的结果是0.0，需要将sql中的三个1024删除一个，以G为单位显示)
4、 执行以下命令，清理指定时间之前的数据、对zabbix数据库执行sql命令
MariaDB [(none)]> use zabbix; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # 清除部分历史,优化查询速度 MariaDB [zabbix]> delete from history where clock < 1605369600; Query OK, 6913 rows affected (1.97 sec) 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 (8.09 sec) MariaDB [zabbix]> delete from history_uint where clock < 1605369600; Query OK, 28563 rows affected (2.91 sec) MariaDB [zabbix]> 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 (10.42 sec) MariaDB [zabbix]> delete from trends where clock < 1605369600; Query OK, 28791 rows affected (0.20 sec) MariaDB [zabbix]> 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.13 sec) MariaDB [zabbix]> delete from trends_uint where clock < 1605369600; Query OK, 67899 rows affected (0.54 sec) MariaDB [zabbix]> 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.21 sec) # 动作日志 告警统计 MariaDB [zabbix]> delete from events where clock < 1605369600; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1675 Current database: zabbix Query OK, 3060 rows affected (1.38 sec) MariaDB [zabbix]> 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.07 sec)
delete from alerts where clock < 1605369600;
optimize table alerts;
# 审计日志 MariaDB [zabbix]> delete from auditlog where clock < 1605369600; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2143 Current database: zabbix Query OK, 69440 rows affected (3.22 sec) MariaDB [zabbix]> 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.04 sec) MariaDB [zabbix]> 注意：sql中的ID是生成Unix时间戳的ID号,需要改为自己生成的ID号
三、启动服务
[root@localhost ~]# systemctl restart zabbix-server zabbix-agent httpd mariadb
使用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的表正在处理事务，这个命令退出并会产生错误信息
转载于:https://www.cnblogs.com/ArchitecTang/p/10173906.html