部门同事说zabbix数据太大,清理一下历史数据,结果以往数据一条都不见了。-_-!
看了网上的教程也都是说直接truncate两张表history和history_uint
我的想法是,以前的历史数据,每个项目每天保留一条,这样的话可以留一些数据供后期参考
先看一下这两张表
根据itemid日期取每天最小的clock
select itemid,date_format(from_unixtime(clock),'%Y-%m-%d'),count(*),min(clock) from history group by itemid,date_format(from_unixtime(clock), '%Y-%m-%d');
执行删除
delete from history where (itemid,clock) not in
(select itemid,min(clock) from history group by itemid,date_format(from_unixtime(clock), '%Y-%m-%d'));
delete from history_uint where (itemid,clock) not in
(select itemid,min(clock) from history_uint group by itemid,date_format(from_unixtime(clock), '%Y-%m-%d'));
delete from trends_uint where (itemid,clock) not in
(select itemid,min(clock) from trends_uint group by itemid,date_format(from_unixtime(clock), '%Y-%m-%d'));