How to Recover Corrupted MySQL Database?

  •   Written By
     
  • Published on
    December 17th, 2019
  • Updated on
    December 26, 2022
  • Read Time
    4 minutes
Summary:- Looking for a way to recover corrupted MySQL Database? If yes, then your search ends here. MySQL Database is used for multiple purposes like data warehousing, logging applications, and e-commerce applications. One can even store a single record of information or entire details of products using this database.

 

But, the data and tables in the MySQL database are stored in different file formats. And losing these files can do harm to your business. So, in this article, we will be discussing some practical methods to recover these types of MySQL files. So, let’s get started.

What is a MySQL Database?

MySQL is a relational database management system (RDBMS) based on SQL. It has now become a component of parent company MySQL AB’s product line of database servers and development tools. Nowadays it is extensively used in applications, such as data warehousing, e-commerce, Web databases, logging applications, and distributed applications. Now, it becomes the world’s most popular open-source database because of its fast performance, high reliability, ease of use, and dramatic cost savings.

Here, we will be discussing how to recover MySQL databases files and some of these file formats are:

.frm – The table structure file

.ibd – In this file format InnoDB table data and indexes are saved

Db.opt – Contains characteristics of a specified database

.myi – The MyISAM table indexes are saved in this file

Now, let’s explore some of the methods to recover MySQL database.

Methods to Restore the MySQL Databases

If you’re using MySQL DB, then there are options for Data Export and Data Import/Restore. By using any of these features, one can easily restore the MySQL database. Users should also create a backup for MySQL database, and then restore it using the mysqldump command in cmd. 

There are other techniques that are used to restore the MySQL database. And that is if you have a backup for the previous data. So, let’s have a look at this method.

  • Firstly, Install MySQL Server on your system. You can also install a similar version if you want to recover data from the existing version.
  • Then, stop the MySQL service to add the data folders.
  • After that, copy the backup data folder into the new MySQL > data >folder.
  • Once the required data is added to the new MySQL folder, restart the MySQL service.
  • Now, check and repair database tables. Users can also check the database tables using mysqlcheck command followed by REPAIR. Performing these steps ensures that the database tables are not corrupt.
  • Once the repairing of corrupt data is done, you can export the database to a SQL dump or SQL file.

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

Restore SQL database from ibdata, and frm files?

It is not that difficult to recover the mysql database from frm files. It’s not enough you just copy the .frm files to the database folder but you also need to copy the ib_logfiles and ibdata file into your data folder. Now, copy the .frm files and just restart the server and your database are restored.

After copying the above files execute the following command:

sudo chown -R mysql:mysql /var/lib/mysql

The above-mentioned command will change the file owner under MySQL and its folder to MySql user. And that is very much important for mysql to read the .frm and ibdata files.

If backups are available and you are technically sound, then you can follow the manual procedure. If you’ve lost completely your database, then the manual method is no more beneficial in restoring it. There is an alternate option that can restore even permanently deleted data.

Alternate Solution to Recover MySQL Database

If the above procedures fail to recover the databases then you can opt for the SysInfo MySQL Database Repair tool. It is one of the best tools to repair and restore data from corrupt MySQL databases. The utility facilitates its users to preview every object of the repaired MySQL database before saving it. It comes with some advanced features on which you can totally rely upon.

Conclusion

The article is demonstrating the ways of restoring corrupt MySQL databases. It can be difficult with manual methods, but it is necessary to retrieve data from corrupt databases. So, here I have mentioned an effective solution that will help you repair and restore the MySQL database to the MySQL server.

Related Post