MENU


Backup and Recovery

Physical backups consist of raw copies of database directories and files; logical backups store the underlying structures and data in a portable, custom logical format. Physical backups are fast and compact, while logical backups are highly portable and can be performed while the server keeps running.

Beyond the methods on this page, file systems such as Veritas, LVM, or ZFS can take their own snapshots of secondary storage. If a server has performance problems while backups run, setting up Replication and performing backups on a replica rather than the source can help.

MySQL Workbench

MySQL Workbench's export/import tools can move data to and from a database, producing .sql files containing the SQL commands needed to recreate it.

mysqldump and mysqlimport

mysqldump performs logical backups, producing SQL statements that reproduce the original database object definitions and table data; it can dump one or more databases for backup or transfer, and can also generate CSV, other delimited text, or XML output.

mysqldump -uroot -pPASSWORD testDB > backup.sql

Saves database testDB to backup.sql. The username is root and the password is PASSWORD; since no host (-h) is given, localhost is assumed. Use -P to specify a port.

mysqldump -uroot -pPASSWORD testDB t1 t2 > backup.sql

Saves only tables t1 and t2 from testDB.

mysqldump -uroot -pPASSWORD --databases db1 db2 db3 > backup.sql

Saves databases db1, db2, and db3. --databases causes the CREATE DATABASE and USE statements to be included as well.

mysqldump -uroot -pPASSWORD --all-databases > x.sql

Dumps every database on the server.

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Exports the database structure without any data.

Other commonly used options:

A .sql file produced by mysqldump contains ordinary runnable SQL statements that recreate the dumped databases:

mysql < backup.sql

Restores the databases, assuming backup.sql contains its own CREATE DATABASE and USE statements.

mysqladmin create db2
mysql db2 < backup.sql

Restores the dump into database db2, even if the dump does not itself contain CREATE DATABASE/USE statements.

mysql db < backup.sql
mysqlimport db backup.txt

Restores a table using the two files generated by mysqldump --tab.

SOURCE backup.sql

Restores a database from within the mysql client itself.

MySQL Shell Dump Utilities

Older documentation and scripts sometimes reference mysqlpump, a utility that once produced parallelized logical dumps; mysqlpump and its lz4_decompress/zlib_decompress helper programs were removed in MySQL 8.4 and are no longer available – scripts that still invoke it must be updated. For instance-wide or multi-schema logical backups, the current recommended tool is MySQL Shell's (mysqlsh) built-in dump/load utilities, which parallelize work across threads, support compression and chunking of large tables, and can dump straight to (or load from) cloud object storage.

util.dumpInstance('/backups/full', {threads: 4})

Dumps every schema on the instance to the given directory using 4 parallel threads. Run inside mysqlsh's JavaScript or Python mode, connected to the source instance.

util.dumpSchemas(['db1', 'db2'], '/backups/schemas')

Dumps only the named schemas rather than the whole instance.

util.loadDump('/backups/full', {threads: 4})

Loads a dump directory produced by util.dumpInstance() or util.dumpSchemas() into a target server, in parallel.

SELECT INTO, LOAD DATA / LOAD XML, IMPORT TABLE

SELECT * FROM tbl LIMIT 1 INTO @a, @b;

SELECT ... INTO @var1, @var2, ... stores the column values of a single row into user variables.

SELECT * FROM tbl INTO OUTFILE 'a.txt'
  FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
     ESCAPED BY '^'
  LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE writes multiple rows to a file in a chosen format. Special characters should be escaped so the file can be read back reliably.

SELECT * FROM tbl LIMIT 1 INTO DUMPFILE 'aa.txt';

SELECT ... INTO DUMPFILE writes a single row to a file with no formatting applied.

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
       [TERMINATED BY 'string']
       [[OPTIONALLY] ENCLOSED BY 'char']
       [ESCAPED BY 'char']]
    [LINES [STARTING BY 'string'] [TERMINATED BY 'string']]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT} [, col_name={expr | DEFAULT}] ...]
Reads rows from a text file into a table at high speed. LOW_PRIORITY delays loading until no other client reads the table; CONCURRENT allows reads during loading; LOCAL reads the file from the client host rather than the server. REPLACE overwrites rows with a matching primary/unique key; IGNORE skips such rows – without either, the rest of the file is ignored when a duplicate is found and LOCAL is not specified. IGNORE number LINES skips leading lines.
If the field's ENCLOSED BY character is ", an instance of it is treated as terminating a field only when followed by the field or line terminator – so "Hello ""WORLD"" !" is read as Hello "WORLD"!

LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt'
  INTO TABLE tbl(column1, @var) SET column2 = @var*100;

A plain load, and a load that computes one column's value from another via a user variable.

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE [db_name.]tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<tagname>']
    [IGNORE number {LINES | ROWS}]
    [(column_or_user_var,...)]
    [SET col_name = expr,...]

Reads data from an XML file into a table. To write a table out as XML instead, invoke the mysql client with --xml and -e: mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml.

IMPORT TABLE FROM sdi_file [, sdi_file] ...

