UKC

How to Fix: Crashed MySQL Database

If your database is marked as crashed and needs to be repaired you may find it will not backup (or migrate) using mysqldump. In these instances you need to login to mysql and run the check/repair process. It’s very easy.

First, you need to authenticate to the mysql server. The example below is for Plesk servers:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Let’s check the table and see the current status:

# mysql> check table db.wp_comments;
+-----------------------------------+-------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+-----------------------------------------------------------+
| db.wp_comments | check | warning | Table is marked as crashed |
| db.wp_comments | check | error | Size of datafile is: 26984448 Should be: 26985708 |
| db.wp_comments | check | error | Corrupt |
+-----------------------------------+-------+----------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

Now we can see the problem, let’s run the REPAIR TABLE facility:

# mysql> repair table db.wp_comments;
+-----------------------------------+--------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+--------+----------+-------------------------------------------------------+
| db.wp_comments | repair | info | Found block that points outside data file at 26984408 |
| db.wp_comments | repair | status | OK |
+-----------------------------------+--------+----------+-------------------------------------------------------+
2 rows in set (4.23 sec)

That’s now all fixed. Yes, it’s that easy! Let’s check the table again to be 100% sure:

# mysql> check table db.wp_comments;
+-----------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+-------+----------+----------+
| db.wp_comments | check | status | OK |
+-----------------------------------+-------+----------+----------+
1 row in set (0.05 sec)

You can now transfer your MySQL database, dump it or re-migrate it as required.