“I am an employee of an organization. Recently while using MySQL 8.0 database (InnoDB engine) for business operations, I have encountered data issues that have rendered some of the business critical data inaccessible. Now, urgently need to get a professionally recommended InnoDB recovery tool.
Hopefully, the innoDB recovery tool can automatically analyze binary logs, identify and correct erroneous update operations, and recover lost inventory data. Requires compatibility with MySQL 8.0 and Ubuntu 20.04 LTS operating system.”
InnoDB is one of the database engines of MySQL, which is now the default storage engine of MySQL and one of the standards for MySQL AB release binary. Compared with the traditional ISAM and MyISAM, the most important feature of InnoDB is that it supports ACID-compatible transaction (Transaction) function, similar to PostgreSQL.
InnoDB, as the default storage engine of MySQL, is widely used in various database systems. However, when InnoDB database fails or data is lost, how to recover data effectively becomes an important issue. In this article, we will introduce five commonly used excellent InnoDB recovery tools, and introduce their principles and usage in detail.
MySQL Enterprise Backup
MySQL Enterprise Backup is the official data backup and recovery tool provided by MySQL. It supports multiple backup methods, including physical and logical backups, and can be used to recover data from InnoDB databases.
Here are the steps for InnoDB recovery with MySQL Enterprise Backup:
- Step 1: Stop MySQL service on the target server: Make sure that the MySQL service on the target server has been completely stopped before starting the recovery.
- Step 2: Clean up the target data directory: If there is already data on the target server and you intend to perform a fresh recovery, you need to empty the MySQL data directory (usually the path specified in the datadir configuration) and remove the existing data files, log files, etc. to avoid conflicts with the recovered data.
- Step 3: Decompress the backup file (if necessary): If the backup is stored in compressed format, use the appropriate decompression utility (e.g. gzip, tar, etc.) to decompress the backup file to a temporary directory.
- Step 4: Perform the recovery operation: Use the MySQL Enterprise Backup command line tool to perform the recovery operation. The basic command syntax is as follows: mebrestore –defaults-file=<path_to_target_mysql_config> –backup-dir=<path_to_backup_files> –datadir=<target_datadir>
Pros
MySQL Enterprise Backup is an official product of MySQL, so it is very compatible with MySQL databases. It adopts efficient data backup and recovery algorithms, which can complete the backup and recovery of a large amount of data in a short period of time.
Cons
MySQL Enterprise Backup is a commercial fee-based software, which may cost more for some small businesses and individual developers. In additional, the use of this tool may require some technical knowledge and may take some learning time for beginners.
The most important thing is that you need a backup for InnoDB recovery.
Using TwinDB as InnoDB Recovery Tool
TwinDB is a specialized tool for InnoDB data recovery, which is based on InnoDB’s internal structure and data storage mechanism to quickly and effectively recover damaged or lost InnoDB databases.
TwinDB supports a wide range of operating systems and MySQL versions, and is characterized by simplicity and ease of use, fast recovery speeds and high data integrity.
Here are the detailed steps of how to recover InnoDB with TwinDB:
- Download and Installation. First of all, you need to download the TwinDB installation package from the official website and follow the prompts to install it. After the installation is complete, start the TwinDB client.
- Connect to the database. In TwinDB client, select the “Connect to Database” option, enter the IP address, port number, user name and password of the MySQL server, and click the “Connect” button. If the connection is successful, you can see the InnoDB table in the database list.
- Select the recovery mode. According to the data loss situation, select the appropriate recovery mode. If only part of the data is lost, you can choose “Fast Recovery” mode; if the whole database is lost, you can choose “Full Recovery” mode.
- Start recovery. Select the tables and data ranges that need to be recovered, click the “Start Recovery” button, TwinDB will start scanning the database and try to recover the data. During the recovery process, you can view the recovery progress and log messages.
- Complete the recovery. When the recovery is complete, TwinDB will export the recovered data to the specified directory. Users can check whether the exported data is complete and perform subsequent processing.
Pros
TwinDB, as a recovery tool for the InnoDB storage engine, its design and functionality closely match InnoDB’s characteristics and failure scenarios to provide targeted solutions for InnoDB-specific issues (e.g., transactional recovery, crash recovery, etc.) that may be handled more efficiently and accurately.
Cons
Even if the tool provides a user-friendly interface, complex recovery tasks may still require users to have some knowledge of database management and experience in using the tool.
MTM Database Recovery for MySQL
MTM Database Recovery is a professional InnoDB recovery tool for Windows, which is mainly used to recover data lost due to various reasons such as misuse, database server abnormality, virus infection, system crash, natural disaster, etc.
MTM Database Recovery can accurately and completely recover the structure and data of InnoDB tables, ensuring database recovery and maintaining data integrity and accuracy. The procedure of using MTM Database Recovery for MySQL to recover InnoDB data is as follows:
Step 1: Download and install MTM Database Recovery software. Take care to select the MySQL version to use.
Step 2: Launch MTM Database Recovery software and through the “Recovery” icon on the top, select the InnoDB files you need to recover, including .frm, .ibdata and .idb.
Check “Recover deleted data” and click “Recover” button, MTM Database Recovery software will start scanning the database files and try to recover the data. During the scanning process, you can see the percentage of recovery progress and other information.
Step 3: After the scanning is completed, you will see a list on the left side, which contains the recovered data files and database objects.
On the right side of the software, you can preview the file details and the recovered data will be marked with a red cross. You can select the data files and database objects to be recovered as per your requirement.
Step 4: Click the “Recover” button and MTM Database Recovery software will start writing the recovered data to a new database file or export it to a CSV file.
Pros
Supports the recovery of database tables, view, functions, stored procedures, and triggers. Capable of repairing various corrupted MySQL databases and retrieving data from inaccessible tables.
Cons
The free-trial plan has limitations and needs to be purchased if you need to export the recovered data.
Percona Data Recovery Tool for InnoDB
Percona, a well-known MySQL solution provider, offers Percona Data Recovery Tool for InnoDB, a powerful InnoDB data recovery tool. It supports fast data extraction from corrupted InnoDB tables or files with an easy-to-use interface and powerful performance.
Follow the steps to recover InnoDB:
- Stop write operations. Immediately after a data loss occurs, stop all write operations to the affected table to prevent new data from overwriting data that may be recovered.
- Copy the .ibd file. Securely copy the .ibd file containing the InnoDB table with the lost data from the database server to the working environment used for recovery operations. Also, if possible, copy the .frm file (table structure file) and the corresponding system tablespace file (ibdata1 or a separate ibd file, depending on the MySQL configuration).
- Install and configure PDRT. Extract the PDRT download package (e.g. percona-data-recovery-tool-for-innodb-<version>.tar.gz): tar -zvxf percona-data-recovery-tool-for-innodb-<version>.tar.gz. Go to the extracted directory: cd percona-data-recovery-tool-for-innodb-<version>.
- Parse the ibd file. Use a PDRT-provided tool (such as parse_innodb_page.py or innodb_space) to parse the ibd file and convert it into a readable format for subsequent analysis and recovery. This step may require specifying table primary key information or other table structure details.
- Identify and extract missing data. Locate deleted or modified data records by analyzing the parsed page structure. Use PDRT-provided tools such as innodb_redo_log_parser to parse the redo logs (if available) to determine the point in time and extent of the deletion or update operation. Based on the parsing results, extract the data records that need to be recovered. This may involve writing scripts or using specific command line options to filter out eligible records and export to SQL statements or CSV files.
- Rebuild the table structure (if required). If a .frm file is not available, manually create an empty table on the target MySQL server with the same structure as the original table based on known table structure information.
- Import the recovered data. Using a tool such as MySQL client or Navicat, import the recovered data into the corresponding table that has been created or cleaned. This usually involves executing a bulk INSERT or LOAD DATA INFILE command.
Pros
Percona Data Recovery Tool for InnoDB recovers corrupted InnoDB data quickly and accurately. Provides a friendly user interface that makes the data recovery process easier.
Cons
When recovering a large amount of data, the tool may consume a lot of system resources, affecting the performance of other applications. Although the tool provides a user-friendly interface, it still requires some technical knowledge of database management to use and configure it properly.
Navicat
Navicat is a widely used database management and development tool that supports a variety of database engines, including InnoDB. Navicat provides data backup and recovery features that make it easy to restore data from InnoDB databases.
Here are the InnoDB recovery procedures:
- Open Navicat. start the Navicat application.
- Connect to the target MySQL server. If you are not already connected, create a new MySQL connection via the Connect menu or toolbar button, enter the server address, port, username, and password, and verify that the connection is successful.
- Select the target database. In Navicat’s object tree view, locate and expand the connection name and select the database from which you want to recover data. If the database does not exist, you may need to create an empty database with the same name first.
- Start the database recovery operation. There are usually two ways to start a recovery operation: “Through the Backup/Recovery feature” or “Import Wizard”. Follow the wizard to complete the InnoDB recovery.
Pros
Navicat provides rich features such as data modeling, data transfer, data synchronization, structure synchronization, import, export, backup, restore, report creation, etc., which helps to manage databases efficiently. It is designed to be intuitive, making it easy for users to perform a variety of database operations and improving developer efficiency.
Cons
Although Navicat has a user-friendly interface, its functions and operations may be more complicated for beginners and require some learning time.
Conclusion
Above are five commonly used recommendations of excellent InnoDB recovery tools. Before using any InnoDB recovery tool, it is recommended to backup the original database and related files to prevent further data loss or corruption. When using InnoDB recovery tool for recovery, it is recommended to turn off MySQL server write operations to avoid interference with the data being recovered.
If there are more tables in the database or a large amount of data, it is recommended to choose a professional InnoDB recovery tool for Windows or MacOS to perform the recovery operation to ensure data integrity and accuracy. If you encounter any problems or error prompts while using InnoDB recovery tool for recovery, it is recommended to check the log messages or contact technical support for assistance.