Recover MySQL InnoDB database from ibdata1 and frm

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 🙂 ).

FabioG

I’m trying to do this but when it gets to the “./constraints_parser -5 -f /var/lib/mysql/ibdata1.recovery >> out.$i” part i get an error saying “./constraints_parser: not found” any idea why this might happen?

Adam Gorge

I also tried innodb_force_recovery to recover MySQL InnoDB database but it did not work for me. I used another MySQL Recovery Software offered by Stellar Phoenix. It is extremely easy to use & repair my corrupt database. Thank you very much as this article gave me an idea to try MySQL Recovery Software. Great work!

Your email address will not be published. Required fields are marked *