Email: service@parnassusdata.com 7 x 24 online support!
DBRECOVER FOR MYSQL 用户手册
DBRECOVER FOR MYSQL是一个MySQL数据库(Innodb)恢复工具，该工具软件可以在MySQL没有备份的情况下，针对实例崩溃，Inoodb字典损坏无法启动数据库实例，DROP DATABASE，DROP TABLE，TRUNCATE TABLE,DELETE TABLE，磁盘/文件系统损坏等场景恢复数据库数据。
特性：
- 图形化界面操作，无需学习命令行
- 支持版本覆盖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...
- 建议首先将MYSQL实例关闭
- 在Windows下双击文件start_dbrecover_mysql.bat启动程序
- 在Linux下运行./start_dbrecover_mysql.sh启动程序
- 选择常规模式
- 选择对应的MYSQL版本，PageSize一般保持默认即可
- 点击选择目录，将MySQL数据文件夹所在目录加入，点击开始
- 在数据库树形图中选择你需要的表，双击可以浏览数据，此处最多显示1000行数据
- 点击导出到文件，会将该表的数据以MYSQLDUMP形式存放到SQL文件中
- 点击导出文件路径，可直达文件目录，其内容如下图
- 文件包含建表语句，和插入语句，使用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:
