zabbix删除历史记录

Zabbix历史数据清理

 

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

 SELECT table_name AS "Tables",round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" from information_schema.TABLES where table_schema = 'zabbix' ORDER BY (data_length + index_length) DESC;

     统计数据库中每个表的条数：

  use information_schema

  select table_schema,table_name,table_rows from tables order by table_rows desc;

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

!/bin/bash User="zabbix" Passwd="123456" Date=`date -d $(date -d "-365 day" +%Y%m%d) +%s` #取365天之前的时间戳 $(which mysql) -u${User} -p${Passwd} -e " use zabbix; 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; "

单独执行SQL语句时，clock不要加引号

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

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

 

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

1 2 3 4 5 6 7

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

 

 

导入库

在新库中建zabbix用户

create database zabbix character set utf8 collate utf8_bin;
grant all privileges on zabbix.* to 'zabbix'@'%';
flush privileges;
GRANT PROCESS ON *.* to 'zabbix'@'%';
flush privileges;
use zabbix;
source /aaaa.sql