MySQL backup script (including zabbix database)

Requirements: backup by day, keep for a week, zabbix database does not back up historical data

MySQL version: 5.7.17

Script:

It is necessary to establish the path mkdir-pv / opt/mysql_backup/auto_backupcat / optUniverse backupUniverse mysqlback.shacks in advance. Dev/nullDBUSER=rootDBPASS=*DATE= Backpath% 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 {}\

Note: for the above script, the zabbix database is brought out separately. You only need to back up the configuration table, but do not need to back up the historical data. If there is no zabbix database on the mysql, you can comment out a large section, and you need to set a keyless login for remote backup, otherwise the scp process needs to manually enter a password, resulting in waiting for execution; or use expect to capture and automatically add a password (you need to install expect first).

[lyj@lyj.com mysql_backup] $pwd/opt/mysql_ backup [Lyj @ lyj.com mysql_backup] $tree. ├── auto_backup │ └── server5_2017-03-06.tar.gz # the file name format after backup is "hostname _ date .tar.gz" ├── mysql_backup.sh └── readme1 directory, 3 files