mysql自带的工具mysqldump，当数据量大了之后进行全备所花的时间比较长，这样将会造成数据库的锁读、从而zabbix服务的监控告警不断，想着做下配置文件的备份、刚好有这么个脚本、满足了需求。
后续会使用xtrabckup来进行完成备份，执行这个脚本所花时间不过几秒
#!/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}" == "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
执行步骤如下
[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 20180109 logs [root@Zabbix-Server zabbix-datafile-backup]# cd 20180109/ [root@Zabbix-Server 20180109]# 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 -2 #Q-2018-1/9 00 6 * * * /etc/zabbix/zabbix-datafile-bak.sh mysqldump
重启下计划任务服务
/sbin/service crond restart