Mysql备份与还原——mysqldump结合binlog

MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

一、mysqldump 备份结合 binlog 日志恢复

1、binlog介绍

mysql 的二进制日志记录着该数据库的所有增删改的操作日志，还包括了这些操作的执行时间。我们可以使用mysqlbinlog命令来查看其内容。

（1）binlog用途：

主从同步，恢复数据库

（2）开启binlog

通过编辑 my.cnf 中的log_bin选项可以开启二进制日志，每次重启 mysql 服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的 number 会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

用mysql> show variables like 'log_bin';查看 bin-log 是否开启，如图：

（3）mysql 提供两种方式查看binlog方式，我们先对数据库进行一下增删改的操作，否则 log 里边数据有点空。

查看 MySQL Server 上的二进制日志：

（4）查看二进制日志信息的命令：

语法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

（5）查看二进制日志中的事件

mysql> show binlog events;

默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。

查看指定的二进制日志中的事件：

mysql> show binlog events in 'mysql-bin.000002';

该命令还包含其他选项以便灵活查看

mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3;// limit 1，1 为偏移量

2、mysqlbinlog工具

语法格式：mysqlbinlog [options] log_file ...

为了便于查看记录了行变化信息的事件在当时具体执行了什么样的 SQL 语句可以使用mysqlbinlog 工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪 SQL 语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如

先切换到 binlog 所在的目录下

#mysqlbinlog mysql-bin.000001

#mysqlbinlog -v mysql-bin.000001

#mysqlbinlog -vv mysql-bin.000001

mysqlbinlog 选项示例：

--start-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。

--stop-datetime

从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。

--start-position

从二进制日志中读取指定 position 事件位置作为开始。

--stop-position

从二进制日志中读取指定 position 事件位置作为事件截至。

3、使用 binlog 恢复之前删除数据（id=2 那条记录）

注：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

（1）查看 binlog 文件，从中找出 delete from test.tb1 where id=2

# cd /usr/local/mysql/data/

# mysqlbinlog -v mysql-bin.000002

显示结果如下：

从中可以看出 delete 事件发生 position 是 287，事件结束 position 是 416

恢复流程：直接用 bin-log 日志将数据库恢复到删除位置 287 前,然后跳过故障点,再进行恢复下面所有的操作。

（2）由于之前没有做过全库备份，所以要使用所有 binlog 日志恢复，所以生产环境中需要很长时间恢复，导出相关 binlog 文件

#mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001.sql

#mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql

#mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql

删除 test 数据库：

mysql>drop database test;

利用 binlog 恢复数据：

恢复完成后，我们检查下表的数据是否完整：

4、mysqldump介绍

mysqldump 是 mysql 用于备份和数据转移的一个工具。它主要产生一系列的 SQL 语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL 命令如 CREATE DATABASE，CREATE TABLE，INSERT 等等。可以用来实现轻量级的快速迁移或恢复数据库。

（1）数据库的导出

导出数据库 test

# mysqldump -uroot -p --flush-logs test > /opt/test.sql//--flush-logs 这个选项就会完整备份的时候重新开启一个新 binlog

数据库的导入

# mysql -uroot -p test

以上就是 mysql 的 binlog 和 mysqldump 工具，下面我们开始实现 mysqldump全库备份+binlog 的数据恢复

检查开启 binlog，先创建一些原始数据

1、创建备份目录

2、全库备份

3、备份 mysqldump 全库备份之前的 binlog 日志

4、模拟下操作失误,将数据修改错误了

5、备份自 mysqldump 之后的 binlog 日志文件

cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/

6、使用 mysqldump 的备份进行全库恢复

# mysql -uroot -p test_db

查询一下数据：

刚才删除的数据（id=2）恢复回来了，但备份后产生的数据却丢失了所以还得利用 binlog 进一步还原。因为删除是在全库备份后发生的，而 mysqldump 全库备份时使用--flush-logs 选项，所以只需要分析全库备份后的 binlog 即 mysql-bin.000002

