zabbix 由于历史数据过大, 因此导致磁盘空间暴涨,  下面是解决方法步骤：

一、分析数据库：

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

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;

View Code

+----------------------------+------------+| Tables | Size in MB |+----------------------------+------------+| history_uint | 29417.00 || history | 8287.00 || events | 2367.67 || trends_uint | 1781.91 || event_recovery | 1211.33 || trends | 548.00 || history_str | 381.53 || history_text | 58.13 || alerts | 46.67 || problem | 31.06 || items | 25.23 || auditlog | 16.53 || items_applications | 6.20 || triggers | 5.31 || item_discovery | 5.30 || housekeeper | 3.52 || functions | 3.33 || graphs | 2.23 || graphs_items | 2.17 || item_preproc | 1.88 || auditlog_details | 1.69 || images | 1.53 || profiles | 0.80 || trigger_discovery | 0.42 || applications | 0.38 || application_template | 0.38 || trigger_depends | 0.33 || graph_discovery | 0.25 || hosts | 0.20 || item_condition | 0.16 || sessions | 0.14 || widget_field | 0.11 || dservices | 0.09 || sysmaps_elements | 0.09 || sysmaps | 0.08 || httptest | 0.08 || interface | 0.08 || mappings | 0.08 || hosts_templates | 0.08 || group_prototype | 0.06 || hostmacro | 0.06 || acknowledges | 0.06 || host_inventory | 0.06 || scripts | 0.06 || sysmaps_links | 0.06 || slides | 0.05 || maintenances_groups | 0.05 || users_groups | 0.05 || dashboard_usrgrp | 0.05 || sysmap_usrgrp | 0.05 || maintenances | 0.05 || dashboard_user | 0.05 || actions | 0.05 || opgroup | 0.05 || sysmap_user | 0.05 || httpstepitem | 0.05 || config | 0.05 || item_application_prototype | 0.05 || opcommand_hst | 0.05 || services_links | 0.05 || host_discovery | 0.05 || opcommand_grp | 0.05 || service_alarms | 0.05 || problem_tag | 0.05 || sysmap_element_trigger | 0.05 || task | 0.05 || drules | 0.05 || media | 0.05 || optemplate | 0.05 || slideshows | 0.05 || icon_mapping | 0.05 || screens | 0.05 || slideshow_usrgrp | 0.05 || sysmaps_link_triggers | 0.05 || icon_map | 0.05 || opmessage_usr | 0.05 || maintenances_windows | 0.05 || screen_usrgrp | 0.05 || application_prototype | 0.05 || httptestitem | 0.05 || opmessage_grp | 0.05 || slideshow_user | 0.05 || maintenances_hosts | 0.05 || screen_user | 0.05 || rights | 0.05 || hosts_groups | 0.05 || application_discovery | 0.05 || httptest_field | 0.03 || opmessage | 0.03 || event_tag | 0.03 || corr_condition_group | 0.03 || regexps | 0.03 || operations | 0.03 || group_discovery | 0.03 || users | 0.03 || corr_condition | 0.03 || opconditions | 0.03 || services_times | 0.03 || dashboard | 0.03 || sysmap_url | 0.03 || conditions | 0.03 || trigger_tag | 0.03 || interface_discovery | 0.03 || services | 0.03 || sysmap_shape | 0.03 || graph_theme | 0.03 || autoreg_host | 0.03 || corr_operation | 0.03 || opcommand | 0.03 || sysmap_element_url | 0.03 || escalations | 0.03 || media_type | 0.03 || httpstep_field | 0.03 || screens_items | 0.03 || httpstep | 0.03 || dhosts | 0.03 || widget | 0.03 || valuemaps | 0.03 || expressions | 0.03 || dchecks | 0.03 || groups | 0.03 || usrgrp | 0.03 || opinventory | 0.02 || proxy_history | 0.02 || proxy_dhistory | 0.02 || correlation | 0.02 || task_remote_command | 0.02 || proxy_autoreg_host | 0.02 || task_close_problem | 0.02 || corr_condition_tagvalue | 0.02 || task_acknowledge | 0.02 || globalvars | 0.02 || corr_condition_tagpair | 0.02 || ids | 0.02 || globalmacro | 0.02 || corr_condition_tag | 0.02 || history_log | 0.02 || timeperiods | 0.02 || task_remote_command_result | 0.02 || dbversion | 0.02 |+----------------------------+------------+140 rows in set (0.68 sec)

View Code

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

!/bin/bashUser="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;"
5.6以后用alter table table_name engine="InnoDB";

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

#remove the zabbix mysql data before 7 day‘s ago0 3 * * 0 /usr/local/script/clearzabbix.sh > /usr/local/script/clearzabbix.log

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

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