In this post I will deal with recovery from a corrupted InnoDB database. Remember that sometimes data cannot be recovered. That’s it. Deal with it and move on. As a matter this article is based on my findings when trying to recover several databases after a crash. In the end I couldn’t recover them, but I thought maybe my article will give you some ideas to try. Maybe it will work for you 🙂
If you already tried innodb_force_recovery with no success, prepare for the worst. I am assuming that your MySQL server is not started because of this.
First of all make a backup copy of you ibdata1 file, you will use this to work on it.
cd /var/lib/mysql dd if=ibdata1 of=ibdata1.recovery conv=noerror
Most of the following things are documented very well here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start but I think there are some issues with their approach. First of all they demonstrate how to recover a single table. That’s perfectly fine, but I had several databases crashed with a dozen of tables each, so I couldn’t afford the luxury to recover each table.
Now download the database recovery tools from percona, in your home directory:
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz tar zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz
Next make MySQL server start
cd /var/lib/mysql mv ibdata1 ibdata1.bak mv ib_logfile0 ib_logfile0.bak mv ib_logfile1 ib_logfile1.bak service mysqld start
The tricky part comes now. Create recovery database and within it create the table structure (this can be done from an old backup, or maybe you can use the frm files from the database you try to recover). Make sure that the tables are using InnoDB as engine.
The following script is modified a bit after the script provided as example here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:advanced_techniques. Put it in the same directory where you extracted the percona recovery tools.
#!/bin/sh db=$1 tables=`mysql -ss -u root -p -e "SHOW TABLES" $db` for i in $tables do #Check how many rows has a table rows=`mysql -u root -p -e "SELECT COUNT(*) FROM $i" -s $db` # Prepare environment echo "Restoring table $i" table=$i perl create_defs.pl --host=localhost --user=root --password=YOUR_PASSWORD --db=$1 --table=$table > include/table_defs.h.$table cd include && rm -f table_defs.h && ln -s table_defs.h.$table table_defs.h cd .. make clean all # Restoring rows found=0 while [ $found -lt 1 ] do echo "" ./constraints_parser -5 -f /var/lib/mysql/ibdata1.recovery >> out.$i found=1 done done
Now execute the script like:
sh recover-tables.sh recovery_database_you_created
If you are lucky you will get some output in out.TABLE_NAME. Clean the file and load the data into database.
I know the script looks like POS. Sorry for that. You can take a look at percona’s script and modify it to your needs. Please feel free to correct me if I am wrong in this post (probably I am 🙂 ).