数据恢复 2023-07-12

如何在MySQL误删数据后进行恢复？ (mysql 误删除数据恢复)

MySQL是一种常用的关系型数据库管理系统，但使用过程中，不可避免地会出现误删数据的情况。误删数据可能会导致严重后果，如果没有及时恢复，将会造成严重的损失。为了避免这一情况发生，本文将介绍如何在MySQL误删数据后进行恢复。

一、使用备份进行恢复

MySQL有一个非常重要的功能，即备份。备份在MySQL中非常容易实现，只需要利用mysqldump命令，就可以将整个数据库备份到指定的文件中。因此，在MySQL误删数据后，我们可以通过备份来进行恢复。

1.查找备份文件

我们需要查找备份文件。在我们备份数据库时，应该将备份文件存储到一个安全的地方，可以是本地硬盘或者其他服务器。当误删数据时，如果我们没有改变备份文件位置，并且备份文件没有损坏，我们可以通过备份文件来恢复数据库。

2.恢复备份文件

如果我们找到备份文件，我们可以通过以下步骤来进行恢复：

（1）关闭MySQL server。

$ sudo /etc/init.d/mysql stop

（2）删除误删除的数据。

$ mysql -u root -p

mysql> drop database dbname;

（3）从备份文件中恢复数据库。

$ mysql -u root -p dbname

其中，dbname是你要恢复的数据库名称，backupfile.sql是备份文件的名称。

（4）重启MySQL server。

$ sudo /etc/init.d/mysql start

通过备份进行恢复的方法简单易行，但是也存在一些问题。进行备份的时间间隔和数据的持久度是一个问题。如果备份不够及时，误删数据后数据的丢失可能无法弥补。如果备份所存储的位置也受到了误删除的影响，我们将无法使用备份进行恢复。

二、使用MySQL的Binlog进行恢复

MySQL具有一个非常重要的日志功能，即Binlog（Binary log），二进制日志。在MySQL中，Binlog记录了所有的数据库修改操作，即使误删除了某个数据，也可以通过Binlog进行恢复。

Binlog记录了数据库执行的所有语句的序列，包括select、update、insert、delete等操作。当一个数据被删除时，它同时也在Binlog中记录了删除这个数据的语句，因此我们可以通过分析Binlog来找到误删数据的记录，并且进行恢复。

使用Binlog进行恢复分为如下几个步骤：

1.查找误删除的Binlog

我们需要查找误删除的Binlog。在MySQL中，Binlog文件通常存储在与数据目录相同的位置。使用以下命令可以查找Binlog目录：

$ mysql -u root -e “SHOW VARIABLES LIKE ‘log_bin_basename'”

这条命令将输出Binlog所在的目录，如“/var/lib/mysql/mysql-bin”。

2.查找误删数据的记录

找到Binlog所在的目录后，我们需要找到误删数据的记录。在MySQL中，可以使用mysqlbinlog命令来查看Binlog的内容。运行以下命令将查看所有的Binlog：

$ mysqlbinlog /var/lib/mysql/mysql-bin.000001

其中，mysql-bin.000001是Binlog的名称，可以根据需要进行更换。

通过查看Binlog，我们可以找到删除误删除数据的操作记录，例如：

# at 248

#160522 11:13:04 server id 123 end_log_pos 276 Delete_rows: table id 21 flags: STMT_END_F

### DELETE FROM `mytable` WHERE `id`=100 ###

这个记录展示了一个delete语句，该语句通过table id 21标识了数据所在的表格，可以通过该标识来查找误删除数据的记录。

3.恢复误删除的数据

找到误删除数据的记录后，我们可以通过以下步骤来进行数据恢复：

（1）获得误删除的数据。

$ mysqlbinlog –start-position= –stop-position=yyy /var/lib/mysql/mysql-bin.000001 | \

