Home > Documentation > MySQL

How to Backup and Restore MySQL Database

This article discovers some basic yet efficient practices of backup and restore for MySQL databases. In generic there are two approaches to these database administration tasks: physical (raw) and logical. We should start from comparing those types of backups.

Physical Backup

Physical backup is a raw copying of the files storing database contents via tools and utilities provided by particular OS. This type of backup provides high performance and so is a good choice for large databases that require to be restored as quickly as possible in case of any problems. MySQL Enterprise Backup can handle hysical backup. The same tool is able to restore any type of MySQL storage engine except NDB. NDB tables should be restored using ndb_restore.

Logical Backup

Logical backup is the most flexible option as it stores the information according to logical database structure. Database objects are exported in form of the correspoding CREATE-statements (CREATE DATABASE, CREATE TABLE, etc). And the data is stored as INSERT-statements or delimited-text files. Two most popular tools for logical backup are mysqldump and mydumper.

To restore SQL-form of logical backups the standard mysql command line client can be used. Delimited-text files can be loaded into MySQL database via LOAD DATA statement or the mysqlimport tools.

Logical vs Physical

Now it is time to compare key characteristics of both backup methods:

Availability - physical backups can be done only when the MySQL server is not running. Logical backups can be performed on live instance of MySQL with the appropriate locking to prevent database changes
Discreteness - despite storage engine, logical backup can be done at the server level (all databases), database level (all tables in the database), or table level. Physical backup is performed on data directory level or the level of individual files
Performance - physical backup is faster than logical because some data conversion is required for the second option
Portability - physical backups can be restored on machines having similar hardware characteristics with the source system, while logical backups do not have such limitation
Volume - storage of physical backup is more compact than logical, since it contains only data in binary form without SQL statements

Have questions? Contact us