Mysql 恢复（1）-基于binlog日志的时间点恢复

DBA最敬畏的大神"莫非"先生提到"可能发生的事情就一定会发生", 数据丢失的风险就像达摩克利斯之剑在头上高悬,数据作为最核心的资产来不得半点懈怠,于是我们从网络上冗余了交换机路由器, 存储上我们使用了昂贵的san(raid10)磁盘阵列,甚至使用多路cpu,和电源,甚至用8路网络绑定成1个网络, 似乎很完美, 不过还是无法避免出现人为的误操作,我们一定要敬畏莫非定律, 因为人的因素是无法预测的, 比如拔错存储电源, 误操作rm命令,网络也有可能会错乱导致无法访问, 数据丢失,这些都是我亲身经历过的事情, 以下我将从mysql角度使用最后的防线来做时间点恢复.

开启log_bin

创建数据库及测试数据

> create database test;

> use test;

> create table tb1 (id int auto_increment primary key,addtime datetime);

> insert into tb1 values(null,now());

> insert into tb1 values(null,now());

> insert into tb1 values(null,now());

> select * from tb1;

+----+---------------------+

id addtime

+----+---------------------+

1 2017-12-15 01:51:53

2 2017-12-15 01:51:56

3 2017-12-15 01:51:56

+----+---------------------+

3 rows in set (0.00 sec)

模拟误操作

此时出现了某个误操作, 直接将表清空了,损失已造成

> select now();

+---------------------+

now()

+---------------------+

2017-12-15 01:55:50

+---------------------+

1 row in set (0.00 sec)

> truncate table tb1;

了解mysqlbinlog

一般来讲我们可以通过mysqldump全备+binlog日志增量备份的方式来做时间点恢复, 不过此处我们假设知道明确的开始时间与结束时间, 将指定的时间区域binlog解析执行就可以了.

查看当前binlog日志文件

查看所有binlog信息

由于本次产生的binlog很小, 所以可以很确定需要的数据在最新的binlog文件node1_mysql-bin.000019中.

mysqlbinlog常用参数

--disable-log-bin或-D 在解析的语句中添加 set sql_log_bin=0 可以禁止恢复过程产生日志

-r 或--result-file 结果文件保存路径

-server-id 指定mysql服务器，确保是由给定服务器id的mysql服务器所生成的日志

--database=db_name，-d db_name只列出该数据库的条目

--offset=N，-o N跳过前N个条目

--start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

--stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

--short-form，-s只显示日志中包含的语句，不显示其它信息。

--start-datetime=datetime

--stop-datetime=datetime

可以使用通配符解析多个文件:

mysqlbinlog hostname-bin.[0-9]*

mysqlbinlog不加参数解析出来部分的sql显示为base64编码格式,需要加上相应的参数才能显示出sql语句,注意并不是具体的操作语句,而是通过二进制转换过来的语句.

--base64-output=decode-rows -v

--verbose选项可以获取更多的可读信息，但是并不是一个原始的SQL语句(类似的)

binlog使用

1. 不使用任何参数

#BINLOG部分是真实的SQL语句，无法看到具体内容

[root@node1 binlog]# mysqlbinlog node1_mysql-bin.000019 more

.....

BINLOG '

lIYyWg8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AcFTI6g=

...

2.添加-v参数

可以看到, 加-v参数后可以看到insert into语句,不过还是没看到create database及create table语句

...

BINLOG '

lIYyWg8BAAAAdwAAAHsAAAABAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AcFTI6g=

'/*!*/;

# at 123

#171214 22:11:32 server id 1 end_log_pos 194 CRC32 0xf13eec28 Previous-GTIDs

.....

BINLOG '

poYyWhMBAAAAMAAAAIMBAAAAAA4BAAAAAAEABHRlc3QAA3RiMQACAxIBAAJvIxpK

poYyWh4BAAAALQAAALABAAAAAA4BAAAAAAEAAgAC//wGAAAAmZ5dYvKy7e15

