mysql 定时增量备份和全备脚本

View Code

 1 #!/bin/bash  
 2   
 3 scriptsDir=/opt/shells  
 4 mysqlDir=/usr/local/mysql  
 5 user=root  
 6 userPWD=  
 7 dataBackupDir=/data/mysqldump  
 8 eMailFile=$dataBackupDir/email.txt  
 9 eMail=httpd4@test.com  
10 logFile=$dataBackupDir/mysqlbackup.log  
11 DATE=`date "+%Y%m%d"`  
12 echo "" > $eMailFile  
13 echo $(date +"%y%m%d %H:%M:%S") >> $eMailFile  
14 cd $dataBackupDir  
15 dumpFile=test-all$DATE.sql  
16   
17 GZDumpFile=test-all$DATE.sql.tar.gz  
18 cd $dataBackupDir  
19   
20 $mysqlDir/bin/mysqldump -u$user -p$userPWD --skip-opt --master-data=2 --flush-logs --single-transaction --add-drop-table --create-options --quick --extended-insert=true --default-character-set=utf8 --disable-keys --triggers -R --all-databases --ignore-table=performance_schema.cond_instances --ignore-table=performance_schema.events_waits_current --ignore-table=performance_schema.cond_instances --ignore-table=performance_schema.events_waits_history --ignore-table=performance_schema.events_waits_history_long  --ignore-table=performance_schema.events_waits_summary_by_instance --ignore-table=performance_schema.events_waits_summary_by_thread_by_event_name --ignore-table=performance_schema.events_waits_summary_global_by_event_name --ignore-table=performance_schema.file_instances --ignore-table=performance_schema.file_summary_by_event_name  --ignore-table=performance_schema.file_summary_by_instance  --ignore-table=performance_schema.mutex_instances --ignore-table=performance_schema.performance_timers  --ignore-table=performance_schema.rwlock_instances --ignore-table=performance_schema.setup_consumers --ignore-table=performance_schema.setup_instruments --ignore-table=performance_schema.setup_timers --ignore-table=performance_schema.threads > $dumpFile  
21   
22   
23 if [[ $? == 0 ]]; then  
24   
25   gzip $dumpFile >> $eMailFile 2>&1  
26   
27   echo "BackupFileName:$GZDumpFile" >> $eMailFile  
28   
29   echo "DataBase Backup Success!" >> $eMailFile  
30 fi  
31   
32   
33   
34 #  cd $dataBackupDir/daily  
35   
36 #  rm -f *  
37   
38   
39 find /data/mysqldump -name "test*.gz" -mtime +10 -type f | xargs rm -rf  
40   
41   
42   
43 echo "--------------------------------------------------------" >> $logFile  
44   
45 cat $eMailFile >> $logFile  
46   
47   
48 cat $eMailFile | mail -s "MySQL Full Backup" $eMail 

上面是全备份

下面是每天备份

View Code

 1     #!/bin/bash  
 2     scriptsDir=/opt/shells  
 3     mysqlDir=/usr/local/mysql  
 4     dataDir=/data/mysql  
 5     user=root 
 6     userPWD=  
 7     dataBackupDir=/data/mysqldump  
 8     dailyBackupDir=$dataBackupDir/daily  
 9     eMailFile=$dataBackupDir/email.txt  
10     eMail=service@test.com  
11     logFile=$dataBackupDir/mysqlbackup.log  
12     HOSTNAME=`uname -n`  
13     echo "" > $eMailFile  
14     echo $(date +"%y%m%d %H:%M:%S") >> $eMailFile  
15     $mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logs  
16     cd $dataDir  
17     fileList=`cat mysql-bin.index`  
18     iCounter=0  
19     for file in $fileList  
20     do  
21       iCounter=`expr $iCounter + 1`  
22     done  
23     nextNum=0  
24     iFile=0  
25     for file in $fileList  
26     do  
27       binLogName=`basename $file`  
28       nextNum=`expr $nextNum + 1`  
29     # 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）  
30       if [[ $nextNum == $iCounter ]]; then  
31         echo "Skip lastest!" > /dev/null  
32       else  
33         dest=$dailyBackupDir/$binLogName  
34     # 跳过已经备份的二进制日志文件  
35         if [[ -e $dest ]]; then  
36           echo "Skip exist $binLogName!" > /dev/null  
37         else  
38     # 备份日志文件到备份目录  
39           rsync -a $binLogName $dailyBackupDir  
40           if [[ $? == 0 ]]; then  
41             iFile=`expr $iFile + 1`  
42             echo "$binLogName Backup Success!" >> $eMailFile  
43           fi  
44         fi  
45       fi  
46     done  
47     chmod -R +r $dailyBackupDir  
48     if [[ $iFile == 0 ]];then  
49       echo "No Binlog Backup!" >> $eMailFile  
50     else  
51       echo "Backup $iFile File(s)." >> $eMailFile  
52       echo "Backup MySQL Binlog OK!" >> $eMailFile  
53     fi  
54     cat $eMailFile | mail -s "MySQL Daily Backup" $eMail  
55     echo "--------------------------------------------------------" >> $logFile  
56     cat $eMailFile >> $logFile  

 

转载于:https://www.cnblogs.com/batcom/archive/2013/03/29/2988492.html