Are you are facing errors and issues 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”, fail to open the tables in MySQL database and more? Read this blog. Such issues indicate corruption or damage in your database file.
The corruption in the MySQL database can occur for various reasons, such as virus or malware intrusion, server crash, abrupt system shutdown, hardware or software failure, etc. In this article, we will learn How to Recover MySQL Database with InnoDB & MyISAM tables.
What causes corruption in MyISAM & InnoDB Tables?
MySQL database tables can get corrupt due to one or more of the following reasons:
- MySQL server instance restarts suddenly.
- Issues in the hard drive where the database is located.
- MySQL process gets interrupted in the middle of writing data to the hard disk.
- Bugs in MySQL code.
- Computer sudden crashes due to power failure.
- The hard disk is out of space
- Malware infection in the computer hosting the database.
How to Recover MySQL Database?
Methods to Repair InnoDB and MyISAM tables in MySQL
Restoring from a backup is one of the most convenient methods in a situation where your database is not readable due to corruption. If you have a readable backup file, then you can restore the MySQL database via mysqldump utility. To use this utility, follow the below steps:
- Initially, create an empty database where you can save the restored database using the following command:
‘mysql > create db_name’
- Then, restore the database using the below command:
mysql -u root -p db_name < dump.sql
- It will restore all the objects of the database. You can check the restored InnoDB tables by using the below command:
‘mysql> use db_name;
Alternatively, you can use PhpMyAdmin to Restore MySQL Tables. PhpMyAdmin is an open-source tool that you can use to repair and restore MySQL (Innodb and MyISAM tables). To do so, follow the below steps:
- Open a web browser on your system and type http://localhost/phpmyadmin to launch phpMyAdmin.
- Add the Login credentials to the phpMyAdmin.
- In PhpMyAdmin cPanel, click the Import option.
- Click Choose File to choose the database backup file on your local system.
- Next, select SQL from the Format dropdown menu and then select Import.
- Wait for a few minutes till the import process is complete. Once it is complete, you will see a success message. If you want to check the restored database then click on the List of Database option in PhpMyAdmin.
Note: You can smoothly restore one database at a time using PhpMyAdmin. However, to restore large-sized database files you can encounter time-out issues.
If you have no backup file, then you follow the below repair methods:
To repair the InnoDB tables, you can use the dump and reload method:
You can dump and reload the InnoDB tables to rebuild them. Here’s how to do so:
- First restart the MySQL server by typing service.msc in the Run window.
- If your MySQL crashes and fails to start, you can use Force InnoDB recovery to rebuild the database. Using this InnoDB setting- innodb_force_recovery, you can control server behavior for the startup time. It usually ranges from 0-6. You can use this setting to let the MySQL server start even when it detects corruption in MySQL tables. Steps to use Innodb_force_recovery to start MySQL Server are as follows:
- Enable the Innodb_force_recovery option from the configuration file. For this, find the configuration file (my.cnf). In Windows, the configuration file is located in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.
- In my.cnf file, locate the [mysqld] section and then insert the below statements:
[mysqld]
Innodb_force_recovery=1
service mysql restart
Note: The default value of innodb_force_recovery is 0. However, you can change its value to ‘1’ to start the InnoDB engine and dump the tables. Dumping tables with “innodb_force_recovery value” of 4 or higher can lead to data loss. So, take the backup of the database before proceeding.
- Enabling the innodb_force_recovery option allows you to access the corrupt table. Next, use the mysqldump command to dump the data in the table as given below:
mysqldump -u user -p database_name table_name > single_dbtable_dump.sql
- After this, run the below command to export all the databases to the dump.sql file:
mysqldump –all-databases –add-drop-database –add-drop-table > dump.sql
- Now, restart the MySQL Server.
- Next, run the DROP DATABASE command to drop the database.
- Next, disable the InnoDB recovery mode by commenting on the following line in [mysqld]:
#innodb_force_recovery=…
- Now, save the applied changes to the configuration file and then restart the MySQL Server.
If your tables are created using MyISAM engine then use myisamchk Command to Recover Data from MySQL database tables. Here is the command:
Note: First stop the MySQL Server.
- Then, execute the following myisamchk command to recover the table:
myisamchk –recover TABLE
- Once the tables are recovered, restart the MySQL Server.
What If the above solutions fail?
If the above methods fail to repair the MySQL database, you can use a third-party MySQL repair tool such as Stellar Repair for MySQL. This software can easily and quickly repair MySQL tables in any state or size. It can repair both InnoDB and MyISAM tables.
Also, the tool is compatible with Windows and Linux Operating systems. It has no file size limitations. It can recover all the data, even deleted objects, from the tables in the MySQL database with absolute precision.
Conclusion
If you are facing issues due to corruption in the MySQL database, restore the backup file or follow the repair methods explained above to repair the database. If you are looking for a single tool to repair tables created in both InnoDB and MyISAM engines, use a reliable MySQL database recovery software -Stellar Repair for MySQL. It can help you resolve all types of corruption errors in the MySQL database without hassle.
