How do you recover MySQL InnoDB data after a crash?

The context

- "What have we got?", asks the emergency surgeon.
- "Damaged server."

We’re talking about a Debian Linux server here.  But the principle stays the same for other flavours, except perhaps for retrieving the MySQL password, stored here in /etc/mysql/debian.cnf.
Damaged in the sense that a disk crash on one of the RAID disks makes it impossible for the OS to run properly, or simply for mysqldump to run normally, which prevents migrating the data from this damaged server to a failover (= a healthy server taking over).
A pity, because we realise that the files in the datadir¹ directory are readable, since the partition holding them can be mounted by physically connecting the disk to the failover.

¹datadir refers to the directory set in the mysql configuration file, visible for example with the command:
grep datadir /etc/mysql/my.cnf


The current situation is that a failover server has taken over the service, but with out-of-date data, since it comes from the last backup.  Not bad, but not ideal, as we’re missing the data between the date of the last backup and the date of the crash. That’s the data we’re going to try to recover . . .

First and foremost, during a crash and a failover (= service resuming on another system), you must immediately“push the pegs”, a personal expression meaning that on the failover machine you’ll set the AUTO_INCREMENT indexes to values higher than what you think the index values were at the time of the crash.
Take round values, rounded up within the same order of magnitude.  When choosing this round value, pick one that leaves enough room for the inserts you’ll make while recovering the data.  As a reminder, the only missing inserts are those between the last backup and the time of the crash, since your failover, at this stage of the procedure, can only use the data from the last backup, the only one available at this stage.

For example, suppose you’re pushing the pegs of a table named order whose id at the time of the crash was around 25000, on 15 September at 5:30pm, and your last known backup holds all the data up to 15 September at 3:00am.  Then, before commissioning your failover (= before opening it to service, in a hurry, I know . . . ), set an AUTO_INCREMENT index of 30000,“to be on the safe side”.
That way, you allow yourself some room to calmly reinject the difference, i.e.  the data from the day of the crash: between 15 September 3:00am and 5:30pm.
Obviously, at this stage the continuity of order numbers is a minor concern, you’ll agree . . .

Reminder

If you classically have MyISAM tables, the data is held in the files named after the tables.
With the InnoDB format, however, the .frm files only hold structural information, not data.  The data is intimately encoded in the ib* binary files.

This article follows a real case of a production machine damaged after an unrecoverable RAID accident:
the DB was running on an old machine, the mysql data directory was assumed intact, but a serious RAID problem prevented the OS from running normally, while the MySQL data itself was assumed uncorrupted.

When a database is damaged, you don’t necessarily know what is corrupted and what isn’t. In the situation I faced, I was fairly confident that the MySQL files themselves weren’t corrupted, as they sat on a healthy disk of the RAID system.
Luckily, they were still readable, by mounting one of the RAID disks on a restore machine.
But since MySQL on Debian partly relies on intrinsic permission/protection logic, the data directory can’t just be crudely copied (e.g.  tar) in the hope that the data will be read on the failover production machine.
So here’s a data recovery procedure that worked for me.
Aware that your incident today isn’t necessarily identical, I’ve tried to describe the context precisely, so you can decide whether to keep reading if you recognise yourself in my description.

The procedure

So you’ve chosen to carry on, very well!
So you’ve“pushed the pegs” on all the data tables likely to grow (as opposed to reference tables), great!
Then we assume an up-to-date schema is available somewhere, namely:

• in version control (it should be there!)
• on a test server where a recent version has been signed off
• at worst on a development server.

The machines involved in this accident scenario can be pictured as a triangle whose three vertices are three entities A, R and P.

• A is the“Accident” server vertex.
• R is the Restore server vertex, a machine made available for the restore exercise
• P is the new Production failover server, healthy, in service of course, but with only an earlier backup as live data, for example the“early morning” backup, i.e.  before business opens.

We pick a third restore machine with a very close MySQL version.

Prepare the minimal ingredients for success, i.e.  what I call the orthogonal base, the minimum necessary and sufficient to extract the data:
the ibdata1, ib_logfile0, ib_logfile1 files taken from the sick or dead server, all the *.frm files, plus a well-known schema in SQL format, which will let you recreate the set of constraints, you know, the very ones created by the commands
ALTER TABLE <tablename> ADD CONSTRAINT that usually come at the end of a dump.

Log in to it, and look for the password of the user the mysqld server runs as.  It’s a random password created when Debian was installed.
See /etc/mysql/debian.cnf on machine A, and note the password in it for later use.

On R:

• While mysqld is up, inject the recovered schema.
• This creates the basic structural files.
• Shut down the mysqld daemon.
• Copy the binary files and the *.frm files onto server R.
• Start mysqld as root.

GRANT ALL PRIVILEGES ON <databasename>.*
TO '<username>'@'localhost'
IDENTIFIED BY "<le mot de passe de debian.cnf>";
FLUSH PRIVILEGES;

• Run a mysqldump of this database on R, and off you go with this blessed dump.
• Edit this dump to add SET FOREIGN_KEY_CHECKS=0 at the top and SET FOREIGN_KEY_CHECKS=1 at the end of each dump, or even add the IGNORE keyword after the INSERTs.
• On server P, log in to mysql on the command line to inject the database recovered this way.

The fact that the injected dump comes from a healthy server, the recovery server R, reassures us, and lets us top up the failover server with live data right up to the time of the crash!