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
1 2 3 | 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
1 2 3 4 | mysqlcheck [options] db_name [tbl_name ...]#examplemysqlcheck --check db_namemysqlcheck --repair db_name |
3. Use myisamchk to check and repair database
1 2 3 4 5 6 | myisamchk --check /path/to/.MYI filemyisamchk --recover /path/to/.MYI file#If you get error cannot create temporary files use this:myisamchk --recover --force /path/to/.MYI file#ormyisamchk --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:
1 2 3 | 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
1 | root 2048 #means 2048MB limit for one file |
Lost connection to database when doing query
To fix: increase connect_timeout in /etc/my.cnf
1 | connect_timeout 100 #connect timeout to 100 seconds |
