zabbix刪除歷史記錄

Zabbix歷史數據清理

 

一、統計數據庫中每一個表所佔的空間:html

 

 

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;mysql

 

二、清理zabbix一週以前的歷史數據:sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
! /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;
"

三、添加到系統計劃任務:數據庫

1
2
#remove the zabbix mysql data before 7 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 語句快bash

由於 truncate 刪除了表,而後根據表結構從新創建它,而 delete 刪除的是記錄,並無嘗試去修改表。post

不過truncate命令雖然快,卻不像delete命令那樣對事務處理是安全的。ui

所以,若是咱們想要執行truncate刪除的表正在進行事務處理,這個命令就會產生退出併產生錯誤信息。url

相關文章
相關標籤/搜索