Won't start: InnoDB: Corrupted page ... Tablespace was not found
https://dev.mysql.com/doc/refman/5.7/en/repair-table.html says:
REPAIR TABLE works for MyISAM, ARCHIVE, and CSV tables.
In other words, REPAIR TABLE does nothing for InnoDB. InnoDB has its own automatic crash recovery that it runs on startup.
But InnoDB crash recovery only works for lost changes that were in progress at the time of the crash. It uses the redo log and the doublewrite buffer to reconstruct lost dirty pages. This process cannot reconstruct data that was corrupted at rest.
The error message actually tells you what to do:
InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
Read more details at https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html:
1 (SRV_FORCE_IGNORE_CORRUPT) Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
Start the server with innodb_force_recovery=1
after moving your tablespace files back where they belong.
Then you can dump the table using mysqldump
, and it should read the pages it can read, skipping corrupted pages. Then you can make a new table to import from the dump:
CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_bad, mytable_new TO mytable;
Then re-import your dumped data.
I assume you do not need help with dumping a single table and importing the dump file.
Re your comment:
It seems like you have more extensive corruption. I suggest the following steps:
- Start the MySQL server with
innodb_force_recovery=6
- Use
mysqldump
to dump all of the data. - Shut down MySQL server.
- Back up the contents of your datadir just in case you want to get professional help to see if they can recover more of the data. Try https://twindb.com, they are the best experts in MySQL database recovery.
- Initialize a new datadir (I would use a new disk device, and take the old one out of service because it now has unknown damage).
- Restore the data dump you created with
mysqldump
.