MySQL Backup and Restore with mysqldump

Introduction

A backup is the process of copying and archiving data so that it can be restored later in case of disaster. mysqldumpis a MySQL program that creates logical backups. This page explains the best practises to back up and restore a MySQL database using mysqldump.

This page is part of the MySQL administration series:

Logical vs Physical Backups

A backup is the process of copying and archiving data so that it can be restored later in case of disaster.

A logical backup copies the table structure and data, without copying the actual data files. Typically logical backup files have INSERT, CREATE DATABASE and CREATE TABLE statements. A physical backup copies the actual data files. For InnoDB tables, it copies the ibdata files, .FRM table files and redo log files.

Logical backups are more flexible and the administrator to search for and edit the backed up data. Physical backup, on the other hand, can be restored much faster.

Mysqldump

mysqldump is a program that creates logical backups. The dump files created usually contain CREATE TABLE  statements followed by INSERT statements containing the database data. The dump file is in plain-text format and can be easily searched and edited.  mysqldump is used to backup entire database servers, individual databases, individual tables, or even subsets of data.

General Usage

The following command backs up a database:

The following command backs up all databases in the server:

To export multiple databases:

NOTE: When using --databases  and --all-databases  parameters, mysqldump will add a CREATE DATABASE and USE database statements to the dump file.

To export specific tables from a database:

mysqldump -u=user –p db1 table1 table2 > dump.sql

Sometimes is also useful to compress the backup:

mysqldump -u=user –p db1 | gzip > dump.sql.gz

Reloading a dump file without database name information

When generating a dump file without the --databases or --all-databases parameters, the dump file generated has neither CREATE DATABASE nor USE database statements.

mysqldump -u=user –p db1 > dump.sql

This means that to load the dump file we need to specify the target database and ensure it exists.

mysql -u=user –p –D db1 < dump.sql

An alternative is to load it from within MySQL with the SOURCE statement.

Reloading a dump file created with –database or –all-databases parameters

The --databases  or --all-databases parameters adds a DROP DATABASE and CREATE DATABASE statements to the generated dump file. This means that the import will drop and recreate a database if already exists in the MySQL server. In addition, the dump file will only create a database with same name (unless the file is manually edited)

To load the dump file into the db1 database, there is no need to specify the target database in the command line:

Exporting a subset from a table

To export a subset of data from a database use the --where parameter:

One INSERT per row

To dump one INSERT statement per row, you can use the --skip-extended-insert:

By default, mysqldump has the --extended-insert parameter enabled. This option dumps multiple rows in each INSERT statement. This makes the dump file smaller and loads faster.

A typical INSERT statement with multiple values looks like this:

Exporting DDL or data only

By default mysqldump exports data and table creation statements.

To export only data, use the --no-create-info parameter:

To export only the creation statements, use the --no-data  parameter:

Backup Procedures, Triggers and Events

mysqldump copies trigger definitions by default, but it does not copy stored procedures or events.  To include store procedures and events, use the --routines and --events parameters:

Similarly, if you want to skip triggers, use the --skip-triggers parameter:

Common mysqldump Parameters

VariablePurpose
–databasesDump the defined databases.

Please note the dump file will contain CREATE DATABASE and USE statements

–all-databasesDump all databases in the database server.

Please note the dump file will contain CREATE DATABASE and USE statements

–local-all-tablesLock all tables across all databases

This option ensures consistency across all databases.

-7-lock-tablesLock all tables in a database before dumping that database.

This option ensures consistency within each database.

This option is enabled by default.

–routinesIncludes stored procedures
— eventsIncludes events
–skip-triggersSkips triggers
–extended-insertUse multiple-row INSERT syntax.

Each INSERT statement has multiple VALUES to insert several rows. This generates smaller dump files that load quicker.

This option is enabled by default.

–skip-extended-insertWrites one INSERT statement for each row
–ignore-tableIgnores a given table from the dump file
–tc-utcThis option allows importing TIMESTAMPs in a server using a different Time Zone.

This parameter add SET TIME_ZONE=’+00:00’ to the dump file to ensure all TIMESTAMPS are in UTC.

This option is enabled by default.

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply