Do you have a website that has a MySQL database? Has it gotten corrupted? So, we have come up with a solution to your problem.
Nowadays, almost everything is based on data and all the data are stored on some database. Mostly we use SQL and No-SQL databases. We will not get in-depth with the database, so coming back to the topic now. As these databases are stored digitally, they might get corrupted sometimes, which can be frustrating. Losing all the data can harm your business directly, and also, it could be an error in your work process.
Table of Contents
Reasons for database corruption:
- Hardware breakdown is one of the key causes. Sometimes it happens that power fluctuations may shut down the system automatically, which causes the disk to stop promptly which leads to database corruption.
- Next is due to failed update of the system. To explain this what happens is that when you are updating your website or system and the update gets failed it leads to the corruption of the database.
In this article, we are going to discuss how we can recover the corrupted MySQL database.
Method 1: Manually recovering MySQL using InnoDB
InnoDB is the default engine for MySQL, which means that it is the function of InnoDB to run the MySQL server. We will force the InnoDB to run in the recovery mode in this method. To recover the data using this method, follow the steps:
- Shut down the MySQL server.
- Take a backup of the datadir.
- Add the given lines in my.cnf file:
innodb_force_recovery=3
innodb_purge_threads=0
Note: There are seven levels for recovery mode starting from 0 to 6. Using the level above four might corrupt the database even more.
Read MySQL documentation for more information.
- Now, save the file and restart the server again. If you can restart the server in recovery mode, then dump all the databases to a sing file using the following command:
mysqldump -u [user] –p[password] –all-databases > all_databases.sql
- Now comment outlines we wrote in my.cnf file.
- Restart the server in normal mode.
- Restore the backup file we created at the start of this process.
If you can successfully restart the server, you are all good to go. However, if you cannot restore the database, we have software to help you fix the database issues. It is called Stellar MySQL Repair Tool.
Method 2: Using Stellar Repair for MySQL
Stellar Repair for MySQL helps you to recover your corrupted database easily. As it is a GUI based software, there is also no need of writing any commands, which means it is very easy to use. This MySQL Repair Tool is available for both Linux and Windows machines, and it has both free and paid options that you can choose from.
Benefits of Stellar software:
- It recovers both InnoDB (.frm, .ibdata, .idb) and MyISAM (.frm, .myd, .myi) database engines.
- It is easy to use as it is GUI based with a great UI.
- It also can batch repairs of multiple databases in one go.
- It Supports MySQL 8.x, 6.x, 5.x, 4.x, and 3.x.
- It also has the function to save the recovered data in various formats like MySQL, HTML and others.
Now, let us see how we can recover the database using this software.
Note: Before you start the process, make sure that the location of ‘ibdata’ and the MySQL database is the same and that MySQL is installed in your system.
- Download and Install the Stellar software on your system. Once installed launch the software.
- Now a dialogue box will appear. Select the database to repair.
- Now the window to Select Database will appear. Choose the database that has to be repaired.
- To begin the process, press the Repair button.
- Once the repair completes, a dialogue box will appear. Click on ok.
- A window will appear and show you all the repaired databases and the recoverable components.
- Go to the File menu and click on the Save option to save all the repaired databases.
- Now a dialog box will appear. Select the format in which you want to save the data and hit save.
- Once the entire saving process is complete, you will be notified using a popup.
Conclusion
Losing your databases can be very difficult, and it will sometimes cost you a lot of time and money. In this article, we have shown two methods to recover your corrupted MySQL database. The first is a manual mode, and it can be tricky if you don’t have the proper knowledge of how to use commands. The second is using a software called Stellar Repair for MySQL. It is easy to use, and it will surely help you recover the databases. We might have solved your problem.
