How to repair a crashed MySQL table

Hage Yaapa

MySQL Table is marked as crashed and should be repaired

Your website stops working and you see a spine-chilling error message "MySQL table is marked as crashed and should be repaired". What now? Well, you need to repair it.
There are two approaches to repairing a crashed table. I hope it is not bad news for you, but they will work only for MyISAM engines. If you are using InnoDB, consider restoring the table from a backup (at the cost of loss of some data). The most common reason for InnoDB tables crashing is lack of disk space - fix that!
How long does it take to repair a crashed MySQL table?

Before you jump on the repair guns, you might want to know how long it might take to repair the table. For a few KB table, it takes about a few seconds; for a few MB table, it will take a few minutes; for a few GB table it will take hours; for really huge GB tables, it might take days to weeks! Also it depends a lot on the available RAM and processor power.

Do you have a backup of the table? It just might be a better option to restore the table from a backup for a HUGE table that has crashed - unless the data is not crucially important. Do you really want to run a week-long repair process? Think about it. And always backup your database.
Repairing the table using the MySQL console
If you have shell access, connect to the MySQL server and do the following:

>use my_database;
>repair table my_crashed_table;

This method is recommended for repairing crashed tables of anything from a few KBs to a few GBs, depending on the RAM and processor power available to you.

Repairing the table using PHPMyAdmin

Use this method only if you don't have access to the shell. Normally it is recommended for small size tables only, say a few KB to a couple of MBs. Anything more than that you'll end up frustrated, and potentially angry. Do the following:

Log on to PHPMyAdmin > select the affected database > select the affected table from the right pane > from the With selected menu, select "Repair table". The crashed table should be repaired in one quick stroke, if the table size is of a few KBs to a few MBS, and the engine is MyISAM.

What causes MySQL tables to crash?

There can be multiple causes of a MySQL table crashing. The number one cause of table crash is running out of disk space. If you are anticipating a potential HUUUUGE amount of data in your database, you better make sure you have the required disk space in advance.

Other potential causes of MySQL table crashes are problems with the operating system, power failures, hardware problems, unexpected termination of the MySQL server, corruption of data due to external programs.

Lessons learnt

1. Backup your database. Backup your database. Backup your database.
2. Plan your architecture with future in mind. Think ahead.
3. Don't try to repair a 700 GB table if you are on a shared hosting.