zabbix 监控数据备份，history，trends等表按天导出压缩备份

#!/bin/bash

red='\e[0;31m' # 红色
RED='\e[1;31m'
green='\e[0;32m' # 绿色
GREEN='\e[1;32m'
blue='\e[0;34m' # 蓝色
BLUE='\e[1;34m'
purple='\e[0;35m' # 紫色
PURPLE='\e[1;35m'
NC='\e[0m' # 没有颜色

source /etc/bashrc
source /etc/profile

MySQL_USER=XXX
MySQL_PASSWORD=XXX
MySQL_HOST=localhost
MySQL_PORT=3306
MySQL_DUMP_PATH=/mysql_backup
MYSQL_DUMP_PATH2=/root/old_134.sql
MYSQL_BIN_PATH=/home/mysql-8.0.25/bin/mysql
MYSQL_DUMP_BIN_PATH=/home/mysql-8.0.25/bin/mysqldump
MySQL_DATABASE_NAME=zabbix
SOCKET=/home/mysql-8.0.25/mysql.sock
DATE=$(date '+%Y-%m-%d')
YESTERDAY=$(date +%Y-%m-%d --date='1 days ago')
dumpdata () {
     ${MYSQL_DUMP_BIN_PATH} --socket=${SOCKET} --opt -u${MySQL_USER} -p${MySQL_PASSWORD} -P${MySQL_PORT} -h${MySQL_HOST} -d  ${MySQL_DATABASE_NAME} $1 >${TABLE_NAME}.sql
     ${MYSQL_DUMP_BIN_PATH} --socket=${SOCKET} --opt -u${MySQL_USER} -p${MySQL_PASSWORD} -P${MySQL_PORT} -h${MySQL_HOST} -t  ${MySQL_DATABASE_NAME} $1 --where=" clock>=UNIX_TIMESTAMP(\"$YESTERDAY\") and clock<UNIX_TIMESTAMP(\"$DATE\")"> /${MySQL_DUMP_PATH}/NotImportantDataForEveryDay/${TABLE_NAME}.${YESTERDAY}.sql

}


MySQLDUMP () {
    [ -d ${MySQL_DUMP_PATH} ] || mkdir ${MySQL_DUMP_PATH}
    cd ${MySQL_DUMP_PATH}
    [ -d logs    ] || mkdir logs
    [ -d ${DATE} ] || mkdir ${DATE}
    cd ${DATE}

    TABLE_NAME_ALL=$(${MYSQL_BIN_PATH} --socket=${SOCKET} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME} -e \
    "show tables"|egrep -v "(Tables_in_zabbix)")
    echo ${TABLE_NAME_ALL}
    for TABLE_NAME in ${TABLE_NAME_ALL}
    do
        if [ "$TABLE_NAME" = "history"  ] || [ "$TABLE_NAME" = "history_log"  ] || [ "$TABLE_NAME" = "history_text"  ] \
        || [ "$TABLE_NAME" = "history_str"  ] || [ "$TABLE_NAME" = "history_uint"  ] ||  [ "$TABLE_NAME" = "acknowledges"  ] \
        ||  [ "$TABLE_NAME" = "alerts"  ] ||[ "$TABLE_NAME" = "auditlog"  ] ||[ "$TABLE_NAME" = "events"  ] \
        ||[ "$TABLE_NAME" = "service_alarms"  ] || [ "$TABLE_NAME" = "trends"  ] ||[ "$TABLE_NAME" = "trends_uint"  ]
        then
            echo "IT'S $TABLE_NAME"
            dumpdata $TABLE_NAME
        else
        ${MYSQL_DUMP_BIN_PATH} --socket=${SOCKET} --opt -u${MySQL_USER} -p${MySQL_PASSWORD} -P${MySQL_PORT} -h${MySQL_HOST} \
        ${MySQL_DATABASE_NAME} ${TABLE_NAME} >${TABLE_NAME}.sql
        sleep 0.01
        fi
    done

    [ "$?" == 0 ] && echo "${DATE}: Backup zabbix succeed"     >> ${MySQL_DUMP_PATH}/logs/ZabbixMysqlDump.log
    [ "$?" != 0 ] && echo "${DATE}: Backup zabbix not succeed" >> ${MySQL_DUMP_PATH}/logs/ZabbixMysqlDump.log

    cd ${MySQL_DUMP_PATH}/
    [ "$?" == 0 ] && rm -rf $(date +%Y-%m-%d --date='180 days ago')
    cd ${MySQL_DUMP_PATH}/NotImportantDataForEveryDay/
    tar -zcvf NotImportantDataForEvery.${YESTERDAY}.tar.gz *.${YESTERDAY}.sql
    rm -rf *.${YESTERDAY}.sql

    exit 0
}

##import功能未作修改，因本脚本中history等数据表只含表结构，慎用
MySQLImport () {
    cd ${MySQL_DUMP_PATH2}
    DATE=$(ls  ${MySQL_DUMP_PATH2} |egrep "\b^[0-9]+-[0-9]+-[0-9]+$\b")
    echo -e "${green}${DATE}"
    echo -e "${blue}what DATE do you want to import,please input date:${NC}"
    read SELECT_DATE
    if [ -d "${SELECT_DATE}" ];then
        echo -e "you select is ${green}${SELECT_DATE}${NC}, do you want to contine,if,input ${red}(yes|y|Y)${NC},\
        else then exit"
        read Input
        [[ 'yes|y|Y' =~ "${Input}" ]]
        status="$?"
        if [ "${status}" == "0"  ];then
            echo "now import SQL....... Please wait......."
        else
            exit 1
        fi
        cd ${SELECT_DATE}
        for PER_TABEL_SQL in $(ls *.sql)
        do
           ${MYSQL_BIN_PATH} --socket=${SOCKET} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME} < ${PER_TABEL_SQL}
           echo -e "import ${PER_TABEL_SQL} ${PURPLE}........................${NC}"
        done
        echo "Finish import SQL,Please check Zabbix database"
    else
        echo "Don't exist ${SELECT_DATE} DIR"
    fi
}

case "$1" in
MySQLDUMP|mysqldump)
    MySQLDUMP
    ;;
MySQLImport|mysqlimport)
    MySQLImport
    ;;
*)
    echo "Usage: $0 {(MySQLDUMP|mysqldump) (MySQLImport|mysqlimport)}"
    ;;
esac

mysqldump：

dumpdata () 导出指定表的前一天数据，history，trends表数据，按天存放在NotImportantDataForEveryDay目录里

mysqldump()将history等表导出表结构与配置表放一起；

最后删除多余的sql文件