MySQL backup script (contains Zabbix database)

Requirements: Backup by day, keep for one week, Zabbix database does not back up historical data

MySQL version: 5.7.17

Script:

Need to establish the path in advance mkdir-pv/opt/mysql_backup/auto_backupcat/opt/mysql_backup/mysqlback.sh

#!/bin/bash# 0 3 * * * bash /opt/mysql_backup/mysqlback.sh &>/ Dev/nulldbuser=rootdbpass=**********date= ' date +%y-%m-%d ' backpath= "/opt/mysql_backup/auto_backup" HOSTNAME = '/bin/hostname ' mysql_back_dir= "$BACKPATH/${hostname}_$date" mysqldump=/usr/bin/mysqldumpmysql=/usr/bin/ mysqltar_dir= $DATETAR =/bin/tarrm=/bin/rmfind=/bin/finddstip=192.168.100.131#if no directory  then create the directoryif [ ! -d  $MYSQL _back_dir ];then         mkdir -p  $MYSQL _back_dirfi#delete 7 day ago  file#lock tables# $MYSQL  -u$DBUSER -p$DBPASS -h$DBHOST -e  "stop slave;" # $MYSQL  -u$DBUSER -p$DBPASS -h$DBHOST -e  "flush tables with read  lock; " #list  mysql databases and dump databasefor i in $ ($MYSQL  -u$ dbuser -p$dbpass -e  "Show databases\g" |grep database|grep -v schema|grep -v zabbix|grep  -v sys|awk  ' {print $2} ') do         cd $ mysql_back_dir                 $MYSQLDUMP  -u$dbuser -p$dbpass --master-data=2 --flush-logs  --lock-all-tables  --opt --default-character-set=utf8 --extended-insert=false --triggers -r -- hex-blob --events    $i  >  $i. sql#       $ mysqldump -u$dbuser -p$dbpass -h$dbhost --single-transaction --master-data=2  $i  |gzip >  $i .sqldone#backup zabbix configuration files.cd  $MYSQL _back_dir   $MYSQLDUMP  -u$dbuser -p$dbpass --master-data=2 --flush-logs  -- lock-all-tables --opt --Default-character-set=utf8 --extended-insert=false --triggers -r --hex-blob --events   zabbix  --ignore-table=zabbix.alerts --ignore-table=zabbix.auditlog -- Ignore-table=zabbix.auditlog_details --ignore-table=zabbix.escalations --ignore-table=zabbix.events  --ignore-table=zabbix.history --ignore-table=zabbix.history_log --ignore-table=zabbix.history_ str --ignore-table=zabbix.history_str_sync --ignore-table=zabbix.history_sync --ignore-table= zabbix.history_text --ignore-table=zabbix.history_uint --ignore-table=zabbix.history_uint_sync  --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint > zabbix.sql#unlock  tables#tar backfile and rm oldfilecd  $BACKPATH $tar czf  $MYSQL _back_ dir.tar.gz ${hostname}_${date} > /dev/null 2>&1scp  $MYSQL _back_dir.tar.gz   $DSTIP:/opt/mysql_backup/auto_Backup$rm -rf ${hostname}_${date} $FIND   $BACKPATH  -mtime +7 -exec  $RM  - rf {} \;

Description: The above script, Zabbix database alone to carry out, only need to back up the configuration table, do not need to back up historical data, if there is no Zabbix database on MySQL, that a large segment can be commented out, offsite backup needs to set the key-free login, otherwise the SCP process need to manually enter the password, causing the execution wait , or use expect for snapping, to automatically add a password (you need to install expect first) process slightly.

[Email protected] mysql_backup]$ pwd/opt/mysql_backup[[email protected] mysql_backup]$ Tree.├──auto_backup│└──serve r5_2017-03-06.tar.gz #备份后的文件名称格式为 "hostname _ date. tar.gz" ├──mysql_backup.sh└──readme1 directory, 3 files

This article is from the "small operation of the attack" blog, please be sure to keep this source http://liyongjian5179.blog.51cto.com/10134786/1903656

MySQL backup script (contains Zabbix database)