history＊ trends＊ Acknowledges Alerts Auditlog Events service_alarms

shell# vim /usr/sbin/zabbix_mysqldump.sh
#! /bin/bash
#author: itnihao
#mail: itnihao#qq.com
#license: apache 2.0
#from :https://github.com/zabbix-book/zabbix-mysql-backup
#usage: chmod 700 ${PATH}/zabbix_mysqldump.sh
#crontab: 0 3 ＊ ＊ ＊ ${PATH}/zabbix_mysqldump.sh mysqldump

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=zabbix                          #MySQL的用户
MySQL_PASSWORD=zabbix                     #MySQL的密码
MySQL_HOST=localhost                      #MySQL的服务器地址
MySQL_PORT=3306                            #MySQL的服务器端口
MySQL_DUMP_PATH=/mysql_backup             #MySQL的备份路径
MYSQL_BIN_PATH=/usr/bin/mysql             #mysql命令的路径
MYSQL_DUMP_BIN_PATH=/usr/bin/mysqldump    #mysqldump命令的路径
MySQL_DATABASE_NAME=zabbix                #库的名称
DATE=$（date '+%Y-%m-%d'）

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} -u${MySQL_USER} -p${MySQL_PASSWORD}  -h${MySQL_HOST} ${MySQL_DATABASE_NAME} -e \
    "show tables"|egrep -v "（Tables_in_zabbix|history＊|trends＊|acknowledges|alerts|auditlog|events|service_alarms）"）
    for TABLE_NAME in ${TABLE_NAME_ALL}
    do
        ${MYSQL_DUMP_BIN_PATH} --opt -u${MySQL_USER} -p${MySQL_PASSWORD}-P${MySQL_PORT} -h${MySQL_HOST} \
        ${MySQL_DATABASE_NAME} ${TABLE_NAME} ＞${TABLE_NAME}.sql
        sleep 0.01
    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='5 days ago'）
    exit 0
}

MySQLImport（）{
    cd ${MySQL_DUMP_PATH}
    DATE=$（ls  ${MySQL_DUMP_PATH} |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} -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

shell# bash /usr/sbin/zabbix_mysqldump.sh mysqldump    #备份数据
shell# bash /usr/sbin/zabbix_mysqldump.sh mysqlimport  #恢复数据