Recovering MySQL Files and Database Tables

Read about recovering a lost or deleted MySQL database, and restoring damaged MySQL tables with myisamchk.

By default, a MySQL database is installed on disk C of the computer:

C:\Program Files\MySQL\MySQL Server 5.7

However, table data is stored in files found in another folder on disk C of your computer, that is:

C:\ProgramData\MySQL\MySQL Server 5.7\Data

Location of these files is shown in the Server Status menu of MySQL Workbench application, in the section Server Directories.

Recovering MySQL Files and Database Tables

For the case when we need to recover database tables, we will be interested in a folder containing data of a specific database and the files which are found there.

Go to view
How to Recover MySQL, MSSql and Oracle Databases ⚕️👨‍💻🖥️

How to Recover MySQL, MSSql and Oracle Databases ⚕️👨‍💻🖥️

MySQL Database Files

MySQL is compatible with a number of file formats such as .sql, .arm, .cnf, .dbs, .ddl, .frm, .ibd, .ism, .mrg, .myd, .myi, .mysql, .opt, .phl, .sal, .sqr, .tmd, .arz, .ibz, .ibc, .qbquery, .rul. However, this is not the topic of this article. Today we are interested in the files where data and tables are stored; by recovering such files, a user will be able to get important information back and avoid losing it in the future.

Data of every database is stored in a folder with its name and depending on the table type, tables are stored in files with the following extensions:

  • db.opt – the file storing characteristics of a database specified when such database was created;
  • .frm – the table structure file;
  • .myd – the file where MyISAM table data is stored;
  • .myi – the file where MyISAM table indexes are stored;
  • .ibd – the file where InnoDB table data and indexes are stored.
my_db. Data of every database is stored in a folder with its name


my_db. Depending on the table type, tables are stored in files with the following extensions...

How to Recover a MySQL Database

Recovering a MySQL database is not difficult technically, but it depends on a number of conditions. For users of MySQL Workbench there is a function of Export and Import/Data Restore for databases.

MySQL Workbench

In addition, a backup copy can be created, and a MySQL database can be restored with mysqldump (which we have described in detail in one of our articles).

cmd. MySQL database can be restored with mysqldump

However, these functions apply more to creatig backups of MySQL data with the help of built-in tools. Advanced users or those who failed to use the backup functions for MySQL databases will also be interested in how to create backups and restore databases manually, by using the abovementioned structure files and table data:

  • Creating a copy of MySQL table database is possible by copying structure files and data files (*.opt, *.frm, *.myd, *.myi for MyIsam; *.opt, *.frm, *.ibd for InnoDB) and savig them into another folder.
  • It is possible to recover MySQL database table information by putting the previously copied structure files and data files into the folders of already existing bases (in our case, these are two bases: my_db и my_db2).

Recovering a Lost or Deleted MySQL Database

If for some reason you deleted a MySQL database, reinstalled Windows or formatted a hard disk, everything can be restored by usig the method described above, by putting the previously copied database files into the folder with the name of the database:

C:\ProgramData\MySQL\MySQL Server 5.7\Data

If the user failed to create a copy of database files beforehand, they can be recovered with Hetman Partition Recovery, and then be sent to the necessary folder using the method described above.

The tool recovers data from any devices, regardless of the cause of data loss.

To do it:

  • Run Hetman Partition Recovery and scan the disk where a MySQL database was stored

    Hetman Partition Recovery. Scan the disk where a MySQL database was stored
  • Using the program, go to the folder C:\ProgramData\MySQL\MySQL Server 5.7\Data,

    Hetman Partition Recovery. Using the program, go to the folder C:ProgramDataMySQLMySQL Server 5.7Data

    or find the necessary database files with the search function:

    Hetman Partition Recovery. Find the necessary database files with the search function

    Please note: files containing table data and formats will have the same name as the table, not as the database.

  • Recover database files

    Hetman Partition Recovery. Recover database files
  • Bring them to the folder with the name of the database

    my_db2. Bring recovered database files to the folder with the name of the database
  • When you start MySQL Workbench after that, the restored table files that you have put into the database folder will be accessible now.

    MySQL Workbench. When you start MySQL Workbench, restored table files that you have put into the database folder will be accessible now

    In the same way, you can recover lost dump files (*.sql) or its archive (*.zip, *.gzip, *.bzip2).

Recovering Damaged MySQL Databases With The Help of Myisamchk

MyISAM tables of a MySQL database can be damaged as a result a sudden interruption of a record process or a computer shutdown, hardware and software errors and failures, and also when trying to debug the table used by the server with the help of myisamchk.

As a result of damage, data can disappear from the table or be wrongly represented, but most often damage to the table results in an error like this: «Incorrect key file for table: ‘table_name’. Try to repair it»

To recover damaged MyISAM tables, you can use the command myisamchk.

Myisamchk works by way of debugging and creating a copy of .myd file, with further replacement of the damaged file with this file. That is why before using this command it is beter to create a backup of the table file which you are going to recover.

So, in order to recover a damaged table use the command:

myisamchk -r -q TABLE_NAME

where, -r -q – is the quick recovery mode. In this case, the index file will be corrected without changing the database file. If the data file contains all necessary things, and the remote links indicate correct positions in the data file, as a result of this command the table will be repaired.

If the previous command brings no result, then create a backup data file and run this command:

myisamchk -r TABLE_NAME

where, -r – is the recovery mode. In this case, incorrect and lost entries will be deleted from the data file, and the index file will be recreated (as described above).

Please note. If table debugging and recovery are going to be done from the command prompt, then the server should be stopped beforehand. It should be noted that when performing mysqladmin shutdown from a remote server mysqld will still operate for some time after shutting down mysqladmin, until all enquiries are stopped and all keys are saved to disk.

Vladimir Artiukh

Author: , Technical Writer

Vladimir Artiukh is a technical writer for Hetman Software, as well as the voice and face of their English-speaking YouTube channel, Hetman Software: Data Recovery for Windows. He handles tutorials, how-tos, and detailed reviews on how the company’s tools work with all kinds of data storage devices.

Oleg Afonin

Editor: , Technical Writer

Oleg Afonin is an expert in mobile forensics, data recovery and computer systems. He often attends large data security conferences, and writes several blogs for such resources as xaker.ru, Elcomsoft and Habr. In addition to his online activities, Oleg’s articles are also published in professional magazines. Also, Oleg Afonin is the co-author of a well-known book, Mobile Forensics - Advanced Investigative Strategies.

Recommended For You

Hello! This is AI-based Hetman Software virtual assistant, and it will answer any of your questions right away.
Start Chat