How to Check And Repair MySQL Database

Sometimes your MySQL database data is corrupted. Usually it is not properly close when querying to the database, because of high server load or MySQL server suddenly down. This will make your database files or tables corrupted. And your web application won’t run normal or even show Internal server error or cannot connect to the mysql database server.

There are many ways to to fix this error:
1. Use MySQL check and repair function

mysql -h [host] -u [user] -p[pass] [db_name]
CHECK TABLE [table_name]
REPAIR TABLE [table_name]

2. Use mysqlcheck to check and repair database

mysqlcheck [options] db_name [tbl_name ...]
#example
mysqlcheck --check db_name
mysqlcheck --repair db_name

3. Use myisamchk to check and repair database

myisamchk --check /path/to/.MYI file
myisamchk --recover /path/to/.MYI file
#If you get error cannot create temporary files use this:
myisamchk --recover --force /path/to/.MYI file
#or
myisamchk --safe-recover --force /path/to/.MYI file

Frequently error when recovering mysql table files:
File size limit exceeded
To fix: increase the file size limit, you can check it with:

ulimit -a
# file size    1024
# means 1024MB limit for one file

You can increase it by edit this file /etc/security/limits.conf, add or edit this line

root  2048 #means 2048MB limit for one file

Lost connection to database when doing query
To fix: increase connect_timeout in /etc/my.cnf

connect_timeout   100 #connect timeout to 100 seconds

Give me your feedback

This site uses Akismet to reduce spam. Learn how your comment data is processed.