mysql truncate 数据恢复_记录一次truncate操作数据恢复

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

实际线上的场景比较复杂，当时涉及了truncate, delete 两个操作，经确认丢数据差不多7万多行，等停下来时，差不多又有共计1万多行数据写入。 这里为了简单说明，只拿弄一个简单的业务场景举例。

测试环境： Percona-Server-5.6.16

日志格式： mixed 没起用gtid

表结构如下：

1

2

3

4

5

CREATETABLE`tb_wubx`(

`id`int(11)NOT NULLAUTO_INCREMENT,

`name`varchar(32)DEFAULTNULL,

PRIMARYKEY(`id`)

)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8

基于某个时间点有一个备份或是有全量的binlog是能恢复数据的一个唯一保证。 例如我们的备份就是一个表结构创建语句,binlog pos相关信息： mysql-bin.000004 , 4，然后进行了如下：

–t1时间 程序写入：

insert into tb_wubx(name) values(‘张三’),(‘李四’);

insert into tb_wubx(name) values(‘隔壁老王’);

–t2时间 某个人员失误

truncate table tb_wubx;

–t3时间 程序写入

insert into tb_wubx(name) values(‘老赵’);

update tb_wubx set name=’老赵赵’ where id=1;

现在表里的数据情况：

1

2

3

4

5

6

7

mysql>select*fromtb_wubx;

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

|id|name|

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

|1|老赵赵|

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

1rowinset(0.00sec)

可以见truncate table操作后，表的自增id又变更为从1开始，原来写入的数据应该是：

+—-+———–+

| id | name |

+—-+———–+

| 1 | 张三 |

+—-+———–+

| 2 | 李四 |

+—-+———–+

| 3 | 隔壁老王 |

+—-+———–+

如果没生truncate table操作，实际的数据应该为：

+—-+———–+

| id | name |

+—-+———–+

| 1 | 张三 |

+—-+———–+

| 2 | 李四 |

+—-+———–+

| 3 | 隔壁老王 |

+—-+———–+

| 4 | 老赵赵 |

+—-+———–+

而且线上的恢复那个表时和序序开发人员了解才知道，原来那个id和缓存及其它地方有依赖，因为id乱了，也会造成程序错乱。这个时间修复id在程序层错乱的事，留给开发人员了关建是给他们讲明白恢复的结果是什么样，我们的关建任务是把数据恢复出来。好，接下来的工作是开始从binlog中恢复数据。

利用: show binary logs; 查看当的log文件分布， 然后利用show binlog events in ‘binary log文件’; 查看log文件的内容，目的是找到truncate发生的日志位置。

另外因为基于备份(由log的启始位置)或是从量log, 如果基于备份有log的起始位置，我们需要处理的log文件是启始位置到发生truncate的日值(后面的数据处理不了，会发生主建冲突的错误造成truncate后的数据不能恢复)，

如果是全量日志，需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置(后面数据会因为主建冲突写不进去)

恢复准备工作，创建一个库用于恢复数据，这里创建了一个re_wubx, 及原结构的表: tb_wubx (相当于恢复了备份，过程省略)

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

1

2

3

4

5

6

7

8

9

10

mysql>showbinarylogs;

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

|Log_name|File_size|

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

|mysql-bin.000001|143|

|mysql-bin.000002|261|

|mysql-bin.000003|562|

|mysql-bin.000004|1144|

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

4rowsinset(0.00sec)

我这里有一个备份文件就是那个创建表的sql语句，位置是mysql-bin.000004 , 4

在这个案例里我只用cover住mysql-bin.000004这个文件。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

mysql>showbinlogeventsin'mysql-bin.000004';

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

|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|

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

|mysql-bin.000004|4|Format_desc|753306|120|Serverver:5.6.16-64.2-rel64.2-log,Binlogver:4|

|mysql-bin.000004|120|Query|753306|209|use`wubx`;truncatetabletb_wubx|

