@zabbix数据库历史与趋势数据占用优化（mysql存储查询）

0|1文章目录

在这里插入图片描述

1|01.数据库大小查询

数据库大小查询

进入数据库，切换到information_schema 库（这个库存放了其他的数据库的关联信息）

#切入数据库信息库
mysql> information_schema


#查询所有数据的大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;


#查询zabbix库的大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix';



#查询zabbix库hosts表大小
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='hosts';

2|02.zabbix案例

2|11)磁盘占用优化

假设zabbix占用空间大，需要优化

zabbix占用大的表 一般为两个表：
history：历史数据存储表
trends：趋势数据存储表

#查看zabbix库容量
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix';
+-----------+
| data      |
+-----------+
| 1760.81MB |
+-----------+
1 row in set (0.01 sec)




#查询zabbix库表大小，并排序
mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='zabbix' order by length desc;
+----------------------------+-------------+--------------+------------+------------+------------+
| TABLE_NAME                 | DATA_LENGTH | INDEX_LENGTH | length     | TABLE_ROWS | total_size |
+----------------------------+-------------+--------------+------------+------------+------------+
| history_uint               |  1125138432 |    503332864 | 1628471296 |    9288216 | 1553.031MB |
| trends_uint                |   360185856 |            0 |  360185856 |    4578474 | 343.500MB  |
| history                    |   192610304 |     89915392 |  282525696 |    3206240 | 269.438MB  |
| trends                     |    92454912 |            0 |   92454912 |    1207380 | 88.172MB   |
| history_str                |    46219264 |     25247744 |   71467008 |     822685 | 68.156MB   |
| alerts                     |     6832128 |      2867200 |    9699328 |      14900 | 9.250MB    |
| items                      |     4669440 |      2359296 |    7028736 |       6470 | 6.703MB    |
| events                     |     3686400 |      3162112 |    6848512 |      26401 | 6.531MB    |
| history_text               |     3162112 |        16384 |    3178496 |        387 | 3.031MB    |
| triggers                   |     2605056 |       294912 |    2899968 |       2860 | 2.766MB    |
| event_recovery             |     1589248 |       540672 |    2129920 |      10712 | 2.031MB    |
| images                     |     1589248 |        16384 |    1605632 |        138 | 1.531MB    |
| functions                  |      409600 |       475136 |     884736 |       5965 | 0.844MB    |
| items_applications         |      360448 |       458752 |     819200 |       5626 | 0.781MB    |
| item_preproc               |      475136 |       212992 |     688128 |       5571 | 0.656MB    |
| item_discovery             |      360448 |       294912 |     655360 |       3818 | 0.625MB    |
| auditlog                   |      311296 |       180224 |     491520 |       2709 | 0.469MB    |
| graphs_items               |      229376 |       196608 |     425984 |       2523 | 0.406MB    |
| graphs                     |      180224 |       114688 |     294912 |        863 | 0.281MB    |
| trigger_depends            |      114688 |       147456 |     262144 |       1362 | 0.250MB    |
| item_rtdata                |      196608 |            0 |     196608 |       3021 | 0.188MB    |
| profiles                   |      114688 |        81920 |     196608 |        700 | 0.188MB    |
| hosts                      |       98304 |        98304 |     196608 |        192 | 0.188MB    |
| item_application_prototype |       81920 |       114688 |     196608 |        945 | 0.188MB    |
| hostmacro                  |       98304 |        81920 |     180224 |        749 | 0.172MB    |
| item_condition             |      114688 |        49152 |     163840 |        897 | 0.156MB    |
| applications               |       81920 |        49152 |     131072 |        894 | 0.125MB    |
| mappings                   |       81920 |        49152 |     131072 |       1139 | 0.125MB    |
| widget_field               |       16384 |        98304 |     114688 |        134 | 0.109MB    |
| media_type                 |       98304 |        16384 |     114688 |         10 | 0.109MB    |
| sysmaps_elements           |       16384 |        81920 |      98304 |          0 | 0.094MB    |
| auditlog_details           |       81920 |        16384 |      98304 |        476 | 0.094MB    |
| trigger_discovery          |       65536 |        16384 |      81920 |        780 | 0.078MB    |
| application_template       |       49152 |        32768 |      81920 |        417 | 0.078MB    |
| sysmaps                    |       16384 |        65536 |      81920 |          0 | 0.078MB    |
| event_tag                  |       65536 |        16384 |      81920 |        565 | 0.078MB    |
| httptest                   |       16384 |        65536 |      81920 |          2 | 0.078MB    |
| sysmaps_links              |       16384 |        49152 |      65536 |          0 | 0.063MB    |
| problem                    |       16384 |        49152 |      65536 |         95 | 0.063MB    |
| scripts                    |       16384 |        49152 |      65536 |          3 | 0.063MB    |
| screens_items              |       49152 |        16384 |      65536 |        211 | 0.063MB    |
| group_prototype            |       16384 |        49152 |      65536 |         14 | 0.063MB    |
| acknowledges               |       16384 |        49152 |      65536 |         17 | 0.063MB    |
| event_suppress             |       16384 |        49152 |      65536 |          0 | 0.063MB    |
| escalations                |       16384 |        49152 |      65536 |          9 | 0.063MB    |
| host_discovery             |       16384 |        32768 |      49152 |          4 | 0.047MB    |
| slideshow_user             |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| correlation                |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| proxy_dhistory             |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| slides                     |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| sysmaps_link_triggers      |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| application_prototype      |       16384 |        32768 |      49152 |        141 | 0.047MB    |
| icon_mapping               |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| opcommand_hst              |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| service_alarms             |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| sysmap_usrgrp              |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| application_discovery      |       16384 |        32768 |      49152 |         84 | 0.047MB    |
| icon_map                   |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| dservices                  |       16384 |        32768 |      49152 |         35 | 0.047MB    |
| opcommand_grp              |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| media                      |       16384 |        32768 |      49152 |          3 | 0.047MB    |
| sysmap_user                |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| httptestitem               |       16384 |        32768 |      49152 |          6 | 0.047MB    |
| services_links             |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| drules                     |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| optemplate                 |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| hosts_templates            |       16384 |        32768 |      49152 |        191 | 0.047MB    |
| actions                    |       16384 |        32768 |      49152 |          7 | 0.047MB    |
| config                     |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| maintenances_windows       |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| screens                    |       16384 |        32768 |      49152 |         53 | 0.047MB    |
| opmessage_usr              |       16384 |        32768 |      49152 |          6 | 0.047MB    |
| hosts_groups               |       16384 |        32768 |      49152 |        196 | 0.047MB    |
| users_groups               |       16384 |        32768 |      49152 |          4 | 0.047MB    |
| maintenances_hosts         |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| screen_usrgrp              |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| opmessage_grp              |       16384 |        32768 |      49152 |          4 | 0.047MB    |
| httpstepitem               |       16384 |        32768 |      49152 |          6 | 0.047MB    |
| autoreg_host               |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| maintenances_groups        |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| screen_user                |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| sysmap_element_trigger     |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| dashboard_usrgrp           |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| maintenances               |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| rights                     |       16384 |        32768 |      49152 |         23 | 0.047MB    |
| slideshows                 |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| dashboard_user             |       16384 |        32768 |      49152 |          1 | 0.047MB    |
| task                       |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| slideshow_usrgrp           |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| opgroup                    |       16384 |        32768 |      49152 |          1 | 0.047MB    |
| interface                  |       16384 |        32768 |      49152 |         28 | 0.047MB    |
| tag_filter                 |       16384 |        32768 |      49152 |          0 | 0.047MB    |
| operations                 |       16384 |        16384 |      32768 |         16 | 0.031MB    |
| lld_macro_path             |       16384 |        16384 |      32768 |         58 | 0.031MB    |
| proxy_history              |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| opconditions               |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| corr_operation             |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| services                   |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| globalmacro                |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| proxy_autoreg_host         |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| sessions                   |       16384 |        16384 |      32768 |        175 | 0.031MB    |
| services_times             |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| expressions                |       16384 |        16384 |      32768 |         10 | 0.031MB    |
| corr_condition             |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| widget                     |       16384 |        16384 |      32768 |         15 | 0.031MB    |
| problem_tag                |       16384 |        16384 |      32768 |         10 | 0.031MB    |
| opcommand                  |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| history_log                |       16384 |        16384 |      32768 |         56 | 0.031MB    |
| config_autoreg_tls         |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| valuemaps                  |       16384 |        16384 |      32768 |        102 | 0.031MB    |
| sysmap_url                 |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| httptest_field             |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| dhosts                     |       16384 |        16384 |      32768 |         35 | 0.031MB    |
| media_type_param           |       16384 |        16384 |      32768 |        123 | 0.031MB    |
| corr_condition_group       |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| usrgrp                     |       16384 |        16384 |      32768 |          6 | 0.031MB    |
| sysmap_shape               |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| dchecks                    |       16384 |        16384 |      32768 |          1 | 0.031MB    |
| group_discovery            |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| conditions                 |       16384 |        16384 |      32768 |         51 | 0.031MB    |
| sysmap_element_url         |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| users                      |       16384 |        16384 |      32768 |          3 | 0.031MB    |
| opmessage                  |       16384 |        16384 |      32768 |         13 | 0.031MB    |
| httpstep_field             |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| interface_discovery        |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| host_tag                   |       16384 |        16384 |      32768 |          5 | 0.031MB    |
| httpstep                   |       16384 |        16384 |      32768 |          2 | 0.031MB    |
| graph_theme                |       16384 |        16384 |      32768 |          4 | 0.031MB    |
| regexps                    |       16384 |        16384 |      32768 |          5 | 0.031MB    |
| trigger_tag                |       16384 |        16384 |      32768 |         85 | 0.031MB    |
| maintenance_tag            |       16384 |        16384 |      32768 |          0 | 0.031MB    |
| hstgrp                     |       16384 |        16384 |      32768 |         25 | 0.031MB    |
| dashboard                  |       16384 |        16384 |      32768 |          2 | 0.031MB    |
| graph_discovery            |       16384 |        16384 |      32768 |        235 | 0.031MB    |
| globalvars                 |       16384 |            0 |      16384 |          0 | 0.016MB    |
| ids                        |       16384 |            0 |      16384 |         56 | 0.016MB    |
| corr_condition_tagvalue    |       16384 |            0 |      16384 |          0 | 0.016MB    |
| corr_condition_tagpair     |       16384 |            0 |      16384 |          0 | 0.016MB    |
| timeperiods                |       16384 |            0 |      16384 |          0 | 0.016MB    |
| housekeeper                |       16384 |            0 |      16384 |          0 | 0.016MB    |
| task_remote_command_result |       16384 |            0 |      16384 |          0 | 0.016MB    |
| corr_condition_tag         |       16384 |            0 |      16384 |          0 | 0.016MB    |
| task_remote_command        |       16384 |            0 |      16384 |          0 | 0.016MB    |
| task_close_problem         |       16384 |            0 |      16384 |          0 | 0.016MB    |
| dbversion                  |       16384 |            0 |      16384 |          0 | 0.016MB    |
| task_check_now             |       16384 |            0 |      16384 |          0 | 0.016MB    |
| task_acknowledge           |       16384 |            0 |      16384 |          0 | 0.016MB    |
| opinventory                |       16384 |            0 |      16384 |          0 | 0.016MB    |
| host_inventory             |       16384 |            0 |      16384 |          0 | 0.016MB    |
+----------------------------+-------------+--------------+------------+------------+------------+
149 rows in set (0.01 sec)

