自动备份MySQL的binlog

mysql -h$dbhost -u$username -p$password -P$port -e 'show binary logs;
复制

#!/bin/bash
dbhost="rm-test.mysql.rds.aliyuncs.com"
username="root"
password="rootpw"
port="3306"
backupDir="/backup/dblog/Binlog"


binlogs=`mysql -h$dbhost -u$username -p$password -P$port -e 'show binary logs;' | awk  '$1 ~ mysql-bin.*/ {print $1}'`


num=0
for binlog in $binlogs;
  do
  num=`expr $num + 1`
  echo "数据库当前的binlog:"$binlog
  done


# 此处获取到binlog的备份文件，过滤掉路径，筛选出文件名
existlogs=`ls -lh $backupDir/mysql-bin.* | awk '{print $9}' | awk -F'/' '{print $5}'`
for existlog in $existlogs;
  do
  echo "当前的备份有:"$existlog
  done
index=0
for binlog in $binlogs;
  do
  index=`expr $index + 1`
  # 如果是最后一个binlog，就不做备份
  if [ "$index" == "$num" ];then
    echo "No need backup last binlog [$index]:"$binlog
  else
    backuped=false
    for existlog in $existlogs;
      do
      # 备份的文件名和binlog文件名一致，说明已备份过
      if [ "${existlog%.*}" == "$binlog" ];then
        backuped=true
        break
      fi
      done
    if $backuped;then
      echo "No need backup $binlog cause it backuped"
    else
      echo "Now backup binlog[$index]:$binlog"
      #解码后备份到backupDir路径下
      mysqlbinlog -u$username -p$password -h$dbhost -P$port --read-from-remote-server -vv --base64-output=decode-rows $binlog > $backupDir/$binlog.log
    fi
  fi
  done
复制

[yyxz@test Binlog]$ ./backupBinlog.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
数据库当前的binlog:mysql-bin.000092
数据库当前的binlog:mysql-bin.000093
数据库当前的binlog:mysql-bin.000094
数据库当前的binlog:mysql-bin.000095
ls: cannot access backup/dblog/Binlog/mysql-bin.*: No such file or directory
Now backup binlog[1]:mysql-bin.000092
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
Now backup binlog[2]:mysql-bin.000093
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
Now backup binlog[3]:mysql-bin.000094
mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
No need backup last binlog [4]:mysql-bin.000095
复制

[yyxz@test Binlog]$ ll -h
total 356M
-rwxrwxr-x 1 yyxz yyxz 1.2K Dec  2 15:12 backupBinlog.sh
-rw-rw-r-- 1 yyxz yyxz 120M Dec  2 15:13 mysql-bin.000092.log
-rw-rw-r-- 1 yyxz yyxz  43M Dec  2 15:13 mysql-bin.000093.log
-rw-rw-r-- 1 yyxz yyxz 194M Dec  2 15:13 mysql-bin.000094.log
复制

[yyxz@test Binlog]$ ./backupBinlog.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
数据库当前的binlog:mysql-bin.000092
数据库当前的binlog:mysql-bin.000093
数据库当前的binlog:mysql-bin.000094
数据库当前的binlog:mysql-bin.000095
当前的备份有:mysql-bin.000092.log
当前的备份有:mysql-bin.000093.log
当前的备份有:mysql-bin.000094.log
No need backup mysql-bin.000092 cause it backuped
No need backup mysql-bin.000093 cause it backuped
No need backup mysql-bin.000094 cause it backuped
No need backup last binlog [4]:mysql-bin.000095
复制

[yyxz@test Binlog]$ crontab -l 
*/10 * * * * /backup/dblog/Binlog/backupBinlog.sh
复制