How do You Restore MySQL Databases From Physical Files?

Open Restoring MyISAM configuration options
If you have taken a physical backup of your database and have a need to restore it, you have probably wondered how. This blog post should provide you with some insight on how to do that.

Restoring MyISAM

If you want to restore data running on the MyISAM engine, keep in mind that the data in this engine consists of three files:

1. The .frm file consisting of the table definition.
2. The .MYD file stores the actual data of a MyISAM table.
3. The .MYI file housing the indexes that are created on a table.

If you have these files with you, simply copy them into the database folder (the default location is /var/lib/mysql) and your MyISAM data should be successfully restored.

Restoring InnoDB

Now InnoDB is another beast altogether. Keep in mind that the data from this engine cannot be restored as easily as MyISAM tables can, due to its structure. Here’s how InnoDB looks from the inside (image by Percona’s CTO Vadim Tkachenko):

Simply copying the .frm and .ibd files will do more harm than good. Copying those files is a good idea if (and only if) you can 100% guarantee that the tablespace ID of the .ibd file matches exactly with the tablespace ID entry in the metadata of ibdata1, otherwise you’re in line for trouble.

So since you cannot just copy the .frm and .ibd files, how do you restore InnoDB?

Data in InnoDB can be backed up (and restored) using Percona’s XtraBackup. Percona’s XtraBackup is based on InnoDB’s crash-recovery functionality. It copies your InnoDB data files, then performs crash recovery on them. While Percona’s XtraBackup is not within the scope of this post, we do have a post about it on MySQLBackups, so take a look if you wish.

Here’s how to restore InnoDB data with Percona’s XtraBackup (do note that Percona’s XtraBackup will only work properly if it is able to connect to the database server and perform operations on the server and the data directory):

• First, prepare your backup:

$ xtrabackup --prepare --target-dir=/backups/

• Copy the backup to the datadir of the server (data can also be moved by using –move-back instead of –copy-back):

$ xtrabackup --copy-back --target-dir=/backups/

Restoring Data with Backup Ninja

Backup Ninja can help you to easily manage your backups and it can also help you restore them locally or on the cloud. Backup Ninja can also be configured to only back up files or back up both the database and the files.

Summary

We hope that this blog post taught you something new in the MySQL backup world. Keep in mind that InnoDB tables cannot be restored as easily as MyISAM tables, but restoring them is important nonetheless. Also keep in mind that when restoring data, you are never alone – Backup Ninja can reduce your data recovery burden at least to some degree. Last but not least, you should always verify your backups – to do so, inspect the restored data and verify the table structure – SHOW TABLES; can be your friend here.