mysql 备份_自动备份MySQL的binlog

实践环境：MySQL8 CentOS7

目的：将binlog解码后备份到某特定路径下，备份的文件名沿用binlog的原文件名，以.log后缀作为结尾。

思路：

获取binlog名称列表

1. 命令格式:

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

2. 上一步获取列表的最后一个binlog不做备份，其他都备份。

3. 对已经备份过的binlog，不做二次备份。

通过文件名来对比是否备份过。

4. Base64解码后，存放为*.log文件。

shell脚本backupBinlog.sh如下：

#!/bin/bashdbhost="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=0for 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  doneindex=0for 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

执行结果示例：

• 第一次执行，备份binlog

[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.000095ls: cannot access /backup/dblog/Binlog/mysql-bin.*: No such file or directoryNow backup binlog[1]:mysql-bin.000092mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.Now backup binlog[2]:mysql-bin.000093mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.Now backup binlog[3]:mysql-bin.000094mysqlbinlog: [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 -htotal 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.logNo need backup mysql-bin.000092 cause it backupedNo need backup mysql-bin.000093 cause it backupedNo need backup mysql-bin.000094 cause it backupedNo need backup last binlog [4]:mysql-bin.000095

最后，通过设置定时任务也可以每间隔10分钟就做一次定时备份动作

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

题图：

女儿 海洋 海龟 心桥 数不清的小心心 生日蛋糕 我