When accessing MySQL Server or database, you may encounter an issue where the MySQL Server stops responding. It can cause downtime and hinder your workflow. There could be several reasons why the MySQL Server is not responding. Corruption in the MySQL database file is one of the major causes of this issue. The database can get corrupted due to one of the below reasons:
- Hardware issues on the system hosting the MySQL database
- Abnormal/sudden termination of MySQL Service
- MySQL Server instance restarts suddenly
- Bugs in MySQL software
- Insufficient system memory
- Corrupted MySQL data files/index files
- Sudden shutdown of the database in the middle of writing data
If the MySQL Server is not responding due to database corruption, then you can repair the database to fix the issue. In this post, we will discuss the methods to repair MySQL database. Before proceeding, you first need to use the InnoDB Crash Recovery Mode to restart the MySQL Server and then repair the database.
Table of Contents
Enable InnoDB Recovery Mode
If the MySQL Server is not responding, then you can check and edit the MySQL configuration file to enable the InnoDB Recovery Mode to start the MySQL Server. Locate the configuration file. By default, it is located in /etc/mysql/my.cnf. In the configuration (my.cnf) file, under [mysqld], add the following statements:
Innodb_force_recovery=1
service mysql restartOnce you have enabled the innodb_force_recovery by changing the value from ‘0’ to ‘1’, the MySQL Server will start. Next, you can check the tables for corruption by using the CHECK TABLE command. Here’s the syntax:
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
FOR UPGRADE
| QUICK
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}If the CHECK TABLE command detects any issue in the table, then repair and restore the corrupt tables in MySQL.
How to Repair Corrupt MySQL Database/Tables?
Follow the below methods to repair and restore corrupt MySQL database/tables.
Method 1:Restore Tables from the Backup File
If you have an updated backup of the MySQL database, then you can easily restore the corrupted or damaged 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 engines. This will restore the entire MySQL database.
- 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.sqlMethod 2:Use the Open-Source Tool – PhpMyAdmin
PhpMyAdmin is an open-source tool that you can use to restore MySQL database. It allows restoring one database at a time. If you have an updated backup file, you can use PhpMyAdmin to restore the MySQL database. 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.
- You can click on “List of databases” to check the restored database.
Note: You may face time-out issues when you try to restore large-sized database files.
Method 3:Use myisamchk Command
You can also use the myisamchk command to recover data from corrupted MySQL database 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 name- After recovering the tables, restart the MySQL Server.
Method 4:Use a Professional MySQL Repair Tool
If the above methods fail to recover the corrupted MySQL database, then you can use an advanced third-party MySQL repair tool, like Stellar Repair for MySQL. It can fix corrupt MySQL databases without any file size limit in just a few clicks. The tool can repair corrupt databases of both InnoDB and MyISAM storage engines. You can save the repaired database file in a new file with the original folder structure. The tool supports MySQL database created on Windows and Linux operating systems.
Conclusion
The “MySQL Server is not responding” issue can be the result of corruption in the database. However, the methods discussed in this article can help you repair and recover the corrupt MySQL database, thus resolving the issue. To quickly repair MySQL database without any data loss, you can install and use Stellar Repair for MySQL. It is a powerful and reliable tool to resolve corruption-related issues in MySQL database.
Related Articles:
