MySQL中truncate误操作后的数据恢复案例
作者：吴炳锡 发布时间：2024-01-12 20:45:56
标签：MySQL
实际线上的场景比较复杂，当时涉及了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
基于某个时间点有一个备份或是有全量的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 | 老赵赵 |
+----+-----------+
1 row in set (0.00 sec)
mysql>select * from tb_wubx;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 老赵赵 |
+----+-----------+
1 row in set (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 （相当于恢复了备份，过程省略）
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)
我这里有一个备份文件就是那个创建表的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.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)
看到这个表刚开始就发生一次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
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>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 -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)
恢复完成。
0
投稿猜你喜欢
Python时间戳操作很多，每次用点时候总是去查，查的麻烦，现在自己也好好归纳一下。我现在刚好有个需求需要获取当天零点时间戳，但是网上查的大- 最近接触了很多数据库的东西，本来是一直接触的是sql server，不过由于项目需要就开始对mysql进行了连接。下面就让我这个菜鸟浅谈下经
- 最近在工作上用到Python的pandas库来处理excel文件，遇到列转行的问题。找了一番资料后成功了，记录一下。1. 如果需要 * 的只有
- java JSP开发之Spring中Bean的使用在传统的Java应用中，bean的生命周期很简单。使用Java关键字new进行bean实例
- Vue 中使用v-for语句抛出错误的解决方案今天在维护以前的项目的时候遇见了下面的报错情况，开始我以为是因为jQuery和Vue冲突了，所
- 本章的前面讨论如何使用SQL向一个表中插入数据。但是，如果你需要向一个表中添加许多条记录，使用SQL语句输入数据是很不方便的。幸运的是，My
- 这篇论坛文章（赛迪网技术社区）根据网友的个人实践扼要的讲解了将MySQL 5.0下的数据导入到MySQL 3.23中的具体方法及步骤，详细内
- 前言python对动态验证码、滑动验证码的降噪和识别，在各种自动化操作中，我们经常要遇到沿跳过验证码的操作，而对于验证码的降噪和识别，的确困
- 前言Vscode是是一个强大的跨平台工具，我自己电脑是mac，公司电脑是win而且是内部环境，导致公司安装软件很费劲。好在vscode许多插
- 题目：解法一：比较元素是否相等思路说明：这种应该是普通人最先想到的解法，先获取到数组之后进行有小到大排序，然后初始化一个min=0（代表新数
- 本文使用TensorFlow实现最简单的线性回归模型，供大家参考，具体内容如下线性拟合y=2.7x+0.6，代码如下:import tens
- 本文实例讲述了python获取Linux下文件版本信息、公司名和产品名的方法，分享给大家供大家参考。具体如下：区别于前文所述。本例是在lin
- 1.切片# 切片：取list或tuple的部分元素nameList = ["Willard","ChenJD&
- 本文实例讲述了js+html5实现canvas绘制镂空字体文本的方法。分享给大家供大家参考。具体实现方法如下：<!DOCTYPE ht
- 1.前言前面学完了SQL Server的基本语法，接下来学习如何在程序中使用sql，毕竟不能在程序中使用的话，实用性就不那么大了。2.最基本
- 一、基础介绍Go 是静态（编译型）语言，是区别于解释型语言的弱类型语言（静态：类型固定，强类型：不同类型不允许直接运算）例如 python
- 1.查询高于平均价格的商品名称：SELECT item_name FROM ebsp.product_mark
- 本文实例为大家分享了python实现UDP文件传输的具体代码，供大家参考，具体内容如下UDP协议下文件传输：服务端import socket
- 本文实例讲述了php防止sql注入中过滤分页参数的方法。分享给大家供大家参考。具体分析如下：就网络安全而言，在网络上不要相信任何输入信息，对
- PHP levenshtein() 函数实例计算两个字符串之间的 Levenshtein 距离：<?php echo levensht
