Corrupted MySQL database:
If you suspect data corruption in a MySQL database,
First make a copy of the database for safe keeping:
mysqldump –user=root post_planner > /tmp/post_plannerdb.sql
Prove it:
To prove that the database is corrupted, do:
mysql> check database
Find the table:
To detemine which table has corruption, do this for each table that you suspect:
mysql> check table post_planner.posted;
+—————————-+—————-+
| Table | Op | Msg_type | Msg_text |
+—————————-+—————-+
| post_planner.posted | check | Error | Can’t open table |
| post_planner.posted | check | error | Corrupt |
+—————————-+—————-+
2 rows in set (0.01 sec)
Once you know that the table is corrupt, you need to recover it. This is easy in MyISAM tables with the CHECK TABLEand REPAIR TABLE, but that doesn’t work with InnoDB. InnoDB tables can be checked with CHECK TABLE, but not repaired with REPAIR TABLE.
Copying over the good data to a new databases:
Here is what I did to fix it:
I created a second table called posted2 like this:
create table posted2 like posted;
SO that gets you the structure. Now lets see what we can do about getting the data:
mysql> insert ignore into posted2 select * from posted limit 1000000;
Query OK, 1000000 rows affected (0.00 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
Keep increasing the limit until you see that the number of Records is fewer than the number you requested. At this point, do the following:
select max(id) from posted2;
This will give you how many records were successfully imported before it encountered corruption. Then you increment through with a the rest of the data using the first record that is not corrupted. You might have to try skipping one by selecting all records that are greater than the last known good record +1. It that fails, skip two on the next attempt. The command to skip the records basically looks like this:
mysql> insert ignore into posted2 select * from posted where ID>1000001;
Query OK, 9999999 rows affected (0.00 sec)
If you encounter more corruption, lather, rinse, repeat