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
ib_logfile* files 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: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
/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.