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

作者：吴炳锡字体：[增加减小] 来源：互联网时间：2017-05-11

吴炳锡通过本文主要向大家介绍了mysql truncate,mysql truncate table,mysql中truncate,mysql中truncate用法,mysql truncate函数等相关知识,希望本文的分享对您有所帮助

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

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

表结构如下：

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,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

</div>

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

–t1时间程序写入：

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

</div>

–t2时间某个人员失误

truncate table tb_wubx;

</div>

–t3时间程序写入

insert into tb_wubx(name) values(‘老赵');
update tb_wubx set name='老赵赵' where id=1;

</div>

现在表里的数据情况：

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)

</div>

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

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

</div>

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

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

</div>

而且线上的恢复那个表时和序序开发人员了解才知道，原来那个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> 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)

</div>

我这里有一个备份文件就是那个创建表的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`; 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

分享到：QQ空间新浪微博腾讯微博微信百度贴吧 QQ好友复制网址打印

您可能想查找下面的文章:

2018-12-05mysql 存储过程的问题
2018-12-05MySQL怎么去除重复数据？
2017-05-11MySQL中的RAND()函数使用详解
2018-12-05虚拟机linux中装mysql数据库
2018-12-05MYSQL学习总结（六）：my.cnf 各参数配置及根据状态优化
2018-12-05关于数据库中架构的相关介绍
2017-05-11MySQL转换Oracle的需要注意的七个事项
2018-12-05MySQL数据库备份与恢复方法
2018-12-05ORACLE 自动提交问题
2018-12-05 MySQL安装教程图文详解

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

您可能想查找下面的文章:

相关文章

文章分类

最近更新的内容