Skip to content

MySQL Table Lost and Missing All Data from Corrupt Table and Database

by Jon on March 16th, 2011

Here is a repair for fixing missing data in a mysql database table. What could happen is a table becomes corrupted and then does not load causing all the data to go missing. Here is an easy way to fix the problem and get your data back.

mysqlcheck database-name

This may output the following lines if there is a problem with a table.

warning  : Table is marked as crashed
warning  : 2 clients are using or haven't closed the table properly
error    : Can't read key from filepos: 88963072
Error    : Incorrect key file for table './database-name/table_name.MYI'; try to repair it
error    : Corrupt

Luckily this can be fixed, there may be some data loss but at least the database will start again to do a dump and backup of what is there. To get it fixed follow the steps below. Make sure to stop your database first, if you are on CentOS/Redhat it would be mysqld on Ubuntu it is mysql.

service mysql stop
cd /var/lib/mysql
# this does check on the file and output the results
myisamchk -c ./database-name/table_name.MYI
# this does the repair on the file
myisamchk -c -r ./database-name/table_name.MYI
service mysql start

Once the database is repaired I would recommend doing a full backup of the database that crashed so you have a restore point if something should happen in the future. I also would setup a nightly backup of the database and off load it to an ftp server or somewhere not physically on that machine.

mysqldump database-name > /usr/src/database-backup.sql

Okay now that that table is fixed there might be a different problem with another table, the error message is below.

mysqlcheck database-name
warning  : Table is marked as crashed
warning  : 1 client is using or hasn't closed the table properly
warning  : Size of indexfile is: 557056      Should be: 546816
warning  : Size of datafile is: 1024868       Should be: 1023876
error    : Found key at page 297984 that points to record outside datafile
error    : Corrupt

To fix this problem I am going to use a the mysqlcheck tool’s repair feature.

mysqlcheck --auto-repair database-name table_name
Repairing tables
database-name.table_name                        OK

Again I would highly recommend doing a database backup at this point.

mysqldump database-name > /usr/src/database-backup.sql

From → Linux

No comments yet

Leave a Reply

Note: XHTML is allowed. Your email address will never be published.

Subscribe to this comment feed via RSS

%d bloggers like this: