Zabbix历史数据清理

特别提醒:

a、文中测试的Zabbix版本为 3.0.3 。

b、清理数据属于高危操作,请在测试环境中验证后再执行线上操作!!!

 

1、统计数据库中每个表所占的空间:

mysql> SELECT table_name AS "Tables",
       round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
      FROM information_schema.TABLES
      WHERE table_schema = 'zabbixdb'
      ORDER BY (data_length + index_length) DESC;

2、清理zabbix一周之前的历史数据:

!/bin/bash
User="zabbixuser"
Passwd="zabbixpass"
Date=`date -d $(date -d "-7 day" +%Y%m%d) +%s` #取7天之前的时间戳
$(which mysql) -u${User} -p${Passwd} -e "
use zabbixdb;
DELETE FROM history WHERE 'clock' < $Date;
optimize table history;
DELETE FROM history_str WHERE 'clock' < $Date;
optimize table history_str;
DELETE FROM history_uint WHERE 'clock' < $Date;
optimize table history_uint;
DELETE FROM history_text WHERE 'clock' < $Date;
optimize table history_text;
DELETE FROM  trends WHERE 'clock' < $Date;
optimize table  trends;
DELETE FROM trends_uint WHERE 'clock' < $Date;
optimize table trends_uint;
DELETE FROM events WHERE 'clock' < $Date;
optimize table events;
"

3、添加到系统计划任务:

#remove the zabbix mysql data before 7 day's ago
0 3 * * 0 /usr/local/script/clearzabbix.sh > /usr/local/script/clearzabbix.log

另:可以使用truncate命令直接清空数据库:

truncate table history;
truncate table history_uint;
truncate table history_str;
truncate table history_text;
truncate table trends;
truncate table trends_uint;
truncate table events;

如果想要删除表的所有数据,truncate语句要比 delete 语句快。

因为 truncate 删除了表,然后根据表结构重新建立它,而 delete 删除的是记录,并没有尝试去修改表。

不过truncate命令虽然快,却不像delete命令那样对事务处理是安全的。

因此,如果我们想要执行truncate删除的表正在进行事务处理,这个命令就会产生退出并产生错误信息。

Leave a Reply

Your email address will not be published. Required fields are marked *