#!/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=zabbix

MySQL_PASSWORD=zabbix #修改对应的密码

MySQL_HOST=localhost

MySQL_PORT=3306

MySQL_DUMP_PATH=/home/zabbix-datafile-backup

MYSQL_BIN_PATH=/usr/bin/mysql

MYSQL_DUMP_BIN_PATH=/usr/bin/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)")

    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}/

    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]+$\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}" == ""  ];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

[root@Zabbix-Server /]# sh zabbix-datafile-bak.sh mysqldump

[root@Zabbix-Server /]# ls

bin   dev  home  lib64  mnt  proc  run   srv  tmp  var                 zabbix-datafile-backup

boot  etc  lib   media  opt  root  sbin  sys  usr  zabbix-data-backup  zabbix-datafile-bak.sh

[root@Zabbix-Server /]# cd zabbix-datafile-backup/

[root@Zabbix-Server zabbix-datafile-backup]# ls

  logs

[root@Zabbix-Server zabbix-datafile-backup]# cd /

[root@Zabbix-Server ]# ls

actions.sql                graphs.sql           interface_discovery.sql         opinventory.sql         slideshow_usrgrp.sql

application_discovery.sql  graph_theme.sql      interface.sql                   opmessage_grp.sql       slides.sql

application_prototype.sql  group_discovery.sql  item_application_prototype.sql  opmessage.sql           sysmap_element_url.sql

applications.sql           group_prototype.sql  item_condition.sql              opmessage_usr.sql       sysmaps_elements.sql

application_template.sql   groups.sql           item_discovery.sql              optemplate.sql          sysmaps_links.sql

autoreg_host.sql           host_discovery.sql   items_applications.sql          profiles.sql            sysmaps_link_triggers.sql

conditions.sql             host_inventory.sql   items.sql                       proxy_autoreg_host.sql  sysmaps.sql

config.sql                 hostmacro.sql        maintenances_groups.sql         regexps.sql             sysmap_url.sql

dbversion.sql              hosts_groups.sql     maintenances_hosts.sql          rights.sql              sysmap_user.sql

dchecks.sql                hosts.sql            maintenances.sql                screens_items.sql       sysmap_usrgrp.sql

dhosts.sql                 hosts_templates.sql  maintenances_windows.sql        screens.sql             timeperiods.sql

drules.sql                 housekeeper.sql      mappings.sql                    screen_user.sql         trigger_depends.sql

dservices.sql              httpstepitem.sql     media.sql                       screen_usrgrp.sql       trigger_discovery.sql

escalations.sql            httpstep.sql         media_type.sql                  scripts.sql             triggers.sql

expressions.sql            httptestitem.sql     opcommand_grp.sql               services_links.sql      users_groups.sql

functions.sql              httptest.sql         opcommand_hst.sql               services.sql            users.sql

globalmacro.sql            icon_mapping.sql     opcommand.sql                   services_times.sql      usrgrp.sql

globalvars.sql             icon_map.sql         opconditions.sql                sessions.sql            valuemaps.sql

graph_discovery.sql        ids.sql              operations.sql                  slideshows.sql

graphs_items.sql           images.sql           opgroup.sql                     slideshow_user.sql

[root@Zabbix-Server zabbix]# crontab -l|tail -

#Q--/

  * * * /etc/zabbix/zabbix-datafile-bak.sh mysqldump

/sbin/service crond restart