innochecksum_changer

This is one of the tools built by the standard make command in the TwinDB source code download.

I haven't yet found any reference to it in the TwinDB documentation.

It has, however, saved my data, and almost certainly also saved a lot of my time fiddling about with all the other tools which I did find various bits of documentation about. I played with them enough to see what they could do, but I didn't go all the way and run each of them on every table of every database I wanted to recover.

Fortunately, I stopped and thought "hello, 'innochecksum_changer' is an interesting name for a tool, I wonder what it does…"

As its name suggests, innochecksum_changer will change (or just check and report errors, if that's what you want) the checksums on InnoDB files.

In my case, the data corruption which had occurred in my ibdata1 file was not severe, but almost any corruption will invalidate checksums, thus causing MySQL to barf on startup and fail to let you even try to repair any damage to your databases.

If you can at least repair the checksums so that they match the (partly-corrupted, admittedly) data, then you can at least start MySQL and investigate what data there is and what state it's in.

In my case I did the following:

1. Fix the checksums
2. Start MySQL (yay!)
3. Run mysqldump on each database in turn to recover what was possible
   • I note that this ran to completion, even though the import process later complained about some of the resulting input
   • This meant that I had a plain-text editable .sql file to work with to correct the data corruption (and a line number in the error report to tell me where to look for it) rather than just a MySQL server which wouldn't start because of "a problem with ibdata1"
4. Stop MySQL
5. Delete (or maybe just move elsewhere, for reasons of safety / paranoia / just in case) the files ibdata1, ib_logfile0 and ib_logfile1
6. Set the innodb_file_per_table option in my.cnf
7. Start MySQL
8. Import each database in turn from its corresponding .sql dump
   • Any databases which have problems can now be inspected in the comfort of a plain text .sql file, and with the helpful assistance of a line number in the import process error message
   • All the other databases are then immediately available as normal - you don't have to fix every last problem in order to get any part of the system working again

As a result, I got back all except one (of nineteen) of my databases, with (as far as I can tell) no data loss in the remaining eighteen, and the one which I didn't trivially recover was:

1. easily recreatable from origin data, therefore no loss at all, in effect
   • (okay, that's a bit lucky for me, but then again it was unlucky that my backups hadn't included the MySQL data directory, so I think this cancels out in terms of universal karma)
2. easily repairable if I'd needed to (I looked at the .sql dump file in the region of the error message, and the problem was obvious and easy to deal with using a text editor). Once I'd fixed the problem, another import showed a further problem, and there were 6 of these in total, matching up with the 6 error regions that ddrescue had reported to me when recovering ibdata1 in the first place.
   • Fixing each of these would have meant I'd end up with a database with 6 regions of data loss in it, but in my opinion that's either:
     • better than ending up with no database at all, or else
     • it's a lesson in making sure your backups are really backing up what you might need to restore

So, before you go to all the trouble of trying to work out how the different parts of TwinDB's toolset fit together, and how many iterations of table-by-table, per database, you'll need to go through, try using innochecksum_changer and see if it just solves nearly all your problems at (as they say) one swell foop.

Go up
Return to main index.