A while ago, my Windows 7 system on which a MySQL Server 5.5.31 was running crashed and corrupted the InnoDB database. The weekly backup that's available does not cover all the tables that were created in the meantime, therefore I would endeavor to recover as much as possible from the data. Right after the crash, I copied the whole data folder of MySQL to an external drive. I would like use this as the starting point for my rescue attempts.
In the following I'll describe the steps of my (not yet convincing) rescue attempt so and would be thankful for any comments or guidance on how to improve it:
- I've now done a fresh install of MySQL Server 5.5.31 on another PC
- I stop the MySQL service with "net stop MySQL" at the command prompt.
- I already figured that I need to adjust the size of the innodb log file in the my.ini file as it deviates (256 MB) from the default value (19MB).
- In the my.ini, I also set innodb_force_recovery=6
- In the data folder of the fresh installation, I overwrite the ibdata1, iblogfile0, iblogfile1 files with those recovered from the crashed machine. I also copy the relevant database (UPDATE: and the mysql) folders into here (NOT the standard
mysql,test and performance folders).
- I start the MySQL service with "net start MySQL".
- I go into MySQL Workbench, open my server instance, go to Data Export, basically leave the default settings, and have every table of my databases exported as an individual dump file. I also set stored procedures to be dumped. Otherwise I do not change the default settings there.
- I start the dump process; it makes its way through 43 out of 195 tables. Of these 43,
- some cannot be recovered yielding an error "mysqldump: Got error: 1146: Table '...whatever...' doesn't exist when doing LOCK TABLES",
- but many can. I assume that when the dump does not yield any error, the table's data is non-corrupted.
Then, after the 44th, all the other table dumps fail as it is reported that the server cannot be connected to anymore:
"mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect
Operation failed with exitcode 2"
These errors then go on for all the remaining tables from the 44th to the 195th.
For the 44th table itself, the error is the following: "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
...table 44...at row: 57". So it seems that for this table corruption is or begins at row 57.
Now to my questions:
- Why is the connection breaking down given that innodb_force_recovery is set to 6?
- How to proceed? I worked out what the 44th table was where the connection was lost and can try to resume the process from the 45th table. But isn't there a better way to do it?
- Once the data has been copied and the server restarts well, should I just try a dump of each table or what alternatives are there?
UPDATE: Additional notes for my later reference
- When re-creating stored routines backed up using SHOW CREATE PROCEDURE ... and SHOW CREATE FUNCTION ..., they must be imported using
(create procedure code of procedure 1)//
(create procedure code of procedure 2)//