MySQL Backup Utility: mysqldump

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

Introduction to the mysqldump tool

The mysqldump is a command-line utility in MySQL used for creating backups of MySQL databases.

The mysqldump tool allows you to dump the structure and/or data of one or more databases into a file, which you can use to restore the databases later.

In practice, you often use the mysqldump for backup and restore operations, database migration, and transferring databases between servers.

mysqldump tool location

The mysqldump tool typically comes with the MySQL server installation by default. Its location depends on your operating system:

• Linux: on Linux systems, including distributions like Ubuntu, CentOS, and others, you can find mysqldump in the /usr/bin/ directory. You can use the which command to locate it: which mysqldump
• Windows: on Windows, you can find the mysqldump in the directory C:\Program Files\MySQL\MySQL Server X.Y\bin\, where X.Y is the version number of the MySQL database server.
• macOS: Like Linux systems, you can find the mysqldump at /usr/bin/ directory on macOS.

It’s a good practice to include the directory that contains mysqldump in your system’s PATH environment variable so that you can invoke the command from any location in the command prompt or terminal.

Basic syntaxes of the mysqldump tool

There are three basic syntaxes for using the mysqldump tool:

1) Dump one or more tables

mysqldump -u root -p [options] db_name [tbl_name ...] > output_fileCode language: plaintext (plaintext)

2) Dump one or more databases

mysqldump -u root -p [options] --databases db_name ... > output_fileCode language: plaintext (plaintext)

3) Dump all databases

mysqldump -u root -p [options] --all-databases > output_fileCode language: plaintext (plaintext)

The mysqldump options

The mysqldump tool provides you with two different ways to specify an option: long form and short form.

For example, if you want to specify the long form for the user, you can use the --user=username option:

--user=username

Alternatively, you can use the shorter and more concise option -u username:

-u username

Here are some common mysqldump options:

The mysqldump tool examples

Let’s explore some examples of using the mysqldump tool to create backups.

1) Creating a backup of a single database

The following command creates a backup of a single database:

mysqldump -u username -p -B db_name > path_to_backup_fileCode language: SQL (Structured Query Language) (sql)

In this syntax:

• mysqldump: This is the command-line utility for MySQL database backups.
• -u username: This option specifies the MySQL user to use for the connection. Replace username with the actual username you want to use.
• -p: This option prompts you for the password associated with the MySQL user specified by the -u option. After entering the command, you will be prompted to enter the password interactively.
• -B: This option tells mysqldump to treat the next argument as a database name, rather than a list of tables. In your command, db_name is the database name you want to back up.
• db_name: Replace this with the name of the MySQL database you want to back up.
• > path_to_backup_file: This part of the command redirects the output of mysqldump to a file specified by path_to_backup_file. This is where the backup data will be stored.

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

mysqldump -u root -p -B classicmodels > D:\backup\classicmodels.sqlCode language: plaintext (plaintext)

It’ll prompt you to enter a password for the root account. After inputting a valid password for the root account, the tool will dump the classicmodels database into the file D:\backup\classicmodels.sql

If you want to put the password in the command, you can use the --password long-form option:

mysqldump -u root --password=Abcd1234 -B classicmodels > D:\backup\classicmodels.sqlCode language: plaintext (plaintext)

It’ll issue a warning:

mysqldump: [Warning] Using a password on the command line interface can be insecure.Code language: plaintext (plaintext)

2) Creating a backup of multiple databases

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

mysqldump -u username -p -B <dbname1>[,<dbname2>, ...] > path_to_backup_fileCode language: plaintext (plaintext)

In this syntax:

• mysqldump: This is the command-line utility for MySQL database backup.
• -u username: This option specifies the MySQL user to be used for the operation. You would replace “username” with the actual MySQL username.
• -p: This option prompts for the MySQL user’s password. After entering the command, you will be prompted to provide the password.
• -B: This option is used to indicate that one or more databases will follow. It’s followed by the list of databases you want to include in the backup.
• <dbname1>[,<dbname2>, ...]: This part specifies the name of the database or databases you want to back up. You can list multiple databases separated by commas.
• > path_to_backup_file: This part of the command redirects the output of the mysqldump operation to a file specified by “path_to_backup_file”.

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

mysqldump -u root -p --databases classicmodels world > D:\backup\databases.sqlCode language: plaintext (plaintext)

3) Creating a backup of all databases

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

mysqldump -u username -p -A > path_to_backup_fileCode language: plaintext (plaintext)

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

mysqldump -u root -p -A > D:\backup\all_databases.sqlCode language: plaintext (plaintext)

4) Creating a backup of specific tables in a database

To make a backup of one or more tables from a database, you use the following command:

mysqldump -u username -p db_name tbl_name1, tbl_name2,... > path_to_backup_fileCode language: plaintext (plaintext)

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

mysqldump -u root -p classicmodels employees > d:\backup\employees.sqlCode language: plaintext (plaintext)

5) Creating a backup of the database structure only

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

mysqldump -u username -p -d db_name > path_to_backup_fileCode language: plaintext (plaintext)

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

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

mysqldump -u root -p -d classicmodels > d:\backup\classicmodels.sqlCode language: plaintext (plaintext)

6) Creating a backup of data only

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

mysqldump -u username -p -t db_name > path_to_backup_fileCode language: plaintext (plaintext)

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 from the database classicmodels:

mysqldump -u root -p -t classicmodels > d:\backup\classicmodels.sqlCode language: plaintext (plaintext)

Summary

• Use the mysqldump utility program to create backups of databases in MySQL Server.