Imports MyISAM tables based on information contained in .sdi (serialized dictionary information) metadata files. Tables should be flushed and locked before their .sdi files are copied.


# On the exporting server: flush and lock the tables, then copy their .sdi
# and MyISAM data/index files out of the data directory
mysql> FLUSH TABLES hr.employees, hr.managers WITH READ LOCK;

$ cd export_basedir/data/hr
$ cp employees_125.sdi /tmp/export
$ cp managers_238.sdi /tmp/export
$ cp employees.{MYD,MYI} /tmp/export
$ cp managers.{MYD,MYI} /tmp/export

mysql> UNLOCK TABLES;

# On the importing server: create the schema, stage the copied files, then
# import the tables from their .sdi metadata
mysql> CREATE SCHEMA hr;

$ cd /tmp/export
$ cp employees_125.sdi /tmp/mysql-files
$ cp managers_238.sdi /tmp/mysql-files
$ cp employees.{MYD,MYI} import_basedir/data/hr
$ cp managers.{MYD,MYI} import_basedir/data/hr

mysql> IMPORT TABLE FROM
    '/tmp/mysql-files/employees.sdi',
    '/tmp/mysql-files/managers.sdi';

LOCK INSTANCE FOR BACKUP

LOCK INSTANCE FOR BACKUP
UNLOCK INSTANCE

Acquires an instance-level backup lock that permits DML during an online backup while preventing operations that could produce an inconsistent snapshot.

Copying Files (MyISAM)

MyISAM tables can be backed up by copying their table files (*.MYD, *.MYI, and associated *.sdi files). For a consistent backup, either stop the server, or lock and flush the relevant tables first:

FLUSH TABLES tbl_list WITH READ LOCK;

Only a read lock is needed, letting other clients keep querying while files are copied. The flush ensures active index pages are written to disk before the backup starts. This method does not work for InnoDB tables, whose modified data may still be cached in memory and not yet flushed to disk even when the server is idle.

Binary Log

MySQL supports incremental backups using the binary log, which records the information needed to replicate changes made after a given backup point. To allow a server to be restored to a point in time, binary logging must be enabled.

Enterprise Backups

MySQL Enterprise Edition customers can use MySQL Enterprise Backup to perform physical backups of entire instances or selected databases and tables, including incremental and compressed backups. Backing up the physical database files restores much faster than logical techniques like mysqldump. InnoDB tables are copied using a hot backup mechanism (ideally InnoDB tables represent a substantial majority of the data); tables from other storage engines use a warm backup mechanism. Because MEMORY tables are not stored on disk, MySQL Enterprise Backup provides its own mechanism to retrieve their contents during a backup. 

# Full database dump
mysqldump -uroot -pPASSWORD testDB > backup.sql

# Dump specific tables only
mysqldump -uroot -pPASSWORD testDB t1 t2 > backup.sql

# Dump several databases, including CREATE DATABASE/USE statements
mysqldump -uroot -pPASSWORD --databases db1 db2 db3 > backup.sql

# Dump every database on the server
mysqldump -uroot -pPASSWORD --all-databases > x.sql

# Schema only, no data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

# Restore a dump that contains its own CREATE DATABASE/USE statements
mysql < backup.sql

# Restore into a specific, already-created database
mysqladmin create db2
mysql db2 < backup.sql

# Restore a --tab dump (schema .sql + data .txt) with mysqlimport
mysql db < backup.sql
mysqlimport db backup.txt

// Run from the mysqlsh JavaScript prompt (or `mysqlsh --js -f this_file.js`),
// connected to the source instance. mysqlpump is removed as of MySQL 8.4 --
// these util.* dump/load functions are the modern replacement.

// Dump every schema on the instance
util.dumpInstance('/backups/full', {threads: 4});

// Dump only specific schemas
util.dumpSchemas(['db1', 'db2'], '/backups/schemas');

// Load a dump back into a (possibly different) server
util.loadDump('/backups/full', {threads: 4});

-- Store a single row's column values into user variables
SELECT * FROM tbl LIMIT 1 INTO @a, @b;

-- Export rows to a delimited file
SELECT * FROM tbl INTO OUTFILE 'a.txt'
    FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        ESCAPED BY '^'
    LINES TERMINATED BY '\r\n';

-- Write a single row to a file with no formatting applied
SELECT * FROM tbl LIMIT 1 INTO DUMPFILE 'aa.txt';

-- Re-import that same file
LOAD DATA INFILE 'a.txt' INTO TABLE tbl
    FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        ESCAPED BY '^'
    LINES TERMINATED BY '\r\n';

-- Load with a computed column via a user variable
LOAD DATA INFILE 'data.txt'
    INTO TABLE tbl(column1, @var) SET column2 = @var * 100;

-- Load an XML file
CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

LOAD XML LOCAL INFILE 'person.xml'
    INTO TABLE person
    ROWS IDENTIFIED BY '<person>';

-- Instance-level backup lock around a physical file copy
LOCK INSTANCE FOR BACKUP;
-- ... copy files at the OS level here ...
UNLOCK INSTANCE;