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 thewhich
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\
, whereX.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_file
Code language: plaintext (plaintext)
2) Dump one or more databases
mysqldump -u root -p [options] --databases db_name ... > output_file
Code language: plaintext (plaintext)
3) Dump all databases
mysqldump -u root -p [options] --all-databases > output_file
Code 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:
Option | Short Form | Meaning |
---|---|---|
–user | -u | username |
–password | -p | password |
–add-drop-database | N/A | Add a DROP DATABASE statement before each CREATE DATABASE statement |
–add-drop-table | N/A | Add a DROP TABLE statement before each CREATE TABLE statement. |
–add-drop-trigger | N/A | Add a DROP TRIGGER statement before each CREATE TRIGGER statement. |
–add-locks | N/A | Enclose each table dump with LOCK TABLES and UNLOCK TABLES statements. |
–all-databases | -A | Perform a database dump for all tables in all databases. |
–databases | -B | Dump one or more databases by instructing mysqldump to treat all name arguments on the command line as database names. |
–no-data | -d | Do not include any row information for the table. |
–result-file | -r | Direct the output to a specified file. If the file already exists, the tool will overwrite it. |
–routines | -R | Include stored routines (procedures and functions) when dumping the databases. |
–no-create-db | -n | Suppress the CREATE DATABASE statements that would otherwise be included in the output when the --databases or --all-databases option is given. |
–no-create-info | -t | Do not include CREATE TABLE statements for each dumped table. |
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_file
Code 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. Replaceusername
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 tellsmysqldump
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 ofmysqldump
to a file specified bypath_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.sql
Code 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.sql
Code 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_file
Code 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.sql
Code 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_file
Code 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.sql
Code 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_file
Code 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.sql
Code 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_file
Code 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.sql
Code 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_file
Code 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.sql
Code language: plaintext (plaintext)
Summary
- Use the
mysqldump
utility program to create backups of databases in MySQL Server.