How to Recover Data from Crashed MySQL Tables?

MySQL database tables may get corrupted due to several reasons, such as server crash, virus or malware intrusion, force or abrupt system shutdown, software or hardware failure, etc. Due to this, you may receive errors, like “Table '.\mysql\db' is marked as crashed and should be repaired”, ERROR 144 - Table './extas_d47727/xzclf_ads' is marked as crashed and last (automatic) repair failed, and more.

In such cases, you can’t read data from the crashed MySQL tables. To regain access to the table data, you need to repair or recover the crashed MySQL table. In this article, we will discuss some simple ways to repair or recover crashed MySQL tables.

Methods to Recover Data from Crashed MySQL Tables

Here are some easy methods that can help you recover data from crashed MySQL tables.

Method 1: Restore Tables from Backup File

If you have an updated backup of the MySQL database, then you can easily restore the crashed MySQL tables from the backup. For this, you can use the mysqldump utility. Follow the below steps:

Note: The utility supports both InnoDB and MyISAM search engine. This will restore the entire MySQL database tables.

• First, you need to create an empty database to save the restored database. For this, use the below command:

‘mysql > create db_name’

• Then, restore the database using the following command:

mysql -u root -p db_name < dump.sql

• This will restore all the database objects. You can check the MySQL tables by using the below command:

‘mysql> use db_name;

mysql > show tables;’

Note: It only recovers the MySQL tables to the point-in time when the backup was created. Any changes in tables made after the backup was created will be lost.

Method 2: Use myisamchk Command to Recover Data from Crashed Tables

You can also use the myisamchk command to recover data from crashed or corrupted MySQL tables. Here’s how to use the myisamchk command:

• First, you need to stop the MySQL Server.

• Then, run the below myisamchk command to recover the table:

myisamchk –recover TABLE

• After recovering the tables, restart the MySQL Server.

Note: This command only supports recovery of MyISAM tables in MySQL.

Method 3: Use PhpMyAdmin to Restore MySQL Tables

PhpMyAdmin is an open source tool that you can use to repair and restore MySQL tables. It supports both InnoDB and MyISAM storage engines. If you have an updated backup file, you can use PhpMyAdmin to restore the MySQL tables. To do so, follow the below steps:

• Open a web browser on your system and enter http://localhost/phpmyadmin to open phpMyAdmin.

• Login to the phpMyAdmin.

• In PhpMyAdmin cPanel, click the Import option.

• Click Choose File to select the database backup file on your local system.

• Next, choose SQL from the Format dropdown menu and then click Import.

• Wait for the import process to complete. Once the process is complete, you will see a success message.

• In PhpMyAdmin, you can click on “List of database” to check the restored database.

Note: PhpMyAdmin allows to restore one database at a time. You may face time-out issues when trying to restore large-sized database files.

Method 4: Use a Professional MySQL Repair Tool

If the above methods fail to recover the crashed or corrupted MySQL tables, then you can use an advanced third-party MySQL repair tool, like Stellar Repair for MySQL. This tool can repair corrupt databases of both InnoDB and MyISAM storage engines and recover all the data with complete integrity and precision. The tool also supports recovery of specific tables, including partition tables. It helps you to save recovered data in different formats, including MySQL and MariaDB. This MySQL repair tool can restore data from MySQL database created on Windows and Linux operating systems.

Conclusion

The error messages, such as Table '.\mysql\db' is marked as crashed and should be repaired, indicate corruption in tables. This post shares some effective methods to repair and recover corrupt MySQL tables. For easy and quick recovery process, you can use an advanced MySQL repair tool, such as Stellar Repair for MySQL. This advanced tool can repair severely damaged or corrupt MySQL database without any data loss. It helps you restore specific tables from the corrupt MySQL database. You can save the recovered data from the damaged MySQL database file to a new healthy file. It helps in resolving the errors related to corruption in MySQL database file.