I discovered way too late that the backup script for our MySQL databases wasn't working properly. Namely, when the disk on the server had already started to fail, and I logged in via SSH and got a screen-full of corrupted binary rubbish. Restoring from the meagre backups that weren't just empty .sql.gz files would loose us about a month's data. I'd really love it if I can avoid that scenario.

I've been able to fsck the disk and have extracted the contents of the /var/lib/mysql directory, with a 100MB ibdata1, ib_logfile* files and .frm and .opt files for each database under subdirectories. I don't have /etc/mysql/my.cnf, everything under there seems to be lost. There are no .ibd files, presumably because innodb_file_per_table was turned off.

I got a MySQL 5.5 install (same version as failed) up on my development machine and attempted to restore the files, following the suggestions here, and here. I put the ibdata1 and .frm and .opt files into /var/lib/mysql set the permissions & owner on the files the same as the defaults (some mysql:mysql, some mysql:root) after I re-created the tables from the schema using the mysql client.

I then ran mysqld with the command:

/usr/sbin/mysqld –innodb_log_file_size=4128768 –innodb_force_recovery=6

It terminates immediately, with no output. A dmesg | tail shows a whole bunch of:

[  781.937089] init: mysql main process (1819) terminated with status 1
[  781.937127] init: mysql respawning too fast, stopped

Both /var/log/mysql.log and /var/log/mysql.err are empty.

I've tried a number of variations on this recovery process theme. I've tried copying the entire recovered mysql data directory into /var/lib, I've tried overwriting only ibdata1 and the database subdirectory with .frm and .opt folders. I've tried with, and without the logfiles, and incrementing the recovery value from 1-6.

If I restore the original mysql directory, mysqld starts up just fine. Apparmor isn't running on the machine (some people had problems with that).

I read on Percona's MySQL Performance Blog that it's possible to restore the data from just the ibdata1 file. I only have 2 tables per database, and I have the schema- but reading the README provided with their recovery tool, it looks like I at least need to be able to start mysql to get information about the tablespace.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.