Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Other links I've looked at (because this is a first post & I don't have 10 rep): mysqlperformanceblog.com/2011/06/03/… egil.biz/how-to-recover-mysql-data-from-innodb –  Inks May 1 '13 at 22:36
    
Just a shot-in-the-dark question: Do you have binary logging enabled ? –  RolandoMySQLDBA May 3 '13 at 15:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.