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:
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);
ib_logfile*files, containing the InnoDB redo logs;
files, which store tables that constitute a database;
.frmfiles, which describe the format of the tables;
my.cnffile, that contains all of the configuration information.
Do note that just copying the
.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
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:
- Lock all tables, perform a read lock on them and issue a SLEEP statement for a few minutes;
- Copy the
.MYIfiles for every table running the MyISAM engine;
- 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 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.
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.