How to Recover MySQL Database Files, if Deleted?

Introduction

MySQL is an open-source RDBMS. SQL refers to Server Query Language used for managing relational databases. MySQL contains records, tables, and indexes to store data and connect them with keys. It is a very powerful tool to manage web applications and store other forms of data.

MySQL contains numerous files related to it. However, regarding the database, there are three prominent files.

• .frm – This file type contains the formatting and structure of a MySQL database table.
• .myd – If the .frm file contains the table structure, .myd files store the data of every row of a table. It is related to the .frm file in terms of data.
• .myi – This is an index file for a database table. It keeps a record of the index of every item on the table.

It is the basic information regarding the MySQL database system. Now, like any other file, MySQL database files can also be corrupted. Before learning how to recover MySQL database files, we will discuss some of the most common reasons for MySQL database corruption.

Reasons for MySQL Database Corruption

MySQL databases can be corrupted due to various reasons. Let us check some common reasons.

• MySQL server crash is the first reason that may lead to MySQL file corruption.
• An incomplete process also causes database corruption. When the MySQL process is stopped before saving, it affects the database file.
• Trying to modify the files located in the root folder of MySQL.
• Virus attack is also one of the prominent reasons for MySQL database corruption.
• Third-party applications that try to edit or modify the files in the root folder.

Most probably, your MySQL database is corrupted because of any of the above reasons. Now, the question comes, ‘How to retrieve data from MySQL database’?

How to Recover MySQL Database Files?

There are different ways through which you can recover your MySQL database files if corrupted or lost. You need to have a basic grip over MySQL terminologies and commands because these techniques are quite complicated for a novice user. Also, there might be slight changes with the differences in the version of the applications.

#1. Recover Database using mysqldump Utility

mysqldump is a command-line tool that performs logical backups. It produces a set of SQL statements that are executed to reproduce the database tables and records and other objects.

• Copy the following statement and paste it into the Notepad.

sudo mysqldump -u [user] -p [database_name] > [filename].sql

• Now, replace some details of the above statement with the below details.
  • user: Username for the database
  • database_name: complete path and name of the database
  • filename: path and name of the mysqldump file

To backup an entire database management system use the following command:

mysqldump –all-databases –single-transaction –quick –lock-tables=false > full-backup-$(date +%F).sql -u root -p

To backup more than one database file use the following command:

sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql

#2. Recover MySQL Deleted Records using phpMyAdmin

Another approach to recover MySQL database files is by using phpMyAdmin. It is a free tool developed on PHP that gives you administrative rights to MySQL. Furthermore, it also enables you to export and import your database. Follow the below steps.

• First, we create a new database to export data into. Open phpMyAdmin.
• Switch to the Databases tab. Enter an appropriate Database name.
• Choose the same collation as in the backup database and press the Create button.
• Now, the new database appears in the navigation panel. Click on the Menu button and select the Import option.
• After that, Importing into the database “Sakila” page will appear.
• In the File to Import section, click on the Choose File button and select the file with the database backup.
• Now, click on the Go button to start restoring the database.
• As the process completes, all the MySQL tables and other objects will get imported to the sakila database.

#3. Use MySQL Workbench to Restore Database

MySQL Workbench is a database design tool that adds new functionalities to the database. It provides data modeling, SQL Development, and other tools to architect the MySQL database. Using the Workbench you can also restore deleted MySQL database files as follow:

• Open MySQL Workbench in your system and click on the Data Import/Restore option in the Management section.
• Select the Import from the Self-Contained file option and locate the sakila database backup file with the .sql extension.
• After that, under the Default schema to be imported to the section, choose the desired database.
• At the bottom, expand the drop-down and select Dump Structure and Data.
• After setting up all parameters, switch to the import Progress tab and click on the Start import button.
• At last, when the process has been completed, close the application.

These are the most effective ways to recover damaged MySQL databases. However, a prominent drawback of these techniques is that they are complicated for new users.

So, how to retrieve data in MySQL?

We provide you with the most effective and easy solution to this problem.

Professional Method to Restore Deleted MySQL Database File

DRS MySQL Database Recovery Tool is the best utility to repair your corrupt MySQL items and recover MySQL database files. You can recover your database in a few simple steps. Along with that, you can save the recovered data directly to the server. Therefore, it is the best solution for every user to recover their lost MySQL database.

Conclusion

I hope this article provides you with suitable solutions to your problem. MySQL database files can get corrupted due to many reasons. There are also different ways you can recover your lost data. However, you need technical expertise to perform the procedure. Therefore, we suggest the most effective professional MySQL database repair tool.