“Recently, when using MySQL database for daily operation and maintenance, we found that the data of an important table has been accidentally deleted, which seriously affects the business operation, and we urgently need to get professional guidance for data recovery.
Attempts have been made to recover the data using MySQL’s flashback feature, but could not be successful as this feature is not enabled. Also tried to recover data from recent full backup and incremental backup, but due to backup strategy issue, some of today’s new order data was lost.
We are looking forward to solutions to recover deleted data from MySQL table based on the above situation, which can help us to resume normal business operation as soon as possible.”
Data deletion is a common operation in database management. However, sometimes due to misuse or other reasons, we may delete the data we need. Fortunately, MySQL provides some methods to help us recover deleted data. Two common methods are described below. Go forward and see how to recover deleted data from MySQL table.
Common Reasons for MySQL Table Data Deletion
Before we talk about how to recover deleted data from MySQL table, we’d better know what are the reasons for the data deletion.
The most common reason for MySQL table data deletion is because of operational errors. Due to misuse by database administrators or developers, such as mistakenly deleting a row or column of a table, or deleting an entire table.
Out of program error. While writing database-related code, errors may occur that result in data deletion. For example, while executing certain logical judgments or conditional statements, unexpected results may occur, resulting in data being deleted by mistake.
Because of malicious attacks. In some cases, hackers may exploit security vulnerabilities to gain access to the database and delete important data.
MySQL table data deletion due to data duplication or redundancy. In order to maintain data consistency and accuracy, there may be some duplicate or redundant data in the database. In order to clean up this data, one may choose to delete some of them.
When some data is no longer of reference or use value, you may choose to delete it to free up storage space, which is one of the reasons for MySQL table data loss.
Or because the data violates constraints. The data in the database is usually limited by some constraints, such as primary keys, uniqueness constraints, foreign keys, and so on. When the data violates these constraints, there may be an option to delete them to repair the integrity of the data.
Method 1: Use UNDO Log to Recover Data
When MySQL performs a delete operation, it first records an UNDO log. This log can be used to roll back the delete operation and recover the data. Thus, the first way of how to recover deleted data from MySQL table is to use UNDO log.
Step 1. Find the most recent backup. First, check whether there is a recent backup, if so, you can use the backup file to restore data.
Step 2. Enable UNDO logging. Enable UNDO logging in the MySQL configuration file. Locate the my.cnf or my.ini file (depending on your operating system and MySQL installation) and find the following line:
Change it to:
Save and close the file. Restart the MySQL service to make the changes take effect.
Step 3. Restore Data. Execute the following SQL commands to restore the data to the state before deletion:
This will roll back the most recent UNDO log and recover the data.
Step 4. Disable UNDO logging. After completing data recovery, it is recommended to disable UNDO logging to prevent accidental rollback operations. Change the value of innodb_force_recovery back to 0 and save the file. Restart the MySQL service to make the changes take effect.
Method 2: Use Database Recovery Tool
If you are unable to recover data through UNDO logs, you can consider using database recovery tools to retrieve deleted data. Commonly used data recovery tools include MTM Database Recovery and other third-party MySQL data recovery tools. Here is an example of how to use MTM Database Recovery:
Step 1. Download and install MTM Database Recovery. Download and install the software from the official website of MTM Database Recovery. Make sure to choose the version that is compatible with your operating system and MySQL version.
Step 2. Select Database File. After launching the software, click the “Recovery” button on the upper left corner and select the MySQL file from which you need to recover table data. And tick the “Recover deleted data” option.
Step 3. Wait for scanning database. Wait for the software to scan the specified database, it will search and display the list of recoverable data.
Step 4. Preview data. Click on the left column to select the database tables, the right preview area will show the deleted table data accordingly with a small icon with a red cross.
Step 5. Recover Deleted MySQL Table Data. Then click the “Export” button. The software will save the data to the specified directory. The software provides two export methods: a. Export to CSV file. b. Export to database.
Above is the second way and its detailed operations for you to recover deleted data from MySQL table.
FAQ: How to Recover Deleted Data from MySQL Table
While using the above two methods for recovering deleted data from MySQL table, you may encounter the following common questions:
1. Question: I have deleted the whole table, can I still recover it?
Yes, if the entire table is deleted, you can try to recover it using a backup file or data recovery tool. However, if the table data is overwritten or new data is written after deletion, the possibility of recovery will be reduced.
2. Question: What should I do if I try to recover data using UNDO log, but an error occurs?
If you get an error while trying to recover data using UNDO log, you can refer to the official MySQL documentation or seek help from technical support. The type of error and the specific situation may vary and you need to handle the error accordingly.
3. Question: I deleted part of the data, can I still fix it by modifying the code?
If you have deleted part of the data in the table and the amount of data deleted is not large, you can consider fixing it by modifying the code. However, if a large amount of data has been deleted, modifying the code may become very complicated and error-prone. In this case, it is recommended to prioritize the use of backup files or data recovery tools for recovery.
4. Question: How to avoid deleting data by mistake again in the future?
In order to avoid mistakenly deleting data again in the future, you can take the following measures:
-
- Backup database regularly: Regular backup of database is an effective way to prevent data loss. You can use the mysqldump command or the backup tool that comes with MySQL to perform backups.
-
- Write a rigorous backup strategy: Make a detailed backup strategy, including backup frequency, backup storage location, backup file management and so on. Also, make sure the backup files are separated from the main database to avoid being deleted at the same time.
-
- Use version control: Using a version control system (e.g. Git) allows for better tracking of code changes and database structure changes. This makes it easier to roll back to a previous version after accidentally deleting data.
-
- Test and audit backups: Regularly test backup files for availability and integrity. Also, regularly audit the quality and integrity of backup files to ensure their reliability.
Conclusion
Common causes of deleted data from MySQL tables include operational errors, program errors, malicious attacks, data duplication or redundancy, data no longer needed, and data violation of constraints.
This article describes two common approaches on how to recover deleted data from MySQL table: the methods that can be used with UNDO logs and database recovery software tools. Using UNDO logs allows you to roll back the most recent UNDO logs and recover the data. Data recovery tools on the other hand can quickly scan and recover deleted data.
To avoid accidentally deleting data again in the future, you can take regular backups of the database, write a strict backup policy, use version control, and test and audit backup files.