Mysql – How to recover/restore corrupted Innodb data files

MySQLrecoveryrestore

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?

Thanks.

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 DELIMITER // (create procedure code of procedure 1)// (create procedure code of procedure 2)// DELIMITER ;

Best Answer

Check the MySQL error log. It looks like trying to read table 44 is actually crashing the server.

"Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server.

The subsequent messages seem to bear this out:

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when
trying to connect
Operation failed with exitcode 2

A quick test of mysqldump on Windows 7 against a MySQL server that is not running returns exactly that same error and exits with %ERRORLEVEL% set to 2. The nature of error 10061 can be found with the perror utility.

C:\>perror 10061
Win32 error code 10061: No connection could be made because the target machine actively refused it.

...so, check the MySQL error log. What's likely happening is that you're hitting corruption severe enough that in spite of innodb_force_recovery, the MySQL Server is crashing. I will then usually restart on its own, but this takes time so may not complete before mysqldump gets an IP connection refused on the subsequent connection attempts. This should all show up in the error log.

I assume that when the dump does not yield any error, the table's data is non-corrupted.

Incorrect assumption. If the dump does not yield any error, it could also mean that that innodb_force_recovery allowed the server to retrieve some of the data. A "successful" dump of a table only means it's not corrupted so severely that innodb_force_recovery can't survive it.

1 (SRV_FORCE_IGNORE_CORRUPT)

Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

^{http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html}

Again, you should see chatter from InnoDB in the MySQL error log.

Final suggestion, don't use workbench. Use the mysql command line client to explore the server schemata and use mysqldump directly from the command line to try to restore individual schemas or individual databases.

update/additional:

how do you recover stored procedures? I get an error because they access corrupt tables

It shouldn't be because the procedures access corrupt tables, because stored procedures and functions aren't validated against their referenced tables in the way views are (drop a table that a view references, and then SHOW CREATE VIEW doesn't work -- the same isn't the case with procs or functions)... MySQL doesn't care whether tables referenced by stored procedures and functions even exist (except at runtime)... however, unlike view definitions, which are stored in individual files, the definitions of procedures and functions are stored in the proc table in the mysql schema, which is a MyISAM table... so I would expect that you'd see error messages referencing that table and CHECK TABLE mysql.proc; and REPAIR TABLE mysql.proc; might get you going again. Note that the version of MySQL Server that you're using for your recovery needs to be the same release series (e.g. 5.5) or the table definition of mysql.proc will be wrong... that shouldn't be the issue here, since if I read the question correctly, the crashed server and the recovery server are both 5.5.31.

If you can SELECT * FROM mysql.proc then you can retrieve the definitions that way if for some reason the SHOW CREATE PROCEDURE statement doesn't work.

And what options for a (partial) rescue are there for the tables that yielded an error in the dumps?

It depends on the nature and scope of the errors encountered. One option, discussed here, involves repeated INSERT ... SELECT ... LIMIT ... OFFSET, extracting blocks of rows from readable pages into a MyISAM table (because you can't write to an InnoDB table while innodb_force_recovery is on). There's also the Percona Data Recovery Tool for InnoDB, though I have never had occasion to use it.

Related Solutions

Mysql – Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore

The error seems to indicate a problem with the primary key on the table mycompany.fact.

InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")

You can open the dump file in any text editor and remove the primary key constraint from the CREATE TABLE schema definition. Then once you've restored your database, you can always add the primary key back in. You would also need to deactivate Foreign Key Checks in your backup file.

-- at the beginning of the file
SET foreign_key_checks = 0;

-- at the end of the file
SET foreign_key_checks = 1;

This may get your database restored, but if there is a problem with the data in the column you're using as the primary key, then you may see other errors.

If that didn't work, I would also try

Backing up all the databases
Stopping MySQL
Delete the ibdata and iblog files
Starting MySQL again
Restore all my databases.

If there is a problem with your InnoDB files, this may be a way to solve it.

The problem could also be deeper and running a fsck on the filesystem that stores your InnoDB files may find corruption on the disk. Rebooting your computer is one way to easily cause an fsck on all partitions.

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk

Best Answer

Related Solutions

Mysql – Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

Related Question