how to repair a damaged mysql table

Just a few minutes ago, i encounted a fairly common problem that would be hard to figure out without someone telling you how to do it. A server with mysql server quit working and we were getting die errors every time we tried to connect to the database. Wanna know how i fixed it?? well here you go...

1) log on to the linux console

2) restart the mysql server

/etc/init.d/mysqld restart

(this works on debian)

3) connect to the sql server

mysql -u=root -p

(note root is the root user for mysql)

4)

mysql> show databases;
+----------+
| Database |
+----------+
| inc |
| horde |
| mysql |
| psa |
| test |
+----------+
5 rows in set (0.00 sec)

5)

mysql> use inc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 'prospects'
Database changed


6)

mysql> show tables;
+-----------------------------------+
| Tables_in_inc |
+-----------------------------------+
| SendingEmails |
| activationsurvey |
| activepeoplewhodonothaveasigntime |
| ad_log |
...
| prospects |
| us_shiprates |
| usps_countries |
| usps_shiprates |
| verificationgifs |
| zips |
| zips_city |
+-----------------------------------+
65 rows in set (0.00 sec)

7)

mysql> check table prospects;
+------------------+-------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+---------------------------------------------------------+
| inc.prospects | check | warning | Table is marked as crashed |
| inc.prospects | check | warning | 1 clients is using or hasn't closed the table properly |
| inc.prospects | check | warning | Size of datafile is: 39660768 Should be: 39660672 |
| inc.prospects | check | error | Found 325275 keys of 325274 |
| inc.prospects | check | error | Corrupt |
+------------------+-------+----------+---------------------------------------------------------+
5 rows in set (0.07 sec)


8)

mysql> repair table prospects;
+------------------+--------+----------+------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------+----------+------------------------------------------------+
| inc.prospects | repair | info | Wrong bytesec: 101-97-100 at 39660672; Skipped |
| inc.prospects | repair | status | OK |
+------------------+--------+----------+------------------------------------------------+
2 rows in set (23.92 sec)

9)

mysql> check table prospects;
+------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| inc.prospects | check | status | OK |
+------------------+-------+----------+----------+
1 row in set (3.64 sec)