Mysql实例MySQL中truncate误 *** 作后的数据恢复案例

概述介绍《Mysql实例MySQL中truncate误 *** 作后的数据恢复案例》开发教程，希望对您有用。

《MysqL实例MysqL中truncate误 *** 作后的数据恢复案例》要点：
本文介绍了MysqL实例MysqL中truncate误 *** 作后的数据恢复案例，希望对您有用。如果有疑问，可以联系我们。

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

MysqL学习测试环境： Percona-Server-5.6.16
日志格式： mixed 没起用gtID

MysqL学习表结构如下：

MysqL学习CREATE table `tb_wubx` (`ID` int(11) NOT NulL auto_INCREMENT,`name` varchar(32) DEFAulT NulL,PRIMARY KEY (`ID`)) ENGINE=InnoDB auto_INCREMENT=2 DEFAulT CHARSET=utf8 CREATE table `tb_wubx` (`ID` int(11) NOT NulL auto_INCREMENT,PRIMARY KEY (`ID`)) ENGINE=InnoDB auto_INCREMENT=2 DEFAulT CHARSET=utf8

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

MysqL学习Ct1时间 程序写入：

MysqL学习insert into tb_wubx(name) values(‘张三'),(‘李四');insert into tb_wubx(name) values(‘隔壁老王');

MysqL学习Ct2时间 某个人员失误

MysqL学习truncate table tb_wubx;

MysqL学习Ct3时间 程序写入

MysqL学习insert into tb_wubx(name) values(‘老赵');update tb_wubx set name='老赵赵' where ID=1;

MysqL学习现在表里的数据情况：

MysqL学习MysqL>select * from tb_wubx;+----+-----------+| ID | name |+----+-----------+| 1 | 老赵赵 |+----+-----------+1 row in set (0.00 sec) MysqL>select * from tb_wubx;+----+-----------+| ID | name |+----+-----------+| 1 | 老赵赵 |+----+-----------+1 row in set (0.00 sec)

MysqL学习可以见truncate table *** 作后,表的自增ID又变更为从1开始,原来写入的数据应该是：

MysqL学习+―-+―――C+| ID | name |+―-+―――C+| 1 | 张三 |+―-+―――C+| 2 | 李四 |+―-+―――C+| 3 | 隔壁老王 |+―-+―――C+

MysqL学习如果没生truncate table *** 作,实际的数据应该为：

MysqL学习+―-+―――C+| ID | name |+―-+―――C+| 1 | 张三 |+―-+―――C+| 2 | 李四 |+―-+―――C+| 3 | 隔壁老王 |+―-+―――C+| 4 | 老赵赵 |+―-+―――C+

MysqL学习而且线上的恢复那个表时和序序开发人员了解才知道,原来那个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 (相当于恢复了备份,过程省略)

MysqL学习MysqL> show binary logs;+------------------+-----------+| Log_name | file_size |+------------------+-----------+| MysqL-bin.000001 | 143 || MysqL-bin.000002 | 261 || MysqL-bin.000003 | 562 || MysqL-bin.000004 | 1144 |+------------------+-----------+4 rows in set (0.00 sec) MysqL> show binary logs;+------------------+-----------+| Log_name | file_size |+------------------+-----------+| MysqL-bin.000001 | 143 || MysqL-bin.000002 | 261 || MysqL-bin.000003 | 562 || MysqL-bin.000004 | 1144 |+------------------+-----------+4 rows in set (0.00 sec)

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

MysqL学习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`; truncate table 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`; truncate table 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 */ |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19 rows in set (0.00 sec) 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`; truncate table 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`; truncate table 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 */ |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19 rows in set (0.00 sec)

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

MysqL学习MysqLbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 MysqL-bin.000004 |MysqL -S /tmp/MysqL.sock re_wubxMysqLbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 MysqL-bin.000004 |MysqL -S /tmp/MysqL.sock re_wubx

MysqL学习恢复结果如下：

MysqL学习MysqL -S /tmp/MysqL.sock re_wubx;MysqL>select count(*) from tb_wubx;+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.02 sec)MysqL>select * from tb_wubx;+----+--------------+| ID | name |+----+--------------+| 1 | 张三 || 2 | 李四 || 3 | 隔壁老王 |+----+--------------+3 rows in set (0.00 sec)MysqL>insert into tb_wubx(name) select name from wubx.tb_wubx;query OK,1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0MysqL> rename table wubx.tb_wubx to wubx.bak_tb_wubx;query OK,0 rows affected (0.04 sec)MysqL> rename table re_wubx.tb_wubx to wubx.tb_wubx;query OK,0 rows affected (0.03 sec)MysqL> select * from wubx.tb_wubx;+----+--------------+| ID | name |+----+--------------+| 1 | 张三 || 2 | 李四 || 3 | 隔壁老王 || 4 | 老赵赵 |+----+--------------+4 rows in set (0.00 sec) MysqL -S /tmp/MysqL.sock re_wubx;MysqL>select count(*) from tb_wubx;+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.02 sec) MysqL>select * from tb_wubx;+----+--------------+| ID | name |+----+--------------+| 1 | 张三 || 2 | 李四 || 3 | 隔壁老王 |+----+--------------+3 rows in set (0.00 sec) MysqL>insert into tb_wubx(name) select name from wubx.tb_wubx;query OK,1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0 MysqL> rename table wubx.tb_wubx to wubx.bak_tb_wubx;query OK,0 rows affected (0.04 sec) MysqL> rename table re_wubx.tb_wubx to wubx.tb_wubx;query OK,0 rows affected (0.03 sec) MysqL> select * from wubx.tb_wubx;+----+--------------+| ID | name |+----+--------------+| 1 | 张三 || 2 | 李四 || 3 | 隔壁老王 || 4 | 老赵赵 |+----+--------------+4 rows in set (0.00 sec)

MysqL学习恢复完成.

总结

以上是内存溢出为你收集整理的Mysql实例MySQL中truncate误 *** 作后的数据恢复案例全部内容，希望文章能够帮你解决Mysql实例MySQL中truncate误 *** 作后的数据恢复案例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。