MySQL中truncate误操作后的数据恢复案例
作者:吴炳锡 发布时间:2024-01-12 20:45:56
实际线上的场景比较复杂,当时涉及了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)
恢复完成。
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 方法一:queue = forms.ModelChoiceField(label=u'队列',queryset=Queue.
- #!/usr/bin/env python# -*- coding:utf-8 -*-"""老规矩以下方法环境
- 边缘检测Canny边缘检测器是一种被广泛使用的算法,并被认为是边缘检测最优的算法,该方法使用了比高斯差分算法更复杂的技巧,如多向灰度梯度和滞
- 前文学习:python数据结构:数据类型.python数据结构输入输出及控制和异常.今天我们来学习面向对象编程,面向对象这种编程方式非常重要
- test.asp 测试演示文件clsrsa.asp 实现rsa加密与解密的vbs类文件下面是代码:1. test.asp<%rem 文
- 环境 python -m venv venvvenv\Scripts\activate 激活环境失败 解决方案输入get-execution
- 引言您可以使用df.loc()函数在Pandas DataFrame的末尾添加一行:#add row to end of DataFrame
- Google Chrome 的发布,使我们更加的注重基于 WebKit 核心的浏览器的表现情况,但我们很多时候“不小心”就会出现问题。考虑下
- 经常会看到这种弹出层背景变暗的效果,感觉手痒于是自己写了一个基于jquery的弹出层类。我习惯先写好结构和样式,然后再写js交互效果。结构定
- 绘制双变量联合分布图有时我们不仅需要查看单个变量的分 布,同时也需要查看变量之间的联系, 往往还需要进行预测等。这时就需要用到双变量联合分布
- 鸭子类型基本定义首先Python不支持多态,也不用支持多态,python是一种多态语言,崇尚鸭子类型。以下是 * 中对鸭子类型得论述:在程
- 即使MyISAM表格式非常可靠(SQL语句对表做的所有改变在语句返回之前被写下),如果下列任何事件发生,你依然可以获得损坏的表:&m
- 本文通过调取高德行政区划查询接口,获取最新的数据信息(省、市、区、经纬度、行政级别、城市编码、行政编码等),并通过mysql.connect
- python,pycharm的环境变量设置官网下载安装python解释器时,如果忘记勾选添加到环境变量[add to path],可进行如下
- 大家好,今天跟大家分享一个用Python实现的学生学籍管理系统:该代码主体由五个函数组成:1.add_stu() 添加2.del_stu()
- 如何判断一个对象是可迭代对象? 方法是通过collections模块的Iterable类型判断:>>> from coll
- 前言关于 Python 中的pass语句,它似乎很简单(只有 4 个字母),即使是没有任何编程经验的初学者也能很快地掌握它的用法。简单而言,
- 经常在网站上看到诸如www.abc.com/?news或者www.abc.com/?id=123这样的网址,一开始觉得很神秘,其实现在看多了
- 图像(包括动图)是传递信息的一种高效方式,往往能增强表象、记忆与思维等方面的反应强度。所谓一图胜千言,说的就是这个道理。今天为大家整理了十张
- 本文主要介绍了ASP连接11种数据库的常用语法,详细内容请参考下文:1.Access数据库的DSN-less连接方法:set adocon=