7、查看 mysql-bin.000002 中的事件，可以看到有删除事件

恢复流程：我们直接用 bin-log 日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作

# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002

我们先用 mysqlbinlog 命令找到 delete 那条语句的位置

通过 mysqlbinlog 命令所显示的结果可以看到误操作 delete 的开始 postion 为 219，结束position 是 422。

8、从二进制日志中读取指定 position=219 事件位置作为截至，即把数据恢复到 delete 删除前

# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

从二进制日志中读取指定 position=422 事件位置作为开始，即跳过删除事件，恢复删除事件之后对数据的正常操作

#mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

9、查看恢复结果

从上面显示可以看出数据恢复到正常状态

注：生产环境中 Mysql 数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond 计划任务周期性执行备份脚本（以下脚本仅供参考）

mysqldump 备份方案：

周日凌晨 1 点全库备份

周一到周六凌晨每隔 4 个小时增量备份一次

设置 crontab 任务，每天执行备份脚本

# crontab –e

#每个星期日凌晨 1:00 执行完全备份脚本

0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔 4 个小时增量备份一次

0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh 脚本内容：

[root@localhost ~]# cat mysqlfullbackup.sh

#!/bin/sh

# Name:mysqlFullBackup.sh

# 定义数据库目录

mysqlDir=/usr/local/mysql

# 定义用于备份数据库的用户名和密码

user=root

userpwd=123456

dbname=test_db

# 定义备份目录

databackupdir=/opt/mysqlbackup

[ ! -d $databackupdir ] && mkdir $databackupdir

# 定义邮件正文文件

emailfile=$databackupdir/email.txt

# 定义邮件地址

# 定义备份日志文件

logfile=$databackupdir/mysqlbackup.log

DATE=`date -I`

echo "" > $emailfile

echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile

cd $databackupdir

# 定义备份文件名

dumpfile=mysql_$DATE.sql

# 使用 mysqldump 备份数据库，请根据具体情况设置参数

$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile //-x--lock-all-tables

# 压缩备份文件

if [ $? -eq 0 ]; then

tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName:$gzdumpfile" >> $emailfile

echo "DataBase Backup Success!" >> $emailfile

rm -f $dumpfile

else

echo "DataBase Backup Fail!" >> $emailfile

fi

# 写日志文件

echo "--------------------------------------------------------" >> $logfile

cat $emailfile >> $logfile

# 发送邮件通知

cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh 脚本内容：

[root@localhost ~]# cat mysqldailybackup.sh

#!/bin/sh

# Name:mysqlDailyBackup.sh

# 定义数据库目录和数据目录

mysqldir=/usr/local/mysql

datadir=$mysqldir/data

# 定义用于备份数据库的用户名和密码

user=root

userpwd=123456

# 定义备份目录，每日备份文件备份到$dataBackupDir/daily

databackupdir=/opt/mysqlbackup

dailybackupdir=$databackupdir/daily

[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily

# 定义邮件正文文件

emailfile=$databackupdir/email.txt

# 定义邮件地址

# 定义日志文件

logfile=$databackupdir/mysqlbackup.log

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` // =let icounter++

done

nextnum=0

ifile=0

for file in $filelist

do

binlogname=`basename $file`

nextnum=`expr $nextnum + 1`

# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）

if [ $nextnum -eq $icounter ]; then

echo "Skip lastest!" > /dev/null

else

dest=$dailybackupdir/$binlogname

# 跳过已经备份的二进制日志文件

if [ -e $dest ]; then

echo "Skip exist $binlogname!" > /dev/null

else

# 备份日志文件到备份目录

cp $binlogname $dailybackupdir

if [ $? -eq 0 ]; then

ifile=`expr $ifile + 1`

echo "$binlogname backup success!" >> $emailfile

fi

fi

fi

done

if [ $ifile -eq 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 Backup" $email

# 写日志文件

echo "--------------------------------------------------------" >> $logfile

cat $emailfile >> $logfile