生产环境zabbix3.2上亿的表数据通过表分区的方式进行历史数据清理 zabbix服务器经常报警io过载，在报警的时候发现是数据库在删除历史数据时耗时较长 数据库积攒了大量的历史数据信息，主要集中在zabbix的history、history_uint、history_str、history_text、trends、trends_uint这几个表中 需要进行清理，两种清理方式： 1.直接清空表，最省事，速度最快，缺陷是会丢失所有的监控历史数据 具体清理语句如下： use zabbix; truncate table history; truncate table history_uint; truncate table history_log; truncate table history_str; truncate table history_text; truncate table trends; truncate table trends_uint; truncate table event_recovery; truncate table events;(有外键关联，无法直接清空表)
解决办法：
mysql> truncate table events;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`zabbix`.`acknowledges`, CONSTRAINT `c_acknowledges_2` FOREIGN KEY (`eventid`) REFERENCES `zabbix`.`events` (`eventid`))
mysql> SET foreign_key_checks=0;
mysql> truncate table events;
Query OK, 0 rows affected (3.37 sec)
mysql> SET foreign_key_checks=1;
Query OK, 0 rows affected (0.13 sec) 2.删除一段时间以前的数据，速度慢，可以保留需要的数据 相对折中的解决方案是先将表分区，然后清理表分区，这样处理相对比较稳妥，清理速度也较快 清理前先备份数据库： 1.备份脚本 # cat /opt/mysql_bak.sh #!/usr/bin/env bash # Program: MySQL 增量备份脚本 使用 percona xtrabackup # Author : jack # Date : 2015-12-29 # update :2017.11.16 by tom ##进入程序目录 cd /usr/local/worksh/xtrabackup_cron/bin # 读取配置文件中的所有变量值, 设置为全局变量 # 配置文件 conf_file="../conf/mysql_increment_hot_backup.conf" # mysql 用户 user=`sed '/^user=/!d;s/.*=//' $conf_file` # mysql 密码 #password=`sed '/^password=/!d;s/.*=//' $conf_file` password="pass" # mysql 备份目录 backup_dir=`sed '/^backup_dir=/!d;s/.*=//' $conf_file` # percona-xtrabackup 备份软件路径 xtrabackup_dir=`sed '/^xtrabackup_dir=/!d;s/.*=//' $conf_file` # 全备是在一周的第几天 full_backup_week_day=`sed '/^full_backup_week_day=/!d;s/.*=//' $conf_file` # mysql 全备前缀标识 full_backup_prefix=`sed '/^full_backup_prefix=/!d;s/.*=//' $conf_file` # mysql 增量备前缀标识 increment_prefix=`sed '/^increment_prefix=/!d;s/.*=//' $conf_file` # mysql 配置文件 mysql_conf_file=`sed '/^mysql_conf_file=/!d;s/.*=//' $conf_file` # 备份错误日志文件 error_log=`sed '/^error_log=/!d;s/.*=//' $conf_file` # 备份索引文件 index_file=`sed '/^index_file=/!d;s/.*=//' $conf_file` # 备份日期 backup_date=`date +%F` # 备份日期 backup_time=`date +%H-%M-%S` # 备份日期 backup_week_day=`date +%u` # 设置备份线程数 backup_thread=4 # 创建相关目录 log_dir=../log var_dir=../var mkdir -p $backup_dir mkdir -p $log_dir mkdir -p $var_dir # 全量备份 function full_backup() { backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day} mkdir -p $backup_dir/$backup_folder $xtrabackup_dir/bin/innobackupex --defaults-file=$mysql_conf_file --user=$user --password=$password --parallel=$backup_thread --no-timestamp $backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1 return $? } # 增量备份 function increment_backup() { backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day} incr_base_folder=`sed -n '$p' $index_file | awk -F '[, {}]*' '{print $3}' | awk -F ':' '{print $2}'` mkdir -p $backup_dir/$backup_folder $xtrabackup_dir/bin/innobackupex --defaults-file=$mysql_conf_file --user=$user --password=$password --no-timestamp --incremental $backup_dir/$backup_folder --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1 return $? } # 删除之前的备份(一般在全备完成后使用) function delete_before_backup() { cat $index_file | awk -F '[, {}]*' '{print $3}' | awk -v backup_dir=$backup_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s ", backup_dir, $2)}}' | /bin/bash cat $index_file | awk -F '[, {}]*' '{print $3}' | awk -v log_dir=$log_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s.log ", log_dir, $2)}}' | /bin/bash } # 备份索引文件 function backup_index_file() { cp $index_file ${index_file}_$(date -d "1 day ago" +%F) } # 备份索引文件 function send_index_file_to_remote() { echo 'send index file ok' } # 添加索引, 索引记录了当前最新的备份 function append_index_to_file() { echo "{week_day:$backup_week_day, dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, type:${1}, date:${backup_date}}" >> $index_file } # 记录 错误消息到文件 function logging_backup_err() { echo "{week_day:$backup_week_day, dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, type:${1}, date:${backup_date}}" >> $error_log } # 清空索引 function purge_index_from_file() { > $index_file } # 清空错误日志信息 function purge_err_log() { > $error_log } # 打包备份 function tar_backup_file() { echo "tar $1 ok" } # 发送备份到远程 function send_backup_to_remote() { echo "send $1 remote ok" } # 判断是应该全备还是增量备份 # 0:full, 1:incr function get_backup_type() { full_backup_week_day=`sed '/^full_backup_week_day=/!d;s/.*=//' $conf_file` backup_type=0 if [ "$full_backup_week_day" -eq `date +%u` ]; then backup_type=0 else backup_type=1 fi if [ ! -n "`cat $index_file`" ]; then backup_type=0 fi return $backup_type } # 测试配置文件正确性 function test_conf_file() { # 判断每个变量是否在配置文件中有配置，没有则退出程序 if [ ! -n "$user" ]; then echo 'fail: configure file user not set'; exit 2; fi if [ ! -n "$password" ]; then echo 'fail: configure file password not set'; exit 2; fi if [ ! -n "$backup_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi if [ ! -n "$full_backup_week_day" ]; then echo 'fail: configure file full_backup_week_day not set'; exit 2; fi if [ ! -n "$full_backup_prefix" ]; then echo 'fail: configure file full_backup_prefix not set'; exit 2; fi if [ ! -n "$increment_prefix" ]; then echo 'fail: configure file increment_prefix not set'; exit 2; fi if [ ! -n "$mysql_conf_file" ]; then echo 'fail: configure file mysql_conf_file not set'; exit 2; fi if [ ! -n "$error_log" ]; then echo 'fail: configure file error_log not set'; exit 2; fi if [ ! -n "$index_file" ]; then echo 'fail: configure file index_file not set'; exit 2; fi } # 执行 function run() { # 检测配置文件值 test_conf_file # 判断是执行全备还是曾量备份 get_backup_type backup_type=$? case $backup_type in 0 ) # 全量备份 full_backup backup_ok=$? if [ 0 -eq "$backup_ok" ]; then # 全备成功 # # 打包最新备份 # tar_backup_file $full_backup_prefix # # 将tar备份发送到远程 # send_backup_to_remote $full_backup_prefix # 备份索引文件 backup_index_file # # 发送索引文件到远程 # send_index_file_to_remote # 清除之前的备份 delete_before_backup # 清除索引文件 purge_index_from_file # 添加索引, 索引记录了当前最新的备份 append_index_to_file $full_backup_prefix else # 全备失败 # 删除备份目录 rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day} # 记录错误日志 logging_backup_err $full_backup_prefix fi ;; 1 ) # 增量备份 increment_backup backup_ok=$? if [ 0 -eq "$backup_ok" ]; then # 增量备份成功 # # 打包最新备份 # tar_backup_file $increment_prefix # # 将tar备份发送到远程 # send_backup_to_remote $increment_prefix # 添加索引, 索引记录了当前最新的备份 append_index_to_file $increment_prefix else # 增量备份失败 # 删除备份目录 rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day} # 记录错误日志 logging_backup_err $increment_prefix fi ;; esac } run chown -R apache.users /data/MySQL_Data_Backup 2.备份数据库脚本需要用到的配置 # 授权语句 show grants for xtrabackup@'localhost'; GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO 'xtrabackup'@'localhost' identified by 'pass'; [root:/data/MySQL_Data_Backup]# cat /usr/local/worksh/xtrabackup_cron/conf/mysql_increment_hot_backup.conf # mysql 用户名 user=xtrabackup # mysql 密码 password="pass" # 备份存放路径 backup_dir=/data/mysql_db_backup # percona-xtrabackup 备份软件路径 xtrabackup_dir=/usr # 全备是在一周的第几天，可以根据当前时间选择备份的时机，比如今天是星期一，需要全量备份就可以把数字改为1 full_backup_week_day=1 # 全量备信息名称 前缀 full_backup_prefix=full # 增量备信息名称 前缀 increment_prefix=incr # mysql配置文件 mysql_conf_file=/etc/my.cnf # 错误日志文件(更具此文件知道备份是否成功) # format: # {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30} error_log=../var/mysql_increment_hot_backup.err # 索引文件 # format: # {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr} index_file=../var/mysql_increment_hot_backup.index 3.查表的整体情况 # 线上eus_zabbix数据库的记录 mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix'; +----------------------------+----------------+------------+ | table_name | total_mb | table_rows | +----------------------------+----------------+------------+ | acknowledges | 0.06250000 | 2 | | actions | 0.04687500 | 15 | | alerts | 5.68750000 | 7759 | | application_discovery | 0.04687500 | 0 | | application_prototype | 0.04687500 | 0 | | application_template | 0.39062500 | 2688 | | applications | 0.35937500 | 2889 | | auditlog | 2.01562500 | 10863 | | auditlog_details | 0.26562500 | 1804 | | autoreg_host | 0.03125000 | 236 | | conditions | 0.03125000 | 32 | | config | 0.04687500 | 0 | | corr_condition | 0.03125000 | 0 | | corr_condition_group | 0.03125000 | 0 | | corr_condition_tag | 0.01562500 | 0 | | corr_condition_tagpair | 0.01562500 | 0 | | corr_condition_tagvalue | 0.01562500 | 0 | | corr_operation | 0.03125000 | 0 | | correlation | 0.01562500 | 0 | | dbversion | 0.01562500 | 0 | | dchecks | 0.03125000 | 1 | | dhosts | 0.03125000 | 0 | | drules | 0.04687500 | 1 | | dservices | 0.04687500 | 0 | | escalations | 0.03125000 | 2 | | event_recovery | 231.89062500 | 1473655 | | event_tag | 0.03125000 | 102 | | events | 1561.00000000 | 12511825 | | expressions | 0.03125000 | 6 | | functions | 2.01562500 | 6957 | | globalmacro | 0.01562500 | 0 | | globalvars | 0.01562500 | 0 | | graph_discovery | 0.12500000 | 1159 | | graph_theme | 0.03125000 | 2 | | graphs | 1.96875000 | 4324 | | graphs_items | 1.79687500 | 8995 | | group_discovery | 0.03125000 | 0 | | group_prototype | 0.06250000 | 7 | | groups | 0.03125000 | 95 | | history | 19667.17187500 | 176303524 | | history_log | 0.01562500 | 0 | | history_str | 777.90625000 | 5587513 | | history_text | 654.35937500 | 2552742 | | history_uint | 45977.17187500 | 373099099 | | host_discovery | 0.04687500 | 2 | | host_inventory | 0.01562500 | 0 | | hostmacro | 0.03125000 | 3 | | hosts | 0.20312500 | 284 | | hosts_groups | 0.04687500 | 298 | | hosts_templates | 0.10937500 | 636 | | housekeeper | 0.06250000 | 586 | | httpstep | 0.03125000 | 4 | | httpstepitem | 0.04687500 | 14 | | httptest | 0.07812500 | 5 | | httptestitem | 0.04687500 | 15 | | icon_map | 0.04687500 | 0 | | icon_mapping | 0.04687500 | 0 | | ids | 0.01562500 | 40 | | images | 1.53125000 | 138 | | interface | 0.04687500 | 212 | | interface_discovery | 0.03125000 | 0 | | item_application_prototype | 0.04687500 | 0 | | item_condition | 0.09375000 | 434 | | item_discovery | 1.76562500 | 6383 | | items | 13.25000000 | 16991 | | items_applications | 4.89062500 | 17609 | | maintenances | 0.01562500 | 0 | | maintenances_groups | 0.04687500 | 0 | | maintenances_hosts | 0.04687500 | 0 | | maintenances_windows | 0.04687500 | 0 | | mappings | 0.03125000 | 143 | | media | 0.04687500 | 2 | | media_type | 0.03125000 | 4 | | opcommand | 0.03125000 | 6 | | opcommand_grp | 0.04687500 | 0 | | opcommand_hst | 0.04687500 | 6 | | opconditions | 0.03125000 | 0 | | operations | 0.03125000 | 19 | | opgroup | 0.04687500 | 0 | | opinventory | 0.01562500 | 0 | | opmessage | 0.03125000 | 8 | | opmessage_grp | 0.04687500 | 4 | | opmessage_usr | 0.04687500 | 2 | | optemplate | 0.04687500 | 0 | | problem | 6.50000000 | 14345 | | problem_tag | 0.04687500 | 1 | | profiles | 0.50000000 | 2272 | | proxy_autoreg_host | 0.01562500 | 0 | | proxy_dhistory | 0.01562500 | 0 | | proxy_history | 0.01562500 | 0 | | regexps | 0.03125000 | 5 | | rights | 0.04687500 | 0 | | screen_user | 0.04687500 | 0 | | screen_usrgrp | 0.04687500 | 0 | | screens | 0.04687500 | 20 | | screens_items | 0.03125000 | 101 | | scripts | 0.06250000 | 3 | | service_alarms | 0.04687500 | 0 | | services | 0.03125000 | 0 | | services_links | 0.04687500 | 0 | | services_times | 0.03125000 | 0 | | sessions | 0.20312500 | 1076 | | slides | 0.04687500 | 0 | | slideshow_user | 0.04687500 | 0 | | slideshow_usrgrp | 0.04687500 | 0 | | slideshows | 0.04687500 | 0 | | sysmap_element_url | 0.03125000 | 0 | | sysmap_url | 0.03125000 | 0 | | sysmap_user | 0.04687500 | 0 | | sysmap_usrgrp | 0.04687500 | 0 | | sysmaps | 0.07812500 | 0 | | sysmaps_elements | 0.09375000 | 0 | | sysmaps_link_triggers | 0.04687500 | 0 | | sysmaps_links | 0.06250000 | 0 | | task | 0.01562500 | 0 | | task_close_problem | 0.01562500 | 0 | | timeperiods | 0.01562500 | 0 | | trends | 1489.54687500 | 19303099 | | trends_uint | 2278.60937500 | 29425077 | | trigger_depends | 0.04687500 | 355 | | trigger_discovery | 0.20312500 | 1878 | | trigger_tag | 0.03125000 | 103 | | triggers | 3.07812500 | 6162 | | users | 0.03125000 | 5 | | users_groups | 0.04687500 | 7 | | usrgrp | 0.03125000 | 7 | | valuemaps | 0.03125000 | 17 | +----------------------------+----------------+------------+ 127 rows in set (0.01 sec) # 数据库的占用空间情况 mysql> 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; +--------------------+-------------+------------+ | TABLE_SCHEMA | data_size | index_size | +--------------------+-------------+------------+ | zabbix | 54649.39 MB | 18038.93MB | | nosql_eye | 0.12 MB | 0.06MB | | performance_schema | 0.00 MB | 0.00MB | | mysql | 2.43 MB | 0.21MB | | information_schema | 0.15 MB | 0.00MB | | sys | 0.01 MB | 0.00MB | +--------------------+-------------+------------+ 6 rows in set (0.19 sec) # 要关闭zabbix_server 否则可能会出现大量连接不到客户端的报警信息 mysql> show full processlistG Connection id: 8787781 Current database: mysql *************************** 1. row *************************** Id: 8787065 User: zabbix Host: 127.0.0.1:41632 db: zabbix Command: Query Time: 255 State: copy to tmp table Info: ALTER TABLE history PARTITION BY RANGE( clock ) ( PARTITION p20190524 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-25 00:00:00")), PARTITION p20190525 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-26 00:00:00")), PARTITION p20190526 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-27 00:00:00")), PARTITION p20190527 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-28 00:00:00")), PARTITION p20190528 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-29 00:00:00")), PARTITION p20190529 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-30 00:00:00")), PARTITION p20190530 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-31 00:00:00")), PARTITION p20190531 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00")), PARTITION p20190601 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-02 00:00:00")), PARTITION p20190602 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-03 00:00:00")), PARTITION p20190603 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-04 00:00:00")), PARTITION p20190604 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-05 00:00:00")), PARTITION p20190605 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-06 00:00:00")), PARTITION p20190606 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-07 00:00:00")), PARTITION p20190607 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-08 00:00:00")), PARTITION p20190608 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-09 00:00:00")), PARTITION p20190609 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-10 00:00:00")), PARTITION p20190610 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-11 00:00:00")), PARTITION p20190611 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-12 00:00:00")), PARTITION p20190612 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-13 00:00:00")), PARTITION p20190613 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-14 00:00:00")), PARTITION p20190614 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-15 00:00:00")), PARTITION p20190615 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-16 00:00:00")), PARTITION p20190616 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-17 00:00:00")), PARTITION p20190617 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-18 00:00:00")), PARTITION p20190618 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-19 00:00:00")), PARTITION p20190619 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-20 00:00:00")), PARTITION p20190620 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-21 00:00:00")), PARTITION p20190621 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-22 00:00:00")), PARTITION p20190622 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-23 00:00:00")), PARTITION p20190623 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-24 00:00:00")), PARTITION p20190624 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-25 00:00:00")), PARTITION p20190625 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-26 00:00:00")) ) Rows_sent: 0 Rows_examined: 0 *************************** 2. row *************************** Id: 8787781 User: root Host: 127.0.0.1:61305 db: mysql Command: Query Time: 0 State: starting Info: show full processlist Rows_sent: 0 Rows_examined: 0 2 rows in set (0.08 sec) mysql> # 在表分区的时候 因为history和history_uint 超过1亿条数据，需要的时间较长 history 接近2亿条数据，下午两点 10分开始 ,到3点11分左右结束，用时1个小时多一点 history_uint 从3点10分开始 5点40左右结束，用时 2个半小时 history_uint | 45977.17187500 | 373099099 [root@aliyun-american-guigu-zabbix:/opt]# ./zabbix_db_partition.sh Ready to partition tables. Ready to update permissions of Zabbix user to create routines Enter root DB user: root Enter root password: pass mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1133 (42000) at line 1: Can't find any matching row in the user table Do you want to backup the database (recommended) (Y/n): n Are you certain you have a backup (y/N): y Ready to proceed: Starting yearly partioning at: 2019 and ending at: 2019 With 30 days of daily history mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1133 (42000) at line 1: Can't find any matching row in the user table Do you want to backup the database (recommended) (Y/n): n Are you certain you have a backup (y/N): y Ready to proceed: Starting yearly partioning at: 2019 and ending at: 2019 With 30 days of daily history Ready to proceed (Y/n): Y Altering table: history Altering table: history_log Altering table: history_str Altering table: history_text Altering table: history_uint Altering table: trends Altering table: trends_uint Creating monthly partitions for table: trends Creating monthly partitions for table: trends_uint Creating daily partitions for table: history Creating daily partitions for table: history_log Creating daily partitions for table: history_str Creating daily partitions for table: history_text Creating daily partitions for table: history_uint Ready to apply script to database, this may take a while.(Y/n): Y mysql: [Warning] Using a password on the command line interface can be insecure. Altering tables history history_log history_str history_text history_uint trends trends_uint trends trends_uint history history_log history_str history_text history_uint Installing procedures If Zabbix Version = 2.0 Do you want to update the /etc/zabbix/zabbix_server.conf to disable housekeeping (Y/n): n Do you want to update the crontab (Y/n): Y The crontab entry can be either in /etc/cron.daily, or added to the crontab for root Do you want to add this to the /etc/cron.daily directory (Y/n): Y Enter email of who should get the daily housekeeping reports: zhengjj@wondershare.cn # 分区是将表从物理上分割开 [root@aliyun-american-guigu-zabbix:/data/mysql_data/zabbix]# ll trends* -rw-r----- 1 mysql mysql 8744 Jun 23 23:04 trends.frm -rw-r----- 1 mysql mysql 377487360 Jun 23 23:07 trends#P#p201901.ibd -rw-r----- 1 mysql mysql 109051904 Jun 23 23:07 trends#P#p201902.ibd -rw-r----- 1 mysql mysql 184549376 Jun 23 23:08 trends#P#p201903.ibd -rw-r----- 1 mysql mysql 218103808 Jun 23 23:08 trends#P#p201904.ibd -rw-r----- 1 mysql mysql 234881024 Jun 23 23:08 trends#P#p201905.ibd -rw-r----- 1 mysql mysql 184549376 Jun 23 23:14 trends#P#p201906.ibd -rw-r----- 1 mysql mysql 98304 Jun 23 23:05 trends#P#p201907.ibd -rw-r----- 1 mysql mysql 8744 Jun 23 23:08 trends_uint.frm -rw-r----- 1 mysql mysql 629145600 Jun 23 23:12 trends_uint#P#p201901.ibd -rw-r----- 1 mysql mysql 159383552 Jun 23 23:12 trends_uint#P#p201902.ibd -rw-r----- 1 mysql mysql 251658240 Jun 23 23:14 trends_uint#P#p201903.ibd -rw-r----- 1 mysql mysql 306184192 Jun 23 23:14 trends_uint#P#p201904.ibd -rw-r----- 1 mysql mysql 327155712 Jun 23 23:14 trends_uint#P#p201905.ibd -rw-r----- 1 mysql mysql 247463936 Jun 23 23:14 trends_uint#P#p201906.ibd -rw-r----- 1 mysql mysql 98304 Jun 23 23:08 trends_uint#P#p201907.ibd # 依次清理history、history_uint等表分区 # 查看表分区情况 mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='history'; +-----------+--------+------------+------------+ | part | expr | descr | table_rows | +-----------+--------+------------+------------+ | p20190524 | clock | 1558767600 | 95591610 | | p20190525 | clock | 1558854000 | 1738773 | | p20190526 | clock | 1558940400 | 1752192 | | p20190527 | clock | 1559026800 | 1782144 | | p20190528 | clock | 1559113200 | 1801800 | | p20190529 | clock | 1559199600 | 1802112 | | p20190530 | clock | 1559286000 | 1801488 | | p20190531 | clock | 1559372400 | 1801800 | | p20190601 | clock | 1559458800 | 1801515 | | p20190602 | clock | 1559545200 | 1802097 | | p20190603 | clock | 1559631600 | 1801608 | | p20190604 | clock | 1559718000 | 1805457 | | p20190605 | clock | 1559804400 | 1821753 | | p20190606 | clock | 1559890800 | 1822392 | | p20190607 | clock | 1559977200 | 1822080 | | p20190608 | clock | 1560063600 | 1820520 | | p20190609 | clock | 1560150000 | 1822080 | | p20190610 | clock | 1560236400 | 1816108 | | p20190611 | clock | 1560322800 | 1816329 | | p20190612 | clock | 1560409200 | 1818508 | | p20190613 | clock | 1560495600 | 1821464 | | p20190614 | clock | 1560582000 | 1821922 | | p20190615 | clock | 1560668400 | 1824264 | | p20190616 | clock | 1560754800 | 1803013 | | p20190617 | clock | 1560841200 | 2855178 | | p20190618 | clock | 1560927600 | 5325855 | | p20190619 | clock | 1561014000 | 5318382 | | p20190620 | clock | 1561100400 | 5236833 | | p20190621 | clock | 1561186800 | 5112051 | | p20190622 | clock | 1561273200 | 5119428 | | p20190623 | clock | 1561359600 | 4973541 | | p20190624 | clock | 1561446000 | 3451243 | | p20190625 | clock | 1561532400 | 0 | +-----------+--------+------------+------------+ 33 rows in set (0.00 sec) mysql> alter table history drop partition p20190524; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema=schema() and table_name='history'; +-----------+--------+------------+------------+ | part | expr | descr | table_rows | +-----------+--------+------------+------------+ | p20190525 | clock | 1558854000 | 1750944 | | p20190526 | clock | 1558940400 | 1752192 | | p20190527 | clock | 1559026800 | 1782144 | | p20190528 | clock | 1559113200 | 1801800 | | p20190529 | clock | 1559199600 | 1802112 | | p20190530 | clock | 1559286000 | 1801488 | | p20190531 | clock | 1559372400 | 1801800 | | p20190601 | clock | 1559458800 | 1803360 | | p20190602 | clock | 1559545200 | 1814280 | | p20190603 | clock | 1559631600 | 1821768 | | p20190604 | clock | 1559718000 | 1822080 | | p20190605 | clock | 1559804400 | 1821768 | | p20190606 | clock | 1559890800 | 1822392 | | p20190607 | clock | 1559977200 | 1822080 | | p20190608 | clock | 1560063600 | 1820520 | | p20190609 | clock | 1560150000 | 1822080 | | p20190610 | clock | 1560236400 | 1820832 | | p20190611 | clock | 1560322800 | 1822080 | | p20190612 | clock | 1560409200 | 1821456 | | p20190613 | clock | 1560495600 | 1822080 | | p20190614 | clock | 1560582000 | 1823952 | | p20190615 | clock | 1560668400 | 1824264 | | p20190616 | clock | 1560754800 | 1916212 | | p20190617 | clock | 1560841200 | 2958781 | | p20190618 | clock | 1560927600 | 5356416 | | p20190619 | clock | 1561014000 | 5406648 | | p20190620 | clock | 1561100400 | 5241600 | | p20190621 | clock | 1561186800 | 5171712 | | p20190622 | clock | 1561273200 | 5168904 | | p20190623 | clock | 1561359600 | 4978896 | | p20190624 | clock | 1561446000 | 3293819 | | p20190625 | clock | 1561532400 | 0 | +-----------+--------+------------+------------+ 32 rows in set (0.00 sec) mysql> alter table history_uint drop partition p20190524; # 清理后的结果 mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix'; +----------------------------+----------------+------------+ | table_name | total_mb | table_rows | +----------------------------+----------------+------------+ | acknowledges | 0.06250000 | 2 | | actions | 0.04687500 | 15 | | alerts | 5.75000000 | 8052 | | application_discovery | 0.04687500 | 0 | | application_prototype | 0.04687500 | 0 | | application_template | 0.39062500 | 2688 | | applications | 0.35937500 | 2889 | | auditlog | 2.01562500 | 10866 | | auditlog_details | 0.26562500 | 1804 | | autoreg_host | 0.03125000 | 236 | | conditions | 0.03125000 | 32 | | config | 0.04687500 | 0 | | corr_condition | 0.03125000 | 0 | | corr_condition_group | 0.03125000 | 0 | | corr_condition_tag | 0.01562500 | 0 | | corr_condition_tagpair | 0.01562500 | 0 | | corr_condition_tagvalue | 0.01562500 | 0 | | corr_operation | 0.03125000 | 0 | | correlation | 0.01562500 | 0 | | dbversion | 0.01562500 | 0 | | dchecks | 0.03125000 | 1 | | dhosts | 0.03125000 | 0 | | drules | 0.04687500 | 1 | | dservices | 0.04687500 | 0 | | escalations | 0.03125000 | 3 | | event_recovery | 231.89062500 | 1479805 | | event_tag | 0.03125000 | 102 | | events | 1561.00000000 | 12553229 | | expressions | 0.03125000 | 6 | | functions | 2.01562500 | 7041 | | globalmacro | 0.01562500 | 0 | | globalvars | 0.01562500 | 0 | | graph_discovery | 0.12500000 | 1243 | | graph_theme | 0.03125000 | 2 | | graphs | 1.96875000 | 4408 | | graphs_items | 1.79687500 | 9205 | | group_discovery | 0.03125000 | 0 | | group_prototype | 0.06250000 | 7 | | groups | 0.03125000 | 95 | | history | 7314.17187500 | 79297383 | | history_log | 1.03125000 | 0 | | history_str | 269.62500000 | 2380636 | | history_text | 196.07812500 | 849802 | | history_uint | 15734.87500000 | 168386374 | | host_discovery | 0.04687500 | 2 | | host_inventory | 0.01562500 | 0 | | hostmacro | 0.03125000 | 3 | | hosts | 0.20312500 | 284 | | hosts_groups | 0.04687500 | 298 | | hosts_templates | 0.10937500 | 636 | | housekeeper | 0.01562500 | 106 | | httpstep | 0.03125000 | 4 | | httpstepitem | 0.04687500 | 14 | | httptest | 0.07812500 | 5 | | httptestitem | 0.04687500 | 15 | | icon_map | 0.04687500 | 0 | | icon_mapping | 0.04687500 | 0 | | ids | 0.01562500 | 40 | | images | 1.53125000 | 138 | | interface | 0.04687500 | 212 | | interface_discovery | 0.03125000 | 0 | | item_application_prototype | 0.04687500 | 0 | | item_condition | 0.09375000 | 434 | | item_discovery | 1.76562500 | 6677 | | items | 13.25000000 | 17285 | | items_applications | 4.89062500 | 16337 | | maintenances | 0.01562500 | 0 | | maintenances_groups | 0.04687500 | 0 | | maintenances_hosts | 0.04687500 | 0 | | maintenances_windows | 0.04687500 | 0 | | mappings | 0.03125000 | 143 | | media | 0.04687500 | 2 | | media_type | 0.03125000 | 4 | | opcommand | 0.03125000 | 6 | | opcommand_grp | 0.04687500 | 0 | | opcommand_hst | 0.04687500 | 6 | | opconditions | 0.03125000 | 0 | | operations | 0.03125000 | 19 | | opgroup | 0.04687500 | 0 | | opinventory | 0.01562500 | 0 | | opmessage | 0.03125000 | 8 | | opmessage_grp | 0.04687500 | 4 | | opmessage_usr | 0.04687500 | 2 | | optemplate | 0.04687500 | 0 | | problem | 6.48437500 | 11847 | | problem_tag | 0.04687500 | 1 | | profiles | 0.50000000 | 2272 | | proxy_autoreg_host | 0.01562500 | 0 | | proxy_dhistory | 0.01562500 | 0 | | proxy_history | 0.01562500 | 0 | | regexps | 0.03125000 | 5 | | rights | 0.04687500 | 0 | | screen_user | 0.04687500 | 0 | | screen_usrgrp | 0.04687500 | 0 | | screens | 0.04687500 | 20 | | screens_items | 0.03125000 | 101 | | scripts | 0.06250000 | 3 | | service_alarms | 0.04687500 | 0 | | services | 0.03125000 | 0 | | services_links | 0.04687500 | 0 | | services_times | 0.03125000 | 0 | | sessions | 0.20312500 | 1077 | | slides | 0.04687500 | 0 | | slideshow_user | 0.04687500 | 0 | | slideshow_usrgrp | 0.04687500 | 0 | | slideshows | 0.04687500 | 0 | | sysmap_element_url | 0.03125000 | 0 | | sysmap_url | 0.03125000 | 0 | | sysmap_user | 0.04687500 | 0 | | sysmap_usrgrp | 0.04687500 | 0 | | sysmaps | 0.07812500 | 0 | | sysmaps_elements | 0.09375000 | 0 | | sysmaps_link_triggers | 0.04687500 | 0 | | sysmaps_links | 0.06250000 | 0 | | task | 0.01562500 | 0 | | task_close_problem | 0.01562500 | 0 | | timeperiods | 0.01562500 | 0 | | trends | 832.81250000 | 13741245 | | trends_uint | 1179.21875000 | 19244765 | | trigger_depends | 0.04687500 | 355 | | trigger_discovery | 0.20312500 | 1962 | | trigger_tag | 0.03125000 | 103 | | triggers | 3.07812500 | 6521 | | users | 0.03125000 | 5 | | users_groups | 0.04687500 | 7 | | usrgrp | 0.03125000 | 7 | | valuemaps | 0.03125000 | 17 | +----------------------------+----------------+------------+ 127 rows in set (0.01 sec) # 分区脚本获取地址 https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh 脚本默认详情数据保留30天，趋势数据保留12个月，如需修改，请修改以下内容： daily_history_min=30 monthly_history_min=12 脚本默认连接数据库信息，更改成你的： DBHOST=localhost DBUSER=zabbix DBPASS=zabbix [root@zabbix:~]# cat /opt/zabbix_db_partition.sh #!/bin/bash # # This script will partition your zabbix database to improve the efficiency. # It will also create stored procedures to do the necessary housekeeping, # and create a cronjob to do this on a daily basis # # This script inspired by the following: # http://zabbixzone.com/zabbix/partitioning-tables/ # # While the basic SQL is from the above page, this script both creates the necessary # SQL for the desired tables, and can create new partitions as the time goes on # assuming that the cronjob has been properly entered. # # # Who to email with cron output # EMAIL="root@localhost" # # How long to keep the daily history # daily_history_min=30 # # How long to keep the monthly history (months) # monthly_history_min=6 # # Years to create the monthly partitions for # first_year=`date +"%Y"` last_year=$first_year cur_month=`date +"%m"|sed 's/^0*//'` if [ $cur_month -eq 12 ]; then last_year=$((first_year+1)) cur_month=1 fi y=`date +"%Y"` SQL="/tmp/partition.sql" PATHTOCRON="/usr/local/zabbix/cron.d" PATHTOMAILBIN="/usr/bin/mail" DUMP_FILE=/tmp/zabbix.sql function usage { cat <<_EOF_ $0 [-h host][-u user][-p password][-d min_days][-y startyear][-n][-s][-e email_address][-b] -h host database host -u user db user -p password user password -d min_days Minimum number of days of history to keep (default: $daily_history_min) -m min_months Minimum number of months to keep trends (default: $monthly_history_min) -y startyear First year to set up with partitions -n noninteractive Run without questions - careful, make sure you know what is going to happen. Needs my.cnf with correct permissions. -b backup Create backup of DB in $DUMP_FILE before alterations (only works with non-interactive mode, -n) -s simulate Create SQL file that would be executed for examination ($SQL) -e email Email address to receive partition update report (default: $EMAIL) After running this script, don't forget to disable housekeeping if you didn't have the script disable it, and add the following cronjob ### Option: DisableHousekeeping # If set to 1, disables housekeeping. # # Mandatory: no # Range: 0-1 ################### Uncomment and change the following line to 1 in ################### Then restart the zabbix server DisableHousekeeping=1 Cron job 0 0 * * * $PATHTOCRON/housekeeping.sh _EOF_ exit } #DBHOST=localhost DBHOST="127.0.0.1" DBUSER=zabbix DBPASS="zabbix" SIMULATE=0 NONINTERACTIVE=0 BACKUP=0 while getopts "m:nsbe:h:u:p:d:y:?h" flag; do case $flag in h) DBHOST=$OPTARG ;; u) DBUSER=$OPTARG ;; p) DBPASS=$OPTARG ;; e) EMAIL=$OPTARG ;; s) SIMULATE=1 ;; n) NONINTERACTIVE=1 ;; b) BACKUP=1 ;; d) h=$OPTARG if [ $h -gt 0 ] 2>/dev/null; then daily_history_min=$h else echo "Invalid daily history min, exiting" exit 1 fi ;; m) h=$OPTARG if [ $h -gt 0 ] 2>/dev/null; then monthly_history_min=$h else echo "Invalid monthly history min, exiting" exit 1 fi ;; y) yy=$OPTARG if [ $yy -lt $y -a $yy -gt 2000 ] 2>/dev/null; then first_year=$yy else echo "Invalid year, exiting" exit 1 fi ;; ?|h) usage ;; esac done shift $((OPTIND-1)) if [ $NONINTERACTIVE != 1 ]; then echo "Ready to partition tables." fi if [ $SIMULATE = 0 ]; then if [ $NONINTERACTIVE = 1 ]; then mysql -B -h $DBHOST -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';" # echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS} mysql -h $DBHOST -e "GRANT LOCK TABLES ON zabbix.* TO '$DBUSER'@'$DBHOST' IDENTIFIED BY '$DBPASS';" if [ $BACKUP = 1 ]; then mysqldump --opt -h $DBHOST -u $DBUSER -p$DBPASS zabbix --result-file=$DUMP_FILE rc=$? if [ $rc -ne 0 ]; then echo "Error during mysqldump, exit code: $rc" fi fi else echo -e " Ready to update permissions of Zabbix user to create routines " echo -n "Enter root DB user: " read DBADMINUSER echo -n "Enter $DBADMINUSER password: " read DBADMINPASS mysql -B -h $DBHOST -u $DBADMINUSER -p$DBADMINPASS -e "GRANT CREATE ROUTINE ON zabbix.* TO '$DBUSER'@'localhost';" echo -e " " echo -ne " Do you want to backup the database (recommended) (Y/n): " read yn if [ "$yn" != "n" -a "$yn" != "N" ]; then echo -e " Enter output file, press return for default of $DUMP_FILE" read df [ "$df" != "" ] && DUMP_FILE=$df # # Lock tables is needed for a good mysqldump # echo "GRANT LOCK TABLES ON zabbix.* TO '${DBUSER}'@'${DBHOST}' IDENTIFIED BY '${DBPASS}';" | mysql -h${DBHOST} -u${DBADMINUSER} --password=${DBADMINPASS} mysqldump --opt -h ${DBHOST} -u ${DBUSER} -p${DBPASS} zabbix --result-file=${DUMP_FILE} rc=$? if [ $rc -ne 0 ]; then echo "Error during mysqldump, rc: $rc" echo "Do you wish to continue (y/N): " read yn [ "yn" != "y" -a "$yn" != "Y" ] && exit else echo "Mysqldump succeeded!, proceeding with upgrade..." fi else echo "Are you certain you have a backup (y/N): " read yn [ "$yn" != 'y' -a "$yn" != "Y" ] && exit fi fi fi if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e " Ready to proceed:" echo -e " Starting yearly partioning at: $first_year" echo "and ending at: $last_year" echo "With $daily_history_min days of daily history" echo -e " Ready to proceed (Y/n): " read yn [ "$yn" = 'n' -o "$yn" = "N" ] && exit fi DAILY="history history_log history_str history_text history_uint" DAILY_IDS="itemid id itemid id itemid" MONTHLY="trends trends_uint" #"acknowledges alerts auditlog events service_alarms" MONTHLY_IDS="" TABLES="$DAILY $MONTHLY" IDS="$DAILY_IDS $MONTHLY_IDS" if [ $NONINTERACTIVE != 1 ]; then echo "Use zabbix; SELECT 'Altering tables';" >$SQL else echo "Use zabbix;" >$SQL fi cnt=0 for i in $TABLES; do if [ $NONINTERACTIVE != 1 ]; then echo "Altering table: $i" echo "SELECT '$i';" >>$SQL fi cnt=$((cnt+1)) case $i in history_log) #echo "ALTER TABLE $i DROP KEY history_log_2;" >>$SQL #echo "ALTER TABLE $i ADD KEY history_log_2(itemid, id);" >>$SQL #echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL #id=`echo $IDS | cut -f$cnt -d" "` #echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL ;; history_text) #echo "ALTER TABLE $i DROP KEY history_text_2;" >>$SQL #echo "ALTER TABLE $i ADD KEY history_text_2 (itemid, clock);" >>$SQL #echo "ALTER TABLE $i DROP PRIMARY KEY ;" >>$SQL #id=`echo $IDS | cut -f$cnt -d" "` #echo "ALTER TABLE $i ADD KEY ${i}id ($id);" >>$SQL ;; esac done echo -en " " >>$SQL for i in $MONTHLY; do if [ $NONINTERACTIVE != 1 ]; then echo "Creating monthly partitions for table: $i" echo "SELECT '$i';" >>$SQL fi echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL for y in `seq $first_year $last_year`; do last_month=12 [ $y -eq $last_year ] && last_month=$((cur_month+1)) for m in `seq 1 $last_month`; do [ $m -lt 10 ] && m="0$m" ms=`date +"%Y-%m-01" -d "$m/01/$y +1 month"` pname="p${y}${m}" echo -n "PARTITION $pname VALUES LESS THAN (UNIX_TIMESTAMP("$ms 00:00:00"))" >>$SQL [ $m -ne $last_month -o $y -ne $last_year ] && echo -n "," >>$SQL echo -ne " " >>$SQL done done echo ");" >>$SQL done for i in $DAILY; do if [ $NONINTERACTIVE != 1 ]; then echo "Creating daily partitions for table: $i" echo "SELECT '$i';" >>$SQL fi echo "ALTER TABLE $i PARTITION BY RANGE( clock ) (" >>$SQL for d in `seq -$daily_history_min 2`; do ds=`date +"%Y-%m-%d" -d "$d day +1 day"` pname=`date +"%Y%m%d" -d "$d day"` echo -n "PARTITION p$pname VALUES LESS THAN (UNIX_TIMESTAMP("$ds 00:00:00"))" >>$SQL [ $d -ne 2 ] && echo -n "," >>$SQL echo -ne " " >>$SQL done echo ");" >>$SQL done ############################################################### if [ $NONINTERACTIVE != 1 ]; then cat >>$SQL <<_EOF_ SELECT "Installing procedures"; _EOF_ fi cat >>$SQL <<_EOF_ /************************************************************** MySQL Auto Partitioning Procedure for Zabbix 1.8 http://zabbixzone.com/zabbix/partitioning-tables/ Author: Ricardo Santos (rsantos at gmail.com) Version: 20110518 **************************************************************/ DELIMITER // DROP PROCEDURE IF EXISTS zabbix.create_zabbix_partitions; // CREATE PROCEDURE zabbix.create_zabbix_partitions () BEGIN _EOF_ ############################################################### for i in $DAILY; do echo " CALL zabbix.create_next_partitions("zabbix","$i");" >>$SQL echo " CALL zabbix.drop_old_partitions("zabbix","$i");" >>$SQL done echo -en " " >>$SQL for i in $MONTHLY; do echo " CALL zabbix.create_next_monthly_partitions("zabbix","$i");" >>$SQL echo " CALL zabbix.drop_old_monthly_partitions("zabbix","$i");" >>$SQL done ############################################################### cat >>$SQL <<_EOF_ END // DROP PROCEDURE IF EXISTS zabbix.create_next_partitions; // CREATE PROCEDURE zabbix.create_next_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE NEXTCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @totaldays = 7; SET @i = 1; createloop: LOOP SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' ); SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00')); CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK ); SET @i=@i+1; IF @i > @totaldays THEN LEAVE createloop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.drop_old_partitions; // CREATE PROCEDURE zabbix.drop_old_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE OLDCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @mindays = $daily_history_min; SET @maxdays = @mindays+4; SET @i = @maxdays; droploop: LOOP SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY); SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' ); CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME ); SET @i=@i-1; IF @i <= @mindays THEN LEAVE droploop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.create_next_monthly_partitions; // CREATE PROCEDURE zabbix.create_next_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE NEXTCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @totalmonths = 3; SET @i = 1; createloop: LOOP SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH); SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' ); SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00')); CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK ); SET @i=@i+1; IF @i > @totalmonths THEN LEAVE createloop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.drop_old_monthly_partitions; // CREATE PROCEDURE zabbix.drop_old_monthly_partitions (SCHEMANAME varchar(64), TABLENAME varchar(64)) BEGIN DECLARE OLDCLOCK timestamp; DECLARE PARTITIONNAME varchar(16); DECLARE CLOCK int; SET @minmonths = $monthly_history_min; SET @maxmonths = @minmonths+24; SET @i = @maxmonths; droploop: LOOP SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH); SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' ); CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME ); SET @i=@i-1; IF @i <= @minmonths THEN LEAVE droploop; END IF; END LOOP; END // DROP PROCEDURE IF EXISTS zabbix.create_partition; // CREATE PROCEDURE zabbix.create_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME; IF RETROWS = 0 THEN SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END // DROP PROCEDURE IF EXISTS zabbix.drop_partition; // CREATE PROCEDURE zabbix.drop_partition (SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64)) BEGIN DECLARE RETROWS int; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME; IF RETROWS = 1 THEN SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' DROP PARTITION ', PARTITIONNAME, ';' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END // DELIMITER ; _EOF_ if [ $SIMULATE = 1 ]; then exit 0 fi if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e " Ready to apply script to database, this may take a while.(Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then mysql --skip-column-names -h ${DBHOST} -u ${DBUSER} -p${DBPASS} <$SQL fi conf=/etc/zabbix/zabbix_server.conf if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -e " If Zabbix Version = 2.0 Do you want to update the /etc/zabbix/zabbix_server.conf" echo -n "to disable housekeeping (Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then cp $conf ${conf}.bak sed -i "s/^# DisableHousekeeping=0/DisableHousekeeping=1/" $conf sed -i "s/^DisableHousekeeping=0/DisableHousekeeping=1/" $conf /etc/init.d/zabbix-server stop sleep 5 /etc/init.d/zabbix-server start 2>&1 > /dev/null fi tmpfile=/tmp/cron$$ if [ $NONINTERACTIVE = 1 ]; then yn='y' else echo -ne " Do you want to update the crontab (Y/n): " read yn fi if [ "$yn" != "n" -a "$yn" != "N" ]; then where= while [ "$where" = "" ]; do if [ $NONINTERACTIVE = 1 ]; then where='Y' else echo "The crontab entry can be either in /etc/cron.daily, or added" echo -e "to the crontab for root " echo -n "Do you want to add this to the /etc/cron.daily directory (Y/n): " read where fi [ "$where" = "" -o "$where" = "y" ] && where="Y" if [ "$where" != "y" -a "$where" != "Y" -a "$where" != "n" -a "$where" != "N" ]; then where="" echo "Response not recognized, please try again" fi done if [ $NONINTERACTIVE != 1 ]; then echo -en " Enter email of who should get the daily housekeeping reports: " read mailto fi [ "$mailto" = "" ] && mailto=$EMAIL mkdir -p $PATHTOCRON cat >$PATHTOCRON/housekeeping.sh <<_EOF_ #!/bin/bash MAILTO=$mailto tmpfile=/tmp/housekeeping$$ date >$tmpfile /usr/bin/mysql --skip-column-names -B -h localhost -u${DBUSER} -p${DBPASS} zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1 $PATHTOMAILBIN -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile rm -f $tmpfile _EOF_ chmod +x $PATHTOCRON/housekeeping.sh chown -R zabbix.zabbix /usr/local/zabbix if [ "$where" = "Y" ]; then cat >/etc/cron.daily/zabbixhousekeeping <<_EOF_ #!/bin/bash $PATHTOCRON/housekeeping.sh _EOF_ chmod +x /etc/cron.daily/zabbixhousekeeping else crontab -l >$tmpfile cat >>$tmpfile <<_EOF_ 0 0 * * * $PATHTOCRON/housekeeping.sh _EOF_ crontab $tmpfile rm $tmpfile fi fi #############################
# 上面的分表脚本执行以后会生成以下自动每天执行分表的计划任务
# 为了安全起见最好自定义每天的计划任务，如果没有自动分表，zabbix获取的监控数据就无法入库，进而不能触发报警，如果刚好有关键业务出现问题没有触发报警，就很悲剧了
[root@aliyun-zabbix:~]# cat /etc/cron.daily/zabbixhousekeeping
#!/bin/bash
/usr/local/zabbix/cron.d/housekeeping.sh
[root@aliyun-zabbix:~]# cat /usr/local/zabbix/cron.d/housekeeping.sh
#!/bin/bash
MAILTO=jack@chinasoft.com
tmpfile=/tmp/housekeeping$$
date >$tmpfile
/usr/local/bin/mysql --skip-column-names -B -h localhost -uzabbix -p'zabbix' zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1
/usr/bin/mail -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile
rm -f $tmpfile
[root@aliyun-zabbix:~]# crontab -l
## zabbix auto partion: history,history_uint,history_str,history_text,history_log,trends,trends_uint
05 00 * * * /bin/bash /usr/local/zabbix/cron.d/housekeeping.sh > /dev/null 2>&1
# 关闭hoursekeeping
