If you’re a MySQL DBA, you’re probably taking backups of your data. Imagine a situation: you lost all of your data, but you do have the .ibd files and the rest of the “default” files associated with InnoDB (ibdata1 and the log files assuming you ran InnoDB). How do you recover your data? Is it even possible? In this blog we will answer that question.
Understanding Your Options
Of course, as you might know recovering InnoDB-based data from .ibd files alone isn’t the easiest of tasks because InnoDB is dependent on ibdata1 since the file stores data and indexes derived from InnoDB tables and so it’s very interlinked with InnoDB itself - you might need to recover your InnoDB tables from .ibd files when you have accidentally removed the ibdata1 file or your ibdata1 file is corrupted.
Before beginning, set the innodb_force_recovery value to 5 in my.cnf (the value of 5 does not look at undo logs when starting the database and sets InnoDB to read-only): also enable innodb_file_per_table and avoid touching your database folder inside of /var/lib/mysql.
- Restart the MySQL service to recreate ibdata1, then take a backup of your database folder.
- Login to MySQL.
- Create a dummy database with the same name. Then, create a dummy table with the same name as the corrupted one (don’t mind the table structure for now).
- Stop the MySQL service, copy the .frm file from the backup you took to replace the .frm file.
- Start MySQL and have a look at the structure of your table - it should now be in place! However, don’t get too happy yet.
- Issue a SHOW CREATE TABLE statement and copy its contents, then create the table with them.
- Stop MySQL, copy the .ibd file from the backup directory to /var/lib/database_name and replace the existing .ibd file from the dummy table.
- Now it’s time for Percona’s tools to shine - download and install the Percona Data Recovery Tool for InnoDB, then run the following (here -o represents the full location of your ibdata1 file, -f represents the full location of your .ibd file, -d represents the database name and -t represents your table name):
ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/database_name/table_name.ibd -d database_name -t table_name
- Now, run a checksum check against InnoDB - make sure you get no error messages (you might need to run this tool several times):
- Finally, you should be good to go - simply start MySQL.
Recovering InnoDB Data with Backup Ninja
Although Backup Ninja cannot help you recover orphaned tables at the moment, but it can help with all kinds of backups - be it full, partial or incremental. Oh, and logical and physical backups aren’t out of the question too! Backup Ninja provides you with a single location to manage backups for multiple databases with different database vendors and backup methods.
Recovering your orphaned InnoDB tables isn’t as hard as it might seem from first glance: you just need to know your way around MySQL and know how to utilize Percona’s toolkit. Backup Ninja can also help you with all kinds of backups while also providing you with a single location to manage backups for multiple database management systems.