mysql增量备份以及日志修复方法

一、准备增量测试数据

• a、准备第一份增量数据

  mysql> create database backup_test;
  mysql> use backup_test;
  mysql> create table test (c1 int(10), c2 varchar(20)) engine=innodb;
  mysql> insert into test values (11, 'test1'),(12, 'test2'),(13, 'test3'),(14, 'test4'),(15, 'test5');

• b、备份前需要将数据库加读锁，防止数据在备份时写入。

  mysql> flush tables with read lock;

• c、通过命令flush logs;将log日志刷盘，写入当前binlog(mysql-bin.000002)，在生成一个新的binlog(mysql-bin.000003)为下次增备做准备。

  mysql> flush logs;

• d、将binlog第一个增备文件mysql-bin.000002直接复制保存即可。也可以将二进制文件导出到文本文件保存，在linux命令行下执行

  cd mysql-bin文件位置
  mysqlbinlog --no-defaults mysql-bin.000002 > ~/test1.sql

• e、解除表锁。

  mysql> unlock tables;

  至此第一个增量备份全部结束，将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件~/test1.sql保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000003)里面。

二、binlog恢复操作：

如上我们可以随意修改test中的数据以方便进行还原测试，你也可以直接清空，删除表：

1、使用binlog直接还原

mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123

执行后你会发现数据恢复到了你删除之前，因为我们提前flush logs;了存储文件，所以可以正常恢复。但是生活不是演习，你常常会在一不小心删除了数据以后才发现问题，即日志存储文件还是位于mysql-bin.000001，如果执行一次上面的命令，你会发现你的数据并没有恢复。WHY? 因为其实你已经恢复了，但是你又把它删除了。

此时我们需要使用mysqlbinlog的时间和位置界定参数：

• --start-datetime:恢复的开始时间
• --stop-datetime:恢复的结束时间
• --start-position:恢复开始的位置
• --stop-position:恢复结束的位置

使用以上的参数，我们可以恢复到删除前一秒：

mysqlbinlog --no-defaults --start-datetime='2016-01-04 15:11:00' --stop-datetime='2016-01-04 15:11:00' mysql-bin.000001 | mysql -uroot -p123

或者执行我们删除之前的代码：

mysqlbinlog --no-defaults --start-position=249 --stop-position=249 mysql-bin.000001 | mysql -uroot -p123

使用--database参数，从binlog日志中恢复单个库：

mysqlbinlog --database=test mysql-bin.000001 |mysql -uroot -p123

如上命令使用mysqlbinlog恢复指定的test数据库,只限本地log日志。

不常用选项：

• -u --user=name日志恢复到的服务器账号
• -p --password[=name]日志恢复到的服务器密码
• -h --host=name日志恢复到的服务器IP，本机不用填写
• --read-from-remote-server从某个MySQL服务器上读取binlog日志

2、通过binlog文本文件source还原

上面我们已经通过~/test1.sql导出了日志文件，使用mysqlbinlog将binlog日志导出成文本文件，使用source执行该文件：

source ~/test1.sql;

常见错误解决办法：

1、binlog恢复数据会出现重复创建数据么？

答案是不会，在恢复过程中如果出现重复创建数据记录，将会出现Duplicate entry '2' for key 'PRIMARY'这样的错误并且结束程序执行。

2、如何在报错的情况下继续执行数据

有时候我们不记得开始和结束时间，希望直接执行而不是遇到错误停止:

• --force-read， -f

使用此选项，如果mysqlbinlog读取它无法识别的二进制日志事件，它将输出警告，忽略该事件，然后继续。如果没有此选项，mysqlbinlog会在读取此类事件时停止。

例：

mysqlbinlog --no-defaults --start-datetime='2018-12-26 14:31:00' --stop-datetime='2018-12-26 14:38:00' mysql-bin.000001 | mysql -uroot -p123 -f

更多操作：MySQL数据库备份还原（基于binlog的增量备份）