Ask Maclean

DBRECOVER FOR MYSQL 用户手册

DBRECOVER FOR MYSQL是一个MySQL数据库(Innodb)恢复工具，该工具软件可以在MySQL没有备份的情况下，针对实例崩溃，Inoodb字典损坏无法启动数据库实例，DROP DATABASE，DROP TABLE，TRUNCATE TABLE,DELETE TABLE，磁盘/文件系统损坏等场景恢复数据库数据。

 

下载连接：https://zcdn.parnassusdata.com/dbrecover-for-mysql-2006.zip

视频教程：

 

 

  • 使用DBRECOVER恢复MySQL中被drop的database https://zcdn.parnassusdata.com/dbrecover-for-mysql-recover-case-drop-database.mp4
  • 使用DBRECOVER恢复奔溃的无法打开的MYSQL实例 https://zcdn.parnassusdata.com/dbrecover-for-mysql-recover-case-crash-instance.mp4
  • 使用DBRECOVER读取mySQL中单个frm和ibd文件中的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-read-single-ibd-frm.mp4
  • 使用DBRECOVER恢复MySQL中被使用SQL DELETE删除的数据 https://zcdn.parnassusdata.com/dbrecover%20for%20mysql%20undelete%20records.mp4
  • 使用DBRECOVER恢复MySQL的最简单例子 https://zcdn.parnassusdata.com/dbrecover%20for%20mysql%20quick%20recovery.mp4
  • 使用DBRECOVER恢复MYSQL中被使用SQL DELETE删除的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-drop-truncate-table-test.mp4
  • 使用DBRECOVER恢复因磁盘或文件系统故障而导致不可用的MySQL实例中的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-filesystem-disk-failure-recovery.mp4

 

 

 

