mysql从binlog恢复数据

首先, 要有一个可用的mysql, 安装步骤在这里不表了, 之前文章里有一篇介绍安装的.

1. 开启binlog.

为了可以从binlog里恢复, 首先要开启记录binlog:

cat /etc/my.cnf

添加如下几行:

log-bin=mysql-bin　　#表示开启binlog, 且binlog物理文件在/var/lib/mysql/mysql-bin.000000x, 同一目录下还有一个mysql-bin.index文件
sync_binlog=1　　#每次事物提交都把binlog写入到磁盘里, 这个很重要, 如果不开启, 在做主从复制的时候有可能造成主从数据不一致
innodb_support_xa=1　　#开启xa分布式事物支持, 也是为通过内部xa事务保证binlog和实际数据一致
binlog_format=ROW　　#binlog格式为ROW, 三个可选项: STATEMENT, ROW, MIXED
server-id=7　　#一定要写这个, 如果只配置log-bin而不配置这一项, mysql服务将因报错而会无法启动, 据说推荐写成服务器IP最后一位

修改好之后, 重启mysql服务:

systemctl restart mysqld.service

好了, 现已开启了binlog了, 可以在 /var/lib/mysql下找到如下文件:

 

先登录进去mysql的命令行( mysql -uroot -pxxxxxx ), 看一下状态:

可以看到当前是使用mysql-bin.000001这个文件来记录binlog的. 当前位置是154, 我观察到对每个binlog在文件, 当还没有什么操作的时候, 位置都会是154.

也可以看一下binlog相关的几个变量:

 

2. 测试恢复数据

下面添加一些测试数据:

create database testbindb;
use testbindb;
create table test_order(id int(10), primary key(id) ) engine=innodb;
insert into test_order values(1);
insert into test_order values(2);
insert into test_order values(3);
insert into test_order values(4);
insert into test_order values(5);
insert into test_order values(6);
insert into test_order values(7);

完事看一眼表:

 

 然后删除这个表:

drop table test_order;

然后把binlog强制写入并重开一个binlog文件:

flush logs;

再看一眼表, 已经不在了:

 

再看一眼状态:

可以看到已经在使用第二个binlog文件了, 相当于之前的建表, 添加测试数据, 再删除表等操作都记录在了mysql-bin.000001里了.

先看一眼这个binlog文件里的内容:

假设我们删除表的操作是误操作, 所以我们希望数据可以恢复到删除前一个操作结束后的状态.

使用mysqlbinlog命令可以达到目的:

mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position 154 --stop-position 2327 | mysql -uroot -p

这里表示要把 mysql-bin.000001 文件里位置154到2327之间的操作进行恢复. 这里有两个要注意的地方:

1. binlog文件位置要使用绝对路径, 否则会说找不到文件, 从而无法恢复
2. 后面接的 | mysql -uroot -p 不能省略, 我个人认为是恢复的时候需要登录, 可以理解

恢复完成后, 刚刚删除的那张表就回来了:

mysqlbinlog命令据说还有以下参数可用: 

1. --start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
2. --stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
3. --start-position：从二进制日志中读取指定position 事件位置作为开始。
4. --stop-position：从二进制日志中读取指定position 事件位置作为事件截至