MySQL中truncate误操作后的数据恢复案例

2024-04-01 00:00:05 37

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

测试环境：Percona-Server-5.6.16
日志格式：mixed没起用gtid

表结构如下：

CREATETABLE`tb_wubx`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(32)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8

CREATETABLE`tb_wubx`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(32)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8

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

–t1时间程序写入：

insertintotb_wubx(name)values(‘张三'),(‘李四');
insertintotb_wubx(name)values(‘隔壁老王');

–t2时间某个人员失误

truncatetabletb_wubx;

–t3时间程序写入

insertintotb_wubx(name)values(‘老赵');
updatetb_wubxsetname='老赵赵'whereid=1;

现在表里的数据情况：

mysql>select*fromtb_wubx;
+----+-----------+
|id|name|
+----+-----------+
|1|老赵赵|
+----+-----------+
1rowinset(0.00sec)

mysql>select*fromtb_wubx;
+----+-----------+
|id|name|
+----+-----------+
|1|老赵赵|
+----+-----------+
1rowinset(0.00sec)

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

+—-+———–+
|id|name|
+—-+———–+
|1|张三|
+—-+———–+
|2|李四|
+—-+———–+
|3|隔壁老王|
+—-+———–+

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

+—-+———–+
|id|name|
+—-+———–+
|1|张三|
+—-+———–+
|2|李四|
+—-+———–+
|3|隔壁老王|
+—-+———–+
|4|老赵赵|
+—-+———–+

而且线上的恢复那个表时和序序开发人员了解才知道，原来那个id和缓存及其它地方有依赖，因为id乱了，也会造成程序错乱。这个时间修复id在程序层错乱的事，留给开发人员了关建是给他们讲明白恢复的结果是什么样，我们的关建任务是把数据恢复出来。好，接下来的工作是开始从binlog中恢复数据。
利用:showbinarylogs;查看当的log文件分布，然后利用showbinlogeventsin‘binarylog文件';查看log文件的内容，目的是找到truncate发生的日志位置。
另外因为基于备份（由log的启始位置）或是从量log,如果基于备份有log的起始位置，我们需要处理的log文件是启始位置到发生truncate的日值（后面的数据处理不了，会发生主建冲突的错误造成truncate后的数据不能恢复），
如果是全量日志，需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置（后面数据会因为主建冲突写不进去）
恢复准备工作，创建一个库用于恢复数据，这里创建了一个re_wubx,及原结构的表:tb_wubx（相当于恢复了备份，过程省略）

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)

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这个文件。

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)

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到后来那个误操作的truncatetable的语句之间的数据就是丢失的数据。
这个恢复可以从mysql-bin.000004pos:4到mysql-bin.000004pos:633即：

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


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

恢复结果如下：

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)

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)

恢复完成。

MySQL中truncate误操作后的数据恢复案例

热门推荐

随机推荐