Clean old data from Zabbix database

clean_zabbix_db.sh

#!/usr/bin/env bash

# Inspired by http://whatizee.blogspot.com/2016/10/zabbix-history-table-clean-up.html
# This scrip clean history* and trends* tables. It will copy "right" data to new tables
# and then drop old bloated ones.

# Enable debug
#set -x

DATE_MINUS_3M=$(date -d "now -3months" +%Y-%m-%d)
DATE_MINUS_1Y=$(date -d "now -1year" +%Y-%m-%d)
CURRENT_DATE=$(date -d "now" +%Y-%m-%d)

EPOCH_DATE_MINUS_3M=$(date -d "$DATE_MINUS_3M" +%s)
EPOCH_DATE_MINUS_1Y=$(date -d "$DATE_MINUS_1Y" +%s)
EPOCH_NOW=$(date -d "$CURRENT_DATE" +%s)

ZABBIX_DATABASE="zabbix"
ZABBIX_USER="zabbix"
ZABBIX_PASSWD="zabbix"

MYSQL_OPTS_FILE="$(mktemp $HOME/.mysqlopts.XXXX)"
MYSQL_OPTS="[client]
password=$ZABBIX_PASSWD"
echo "$MYSQL_OPTS" > "$MYSQL_OPTS_FILE"
chmod 600 "$MYSQL_OPTS_FILE"
# remove mysql conf file on Ctrl-C and some other signals
trap "rm -f '$MYSQL_OPTS_FILE'" SIGINT SIGHUP SIGTERM

HISTORY_BACKUP_PATH="./zabbix_history"
TRENDS_BACKUP_PATH="./zabbix_trends"

# Edit table list as necessary
HISTORY_TABLES="history_str
history_text
history
history_uint"

TRENDS_TABLES="trends
trends_uint"

echo "------------------------------------------
Keep history until date: $DATE_MINUS_3M
Keep history until: $EPOCH_DATE_MINUS_3M
Keep trends until date: $DATE_MINUS_1Y
Keep trendsuntil: $EPOCH_DATE_MINUS_1Y
Today's Date : $CURRENT_DATE
Epoch For Today's Date : $EPOCH_NOW
-------------------------------------"

echo "- Stopping Zabbix Server"
service zabbix-server stop
sleep 1
echo "##########################################"

make_clean(){
	TABLES=$1
	EPOCH_DATE_IN_PAST=$2
	BACKUP_FILE_PATH=$3
	test -d "${BACKUP_FILE_PATH}" || mkdir "${BACKUP_FILE_PATH}"

	while read -r CURRENT_TABLE; do
		echo "- Backing up '${CURRENT_TABLE}' Table. Location: ${BACKUP_FILE_PATH}/${CURRENT_TABLE}.sql"
		mysqldump --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE ${CURRENT_TABLE} >\
		"${BACKUP_FILE_PATH}/${CURRENT_TABLE}.sql"
		sleep 1

		echo "- Create Temp (${CURRENT_TABLE}_${EPOCH_NOW}) Table"
		mysql --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE \
		-e "CREATE TABLE ${CURRENT_TABLE}_${EPOCH_NOW} LIKE ${CURRENT_TABLE};"
		sleep 1

		echo "- Inserting from '${CURRENT_TABLE}' Table to Temp (${CURRENT_TABLE}_${EPOCH_NOW}) Table"
		mysql --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE \
		-e "INSERT INTO ${CURRENT_TABLE}_${EPOCH_NOW} SELECT * FROM ${CURRENT_TABLE} WHERE clock > '${EPOCH_DATE_IN_PAST}' AND itemid IN (SELECT itemid FROM items);"
		sleep 1

		echo "- Rename Table '${CURRENT_TABLE}' to ${CURRENT_TABLE}_${EPOCH_NOW}_old"
		mysql --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE \
		-e "ALTER TABLE ${CURRENT_TABLE} RENAME ${CURRENT_TABLE}_${EPOCH_NOW}_old;"
		sleep 1

		echo "- Rename Temp Table (${CURRENT_TABLE}_${EPOCH_NOW}) to Original Table (${CURRENT_TABLE})"
		mysql --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE \
		-e "ALTER TABLE ${CURRENT_TABLE}_${EPOCH_NOW} RENAME ${CURRENT_TABLE};"
		sleep 1

		echo "- Dropping Old Table (${CURRENT_TABLE}_${EPOCH_NOW}_old), As we have already Backed it up."
		mysql --defaults-file=$MYSQL_OPTS_FILE -u$ZABBIX_USER $ZABBIX_DATABASE \
		-e "DROP TABLE ${CURRENT_TABLE}_${EPOCH_NOW}_old;"
		sleep 1
		echo "##########################################"
	done <<< "$TABLES"
}

# clean trends
make_clean "$TRENDS_TABLES" "$EPOCH_DATE_MINUS_1Y" "$TRENDS_BACKUP_PATH"

# clean history
make_clean "$HISTORY_TABLES" "$EPOCH_DATE_MINUS_3M" "$HISTORY_BACKUP_PATH"

rm -f "$MYSQL_OPTS_FILE"

echo "- Starting Zabbix Server"
service zabbix-server start

# Disable debug
#set +x