How to Recover MySQL Database?

Himanshu Goyal Himanshu Goyal Updated On - 17 Jul 2019

MySQL is an open-source RDBMS system that uses SQL. It is used for various purposes like data warehousing, logging applications, and e-commerce applications. With MySQL database, you can even store a single record of information or an entire inventory of products.

Also, MySQL database files are compatible with many file formats, such as .sql, .cnf, .ddl, .arm, .qbquery, etc. However, the data and tables in the MySQL database are stored in different file formats. And losing these files can cause a lot of harm to your business.

In this article, we have mentioned some practical methods to recover these types of MySQL files. Some of these file formats are:

  • .frm – The table structure file
  • .ibd – In this file format InnoDB table data and indexes are stored
  • Db.opt – Contains characteristics of a specified database
  • .myi – The MyISAM table indexes are stored in this file

Now, let’s move to the methods to recover MySQL database.

Methods to Recover MySQL Databases

Recovering MySQL database is not that difficult, but it depends on various conditions. For instance, if you’re using MySQL Workbench, then there are options of Data Export and Data Import/Restore. Using any of these features, you can easily restore your MySQL database.

You can also create a backup for MySQL database, and then restore it using the mysqldump command in cmd. There is one other method that can be used to restore MySQL database to a new MySQL server if you have a backup for the previous data. Let’s have a more clear look at this method.

Note: This method only works for MyISAM tables and doesn’t restore InnoDB tables.

  1. Download and Install MySQL Server on your system. You can also install a similar version if you want to restore data to the existing version.
  2. Now, stop the MySQL service to add the data folders.
  3. Copy the backup data folder into the new MySQL–>data–>folder.
  4. After adding the required data to the new MySQL folder, restart the MySQL service.
  5. Now, check and repair database tables – You can check the database tables using mysqlcheck command followed by REPAIR. Performing these steps ensure that the database tables are not corrupt.
  6. After repairing any corrupt data, you can export the database to a SQL dump or SQL file.

That’s it! Your MySQL database has been restored.

Now, the manual approach can be followed only if backups are available and you are technically good. If you’ve lost your database completely, then the manual method is not beneficial in restoring it. So, we have an alternate option that can restore even permanently deleted data.

Quick Solution to Recover MySQL Database

Kernel for MySQL Database is a great tool to repair and restore data from corrupt MySQL databases. It allows users to preview every object of the repaired MySQL database before saving it. It comes with some advanced features, such as the option to restore data online or local disk in offline mode, fix corruption errors of the databases, multiple saving options, etc.

Let’s understand how this extensive tool works in restoring MySQL databases.

  1. Download and install Kernel for MySQL Database Repair tool on your system.
  2. Download

  3. Launch the tool and click the Browse button to select the specific database file and hit the Recover button.
  4. The database will be added to the tool; you can preview the objects, keys, and tables by selecting it.
  5. To save the database, select the table(s) and the property(s) by marking the checkboxes and click the Save button.
  6. The saving mode will appear on the screen. Now, you can choose whether you want to restore the database online or save it on your local computer.
  7. To restore data to MySQL, select the MySQL settings mode. Enter the details for the required fields, such as ServerHost, Port, User, and Password. You can check the connection by clicking the Test Connection button.
  8. Now, select whether you want to create a new database or restore it to an existing database, and then click Save.
  9. The saving process will start, once it is complete a window will appear on the screen displaying Process Complete, click OK to end the process.

  10. Similarly, you can save the database in the batch file mode, and then restore it to MySQL server using cmd on your Windows operating system. Here is a preview screenshot of how it will work.

Conclusion

Restoring corrupt MySQL databases can be difficult with manual methods, but it is necessary to retrieve data from corrupt databases. So, here we have mentioned an effective solution that will help you repair and restore MySQL database to MySQL server.

Download