Zabbix数据库定时删除一些大表历史数据

zabbix数据库表空间占用查询语句

一、查询所有数据库占用空间大小

SELECT
  TABLE_SCHEMA,
  CONCAT(
    TRUNCATE(SUM(data_length) / 1024 / 1024, 2),
    ' MB'
  ) AS data_size,
  CONCAT(
    TRUNCATE(SUM(index_length) / 1024 / 1024, 2),
    'MB'
  ) AS index_size
FROM
  information_schema.tables
GROUP BY TABLE_SCHEMA
ORDER BY data_length DESC;

二、查询对应数据库表占用空间大小

SELECT
  TABLE_NAME,
  CONCAT(
    TRUNCATE(data_length / 1024 / 1024, 2),
    ' MB'
  ) AS data_size,
  CONCAT(
    TRUNCATE(index_length / 1024 / 1024, 2),
    ' MB'
  ) AS index_size
FROM
  information_schema.tables
WHERE TABLE_SCHEMA = 'zabbix'
GROUP BY TABLE_NAME
ORDER BY data_length DESC;

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

查看脚本内容
cat clean_history.sh

#!/usr/bin/env bash
set -x

User="zabbix"
Passwd="password"
Date=`date -d $(date -d "-30 day" +%Y%m%d) +%s`
$(which mysql) -u${User} -p${Passwd} -e "
use zabbix;
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  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;
"

添加定时任务
crontab -e

59 23 1,15 * * su -c 'sh /usr/bin/clean_history.sh'