DBRECOVER for MySQL User Guide

Overview

DBRECOVER for MySQL is a MySQL/InnoDB recovery tool. When no usable backup is available, it can recover data from instance crashes, corrupted InnoDB dictionaries that prevent the database from starting, DROP DATABASE, DROP TABLE, TRUNCATE TABLE, row-level DELETE, and disk or file-system damage.

Features:

• Graphical user interface — no command line required.
• Supports MySQL 5.1 through 8.0, including the new MySQL 8.0 data dictionary.
• InnoDB storage engine only.
• Recovers data after DROP DATABASE, DROP TABLE, TRUNCATE TABLE, and row-level DELETE.
• Recovers from disk failure and file-system damage.
• Reads FRM files and converts them into CREATE TABLE statements.
• Recovers large objects (LOB / TEXT).
• Outputs results as MYSQLDUMP-format SQL files.
• Java-based; runs on Windows, Red Hat, CentOS, and Ubuntu.
• Free trial limits: up to 1,000 rows per table, and up to 100 rows when recovering data removed by DELETE.

After DBRECOVER outputs MYSQLDUMP-format SQL, you can import it back into MySQL and confirm the row count:

mysql -uroot -p < actor.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 actor;
+----------+
| count(*) |
+----------+
|     200  |
+----------+
1 row in set (0.00 sec)

Recover from a DROP DATABASE scenario

Reproduce the failure for the demo:

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

#sync
#sync

1. Start the DBRECOVER for MySQL software and select the DROP DATABASE recovery scenario.

2. Choose the correct MySQL database version.

3. Choose SELECT DIRECTORY and enter the path to the @@datadir directory, then click Start.

4. The software will scan the ibdata1 or mysql.ibd files and the @@datadir disk.

5. Click on the dropped-databases node to find and recover tables from the dropped databases.

Recover from DROP TABLE and TRUNCATE TABLE scenarios

The recovery steps apply to both DROP TABLE and TRUNCATE TABLE scenarios. First, reproduce the failure:

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

1. Start the software and select the DROP TABLE recovery scenario.

2. Choose the correct MySQL database version.

3. Select SELECT DIRECTORY, enter the path to the @@datadir directory, and click Start.

4. The software will scan ibdata1 or mysql.ibd and the disk where @@datadir is located.

Important: Point DBRECOVER at the original @@datadir, not at a copy made after the failure. DBRECOVER scans the file system of @@datadir's mount point to find the dropped data, and a copied directory loses that filesystem-level information.

5. Click on the dropped-tables node under the corresponding database to see if the dropped table exists.

For TRUNCATE TABLE, the table is still listed under its normal database node — open it in the tree to view and export the data.

The table contents appear on the right of the interface; from there, follow the standard export workflow.

Recover rows deleted by a DELETE statement

When rows have been removed by an accidental DELETE, recover them with the undelete feature of DBRECOVER for MySQL. First, reproduce the failure:

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)

1. Locate the corresponding .ibd file, e.g. employees.ibd for the employees table.
2. Start DBRECOVER for MySQL, select Add ibd file from the start menu, and add the ibd file.

3. Right-click on the ibd file and choose Scan. After scanning, corresponding page files appear.

4. Right-click and paste the table's CREATE TABLE statement (use SHOW CREATE TABLE in MySQL to obtain it).

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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------+
1 row in set (0.01 sec)

5. Click Confirm. The table appears on the right; click Deleted Data to view the recoverable rows.

6. Click Export to file to recover the deleted data as MYSQLDUMP-format INSERT statements.

FRM files to CREATE TABLE SQL

DBRECOVER for MySQL can read FRM files for free and convert them back into CREATE TABLE statements — no license required.

1. Select Add frm file on the main interface of the software.
2. Double-click the added FRM file (or right-click to view). The reconstructed CREATE TABLE statement appears on the right of the interface, ready to copy.

Check recoverable row count on data tables

This feature reports how many rows DBRECOVER can recover from a given table — useful for sizing the recovery before you commit to a license.

Right-click on the table and select Show Table Recoverable Row Count.

For recovery cases that this guide does not cover, or for paid recovery assistance, contact liu.maclean@gmail.com or see the DBRECOVER for MySQL pricing page.