If you have ever used MySQL, you have probably taken backups of your data. And if you have ever taken backups of your data, chances are you have heard of two backup types: physical and logical backups.Physical backups backup data at a filesystem level (physical backups are raw copies of directories and files that store database contents).Logical backups backup statements that re-create the data.

Physical Backups of InnoDB

In order to take a physical clod backup of InnoDB, you would need to perform a slow shutdown of the engine (slow shutdown can be specified by setting the innodb_fast_shutdown variable to 0), back up all directories and files related to it, and then restore the engine. The files are:

  • The ibdata1 file, storing several data classes of information vital for InnoDB to function correctly (the file stores data and indexes of InnoDB tables, table metadata, MVCC data, the insert buffer, and the doublewrite buffer);
  • The ib_logfile* files, containing the InnoDB redo logs;
  • The .ibd files, which store tables that constitute a database;
  • The .frm files, which describe the format of the tables;
  • The my.cnf file, that contains all of the configuration information.

Do note that just copying the .ibd and .frm files alone is asking for trouble – only do that if you can guarantee that the tablespace ID of the .ibd file is an exact match to the tablespace ID in the metadata of the ibdata1 file.
The data directory can be backed up by running the query SHOW GLOBAL VARIABLES LIKE ‘datadir’; to figure out its location, then copying the data.

Physical Backups of MyISAM

Taking a physical backup of MyISAM is not as complex. To take a physical backup of MyISAM:

  1. Lock all tables, perform a read lock on them and issue a SLEEP statement for a few minutes;
  2. Copy the .frm, .MYD, and .MYI files for every table running the MyISAM engine;
  3. After you have copied the above files, kill the SLEEP statement.

You might also want to take a copy of the my.cnf file that contains all of the configuration information.

Logical Backups

Logical backups are performed when the MySQL server is still running – the server is not taken offline. To take a logical backup you can utilize the mysqldump utility (the utility is covered in-depth in this blog). One of the simplest usage scenarios of the tool are as follows:

$ mysqldump -u root -p database_name > backup.sql
Here
root is your user, database_name is the database name that needs to be backed up, and the backup.sql is the file name of the backup.
You can also take a backup by using the SELECT … INTO OUTFILE … query.

To restore a logical backup, import the .sql format dump files through the mysql client or through another tool like phpMyAdmin. Backups taken using the SELECT ... INTO OUTFILE … queries can be recovered by using the LOAD DATA INFILE query.

Summary

Physical backups are backups that copy the actual data files, logical backups are backups that copy statements that re-create the data. Logical backups can only be taken when the database is running, physical backups can be taken when the database is online (hot backup) or when it’s offline (cold backup) – before planning your backup strategy, evaluate your needs carefully and choose the method most suitable for you.

Contributor
Comments to: MySQL Physical vs Logical Backups

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

Attach images - Only PNG, JPG, JPEG and GIF are supported.