记录一次truncate操作数据恢复

标签: MySQL解错方案 truncate 恢复 | 发表时间:2014-04-30 06:48 | 作者:OurMySQL
出处:http://ourmysql.com

实际线上的场景比较复杂,当时涉及了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 | 隔壁老王 |
+—-+———-+

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

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会怎么样 ?

猜您喜欢

相关 [记录 truncate 操作数] 推荐:

记录一次truncate操作数据恢复

- - OurMySQL
实际线上的场景比较复杂,当时涉及了truncate, delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入. 这里为了简单说明,只拿弄一个简单的业务场景举例. 测试环境: Percona-Server-5.6.16. 日志格式: mixed 没起用gtid.

hadoop记录

- - 开源软件 - ITeye博客
MapReduce的特征 1. Map1结果与Map2结果重叠现象. (传统的分布式计算无法解决) 方案:Map2与Map2原封不动的把数据传到Reduce; 问题:结果Map啥事没干,Reduce最终累死, 分而治之成为了空谈. reduce任务工作过程: reduce是将map的输出作为reduce的输入,只要有一个map任务执行完就会有reduce任务开始执行.

A5版聊记录

- 章明 - SEM 一家之言
这是上周接受A5版聊的记录,感谢光年论坛网友 cx69 的整理. 由于很多人不逛论坛的,所以发到这个博客上. 我觉得这个访谈的内容对大家有帮助. 问:嘉宾你好,嘉宾的是科学的SEOer,首先问,什么样的SEO才是科学的?平时站长用的SEO方法都是不科学的吗?科学是相对什么而言的?. 嘉宾说,提出“科学的SEO”的说法,就是要来抵抗那种“猜谜语式的SEO”.

杭州归来 – interACT记录

- Millie - Plidezus'sky
纪念拍了卖2.0在AppStore中得到推荐~ 没下的同学点此下载咯~. 和同事一起参加了淘宝技术嘉年华,其中interACT中百度MUX的同学分享都是干货,很是受用. 碎碎的记录了一些觉得重要的地方,在此整理下. 虽然举的是杭州出租车的例子,但是每个产品也是如此,用户能记住的都是那些让他们极度不爽的地方.

Sony LT26i 刷机记录

- - 依云's Blog
声明:本文以期有用的目的写作,不保证本文所述操作能够部分或者完全地满足他人的需求,也不保证其不会给他人造成损失. 按本文操作者,如对其生命财产造成任何损失请自理,在法律许可的范围内本人不承担责任. 注意:除 SD 卡数据外,其它数据均可能永久性丢失. 首先,开启手机的 USB 调试模式. Sony Xperia S 版 ROM,将其放到 SD 卡根目录: adb push cm-9.1.0-nozomi.zip /mnt/sdcard.

mysql记录耗时的sql

- - 数据库 - ITeye博客
mysql记录耗时的sql. mysql可以把耗时的sql或未使用索引的sql都记录在slow log里,供优化分析使用. 1.mysql慢查询日志启用:. mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢. 这说明slow log功能没有启用,要启用需要修改mysql的配置文件,在配置文件"[mysqld]"里添加如下俩参数:.

Kettle运行日志记录

- - 开源软件 - ITeye博客
原创文章,转载请注明出处:http://qq85609655.iteye.com/blog/2173882. 在kettle.properties中,可以定义参数. 来全局设置,记录kettle的job和trans日志,. 记录日志,会进行锁表,在kettle作业非常多的情况下,容易造成表锁,以及队列等待job日志记录的情况严重影响效率.

springboot aop日志记录

- - 编程语言 - ITeye博客
一、POM增加AOP JAR包. 三、SysAspect类. 注:@annotation(cn.com.hfai.controller.system.Logweb) 一定要指定Logweb类. 四、在Controller类的方法之上加上注解 @Logweb 即可. 注:这个只是打印在控制台上,若想放到数据库中,则需要增加操作数据库的业务代码.

记录用户体验细节

- 競 - rank's technical notes
突然有个想法,想把原来我发现的,看到的,听到的一些好的用户体验列出来,这些经验对于做终端的开发,都是值得我们学习的. 现在能想到的几条,以后想起来慢慢加上来. windows虽然IE做得不怎么样,但你可知道触发拖动事件不是mousedown开始就叫拖拽(DragDrop),而是有两个条件,一个是mousedown后1秒,或者鼠标移动3px后再开始触发拖动的么.

钱云会案证人调查记录

- 貓 - 王克勤