'/*!*/;

### INSERT INTO `test`.`tb1`

### SET

### @1=6

### @2='2017-12-14 22:11:50'

# at 432

3.添加--base64-output=DECODE-ROWS

可以看到create以及insert语句

...

create database test

...

create table tb1 (id int auto_increment primary key,

addtime datetime)

/*!*/;

# at 1686

#171215 1:51:53 server id 1 end_log_pos 1751 CRC32 0x4ab93933 GTIDlast_committed=8sequence_number=9rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

# at 1751

#171215 1:51:53 server id 1 end_log_pos 1831 CRC32 0xecdfb825 Querythread_id=25exec_time=0error_code=0

BEGIN

/*!*/;

# at 1831

#171215 1:51:53 server id 1 end_log_pos 1879 CRC32 0xf2f7a65e Table_map: `test`.`tb1` mapped to number 272

# at 1879

#171215 1:51:53 server id 1 end_log_pos 1924 CRC32 0x91f5452b Write_rows: table id 272 flags: STMT_END_F

### INSERT INTO `test`.`tb1`

### SET

### @1=1

### @2='2017-12-15 01:51:53'

添加2个v参数后还可以看到参数的数据类型

mysqlbinlog node1_mysql-bin.000019 -vv --base64-output=DECODE-ROWS

### INSERT INTO `test`.`tb1`

### SET

### @1=6/*INTmeta=0 nullable=0 is_null=0 */

### @2='2017-12-14 22:11:50' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

# at 432

开始恢复

确定所需binlog范围并应用

方法1, 确定position

第一个insert位置

注意, 实际测试时使用1879导致失去第一个insert, 需要使用insert的前一个position 1831

# at 1831

#171215 1:51:53 server id 1 end_log_pos 1879 CRC32 0xf2f7a65e Table_map: `test`.`tb1` mapped to number 272

# at 1879

#171215 1:51:53 server id 1 end_log_pos 1924 CRC32 0x91f5452b Write_rows: table id 272 flags: STMT_END_F

### INSERT INTO `test`.`tb1`

### SET

truncate前位置

# at 2558

#171215 1:55:59 server id 1 end_log_pos 2643 CRC32 0x661d959c Querythread_id=25exec_time=0error_code=0

truncate table tb1

测试查看有几个insert

mysqlbinlog node1_mysql-bin.000019 -vv --base64-output=DECODE-ROWS --start-position=1831 --stop-position=2558 grep -i insert

### INSERT INTO `test`.`tb1`

### INSERT INTO `test`.`tb1`

### INSERT INTO `test`.`tb1`

开始恢复指定数据:

由于开启了gtid, 此处需要添加参数 --skip-gtids.

mysqlbinlog node1_mysql-bin.000019 --skip-gtids --start-position=1831 --stop-position=2558 mysql --login-path=admin

数据已恢复

(root:localhost:Fri Dec 15 06:07:06 2017)[test]> select * from tb1;

+----+---------------------+

id addtime

+----+---------------------+

1 2017-12-15 01:51:53

2 2017-12-15 01:51:56

3 2017-12-15 01:51:56

+----+---------------------+

方法2:使用时间限定范围

方法1中其实也找到了我们需要的时间范围

'171215 1:51:53' 到'171215 1:55:59'

[root@node1 binlog]# mysqlbinlog --disable-log-bin --skip-gtids --start-datetime='2017-12-15 1:51:53' --stop-datetime='2017-12-15 1:55:59' --base64-output=DECODE-ROWS -vv node1_mysql-bin.000019grep -i insert

### INSERT INTO `test`.`tb1`

### INSERT INTO `test`.`tb1`

### INSERT INTO `test`.`tb1`

恢复方法类似:

mysqlbinlog node1_mysql-bin.000019 --skip-gtids --start-datetime='2017-12-15 1:51:53' --stop-datetime='2017-12-15 1:55:59' mysql --login-path=admin