If you have ever dealt with MySQL, chances are you took backup copies. And if you ever took backup copies, chances are you verified them. Restoring MySQL data is one of the most important things to do after taking a backup of your data - in this blog post we will look through the similarities and differences between restoring two of the most popular MySQL engines - InnoDB and MyISAM.
Before we get into how to restore InnoDB, we should probably show you how it looks under the hood. Here’s how the busiest file in the InnoDB infrastructure - ibdata1 - looks like under the hood (image by Percona’s CTO Vadim Tkachenko):
The ibdata1 file consists of:
- The data of InnoDB tables.
- The indexes of InnoDB tables.
- InnoDB table metadata.
- The Multiversion Concurrency Control (MVCC) data.
- The doublewrite buffer - the purpose of such a buffer is to prevent data corruption.
- The insert buffer - such a buffer is used by InnoDB to buffer updates to the same page so they can be performed at once and not one after another.
The ibdata1 is interlinked with multiple other files - most notably, the log files which are the InnoDB redo logs. If MySQL ever crashes, starting up the MySQL daemon will read across those two files (ib_logfile0 and ib_logfile1) to check for any data changes that were not posted to the double write buffer in ibdata1 and redo them. If you’re using the innodb_file_per_table option (which was enabled by default from MySQL 5.6.6) know that each table running the InnoDB engine gets its own .ibd file making it easier to recover space in your tables.
In general, to restore data from the InnoDB engine you can use the data generated by mysqldump if you took a logical backup. If you took a physical copy of your data, follow these steps:
- Shut down MySQL (or MariaDB).
- Put all of your InnoDB files into their original locations (InnoDB files consist of the ibdata1 file, the ib_logfile* files containing the redo logs, the .ibd files and the .frm files), also make sure the contents of your my.cnf file are as they should be.
- Start MySQL / MariaDB.
For MyISAM, the main mechanism used for caching is the key cache. The main setting for the MyISAM key cache is the key_buffer_size - the MyISAM infrastructure is not as complex as InnoDB’s so restoring MyISAM is not as difficult as restoring InnoDB either. Simply copying the .frm, .MYD and .MYI files into the database folder will do the trick.
The Similarities and Differences of Restoring InnoDB and MyISAM
The similarities between restoring InnoDB and MyISAM include:
- You can restore data that was taken from tables running both InnoDB and MyISAM by using, for example, phpMyAdmin, through the CLI or using Percona’s XtraBackup. Percona’s XtraBackup will copy the MyISAM data first, InnoDB data next and the log files last.
- You can restore data that is taken from tables running both engines by using a physical backup: do note that doing so is more difficult on InnoDB than on MyISAM since you have to acquire all of the interlinked InnoDB files (we’re talking about the ibdata1 file, also the redo logs, the .ibd files and the .frm files)
The differences between restoring InnoDB and MyISAM include:
- MyISAM data is way easier to restore if a physical backup was taken.
- If you want to use COUNT(*) queries when verifying your backup, be advised that these sorts of queries will work faster on tables running the MyISAM engine since for MyISAM this number is stored in the table metadata.
- MyISAM does not have automatic crash recovery, but it does offer repair table functionality.
- Usually MyISAM tables have a smaller disk footprint than InnoDB tables.
Restoring Data with Backup Ninja
One of Backup Ninja’s propositions is its ability to create backups for open source databases like MySQL, PostgreSQL, Percona, MongoDB and MariaDB - automatically. Backup Ninja can also help you restore your backups:
To restore your backup using Backup Ninja, first decompress the backup, then restore it, apply the InnoDB log files to make sure the backup is consistent and make sure the ownership is for the user / group MySQL. After you’ve done that, restart your database service.
It is also worth noting that other than Backup Ninja, phpMyAdmin and the CLI, MySQL Enterprise Backup can also help you restore your data, but bear in mind that while the tool can back up non-InnoDB based data (for example, MyISAM tables), the server to be backed up must support InnoDB. If you want to learn more about how MySQL Enterprise Backup works, this post on MySQLBackups should provide more insight.
Usually data existing in both InnoDB and MyISAM engines can be restored pretty easily (that can be done via phpMyAdmin, by simply using a CLI or other tools), but since the InnoDB engine has a lot of related things under its hood, restoring the data associated with InnoDB is sometimes harder than restoring data associated with MyISAM - especially when dealing with physical backups. Another upside of MyISAM-based data recovery is that its tables usually have a smaller footprint on the disk. As far as backup verification is concerned, it can also sometimes be beneficial to see how many rows your tables have if you restore data that is used by the MyISAM engine since this engine allows you to utilize faster COUNT(*) queries to see the exact amount of rows existing in a table - it’s harder (although not impossible) to do so in InnoDB.