This blog will walk you through ways to overcome database corruption which can be the cause of errors like the error: MySQL shutdown unexpectedly.
Database corruption can occur due to several issues, including faulty applications, bad sectors on your hard drive, sudden power failure, or improper shutdowns. When the database gets corrupted, you may encounter various errors and issues, such as:
- Tables failing or crashing unexpectedly.
- MySQL being unable to start up, being unable to log in, or even the error: MySQL shutdown unexpectedly.
- Problems reading data from the tables in MySQL.
- File read errors and more.
This article will walk you through the ways you can repair your MySQL database once data becomes corrupt.
How to Check for Corruption in a MySQL Server Database?
To check for corruption in the MySQL database, you can use the CHECK TABLE command. This command checks the tables inside your database and displays an error with the table name, which is marked as corrupted if any issues are found. Check for MySQL table corruption by running CHECK TABLE in the following way:
Note: Make sure you have the required privileges to run CHECK TABLE.
CHECK TABLE tbl_name [, tbl_name] [options]
Here, tbl_name is the name of your table(s), and options is one or more of the available options (FOR UPGRADE|QUICK|FAST|MEDIUM|EXTENDED|CHANGED.) For more information, refer to the MySQL documentation.
The CHECK TABLE command can work perfectly for checking tables based on MyISAM; however, on InnoDB tables, it works a little differently, and the efficiency of such a command depends on various factors like the size of your tables and others, and the CHECK TABLE command won’t fix the error: MySQL shutdown unexpectedly. The command can, however, analyze and verify the integrity of the tables in your database if you run MySQL.
First, check the error log to identify the exact issues looking for messages related to table corruption, crashes, or the error: MySQL shutdown unexpectedly., then follow the steps outlined below.
How to Overcome Database Corruption in MySQL?
Some of the methods to repair a corrupt MySQL database are as follows:
Method 1: Restore Tables From a Backup or Use phpMyAdmin
Restoring From a Backup
If you have a recent, healthy backup of your MySQL database or the tables within, then you can recover the tables from a backup. For this, you can use the mysqldump utility by following these steps:
- Create an empty database.
- Restore the database by loading the dump into it through the CLI or phpMyAdmin (keep in mind that providing passwords through the CLI is insecure):
mysql [-uroot -p…] db_name < dump.sql
This command will restore your table structure. Check the state of the restored tables using the SHOW TABLES; command.
phpMyAdmin
You can also use phpMyAdmin to work with the tables inside MySQL. To restore data using phpMyAdmin:
- Open up phpMyAdmin.
- Once the phpMyAdmin tool interface is displayed, click on the option named Databases.
3. Under the Databases tab, search and click on the affected or corrupted database. You will see a list of all the tables in it.
4. Select the Check All option to select and repair all of the tables in the selected MySQL database. Next, click on the dropdown labeled With selected: and then click on the Repair Table option.
5. Wait till the repair process is completed.
phpMyAdmin can throw errors when repairing some InnoDB-based tables due to the design of the storage engine. Also, keep in mind that phpMyAdmin has some limitations on the size of your files that may lead to timeout issues.
Method 2: the Dump and Reload Method
If your InnoDB tables are severely corrupted and you’re facing problems beyond the error: MySQL shutdown unexpectedly., then you can use the dump and reload method to recover them. If MySQL Server fails to start, use the InnoDB force recovery option to forcibly start the storage engine. If the MySQL InnoDB forced recovery feature is not working, try to set the value of innodb_force_recovery to a higher value – it accepts values from 0 to 6. Once you have set the level for innodb_force_recovery by running the SET statement or in my.cnf, run the following command to dump the table data:
mysql [-u user –p] database_name table_name > table_name_dump.sql
After this, use the command below to export all of your tables to a file called dump.sql:
mysql –all-databases –add-drop-database –add-drop-table > dump.sql
Now, restart the MySQL Server. Optionally, disable the InnoDB recovery mode by adding a comment (#) next to the innodb_force_recovery variable in the configuration file. Restart MySQL with the forced recovery disabled.
Note: If your database tables are severely corrupted, you might need to set the value of innodb_force_recovery to more than 4. Note that values higher than 4 can lead to data loss.
Method 3: Use the myisamchk Command
You can use the myisamchk command to repair tables based on MyISAM too. It can help you recover all the data from the corrupt MyISAM tables, except unique keys. Run myisamchk like myisamchk –recover [tablename].
Do note that MyISAM shouldn’t be used in production as it’s considered to be an unreliable storage engine.
Method 4: Use a Professional MySQL Repair Tool
If you fail to repair MySQL database tables with the above-mentioned methods or need a faster and more reliable solution to repair your MySQL database, consider using a third-party professional MySQL repair tool like Stellar Repair for MySQL. This DIY repair tool can easily and quickly repair corrupted tables created on both InnoDB and MyISAM storage engines. This tool can recover all the objects, including tables, foreign keys, and unique keys from a corrupt MySQL database and can do so with precision and integrity. It won’t help you avoid errors like the error: MySQL shutdown unexpectedly., but it can help you open the MySQL database tables that are inaccessible due to corruption or severe damage, and also allows you to save the recovered data in multiple file formats, including SQL, CSV, and HTML. It works on both Windows and Linux systems.
Conclusion
Your MySQL can become corrupted due to a variety of different reasons. To fix errors beyond the error: MySQL shutdown unexpectedly. and repair your MySQL database, follow the database repair methods mentioned in this article. Alternatively, consider using a MySQL repair tool like Stellar Repair for MySQL. Whatever the reason for database corruption, Stellar Repair for MySQL can help repair both InnoDB and MyISAM tables inside MySQL, as well as allow for selective recovery for tables inside your database with no data loss.
FAQ
How to Overcome MySQL Database Corruption?
To reliably overcome MySQL database corruption, first check your error logs for clues and corruption-related messages, then check for corrupted tables with something like SHOW TABLE STATUS or CHECK TABLE, and finally, restore your data from a backup or use a professional data restoration tool.
How to Restore Data in MySQL?
To restore data in MySQL, first ensure you have a reliable backup at hand, then restore it through the CLI or SQL clients like DbVisualizer, phpMyAdmin, or the like.
How to Overcome the Error: MySQL Shutdown Unexpectedly.?
To overcome the error: MySQL shutdown unexpectedly., review the applicable error log files, verify that you have sufficient disk space to run applications, and check for corrupted data files.