Zabbix监控如何清空历史数据

Zabbix监控如何清空历史数据

概述：

zabbix在运行一段时间过后，会留下大量的历史数据，我们会发现zabbix的数据库会一直越来越大。运行三个月的zabbix的数据库会达到10个G左右根据监控的服务器和交换机数量有关以及模板里面的监控项和数据。
zabbix里面最大的就是表就是历史记录的表了，网上很多人都是在写全部清空这些表的数据，其实我们可以按照时间来删除里面的数据。
里面最大的表就是“history”和history_unit两个表
zabbix里面的时间是用的时间戳方式记录，我们可以转换一下，然后根据时间戳来删除；

# 将当前时间转换为时间戳
[root@Halo ~]# date +%s
1640657402

比如要删除2014年的1月1号以前的数据

将标准时间转换为时间戳

date -d '2014-01-01 00:00:01' +%s
1388505601

mysql清理数据

删除指定日期数据

mysql> DELETE FROM `history_uint` WHERE `clock` < 1388505601;
	# 删除小于这个时间戳的数据
 
mysql> optimize table history_uint;

注：执行过第二行命令之后可能会需要很长的一段时间，中间不要中断，否则容易丢失数据。

清空历史数据

上面是比较实用的按照时间段删除历史数据，也有方法可以全部清除历史监控数据，Zabbix清空历史记录mysql数据库操作：

mysql -uroot -p 输入mysql密码
use 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所有历史监控数据，请操作之前备份好数据库！

按事件ID删除一条数据

mysql> select * from events where eventid = 12315;
+---------+--------+--------+----------+------------+-------+--------------+-----------+--------------------------------------------------------------------------------------------------+----------+
| eventid | source | object | objectid | clock      | value | acknowledged | ns        | name                                                                                             | severity |
+---------+--------+--------+----------+------------+-------+--------------+-----------+--------------------------------------------------------------------------------------------------+----------+
|    12315 |      0 |      0 |    19878 | 1635950417 |     1 |            0 | 645573211 | "ShellHWDetection"" (Shell Hardware Detection) is not running (startup type automatic)          |        3 |
+---------+--------+--------+----------+------------+-------+--------------+-----------+--------------------------------------------------------------------------------------------------+----------+
1 row in set (0.00 sec)
 
mysql> 
mysql> delete from events where eventid = 12315;
Query OK, 1 row affected (0.01 sec)

还可以借助工具进行查询后按事件名称排序删除。

mysql> select * from events;
 
 
 7126 |      0 |      0 |    19897 | 1635725918 |     0 |            0 | 692556020 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        0 |
|    7127 |      0 |      0 |    19897 | 1635726278 |     1 |            0 | 437494616 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        3 |
|    7128 |      0 |      0 |    20138 | 1635726305 |     0 |            0 |  59973165 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        0 |
|    7129 |      0 |      0 |    20274 | 1635726350 |     0 |            0 | 748569592 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        0 |
|    7134 |      0 |      0 |    20400 | 1635726488 |     0 |            0 | 663322422 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        0 |
|    7135 |      0 |      0 |    20138 | 1635726605 |     1 |            0 | 256557489 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        3 |
|    7136 |      1 |      2 |        1 | 1635726628 |     0 |            0 |         0 |                                                                                                                           |        0 |
|    7137 |      1 |      1 |        1 | 1635726628 |     0 |            0 |         0 |                                                                                                                           |        0 |
|    7138 |      1 |      2 |        2 | 1635726634 |     0 |            0 |         0 |                                                                                                                           |        0 |
|    7139 |      1 |      1 |        2 | 1635726634 |     0 |            0 |         0 |                                                                                                                           |        0 |
|    7140 |      0 |      0 |    20274 | 1635726710 |     1 |            0 | 393039388 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        3 |
|    7141 |      0 |      0 |    20400 | 1635726848 |     1 |            0 | 114561341 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        3 |
|    7145 |      0 |      0 |    21004 | 1635727482 |     0 |            0 | 529440823 | "BITS" (Background Intelligent Transfer Service) is not running (startup type automatic delayed)                          |        0 |
|    7146 |      1 |      2 |        3 | 1635727659 |     0 |            0 |         0 |                                                                                                                           |        0 |
|    7147 |

名称	动作
Windows CPU by Zabbix agent active	
Windows filesystems by Zabbix agent active	
Windows generic by Zabbix agent active	
Windows memory by Zabbix agent active	
Windows network by Zabbix agent active	
Windows physical disks by Zabbix agent active	
Windows services by Zabbix agent active	
Zabbix agent	
 
 
curl -L -c cookie -b cookie 'http://202.205.161.80:8003/zabbix/queue.php?config=0'
 curl -L -c cookie -b cookie -d 'name=Admin&password=zabbix&autologin=1&enter=Sign+in' 'http://202.205.161.80:8003'