特性：

  • 图形化界面操作，无需学习命令行
  • 支持版本覆盖MYSQL 5.1 到 MySQL 8.0(2020年5月），支持MYSQL 8.0的全新数据字典结构
  • 支持INNODB存储引擎， MYISAM支持仍在开发中
  • 支持各种故障导致的MYSQL实例崩溃下的数据恢复
  • 恢复结果为MYSQLDUMP格式的SQL文件
  • 支持对DELETE 操作的数据行恢复
  • 支持对DROP TABLE, TRUNCATE TABLE 操作的表恢复
  • 支持对DROP DataBase操作的数据库恢复
  • 支持因磁盘故障/文件系统损坏等情况下的恢复
  • 免费版支持每张表抽取1000行数据，每张表抽取100行被delete的数据
  • 基于JAVA开发，支持Windows/Redhat/Centos/Ubuntu等操作系统
  • 支持对FRM文件的恢复，将FRM文件解析为CREATE TABLE的SQL语句
  • 支持对LOB/TEXT等大对象的恢复

 

针对MySQL数据库无法打开的场景的恢复

 

此场景下用户尝试启动MySQL实例，但MySQL实例可能因数据库本身损坏而CRASH进程崩溃。

相关报错可能如下：

 

InnoDB: Waiting for the background threads to start
InnoDB: Error: tablespace size stored in header is 3712 pages, but
InnoDB: the sum of data file sizes is only 3072 pages
InnoDB: Cannot start InnoDB. The tail of the system tablespace is
InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
InnoDB: inappropriate way, removing ibdata files from there?
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
InnoDB: a startup if you are trying to recover a badly corrupt database.


InnoDB: Assertion failure in thread 3876 in file ha_innodb.cc line 17352
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.


InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.


InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery


[ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace database/table uses space ID: 882 at filepath

[Note] InnoDB: Starting crash recovery.
[ERROR] InnoDB: Tablespace 11904 was not found at ./example_db/example1.ibd.
[ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
[ERROR] InnoDB: Tablespace 11905 was not found at ./example_db/example2.ibd.
[ERROR] InnoDB: Cannot continue operation.


InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1

InnoDB: Database page corruption on disk or a failed file read of page 660. A table cannot be properly queried with the SELECT statement - additional possible output: MariaDB [psa]> select * from db_example.misc;
ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
  1. 建议首先将MYSQL实例关闭
  2. 在Windows下双击文件start_dbrecover_mysql.bat启动程序
  3. 在Linux下运行./start_dbrecover_mysql.sh启动程序(请确认在Linux上已安装这些包gtk2 libXtst.x86_64 xclock xorg-x11-xauth.x86_64 unzip: Centos 上yum install gtk2 libXtst.x86_64 xclock xorg-x11-xauth.x86_64 unzip)，推荐使用mobaxterm程序来启动远程图形化(https://zcdn.askmac.cn/MobaXterm_Portable_v20.2.zip)
  4. 选择常规模式
  5. 选择对应的MYSQL版本，PageSize一般保持默认即可
  6. 点击选择目录，将MySQL数据文件夹所在目录加入，点击开始
  7. 在数据库树形图中选择你需要的表，双击可以浏览数据，此处最多显示1000行数据
  8. 点击导出到文件，会将该表的数据以MYSQLDUMP形式存放到SQL文件中
  9. 点击导出文件路径，可直达文件目录，其内容如下图
  10. 文件包含建表语句，和插入语句，使用mysql -uroot -p < 导出文件.sql 导入数据。

 

mysql -uroot -p < employees.sql
Enter password: ********


mysql -uroot -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use  employees;
Database changed

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

针对drop database场景的恢复

 

mysql> drop database employees;
Query OK, 14 rows affected (0.16 sec)

#sync
#sync

 

启动DBRECOVER FOR MYSQL软件，选择DROP DATABASE恢复场景：

 

 

选择正确的MYSQL数据库版本：

 

 

选择SELECT DIRECTORY，输入@@datadir所在目录路径， 点击start

 

 

软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘：

 

 

之后点击dropped databases节点，可以找到相关被drop删除的数据库子节点，并恢复其中的表数据：

 

针对drop table 及 truncate table 场景的恢复

 

以下恢复步骤即适用于drop table 也适用于 truncate table

mysql> select count(*) from employees.employees;

+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.09 sec)

mysql> select @@datadir;
+-----------+
| @@datadir |
+-----------+
| /m01/     |
+-----------+
1 row in set (0.00 sec)

mysql> drop table employees.employees;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails


mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table employees.employees;
Query OK, 0 rows affected (0.02 sec)

mysql> ^DBye
 
 
#sync
#sync

 

 

启动DBRECOVER FOR MYSQL软件，选择DROP TABLE恢复场景：

 

 

选择正确的MYSQL数据库版本：

 

 

选择SELECT DIRECTORY，输入@@datadir所在目录路径， 点击start

 

 

软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘：

 

!!!注意这里一定要输入原始@@datadir所在目录；不能是出现问题后拷贝原文件的一个目录。因为软件需要扫描@@datadir所在文件系统所在挂载点才能找到被drop的数据。

 

 

之后点击对应数据库下的dropped tables节点，看是否存在对应被drop的表:

 

 

针对truncate table，只需要在数据库树形图中查看对应的普通数据表节点即可看到数据，并导出数据

可以在界面右侧观察到该表的数据，之后的恢复与常规模式一致。

 

针对delete table场景的恢复

 

mysql> use  employees;
Database changed

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)


mysql> delete from employees;


mysql> flush table employees with read lock;
Query OK, 0 rows affected (0.01 sec)

例如以上误删除表上记录的场景，我们可以用dbrecover for mysql的undelete功能恢复。

找到该表对应的ibd文件，例如 上例中employees表对应employees.ibd。

启动dbrecover for mysql程序，在开始菜单中选择添加ibd文件，并加入该ibd文件。

 

 

右击ibd文件选择扫描，扫描后出现对应的page文件，右键选择输入建表的SQL语句。

 

 

建表语句可以通过show create table 命令在mysql中获得：

 

mysql> show create table employees;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                  |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

 

点击确定后，界面右侧出现表信息，点击deleted数据：

 

 

点击导出到文件，即可将delete掉的数据恢复为MYSQLDUMP形式的INSERT语句

 

FRM文件的恢复场景

FRM文件的读取恢复功能是DBRECOVER FOR MYSQL的免费功能。

在某些场景下我们需要从MYSQL的FRM文件中获得建表语句，只需要在软件主界面下选择添加frm文件：

 

 

双击加入的frm文件或右键查看文件，可以在右侧界面看到该FRM文件对应的建表语句，用户可复制该语句。

 

 

检查数据表上可恢复的行数

在恢复场景中，用户可以通过该功能了解某张表所能恢复的记录数量。

选中数据表，右键选择Show Table Recoverable Row Count:

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *