1、统计数据库中每个表所占的空间:
1
2
3
4
5
|
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一周之前的历史数据:
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;
"
|
3、添加到系统计划任务:
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 语句快。
因为 truncate 删除了表,然后根据表结构重新建立它,而 delete 删除的是记录,并没有尝试去修改表。
不过truncate命令虽然快,却不像delete命令那样对事务处理是安全的。
因此,如果我们想要执行truncate删除的表正在进行事务处理,这个命令就会产生退出并产生错误信息。
转载于:https://blog.51cto.com/kusorz/1916121