#!/bin/bash
# 参考: https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql
# 功能: 放入定时任务,为zabbix创建和删除分区
MY_HOST="数据库服务器"
MY_USER="数据库用户名"
MY_PASS="数据库密码"
MY_DB="数据库库名"
# 创建分区时的最大天数
MAX_DAYS=14
# 删除分区时保留的天数
MIN_DAYS=7
function MySQLStatQuery() {
if [ -z "$1" ]; then
echo "ERROR: [MySQLStatQuery] sql empty."
else
mysql -s --skip-column-names -h${MY_HOST} -u${MY_USER} -p${MY_PASS} ${MY_DB} -e "$1"
fi
}
function CreatePartition() {
local schemaName="$1"
local tableName="$2"
local partitionName="$3"
local clock="$4"
local sql="SELECT count(1) FROM information_schema.partitions WHERE table_schema = '${schemaName}' AND table_name = '${tableName}' AND partition_name = '${partitionName}';"
local partitionExist=$(MySQLStatQuery "$sql")
if [ $partitionExist -eq 0 ]; then
echo "INFO: [CreatePartition] schema_name='$schemaName' table_name='$tableName' partition_name='$partitionName' clock='$clock'"
sql="ALTER TABLE ${schemaName}.${tableName} ADD PARTITION ( PARTITION ${partitionName} VALUES LESS THAN (UNIX_TIMESTAMP('${clock}')));"
MySQLStatQuery "$sql"
fi
}
function DropPartition() {
local schemaName="$1"
local tableName="$2"
local partitionName="$3"
local sql="SELECT count(1) FROM information_schema.partitions WHERE table_schema = '${schemaName}' AND table_name = '${tableName}' AND partition_name = '${partitionName}';"
local partitionExist=$(MySQLStatQuery "$sql")
if [ $partitionExist -eq 1 ]; then
echo "INFO: [DropPartition] schema_name='$schemaName' table_name='$tableName' partition_name='$partitionName'"
sql="ALTER TABLE ${schemaName}.${tableName} DROP PARTITION ${partitionName};"
MySQLStatQuery "$sql"
fi
}
function CreateNextPartition() {
local schemaName="$1"
local tableName="$2"
local nextClock=""
local partitionName=""
local clock=""
local i=1
while true; do
nextClock=$(date +%Y%m%d --date "$i days")
partitionName="p$nextClock"
i=$(expr $i + 1)
clock="$(date '+%Y-%m-%d 00:00:00' --date "$i days")"
CreatePartition "$schemaName" "$tableName" "$partitionName" "$clock"
[ $i -gt $MAX_DAYS ] && break
done
}
function DropOldPartition() {
local schemaName="$1"
local tableName="$2"
local oldClock=""
local partitionName=""
local i=$MAX_DAYS
while true; do
oldClock=$(date +%Y%m%d --date "-$i days")
partitionName="p$oldClock"
DropPartition "$schemaName" "$tableName" "$partitionName"
i=$(expr $i - 1)
[ $i -lt $MIN_DAYS ] && break
done
}
function main() {
# 需要分区的表,库名默认为zabbix
local tables=(history history_log history_str history_text history_uint)
local tableName=""
for tableName in ${tables[@]}; do
CreateNextPartition 'zabbix' "$tableName"
done
for tableName in ${tables[@]}; do
DropOldPartition 'zabbix' "$tableName"
done
}
main $*
存储过程改成了脚本,方便维护和增功能。