MYSQL的增量备份

在进行数据库备份时，如果数据量非常非常的大，要频繁的用 mysqldump进行全备份不太现实。这时我们就要用到全备份和增量备份相结合来对数据库进行备份。下面主要讲的是增量备份。

一、定义：

mysql数据库会自动把用户对mysql数据库的操作记录到二进制文件中，当用户希望恢复的时候可以使用备份文件进行恢复。

增量备份会记录 （dml 语句 建表语句 不会记录select语句）

记录的（a 操作语句本身 b 操作的时间  c 操作的位置 position）

注意：MYSQL的增量备份是在5.0版本之后才支持的。

二、增量备份实践

1、配置

在MYSQL的配置文件 my.ini 或者是 my.conf 中启用二进制备份

在mysqld结点下添加以下内容，如原先有则修改之

log-bin=c:\mysqllog\log-bin

注意：如果我们写的如果不是绝对路径，它就会在datadir指定的目录下去找该目录。而且该目录必须是真实存在的，否则MYSQL无法启动。

重新启动MYSQL服务后，在该目录（C:\mysqllog）下就会生成两个文件。

log-bin.000001         增量备份文件，存放的是对数据库的操作，是一个二进制文件。

log-bin.index    这个是索引备份，指定有哪些增量备份

 

2、模拟操作

我们在mysql的控制台执行以下语句。

create database temp;
use temp;
create table temp(id int primary keyauto_increment,name varchar(32));
insert into temp values(null,"name1");
insert into temp values(null,"name2");
insert into temp values(null,"name3");
insert into temp values(null,"name4");
insert into temp values(null,"name5");
drop table temp;

上面的意思就是建立一个“temp”表，然后插入5条数据，然后把表删除。这样就模拟了数据丢失的操作。

3、恢复数据

现有如下需求，要恢复temp表。要实现恢复，我们就先要明白增量备份到底记录了写什么。

我们在CMD下转到MYSQL的BIN目录下。执行mysqlbinlog命令，来查看日志文件中到底记录了写什么。

 

（1）、查看增量日志

命令格式：

mysqlbinlog 增量备份日志路径

示例：

如下我执行的命令：

mysqlbinlog.exe c:\mysqllog\log-bin.000001

 执行完之后我们就可以看到类似于下面的结果：

 

从结果我们可以很明显的看到我们之前执行的操作都记录在里面。这里只截取了插入“name5”和删除表时的操作记录。这个记录都包括了时间、位置和操作本身。知道这些后，我们就可以进行表的恢复了。

 

（2）方式一：通过时间点恢复

 

在CMD下转到MYSQL的BIN目录下，执行以下命令

命令格式：

mysqlbinlog  --start-datatime="开始时间" --stop-datatime="结束时间" 日志目录 | mysql –uroot–proot

命令解释：

（1）这条命令如不写“--start-datatime="开始时间" ”，则会从日志的最开始一直恢复到什么时候。同理不写“--stop-datatime="结束时间"”意思就是说从什么时候开始恢复。

（2）“mysql –uroot –proot”是登录数据库的意思，用户名和密码由自己的环境决定，我的测试环境都为“root”。

示例：

从上面的截图可知，删除表的时间是2014-10-228:58:17 ,我们只要从一开始恢复到这个时间点之前就可恢复我们的表了。所以我们执行的命令如下：

mysqlbinlog --stop-datetime="2014-10-228:58:16" c:\mysqllog\log-bin.000001 | mysql -uroot –proot

执行完之后，我们可在查看数据库，数据的确是恢复回来了。

 

（3）方式二、通过位置来恢复

在CMD下转到MYSQL的BIN目录下，执行以下命令

命令格式：

mysqlbinlog  --start-position="开始位置" --stop-position="结束位置" 日志目录 | mysql –uroot–proot

命令解释：

这条命令如不写 “--start-position="开始位置" ”，则会从日志的最开始一直恢复到什么位置。同理不写“--stop-position="结束位置"”意思就是说从什么位置开始恢复

示例：

从下面的截图可知，删除表语句的位置为“1645”而它的前一个位置为“1528”，我们只需要恢复到“1528”这个位置即刻，所以我们的命令为：

mysqlbinlog --stop-position=1528c:\mysqllog\log-bin.000001 | mysql -uroot –proot

注意：在执行命令前，要删除temp 表，因为，之前我们恢复过一次，如果不删除就恢复会报表存在的错误。

 

三、增量备份文件的维护：

1、Mysql控制台下执行一下指令可以清除二进制日志文件：reset master;文件并没有消失，只是内容没了。

2、在my.ini中可以设置日志过期天数过期的日志会被自动删除，有利于减少我们管理日志的工作量：

expire_logs_day=7

这里设定日志可以保存7天，7天之后过期的日志将被自动删除。

四、全备份与增量备份相结合

在日常应用中可以将自动全备份和增量备份结合在一起使用，例如，每周日进行一次全备份，将增量备份的日志清理时间设置为7天以上。如果那天数据库出了问题，可以全备份和增量备份配合恢复。