[root@localhost ~]# chmod +x partitiontables_gt_zbx34.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=12
#
# 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 /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 "\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"
#"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 "\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
QQ群⑧：
窥视卡
雷达卡
发表于 2019-1-20 09:30:58
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