#!/bin/bashUserrootPasswdhistory_Datedate -d $(date -d -15 day %Y%m%d) %s #取30天之前的时间戳trends_Datedate -d $(date -d -60 day %Y%m%d) %s #取30天之前的时间戳#Datedate -d 20160719 09 %s#echo $Date#exit$(which mysql) -u${User} -e use zabbix;DELETE FROM history WHERE clock $history_Date;#select * from history WHERE clock $history_Date limit 2;optimize table history;DELETE FROM history_str WHERE clock $history_Date;#select * FROM history_str WHERE clock $history_Date limit 2;optimize table history_str;DELETE FROM history_uint WHERE clock $history_Date#select * FROM history_uint WHERE clock $history_Date limit 2;optimize table history_uint;DELETE FROM trends WHERE clock $trends_Date;#select * FROM trends WHERE clock $trends_Date limit 2;optimize table trends;DELETE FROM trends_uint WHERE clock $trends_Date;#select * FROM trends_uint WHERE clock $trends_Date limit 2;optimize table trends_uint;DELETE FROM events WHERE clock $history_Date;#select * FROM events WHERE clock $history_Date limit 2;optimize table events;其中histroy是详细的历史数据trends是图表趋势数据。会将histroy数据保留7天trend数据保留365天。这个是用于查找数据库大小的命令SELECT table_schema , sum( data_length index_length ) / 1024 / 1024 Data Base Size in MB FROM information_schema.TABLES GROUP BY table_schema;------------------------------------------| table_schema | Data Base Size in MB |------------------------------------------| db_hk | 0.14158630 || information_schema | 0.00976563 || mysql | 0.68990040 || performance_schema | 0.00000000 || zabbix | 7204.50000000 |------------------------------------------这个命令可以用来查找最大的几个table sizeSELECT table_name AS Tables,round(((data_length index_length) / 1024 / 1024), 2) Size in MBFROM information_schema.TABLESWHERE table_schema zabbixORDER BY (data_length index_length) DESC limit 10;-----------------------------------| Tables | Size in MB |-----------------------------------| history_uint | 4200.38 || history | 1708.34 || history_log | 483.67 || trends_uint | 424.78 || trends | 201.80 || events | 134.84 || alerts | 24.63 |清理历史数据表truncate table history;truncate table history_uint;mysql truncate table history;Query OK, 0 rows affected (2 min 58.82 sec)mysql truncate table history_uint;Query OK, 0 rows affected (3.73 sec)TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快且使用的系统和事务日志资源少。 DELETE 语句每次删除一行并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据并且只在事务日志中记录页的释放。TRUNCATE,DELETE,DROP放在一起比较TRUNCATE TABLE删除内容、释放空间但不删除定义。DELETE TABLE:删除内容不删除定义不释放空间。DROP TABLE删除内容和定义释放空间。