|mysql-bin.000004|209|Query|753306|281|BEGIN|

|mysql-bin.000004|281|Table_map|753306|334|table_id:91(wubx.tb_wubx)|

|mysql-bin.000004|334|Write_rows|753306|393|table_id:91flags:STMT_END_F|

|mysql-bin.000004|393|Xid|753306|424|COMMIT/* xid=1073 */|

|mysql-bin.000004|424|Query|753306|496|BEGIN|

|mysql-bin.000004|496|Table_map|753306|549|table_id:91(wubx.tb_wubx)|

|mysql-bin.000004|549|Write_rows|753306|602|table_id:91flags:STMT_END_F|

|mysql-bin.000004|602|Xid|753306|633|COMMIT/* xid=1074 */|

|mysql-bin.000004|633|Query|753306|722|use`wubx`;truncatetabletb_wubx|

|mysql-bin.000004|722|Query|753306|794|BEGIN|

|mysql-bin.000004|794|Table_map|753306|847|table_id:92(wubx.tb_wubx)|

|mysql-bin.000004|847|Write_rows|753306|894|table_id:92flags:STMT_END_F|

|mysql-bin.000004|894|Xid|753306|925|COMMIT/* xid=1081 */|

|mysql-bin.000004|925|Query|753306|997|BEGIN|

|mysql-bin.000004|997|Table_map|753306|1050|table_id:92(wubx.tb_wubx)|

|mysql-bin.000004|1050|Update_rows|753306|1113|table_id:92flags:STMT_END_F|

|mysql-bin.000004|1113|Xid|753306|1144|COMMIT/* xid=1084 */|

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

19rowsinset(0.00sec)

看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。

这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即：

1

mysqlbinlog--rewrite-db='wubx->re_wubx'--start-position=4--stop-position=633mysql-bin.000004|mysql-S/tmp/mysql.sockre_wubx

解释：将binlog中wubx替换为re_wubx，通过管道输出，并在re_wubx库中重新执行，登录方式是socket直接免密，可以修改为常规的登录方式，这个文档中有好多错别字，要注意两个不同的库wubx和re_wubx，文章的中心议题是，在truncate误操作后，数据被破坏，有insert 、update，这个场景下的恢复思路是：1.新建一个空库，利用mysqlbinlog解析二进制日志，并做一些修改，将二进制日志中老库名替换为新库名，并指定起始和截止pos，再定向到mysql客户端去恢复数据，2.将老库老表(也即故障发生后的状态)select 出来insert到新库新表，(对于自增键就不要select了，让系统自增)，3.再rename，这里有个点要注意，renametablere_wubx.tb_wubxtowubx.tb_wubx; 也就是通过rename可以将表移动到另外一个库下，并改名

恢复结果如下：

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

mysql-S/tmp/mysql.sockre_wubx;

mysql>selectcount(*)fromtb_wubx;

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

|count(*)|

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

|3|

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

1rowinset(0.02sec)

mysql>select*fromtb_wubx;

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

|id|name|

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

|1|张三|

|2|李四|

|3|隔壁老王|

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

3rowsinset(0.00sec)

mysql>insertintotb_wubx(name)selectnamefromwubx.tb_wubx;

QueryOK,1rowaffected(0.00sec)

Records:1Duplicates:0Warnings:0

mysql>renametablewubx.tb_wubxtowubx.bak_tb_wubx;

QueryOK,0rowsaffected(0.04sec)

mysql>renametablere_wubx.tb_wubxtowubx.tb_wubx;

QueryOK,0rowsaffected(0.03sec)

mysql>select*fromwubx.tb_wubx;

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

|id|name|

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

|1|张三|

|2|李四|

|3|隔壁老王|

|4|老赵赵|

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

4rowsinset(0.00sec)

恢复完成。

想一想，如果我跳过那个truncate继续执行那些binlog会怎么样 ？