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

#!/bin/bash 
                
scriptsDir=/opt/shells 
mysqlDir=/usr/local/mysql 
user=root 
userPWD= 
dataBackupDir=/data/mysqldump 
eMailFile=$dataBackupDir/email.txt 
eMail=httpd4@test.com 
logFile=$dataBackupDir/mysqlbackup.log 
DATE=`date "+%Y%m%d"` 
echo "" > $eMailFile 
echo $(date +"%y%m%d %H:%M:%S") >> $eMailFile 
cd $dataBackupDir 
dumpFile=test-all$DATE.sql 
                
GZDumpFile=test-all$DATE.sql.tar.gz 
cd $dataBackupDir 
                
$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 
                
                
if [[ $? == 0 ]]; then 
                
  gzip $dumpFile >> $eMailFile 2>&1 
                
  echo "BackupFileName:$GZDumpFile" >> $eMailFile 
                
  echo "DataBase Backup Success!" >> $eMailFile 
fi 
                
                
                
#  cd $dataBackupDir/daily 
                
#  rm -f * 
                
                
find /data/mysqldump -name "test*.gz" -mtime +10 -type f | xargs rm -rf 
                
                
                
echo "--------------------------------------------------------" >> $logFile 
                
cat $eMailFile >> $logFile 
                
                
cat $eMailFile | mail -s "MySQL Full Backup" $eMail

上面是全备份

下面是每天备份

#!/bin/bash 
scriptsDir=/opt/shells 
mysqlDir=/usr/local/mysql 
dataDir=/data/mysql 
user=root
userPWD= 
dataBackupDir=/data/mysqldump 
dailyBackupDir=$dataBackupDir/daily 
eMailFile=$dataBackupDir/email.txt 
eMail=service@test.com 
logFile=$dataBackupDir/mysqlbackup.log 
HOSTNAME=`uname -n` 
echo "" > $eMailFile 
echo $(date +"%y%m%d %H:%M:%S") >> $eMailFile 
$mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logs 
cd $dataDir 
fileList=`cat mysql-bin.index` 
iCounter=0 
for file in $fileList 
do 
  iCounter=`expr $iCounter + 1` 
done 
nextNum=0 
iFile=0 
for file in $fileList 
do 
  binLogName=`basename $file` 
  nextNum=`expr $nextNum + 1` 
# 跳过最后一个二进制日志(数据库当前使用的二进制日志文件) 
  if [[ $nextNum == $iCounter ]]; then 
    echo "Skip lastest!" > /dev/null 
  else 
    dest=$dailyBackupDir/$binLogName 
# 跳过已经备份的二进制日志文件 
    if [[ -e $dest ]]; then 
      echo "Skip exist $binLogName!" > /dev/null 
    else 
# 备份日志文件到备份目录 
      rsync -a $binLogName $dailyBackupDir 
      if [[ $? == 0 ]]; then 
        iFile=`expr $iFile + 1` 
        echo "$binLogName Backup Success!" >> $eMailFile 
      fi 
    fi 
  fi 
done 
chmod -R +r $dailyBackupDir 
if [[ $iFile == 0 ]];then 
  echo "No Binlog Backup!" >> $eMailFile 
else 
  echo "Backup $iFile File(s)." >> $eMailFile 
  echo "Backup MySQL Binlog OK!" >> $eMailFile 
fi 
cat $eMailFile | mail -s "MySQL Daily Backup" $eMail 
echo "--------------------------------------------------------" >> $logFile 
cat $eMailFile >> $logFile


知识共享许可协议
《mysql 定时增量备份和全备脚本》常伟华 创作。
采用 知识共享 署名-相同方式共享 3.0 中国大陆 许可协议进行许可。
  • 多说评论
  • 签名
  • 新浪微博
  • 默认评论
  • Tab Header 5

0 条评论 / 点击此处发表评论

Tab Content 5

开发技术


开发平台和工具

sitemap     169.23ms