[转帖]mysql中truncate删除记录恢复笔记_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1953 | 回复: 0

主题： [转帖]mysql中truncate删除记录恢复笔记

napolenAx

注册用户

等级：少校
经验：802
发帖：118
精华：1
注册：2011-8-30
状态：离线
发送短消息息给napolenAx

加好友发送短消息息给napolenAx

发消息

发表于：

2014-11-20 11:06:37 | [全部帖] [楼主帖]

楼主

truncate命令是mysql中删除记录一个命令它可以一次性删除当前表中所有记录并且不留任何日志了，同时这个表的ID就自动初化从1开始，今天我就来给大家尝试一个利用truncate清除记录之后恢复过程，希望给各位朋友解决你的问题。

实际线上的场景比较复杂，当时涉及了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=InnoDB AUTO_INCREMENT=2DEFAULT CHARSET=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;

mysql>SELECT*FROM tb_wubx;
+----+-----------+| id | name |+----+-----------+|1| 老赵赵 |+----+-----------+1ROWINSET(0.00 sec)

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

+—-+———-+
| id | name |
+—-+———-+

| 1 | 张三 |

+—-+———-+

| 2 | 李四 |

+—-+———-+

| 3 | 隔壁老王 |

+—-+———-+
+—-+———-+
| 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 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

mysql>SHOWBINARY logs;
+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 |143|| mysql-bin.000002 |261|| mysql-bin.000003 |562|| mysql-bin.000004 |1144|+------------------+-----------+4ROWSINSET(0.00 sec)

我这里有一个备份文件就是那个创建表的sql语句，位置是mysql-bin.000004 , 4
在这个案例里我只用cover住mysql-bin.000004这个文件。

mysql>SHOW binlog events IN'mysql-bin.000004';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| mysql-bin.000004 |4| Format_desc |753306|120| Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4|| mysql-bin.000004 |120| Query |753306|209|USE`wubx`; TRUNCATETABLE tb_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: 91 flags: 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: 91 flags: STMT_END_F || mysql-bin.000004 |602| Xid |753306|633| COMMIT /* xid=1074 */|| mysql-bin.000004 |633| Query |753306|722|USE`wubx`; TRUNCATETABLE tb_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: 92 flags: 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: 92 flags: STMT_END_F || mysql-bin.000004 |1113| Xid |753306|1144| COMMIT /* xid=1084 */|+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19ROWSINSET(0.00 sec)

看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。
这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即：

mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx
mysql -S /tmp/mysql.sock re_wubx;
mysql>SELECTCOUNT(*)FROM tb_wubx;
+----------+|COUNT(*)|+----------+|3|+----------+1ROWINSET(0.02 sec)
mysql>SELECT*FROM tb_wubx;
+----+--------------+| id | name |+----+--------------+|1| 张三 ||2| 李四 ||3| 隔壁老王 |+----+--------------+3ROWSINSET(0.00 sec)
mysql>INSERTINTO tb_wubx(name)SELECT name FROM wubx.tb_wubx;
Query OK,1ROW affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>RENAMETABLE wubx.tb_wubx TO wubx.bak_tb_wubx;
Query OK,0ROWS affected (0.04 sec)
mysql>RENAMETABLE re_wubx.tb_wubx TO wubx.tb_wubx;
Query OK,0ROWS affected (0.03 sec)
mysql>SELECT*FROM wubx.tb_wubx;
+----+--------------+| id | name |+----+--------------+|1| 张三 ||2| 李四 ||3| 隔壁老王 ||4| 老赵赵 |+----+--------------+4ROWSINSET(0.00 sec)

恢复完成。
想一想，如果我跳过那个truncate继续执行那些binlog会怎么样

总结：从这次数据丢失给删除之后得到一些感想了我们还是要经常对数据库进行备份了，这样可以保证我们的数据不给丢失了，同时也可以保证我们网站数据安全。

--转自