2|22）优化表的大小

清理表的村存储数据：
表数据只需要保留一个月，删除某个表超时一个月的数据；
删除表数据，需要获取获取时间戳，删除表数据后，执行optimize table table_name 立刻释放磁盘空间

【时间戳在线换算】

#删除之前的1656864000存储的数据，通过时间戳换算，数据过大，可能很慢
mysql> delete from zabbix.history_uint  where clock < 1656864000;
Query OK, 184017 rows affected (1 min 13.99 sec)





#立即释放磁盘占用空间 
mysql> optimize table zabbix.history_uint;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3 min 7.93 sec)





#继续查看表的大小（确认到表已经减少了）
mysql>  select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='zabbix' and table_name='history_uint';
+----------+
| data     |
+----------+
| 542.98MB |
+----------+
1 row in set (0.00 sec)

2|33）服务器上查询确认表大小

筛选 查看数据库大小

#文件大小查看(查看zabbix库中表的ibd文件大小)
shell> du -s  /var/lib/mysql/zabbix/* |sort -rn |head -10
847876	/var/lib/mysql/zabbix/history_uint.ibd
368644	/var/lib/mysql/zabbix/trends_uint.ibd
290820	/var/lib/mysql/zabbix/history.ibd
135172	/var/lib/mysql/zabbix/history_str.ibd
110596	/var/lib/mysql/zabbix/trends.ibd
57348	/var/lib/mysql/zabbix/history_text.ibd
18436	/var/lib/mysql/zabbix/alerts.ibd
14340	/var/lib/mysql/zabbix/events.ibd
14336	/var/lib/mysql/zabbix/items.ibd
10244	/var/lib/mysql/zabbix/event_recovery.ibd

2|44）数据库分区自动化脚本

【数据库分区脚本】

#数据库分区脚本
shell> wget https://dl.cactifans.com/zabbix/partitiontables_gt_zbx34.sh



#执行脚本即可（脚本配置无问题）
shell> ./partitiontables_gt_zbx34.sh




#查看分区后的表数据（history）
mysql> use zabbix;
mysql> select partition_name ,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema=schema() and table_name='history';

脚本基础应用配置：

#优化脚本（基础配置）
shell> egrep "^[^#]" partitiontables_gt_zbx34.sh 
EMAIL="zeny@foxmail.com"    #邮箱
daily_history_min=30        #历史数据存储保留时间
monthly_history_min=12      #趋势数据存储时间配置
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     #数据库连接地址
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';"
                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 "\nReady to update permissions of Zabbix user to create routines\n"
		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 "\n"
		echo -ne "\nDo you want to backup the database (recommended) (Y/n): "
		read yn
		if [ "$yn" != "n" -a "$yn" != "N" ]; then
			echo -e "\nEnter 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 "\n\nReady to proceed:"
	echo -e "\nStarting yearly partioning at: $first_year"
	echo "and ending at: $last_year"
	echo "With $daily_history_min days of daily history"
	echo -e "\n\nReady 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" 
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 "\n" >>$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 "\n" >>$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 "\n" >>$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 "\n" >>$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 "\n\nReady 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 "\nIf Zabbix Version = 2.0 \nDo 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 "\nDo 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\n"
			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 "\nEnter email of who should get the daily housekeeping reports: "
		read mailto
	fi
	[ "$mailto" = "" ] && mailto=$EMAIL
	mkdir -p $PATHTOCRON
	cat >$PATHTOCRON/housekeeping.sh <<_EOF_
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_
$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

__EOF__

本文作者：ଲ小何才露煎煎饺
本文链接：https://www.cnblogs.com/zeny/p/16656501.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   ଲ小何才露煎煎饺  阅读(246)  评论(0)  编辑  收藏  举报
相关博文：
· @zabbix web登录忘记密码（用户密码重置及反解）
· @Zabbix6.0 HA原生高可用集群部署方案（Ubuntu 20.04）
· ZABBIX之MySQL数据库历史表优化
· zabbix使用
· GreatSQL内存消耗异常排查攻略：从系统到应用层面的深入分析
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
点击右上角即可分享
微信分享提示