From:       To:      
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:

Have questions? Contact us

See also

Useful MySQL Queries
Tuning MySQL Performance
Configuring MySQL for Intelligent Converters