关于Zabbix的mysql数据库定时删除的说明

关于Zabbix的mysql数据库定时删除的说明

** zabbix使用mysql作为后台数据库，使用过程中mysql数据库会随着时间的推移而逐渐增大，而磁盘不可能无限大，所以定时删除历史数据显得额外重要. **

• 本文介绍如何使用mysql事件完成定时删除，主要是对history、history_log、history_text、history_uint、history_str、trends、trends_unit这六张大表进行定时删除。
  1、首先开启mysql的事件功能，在my.cnf中的[mysqld]部分添加如下内容，然后重启mysql
  event_scheduler=ON
  重启后查看是否成功开启,ON代表开启：

show variables like 'event_scheduler';

2、创建事件，指定每天23点30分定时删除10天前的数据。
删除history_text：

create event everyday_delete_history_text on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from history_text where clock < (UNIX_TIMESTAMP(curdate())-8000);

删除history：

create event everyday_delete_history on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from history where clock < (UNIX_TIMESTAMP(curdate())-8000);

删除history_log:

create event everyday_delete_history_log on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from history_log where clock < (UNIX_TIMESTAMP(curdate())-8000);

删除history_str:

create event everyday_delete_history_str on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from history_str where clock < (UNIX_TIMESTAMP(curdate())-8000);

删除trends：

create event everyday_delete_trends on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from trends where clock < (UNIX_TIMESTAMP(curdate())-8000);

删除trends_unit:

create event everyday_delete_trends_unit on schedule every 1 day starts '2020-09-23 23:30:00' on completion preserve enable do delete from trends_unit where clock < (UNIX_TIMESTAMP(curdate())-8000);

8000是10天的总秒数，若有修改保留时间，修改这个值就可以。
查看已创建的事件：

SELECT * FROM information_schema.EVENTS;

查看时间的执行情况：

select event_name,created,starts,ends,interval_value,interval_field,last_executed \
from information_schema.events where status='enabled' order by last_executed desc;

删除不需要的事件：

drop event if exists everyday_delete_history;