MySQL中truncate误操作后的数据恢复案例_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)

恢复完成。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
mysql truncate 恢复、mysql误删数据恢复、mysql误删表恢复、mysql数据库误删恢复、mysql 误删除 恢复，以便于您获取更多的相关知识。

时间： 2024-08-22 15:17:26

MySQL中truncate误操作后的数据恢复案例_Mysql的相关文章

Mysql中的触发器简单介绍及使用案例_Mysql

什么是触发器? 触发器是数据库的一个程序,他是用来监听着数据表的某个行为,一旦数据表的这个行为发生了,马上执行相应的sql语句 触发器的语法结构: create trigger 触发器的名称触发器事件 on 监听的表名 for each row 行为发生后执行的sql语句 触发器事件组成::两部分组成: 触发器事件发生的时间-----是在监听的表的行为 after before 常用的是after 触发器执行的内容:增删改 创建order 表的时候,需要注意,因为order在mysql中是一个关

MySQL误操作后快速恢复数据的方法_Mysql

摘要: 利用binlog闪回误操作数据. 基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. 传统解法 用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态.然后跳过误操作的SQL,再继续应用binlog.此法费时费力,不值得再推荐. 利用binlog2sql快速闪回 首先,确认你的MySQ

MySQL数据库误操作后快速回滚的方法_Mysql

基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. binlog2sql快速回滚 首先,确认你的MySQL server开启了binlog,设置了以下参数: [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_siz

MySQL误操作后怎么恢复数据？MySQL误操作后快速恢复数据的教程

摘要: 利用binlog闪回误操作数据. 基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. 传统解法 用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态.然后跳过误操作的SQL,再继续应用binlog.此法费时费力,不值得再推荐. 利用binlog2sql快速闪回 首先,确认你的MySQ

详解MySQL误操作后怎样进行数据恢复_Mysql

一.开启binlog. 首先查看binlog是否开启 mysql> show variables like "log_bin"; +---------------+-------+ |Variable_name | Value +---------------+-------+ | log_bin OFF +---------------+-------+ 1 row in set (0.00 sec) 值为OFF,需开启,开启binlog方式如下: #vim /etc/my.c

【MySQL】恢复误操作的方法

一 .前言 本周接二连三的出现开发人员在测试环境和生产误操作导致数据库误删除/更新,对DBA而言,回滚数据着实是一件头疼的事情,凡涉及到恢复线上数据必然对应用带来一定的影响.大多数情况是开发误操作delete数据,update多数行,根据之前的操作经验,本文介绍常用的恢复方法. 写本文的时候 Monogdb 也被曝出有被利用安全漏洞,数据被删除了,希望各位DBA/安全相关人员及时查看自己负责的业务数据库安全相关问题,保护好自己的数据. 二.常用的恢复方式 2.1 利用备份恢复 使用这种方式的前提

Windows中哪些误操作会损害硬盘

  一般地,现在的硬盘都加入了S.M.A.R.T的自动侦测技术,以便让用户能在致命的故障出现前看到先兆,备份好数据--但这都是针对正常操作情况下设计的,如果用户的使用方法如下所列,故障的出现将可能是无先兆的,也就是突然死亡. 一.在开机和关机的时候突然强行切断电源 现在的电源及主板的ATX设计,普遍实现了软关机的功能.这种设计让人倍感方便.但是软关机要先完成一系列的关闭正在运行的程序的操作,加上各种操作系统及各主板厂家设计上的兼容性.BUG,Windows在进行关闭应用程序然后切断电源的时候经常

mysql之delete删除记录后数据库大小不变_Mysql

当DELETE后面跟条件的时候,则就会出现这个问题 delete from table_name where 条件 删除数据后,数据表占用的空间大小不会变. 不跟条件直接delete的时候. delete from table_name 清除了数据,同时数据表的空间也会变为0 如果已经删除了表数据的很大一部分,或者有很多变化和变长表行(VARCHAR表,VARBINARY.BLOB或文本列)进行了更改,因为删除操作后在数据文件中留下碎片所致.DELETE只是将数据标识位删除,并没有整理数据文件,

MySQL数据库InnoDB引擎下服务器断电数据恢复方法_Mysql

说明: 线上的一台MySQL数据库服务器突然断电,造成系统故障无法启动,重新安装系统后,找到之前的MySQL数据库文件夹. 问题: 通过复制文件的方式对之前的MySQL数据库进行恢复,发现在程序调用时找不到数据库中的表,造成网站无法正常访问. 分析: 1.MySQL数据库,使用拷贝文件方式来恢复数据库,只支持MyISAM引擎: 2.如果有数据库或数据表使用了InnoDB引擎,恢复的时候,必须连同MySQL数据库目录下的ibdata1文件一起拷贝过来. 解决办法: 1.停止MySQL服务 serv