Zabbix监控如何清空历史数据

电玩女神 2024-03-27 10:15 80阅读 0赞

## Zabbix监控如何清空历史数据 ##

### 概述： ###

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

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

![image.png][]

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

#### 将标准时间转换为时间戳 ####

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

![image.png][image.png 1]

#### 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)

![image.png][image.png 2]

![image.png][image.png 3]

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

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'

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7eb8f7b3a06242569ec1963371216b2a.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7ebfaeb63fe6431091c0d8bd90cabc54.png
[image.png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c462342609074b0cac047237507d074c.png
[image.png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5692e87f3dbf47e7bab29e9d57791cce.png