mysql数据备份

​ 首先看一下我们当前服务器的数据备份逻辑。

​ 第一点，要开启数据库的binlog。在数据库的配置文件中添加以下配置，为了避免binlog太大，指定了8天为binlog的有效期。

log_bin                 = /var/lib/mysql/bakup/mysql-bin.log
expire_logs_days        = 8
max_binlog_size         = 200M
binlog_do_db            = mydb

​ 第二点，添加一个定时任务，每周末执行如下的数据库备份脚本。下面脚本的逻辑如下：

1. 执行时将上周末导出的mysql数据文件和binlog做一次打包备份。因为binlog设置了有效期，其实打包进去的binlog是最近8天产生的binlog。也就是这个数据文件加上一块打包的binlog是能够恢复数据到当前打包的时候的数据库状态。
2. 备份完成之后再导出一份mysql数据文件，以便下次备份使用。

#!/bin/sh
#备份思路为先做一次完全备份然后将增量日志拷贝到对应路径与完全备份文件一起打包存放，然后进行日志回滚并再进行完全备份一次做为下轮增量备份的启始点。

LOGPATH=/var/lib/mysql/bakup/   #mysql二进制文件的存储地址。不要用默认的地址，新建一个文件夹单独存放增量日志
BAKPATH=/mnt/dbbinlog/    #数据备份压缩文件的存储的地址
FILENAME=bakfile                #数据备份文件的存储地址
SQL=data.sql                    #完全备份文件名称

cd $BAKPATH
if [ ! -d "$FILENAME" ]; then
  mkdir "$FILENAME"
  cd $FILENAME
  if [ -f $SQL ]; then
  echo "hava backFile so to back binlog!"
  else
        mysqldump -uuser -ppassword -E -R mydb >$BAKPATH$FILENAME/$SQL
  fi
fi

echo "move binlogfile!!"
cp -r $LOGPATH $BAKPATH$FILENAME

sleep 5
echo "move binlogfile end"

echo "tar backfile"
DATE="`date +%y%m%d%H%M%S`_bak.tar.gz"  #压缩的文件名称

cd $BAKPATH
tar zcvf $DATE $FILENAME


rm -rf $BAKPATH$FILENAME

echo "bak now data"
mkdir $BAKPATH$FILENAME
mysqldump -uuser -ppassword -E -R mydb >$BAKPATH$FILENAME/$SQL

find ./ -mtime +10 -name "*.tar.gz"  -exec rm -rf {} \;

echo "clean binlog file"

有了备份的数据文件就可以进行数据库备份的操作。

首先，登录到数据库中，删除数据已经混乱的数据库，然后重建一个新的空的数据库

drop database mydb;
create database mydb default charset utf8 collate utf8_general_ci;

然后将最近一次的导出的数据库文件导入的数据库文件导入到空的数据库中，将数据恢复到最近一次备份的时候。

source /mnt/dbbinlog/bakfile/data.sql

执行完成之后，使用binlog将上次备份到当前时间产生的数据文件进行恢复。根据时间点进行binlog的数据恢复，比如上次是 3月31日 02:00 备份的，数据库出现故障的时间是 4月3日 11:00。可以执行如下命令

mysqlbinlog  --start-datetime="2019-03-31 02:00:00" --stop-datetime="2019-04-03 11:00:00" mysqlbin-log.000001 | mysql -uuser -ppassword

对当前的每个binlog的文件都执行以上命令。确保没有遗漏。

需要注意的几个地方：

• 要关注服务器的磁盘使用情况，如果磁盘空间不足，会导致数据备份不完整
• binlog执行的时候开始时间点要比数据库上次备份的时间点稍微延后，防止已存在的数据重复更新或者插入，导致数据恢复失败。结束时间点要比出现故障稍微往前挪，避免把故障数据恢复到当前数据库中。
• mysqlbinlog命令的执行需要相应的权限，如果执行失败请检查权限。