grep -E “INSERT INTO \`mytable|UPDATE \`mytable” > restore.sql

其中，和yyy是误删除操作在Binlog文件中的位置，在查找误删除记录后，我们可以获得这些位置数据。restore.sql是我们要恢复的SQL语句的文件名。

（2）恢复数据。

$ mysql -u root -p dbname

这个命令将会执行从Binlog文件中获得的恢复语句并恢复数据到数据库中。

使用Binlog进行恢复的方法需要一定的技能，但是它的优点在于可以准确地找到误删除的数据，并且不需要备份文件的支持。

在本文中，我们介绍了两种在MySQL误删数据后进行恢复的方法。备份方法简单易行，但是可能会存在时间间隔和持久度的问题。Binlog方法具有恢复准确度高的优势，但是需要具有一定的技能。因此，在误删数据后，可以根据实际情况选择使用哪种方法进行恢复。为了避免误删除数据的情况发生，需要在平时的数据库使用过程中注意备份和日志的记录，减少误删数据的风险。

相关问题拓展阅读：

mysql 数据被删除能恢复吗
mysql数据库被删除了，怎么恢复吗

mysql 数据被删除能恢复吗

1 找个别的机器安装个同版本的mysql或从已安装同版本的其他机器上（非同版本的也可以试下）：

拷贝 mysql/data/mysql 目录到你的mysql/data/ 下吧

2 试着启动mysql服务，如果能启动了，理论上应该丢失的只有用户、授权等一些系统掘槐信息，不影响你的使用的数据；

如果不能启动，看错误日志，争取启动了。

3 赶紧把数据备份一份出来，重新把判渗友所有喊兄库（只是你后来创建的业务相关的库，不包括mysql库）都删了，重新导入一遍。理论上不这样也可以，但只是非生产重要的环境下。

4 重新做用户授权。

每个 DBA 是不是粗皮都有过删库的经历？删库了没有备份怎么办？备份恢复后无法启动服务什么情况？表定义损坏数据无法读取怎么办？逗凳液

我曾遇到某初创互联网企业，因维护人员不规范的备份恢复操作，导致系统表空间文件被初始化，上万张表无法读取，花了数小时才抢救回来。

当你发现数据无法读取时，也许并非数据丢失了，可能是 DBMS 找不到描述数据的信息。

背景

先来了解下几张关键的 InnoDB 数据字典表，它们保存了部分表定义信息，在我们恢复表结构时需要用到。

SYS_TABLES 描述 InnoDB 表信息CREATE TABLE `SYS_TABLES` (`NAME` varchar(255) NOT NULL DEFAULT ”, 表名`ID` bigint(20) unsigned NOT NULL DEFAULT ‘0’, 表id`N_COLS` int(10) DEFAULT NULL,`TYPE` int(10) unsigned DEFAULT NULL,`MIX_ID` bigint(20) unsigned DEFAULT NULL,`MIX_LEN` int(10) unsigned DEFAULT NULL,`CLUSTER_NAME` varchar(255) DEFAULT NULL,`SPACE` int(10) unsigned DEFAULT NULL, 表空间idPRIMARY KEY (`NAME`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;SYS_INDEXES 描述 InnoDB 索引信息CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT ‘0’, 与sys_tables的id对应山物 `ID` bigint(20) unsigned NOT NULL DEFAULT ‘0’, 索引id `NAME` varchar(120) DEFAULT NULL,索引名称 `N_FIELDS` int(10) unsigned DEFAULT NULL, 索引包含字段的个数 `TYPE` int(10) unsigned DEFAULT NULL, `SPACE` int(10) unsigned DEFAULT NULL, 存储索引的表空间id `PAGE_NO` int(10) unsigned DEFAULT NULL, 索引的root page id PRIMARY KEY (`TABLE_ID`,`ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;SYS_COLUMNS 描述 InnoDB 表的字段信息CREATE TABLE `SYS_COLUMNS` ( `TABLE_ID` bigint(20) unsigned NOT NULL, 与sys_tables的id对应 `POS` int(10) unsigned NOT NULL, 字段相对位置 `NAME` varchar(255) DEFAULT NULL, 字段名称 `MTYPE` int(10) unsigned DEFAULT NULL, 字段编码 `PRTYPE` int(10) unsigned DEFAULT NULL, 字段校验类型 `LEN` int(10) unsigned DEFAULT NULL, 字段字节长度 `PREC` int(10) unsigned DEFAULT NULL, 字段精度 PRIMARY KEY (`TABLE_ID`,`POS`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;SYS_FIELDS 描述全部索引的字段列CREATE TABLE `SYS_FIELDS` ( `INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;./storage/innobase/include/dict0boot.h 文件定义了每个字典表的 index id，对应 id 的 page 中存储着字典表的数据。

这里我们需要借助 undrop-for-innodb 工具恢复数据，它能读取表空间信息得到 page，将数据从 page 中提取出来。

# wget yum install -y gcc flex bison# make# make sys_parser

# ./sys_parser 读取表结构信息

sys_parser databases/table

stream_parser 读取 InnoDB page 从 ibdata1 或 ibd 或分区表

# ./stream_parserYou must specify file with -f optionUsage: ./stream_parser -f Where: -hPrint this help -V or -g – Print debug information -s size – Amount of memory used for disk cache (allowed examples 1G 10M). Default 100M -Tretrieves only pages with index id = NM (N – high word, M – low word of id) -t size – Size of InnoDB tablespace to scan. Use it only if the parser can’t determine it by himself.

c_parser 从 innodb page 中读取记录保存到文件

# ./c_parserError: Usage: ./c_parser -4|-5|-6 -f -t table.sql Where -f — InnoDB page or directory with pages(all pages should have same index_id) -t — CREATE statement of a table -o — Save dump in this file. Otherwise print to stdout -l — Save SQL statements in this file. Otherwise print to stderr -h — Print this help -d — Process only those pages which potentially could have deleted records (default = NO) -D — Recover deleted rows only (default = NO) -U — Recover UNdeleted rows only (default = YES) -V — Verbose mode (lots of debug information)innodb_datafile is in REDUNDANT formatinnodb_datafile is in COMPACT formatinnodb_datafile is in MySQL 5.6 format -T — retrieves only pages with index id = NM (N – high word, M – low word of id) -b — Directory where external pages can be found. Usually it is pages-XXX/FIL_PAGE_TYPE_BLOB/ -i — Read external pages at their offsets from . -p prefix — Use prefix for a directory name in LOAD DATA INFILE command

接下来，我们演示场景的几种数据恢复场景。

场景1：drop table

是否启用了 innodb_file_per_table 其恢复方法有所差异，当发生误删表时，应尽快停止MySQL服务，不要启动。若 innodb_file_per_table=ON，更好只读方式重新挂载文件系统，防止其他进程写入数据覆盖之前块设备的数据。

如果评估记录是否被覆盖，可以表中某些记录的作为关键字看是否能从 ibdata1 中筛选出。

# grep WOODYHOFFMAN ibdata1

Binary file ibdata1 matches

也可以使用 bvi（适用于较小文件）或 hexdump -C（适用于较大文件）工具

以表 sakila.actor 为例CREATE TABLE `actor` (`actor_id` allint(5) unsigned NOT NULL AUTO_INCREMENT,`first_name` varchar(45) NOT NULL,`last_name` varchar(45) NOT NULL,`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`actor_id`),KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

首先恢复表结构信息1. 解析系统表空间获取 page 信息

./stream_parser -f /var/lib/mysql/ibdata1

2. 新建一个 schema，把系统字典表的 DDL 导入

cat dictionary/SYS_* | mysql recovered

3. 创建恢复目录

mkdir -p dumps/default

4. 解析系统表空间包含的字典表信息，

./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/01.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/02.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/03.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/04.page -t dictionary/SYS_FIELDS.sql > dumps/default/SYS_FIELDS 2> dumps/default/SYS_FIELDS.sql

5. 导入恢复的数据字典

cat dumps/default/*.sql | mysql recovered

6. 读取恢复后的表结构信息

./sys_parser -pmsandbox -d recovered sakila/actor

由于 5.x 版本 innodb 引擎并非完整记录表结构信息，会丢失 AUTO_INCREMENT 属性、二级索引和外键约束， DECIMAL 精度等信息。

若是 mysql 5.5 版本 frm 文件被从系统删除，在原目录下 touch 与原表名相同的 frm 文件，还能读取表结构信息和数据。若只有 frm 文件，想要获得表结构信息，可使用 mysqlfrm –diagnostic /path/to/.frm，连接 mysql 会显示字符集信息。

innodb_file_per_table=OFF

因为是共享表空间模式，数据页都存储在 ibdata1，可以从 ibdata1 文件中提取数据。

1. 获取表的 table id，sys_table 存有表的 table id，sys_table 表 index id 是1，所以从01.page 获取表 id./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/01.page -t dictionary/SYS_TABLES.sql | grep sakila/actorB28 2AD4D SYS_TABLES “sakila/actor” 0 “”B28 2AD4D SYS_TABLES “sakila/actor” 0 “” 0

2. 利用 table id 获取表的主键 id，sys_indexes 存有表索引信息，innodb 索引组织表，找到主键 id 即找到数据，sys_indexes 的 index id 是3，所以从03.page 获取主键 id

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/03.page -t dictionary/SYS_INDEXES.sql | grepBABCA SYS_INDEXES”PRIMARY”BAC3C SYS_INDEXES”idx_actor_last_name”BABCA SYS_INDEXES”PRIMARY”BAC3C SYS_INDEXES”idx_actor_last_name”

3. 知道了主键 id，就可以从对应 page 中提取表数据，并生成 sql 文件。

./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/76.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

4. 最后导入恢复的数据

cat dumps/default/*.sql | mysql sakila

更多详细情况点击

网页链接

mysql数据库被删除了，怎么恢复吗

你这个问题就不好办了，因为数据文件要随时改变圆基如。所以你恢复是会有锋衡很多同名的文件，一定要确定橘启是最新的那个才有可能恢复。如果确认是最新的数据文件也无法导入到数据库中的话，就没有办法恢复了！！

除非你数据很重要，由专业人员将你的文件修复！

可以安装个电脑管家在电脑上面

然后选卜腔择工具箱，可以看到有一个文件数据恢型唤衫复工具

使用这个工具，可以检测出电脑里面误删的文件链或，然后进行恢复

mysql应该难恢复了，oracle才有垃圾回收站，可以从recyclebin中弄回来

mysql 没有ctrl+z