Summary: in this tutorial, you will learn how to use the mysqldump tool to make a backup one or more databases in a MySQL Server.

Introduction to mysqldump tool

The mysqldump tool allows you to make a backup of one or more databases by generating a text file that contains SQL statements which can re-create the databases from scratch.

The mysqldump tool is located in the root/bin directory of the MySQL installation directory.

To access the mysqldump tool, you navigate to the root/bin folder and use the mysqldump command with the following options.

Here are the common mysqldump options:

add-drop-table

Includes a DROP TABLE statement for each table in the database.

add-locks

Includes LOCK TABLES and UNLOCK TABLES statements before and after each INSERT statement. It improves the data restoration speed from the dump file.

all-databases

Creates a dump of all databases on the MySQL server.

create-options

Includes ENGINE and CHARSET options in the CREATE TABLE statement for each table.

databases

Creates a dump of one or more databases.

disable-keys

Instructs MySQL to disable index updates during data load for MyISAM tables. MySQL will create indexes after mysqldump completes loading data. This option improves the speed of restoration.

extended-insert

Combines single-row INSERT statements into a single statement that insert multiple table rows; This option also helps speed up data restoration.

flush-logs

Flushes the server logs before dumping the data. This is useful in conjunction with incremental backups.

lock-tables

Ensures that the dump is a consistent snapshot by locking all the tables in a database during the dump.

no-data

create a dump file that contains statements necessary for re-creating the database structure only (only CREATE DATABASE, CREATE TABLE…), not the data (no INSERT statements).

opt

The mysqldump tool uses the opt by default.

The opt option enables the following options: add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, and set-charset.

To disable this option, you use skip-opt. If you want to skip each individual option, you use skip-<option_name>. For example, to skip the disable-keys option, you use skip-disable-keys option.

quick

Instructs mysqldump to not buffer tables in memory before writing to the file. This option speeds up dumps from big tables.

result-file

Specifies the path to the output dump file.

set-charset

Specifies the character set such as latin1 or utf8 of the database.

tables

Creates a dump of one or more tables.

where

Dumps only rows that satisfies a condition in the WHERE clause.

Using the mysqldump tool to make a backup of databases

Let’s take some examples of using the mysqldump tool to backup database examples.

1) Using the mysqldump tool to make a backup of a single database

The following command backs up a single database from a MySQL Server:

Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The <username> is the user account that will login to the MySQL Server.
• The <password> is the password for the <username>.
• The <path_to_backup_file> is the path to the backup file.
• The --databases is an option that instructs the mysqldump tool to create a dump of the specified databases.
• The <database_name> is the name of the database that you want to back up.

For example, the following command creates a backup of the database classicmodels to the file c:\backup\classicmodels.sql:

2) Using the mysqldump tool to make a backup of multiple databases

To make a backup of multiple databases, you specify a list of the database names after the --database option:

Code language: HTML, XML (xml)

For example, the following command makes a backup of the classicmodels and world databases:

3) Using the mysqldump tool to make a backup of all databases

To make a backup of all databases in a MySQL Server, you use the –all-database option:

Code language: HTML, XML (xml)

The following statement makes a backup of all databases in the current MySQL server:

4) Using the mysqldump tool to make a backup of specific tables from a database

To make a backup of specific tables from a database, you use the following command:

Code language: HTML, XML (xml)

You can also specify multiple tables after the database name, one after the other:

Code language: HTML, XML (xml)

For example, to make a backup of the employees table from the classicmodels database, you use the following command:

5) Using a mysqldump tool to make a backup of database structure only

To make a backup of the database structure only, you use the --no-data option:

Code language: HTML, XML (xml)

The statement will generate a dump file that contains the SQL statement necessary to re-create the database structure. And the dump file does not contain INSERT statements.

For example, the following statement makes a backup of the database structure of the database classicmodels:

6) Using the mysqldump tool to make a backup of data only

To make a backup of the database data only, you use the --no-create-info option:

Code language: HTML, XML (xml)

The statement will generate a dump file that contains the SQL statements necessary to lock tables and insert data into the tables. It has no CREATE TABLE statements.

The following command makes a backup of data of the database classicmodels:

In this tutorial, you have learned how to use the mysqldump tool to make a backup of databases in MySQL Server.