Taking Physical Backups for MySQL

If you’ve ever had any sort of issue with MySQL, you have probably needed to restore a MySQL database. In that scenario, you have probably used mysqldump or Percona XtraBackup to accomplish the task – the mysqldump client is a backup app written by Igor Romanenko, it is mostly used to dump a database or a set of databases for backup procedures or transferring into another database server. Percona XtraBackup on the other hand is based on InnoDB’s crash-recovery functionality.

In general, backups can be categorized into two categories: physical and logical. Physical backups backup data at a system level while logical backups backup statements that re-create the data: this blog post on MySQLBackups should help you learn more about the differences between them.

What are Physical Backups?

As already mentioned above, physical backups backup data at a system level. In general, physical backups can be taken by following these steps:

1. Shut down the MySQL server – make sure to perform a clean shutdown (also known as a slow shutdown) – such a shutdown operation performs additional InnoDB flushing operations before completing. Simply specify the parameter innodb_fast_shutdown=0 in your my.cnf file or run the query SET GLOBAL innodb_fast_shutdown=0; – the value of innodb_fast_shutdown determines whether the backup operation is slow or fast: 0 performs a slow shutdown, 1 performs a fast shutdown. With a value of 2 InnoDB will shut down as if MySQL had crashed – recovery time is often slower when a value of 2 is in use.
2. Back up all files related to InnoDB (ibdata, .ibd, .frm, also the ib_logfile* files).
3. Back up the contents of your my.cnf configuration file.

Physical backups are typically faster than logical backups because they only involve file copying.

Physical Backup Gotchas – Restoring MyISAM and InnoDB

Some people may wonder if it’s possible to simply back up the files that their database engine is using and restore them on a different server. Is it feasible to do that? Well, sometimes.

To restore your MyISAM tables you can simply copy the .FRM, .MYD and .MYI files (.FRM files are the table definition files, .MYD files store the actual data and .MYI files store the indexes that are created on a table), however, everything isn’t so simple in InnoDB – InnoDB files cannot be copied in the same way MyISAM files can. Simply copying the .frm and .ibd files associated with InnoDB tables is asking for trouble – you should only do so if you can guarantee that the tablespace ID of the .ibd file matches exactly with the tablespace ID entry in the metadata of ibdata1.

Summary

Physical backups are comprised of the directories and files that store database contents. Such a way of backing up your data is often faster than using logical backups because only file copying is involved.