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
