MySQL full backup script: Data + binary log + BACKUP log

I. Scripting notes

1.mysql data files and binary log files are best saved on different partitions or storage devices

2. After the backup is complete, take care to modify the data permissions to prevent the disclosure of important information, which hosts which users can use to recover

3. View the exported 2 log file to see if it is logical and business, 2 binary log files can only guarantee the physical data point-in-time restore, but does not guarantee the data logically on the correct recovery (such as drop statement)

4. To ensure that data recovery is not a problem recommend a data recovery test on a test machine

5. Recovery test OK can be put into crontab scheduled task

1 #!/bin/bash2 #3 # Function: Implement MySQL full backup4 # Notes:5 6 # define the relevant variables used by the backup (need to make corresponding changes according to the user's actual environment)7 Backup_data=/backup/mysql/full8 Backup_binlog=/backup/mysql/logbin9 Mysql_binlog=/mydata/log-binTen mysql_bin_path= ' ps-ef | grep-e "mysqld[[:space:]]+" | awk-f "' {print $8} ' | sed-r ' [email protected][^/]+/[em Ail protected]@ ' One Mysql_user=root A MYSQL_PASSWORD=MYSQLDBA - Logfile=/var/log/mysqldump.log -  the # function Definition -  - ########################[mysql Health Test]###################### - Health_check () { + if Pgrep mysqld &>/dev/null;then - mysqld_pid= ' pgrep-l mysqld | grep-e "mysqld\>" | awk ' {print '} ' + echo "$ (date +"%y-%m-%d%h:%m:%s ") $mysqld _pid [Note] Server Mysql is Running ..." >> $logfile A Else at service mysqld start &>/dev/null | | echo "$ (date +"%y-%m-%d%h:%m:%s ") [Warning] Server MySQL is not Runni Ng, Backup Failed ">> $logfile && return 5 - fi - } -  - #######################[mysqldump Full backup]################### - Full_backup () { in local backup_name=full-' Date +%f-%t '. SQL - [!-D $backup _data] && mkdir-p $backup _data to ${mysql_bin_path}mysqldump-u$mysql_user-p$mysql_password--all-databases--lock-all-tables--flush-logs-- master-data=2 + --triggers--routines--events--set-gtid-purged=off-r ${backup_data}/$backup _name &>/dev/null - if Grep-q "Change MASTER to" ${backup_data}/$backup _name; the return 0 * Else $ return 5Panax Notoginseng fi - } the  + ########################[mysql binary log backup]##################### A Bin_backup () { the [!-D $backup _binlog] && mkdir-p $backup _binlog + CD $mysql _binlog - tar-jcf bin-' date + '%f-%h '%M '%s '. tar.bz2 * &>/dev/null $ \mv-f bin*.tar.bz2 $backup _binlog $ } -  - # main Function main the  - Health_checkWuyi if [$?-eq 0];then the echo "$ (date +"%y-%m-%d%h:%m:%s ") [Note] MySQL full backup start ..." >> $logfile - Else Wu echo "$ (date +"%y-%m-%d%h:%m:%s ") [Warning] Server MySQL is not running, Backup failed" | mail-s "MySQL backup I S failed "[email protected] ' hostname ' - fi About Full_backup $ if [$?-eq 0];then - echo "$ (date +"%y-%m-%d%h:%m:%s ") [Note] MySQL full backup is finished" >> $logfile - chmod-r $backup _data - Else A echo "$ (date +"%y-%m-%d%h:%m:%s ") [Warning] Function full_backup () execution failed, backup was interrupted" | ca T | Tee-a $logfile | Mail-s "MySQL backup is failed" [email protected] ' hostname ' + fi the echo "$ (date +"%y-%m-%d%h:%m:%s ") [Note] MySQL binary log file backup is started ..." >> $logfile - Bin_backup $ if [$?-eq 0];then the echo "$ (date +"%y-%m-%d%h:%m:%s ") [Note] MySQL binary log file backup is finished" >> $logfile the chmod-r $backup _binlog the Else the echo "$ (date +"%y-%m-%d%h:%m:%s ") [Warning] Mysql binary log file backup is not completed" | cat | tee-a $LOGFIL e | Mail-s "MySQL binary log backup failed" [email protected] ' hostname ' -Fi

Two. Example

View backup data and logs after the script executes

　

Script execution Process tracking

Join the crontab Task

　

MySQL full backup script: Data + binary log + BACKUP log