Backup
MySQL
SQL

mysqldump: How to Backup and Restore MySQL Databases

intro

Let’s learn how to dump a MySQL database with the mysqldump command-line utility and explore all its options for advanced use.

Tools used in the tutorial
Tool Description Link
MySQL logo MySQL
THE MYSQL DATABASE

mysqldump is a versatile MySQL tool for creating database backups. In this guide backed by real-world examples, you will learn how to use it to master the art of logical backups in a MySQL database.

Let's dive in!

What Is mysqldump?

mysqldump is a command-line client for creating logical backups in MySQL. In particular, it procures a set of SQL statements that are executed to reproduce the original object definitions and table data in the dumped databases. The tool can also generate output in CSV, other delimited text, or XML format.

For an equivalent tool, but in PostgreSQL, read our complete guide on pg_dump.

How To Use the mysqldump Command

The basic syntax of the mysqldump command to dump a single database or tables from it is:

Copy
        
1 mysqldump [options] <db_name> [table_name_1 ... table_name_n]

Where:

  • [options] is an optional list of the mysqldump options and flags we will explore in the next section.
  • <db_name> is the name of the MySQL database to backup.
  • [table_name_1 ... table_name_n] is an optional list of tables to be dumped, separated by spaces. If omitted, the backup tool will dump all tables in the specified database.

Instead, the mysqldump all databases dump syntax is:

Copy
        
1 mysqldump [options] --all-databases

If you need to export only a few databases, the syntax is:

Copy
        
1 mysqldump [options] --databases db_name_1 ... db_name_n

Where:

  • db_name_1 ... db_name_n is the mandatory list of databases to be dumped, separated by spaces.

mysqldump Options: Complete List

Here is a complete list of all mysqldump options currently available:

  • --add-drop-database: Add a DROP DATABASE statement before each CREATE DATABASE statement.
  • --add-drop-table: Add a DROP TABLE statement before each CREATE TABLE statement.
  • --add-drop-trigger: Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
  • --add-locks: Surround each table dump with LOCK TABLES and UNLOCK TABLES statements.
  • --all-databases: Dump all tables in all databases.
  • --allow-keywords: Allow the creation of column names that are keywords.
  • --apply-replica-statements: Include STOP REPLICA prior to the CHANGE REPLICATION SOURCE TO statement and START REPLICA at the end of the output.
  • --bind-address: Use specified network interface to connect to MySQL Server.
  • --character-sets-dir: Directory where character sets are installed.
  • --column-statistics: Write ANALYZE TABLE statements to generate statistics histograms.
  • --comments: Add comments to the dump file.
  • --compact: Produce a more compact output.
  • --compatible: Produce an output that is more compatible with other database systems or with older MySQL servers.
  • --complete-insert: Use complete INSERT statements that include column names.
  • --compression-algorithms: Permitted compression algorithms for connections to the server.
  • --create-options: Include all MySQL-specific table options in CREATE TABLE statements.
  • --databases: Interpret all name arguments as database names.
  • --debug: Write debugging log.
  • --debug-check: Print debugging information when the operation completes.
  • --debug-info: Print debugging information, memory, and CPU statistics when program exits.
  • --default-auth: Authentication plugin to use.
  • --default-character-set: Specify default character set.
  • --defaults-extra-file: Read the specified option file in addition to usual option files.
  • --defaults-file: Read only the specified option file.
  • --defaults-group-suffix: Option group suffix value.
  • --delete-source-logs: On a replication source server, delete the binary logs after performing the dump operation.
  • --disable-keys: For each table, surround INSERT statements with statements to disable and enable keys.
  • --dump-date: Include dump date as "Dump completed on" comment if -comments is given.
  • --dump-replica: Include the CHANGE REPLICATION SOURCE TO statement that lists binary log coordinates of the replica's source.
  • --enable-cleartext-plugin: Enable cleartext authentication plugin.
  • --events: Dump events from dumped databases.
  • --extended-insert: Use multiple-row INSERT syntax.
  • --fields-enclosed-by: This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA.
  • --fields-escaped-by: This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA.
  • --fields-optionally-enclosed-by: This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA.
  • --fields-terminated-by: This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA.
  • --flush-logs: Flush MySQL server log files before starting the dump.
  • --flush-privileges: Emit a FLUSH PRIVILEGES statement after dumping a MySQL database.
  • --force: Continue even if an SQL error occurs during a table dump.
  • --get-server-public-key: Request RSA public key from the server.
  • --help: Display help message and exit.
  • --hex-blob: Dump binary columns using hexadecimal notation.
  • --host: Host on which MySQL server is located.
  • --ignore-error: Ignore specified errors.
  • --ignore-table: Do not dump the given table.
  • --include-source-host-port: Include SOURCE_HOST and SOURCE_PORT options in CHANGE REPLICATION SOURCE TO statement produced with -**-**dump-replica.
  • --insert-ignore: Write INSERT IGNORE rather than INSERT statements.
  • --lines-terminated-by: This option is used with the -**-**tab option and has the same meaning as the corresponding clause for LOAD DATA.
  • --lock-all-tables: Lock all tables across all databases.
  • --lock-tables: Lock all tables before dumping them.
  • --log-error: Append warnings and errors to the given file.
  • --login-path: Read login path options from mylogin.cnf.
  • --max-allowed-packet: Maximum packet length to send to or receive from the server.
  • --mysqld-long-query-time: Session value for slow query threshold.
  • --net-buffer-length: Buffer size for TCP/IP and socket communication.
  • --network-timeout: Increase network timeouts to permit larger table dumps.
  • --no-autocommit: Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements.
  • --no-create-db: Do not write CREATE DATABASE statements.
  • --no-create-info: Do not write CREATE TABLE statements that re-create each dumped table.
  • --no-data: Do not dump table contents.
  • --no-defaults: Read no option files.
  • --no-set-names: Same as -**-**skip-set-charset.
  • --no-tablespaces: Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output.
  • --opt: Shorthand for -**-**add-drop-table, -**-**add-locks, -**-**create-options, -**-**disable-keys, -**-**extended-insert, -**-**lock-tables, -**-**quick, -**-**set-charset.
  • --order-by-primary: Dump each table's rows sorted by its primary key, or by its first unique index.
  • --password: Password to use when connecting to the server.
  • --password1: First multifactor authentication password to use when connecting to the server.
  • --password2: Second multifactor authentication password to use when connecting to the server.
  • --password3: Third multifactor authentication password to use when connecting to the server.
  • --pipe: Connect to the server using the named pipe (Windows only).
  • --plugin-authentication-kerberos-client-mode: Permit GSSAPI pluggable authentication through the MIT Kerberos library on Windows.
  • --plugin-dir: Directory where plugins are installed.
  • --port: TCP/IP port number for connection.
  • --print-defaults: Print default options.
  • --protocol: Transport protocol to use.
  • --quick: Retrieve rows for a table from the server a row at a time.
  • --quote-names: Quote identifiers within backtick characters.
  • --replace: Write REPLACE statements rather than INSERT statements.
  • --result-file: Direct output to a given file.
  • --routines: Dump stored routines (procedures and functions) from dumped databases.
  • --server-public-key-path: Path name to the file containing RSA public key.
  • --set-charset: Add SET NAMES default_character_set to output.
  • --set-gtid-purged: Whether to add SET @@GLOBAL.GTID_PURGED to output.
  • --shared-memory-base-name: Shared-memory name for shared-memory connections (Windows only).
  • --show-create-skip-secondary-engine: Exclude SECONDARY ENGINE clause from CREATE TABLE statements.
  • --single-transaction: Issue a BEGIN SQL statement before dumping data from the server.
  • --skip-add-drop-table: Do not add a DROP TABLE statement before each CREATE TABLE statement.
  • --skip-add-locks: Do not add locks.
  • --skip-comments: Do not add comments to dump file.
  • --skip-compact: Do not produce more compact output.
  • --skip-disable-keys: Do not disable keys.
  • --skip-extended-insert: Turn off extended-insert.
  • --skip-generated-invisible-primary-key: Do not include generated invisible primary keys in the dump file.
  • --skip-opt: Turn off options set by -opt.
  • --skip-quick: Do not retrieve rows for a table from the server a row at a time.
  • --skip-quote-names: Do not quote identifiers.
  • --skip-set-charset: Do not write SET NAMES statement.
  • --skip-triggers: Do not dump triggers.
  • --skip-tz-utc: Turn off [tz-utc](<https://dev.mysql.com/doc/refman/8.3/en/time-zone-support.html>).
  • --socket: Unix socket file or Windows named pipe to use.
  • --source-data: Write the binary log file name and position to the output.
  • --ssl-ca: File that contains list of trusted SSL Certificate Authorities.
  • --ssl-capath: Directory that contains trusted SSL Certificate Authority certificate files.
  • --ssl-cert: File that contains X.509 certificate.
  • --ssl-cipher: Permissible ciphers for connection encryption.
  • --ssl-crl: File that contains certificate revocation lists.
  • --ssl-crlpath: Directory that contains certificate revocation-list files.
  • --ssl-key: File that contains X.509 key.
  • --ssl-mode: Desired security state of connection to the server.
  • --ssl-session-data: File that contains SSL session data.
  • --ssl-session-data-continue-on-failed-reuse: Whether to establish connections if session reuse fails.
  • --tab: Produce tab-separated data files.
  • --tables: Override -databases or B option.
  • --tls-ciphersuites: Permissible TLSv1.3 ciphersuites for encrypted connections.
  • --tls-version: Permissible TLS protocols for encrypted connections.
  • --triggers: Dump triggers for each dumped table.
  • --tz-utc: Add SET TIME_ZONE='+00:00' to dump file.
  • --user: MySQL user name to use when connecting to the server.
  • --verbose: Verbose mode.
  • --version: Display version information and exit.
  • --where: Dump only rows selected by the given WHERE condition.
  • --xml: Produce XML output.
  • --zstd-compression-level: Compression level for connections to servers that use zstd compression.

Note: The --opt option is enabled by default, which means that mysqldump will utilize a set of predefined options aimed at optimizing the backup process. These options include --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. This default configuration ensures efficient database backups in most common use cases.

For more information on how to use these options, refer to the official documentation. Keep in mind that some of them have a shortened version (e.g. -u and --user are equivalent mysqldump options).

mysqldump Example List

Now that you know how the MySQL dumping utility works, it is time to explore a complete mysqldump example list!

Note: In the examples in this section, the user will always be admin and the database name will always be company. Change those fields accordingly to make the instructions below work in your specific scenario.

Dump a Database Into an SQL Script Format

Copy
        
1 mysqldump -u admin company > company_backup.sql

The result will be a company_backup.sql file.

Given that SQL script, you can use it to populate a new_company database with mysql as follows:

Copy
        
1 mysql -u admin new_company < company_backup.sql

Dump a Database Into a Directory With a Dump for Table

Copy
        
1 mysqldump -u admin company --tab=company_backup

The result will be a company_backup folder containing:

  • table_name.sql files that contain the CREATE TABLE statement to create the tables.
  • table_name.txt files that contain their data.

Export Data Only

Copy
        
1 mysqldump -u admin company --no-create-info --no-create-db > company_data_only.sql

The resulting SQL script will not involve CREATE DATABASE and CREATE TABLE statements.

Export Schema Only

Copy
        
1 mysqldump -u admin company --no-data > company_schema_only.sql

This mysqldump schema only command produces an SQL script that contains only CREATE TABLE statements.

Include Only Some Databases

Suppose you only want to export databases users and employees. You can achieve that with:

Copy
        
1 mysqldump -u admin --databases 'users' 'employees' > company_backup.sql

Include Only a Few Tables

Assume you only want to dump tables orders and products. Achieve that goal with:

Copy
        
1 mysqldump -u admin company --tables 'orders' 'products' > company_backup.sql

Bear in mind that --databases and --all-databases flags have no effect when specifying a --tables option.

Conclusion

In this guide, you learned what mysqldump is and what it has to offer. This powerful MySQL command-line utility helps you generate database backups. The tool supports a lot of options, and here you had the opportunity to see them all. You also explored some of them with dedicated examples.

Creating effective MySQL dumps is not always easy, and you may prefer a more advanced tool like DbVisualizer! In addition to offering all the features of a powerful database client, DbVisulizer allows you to export your databases with just a few clicks. It also offers advanced query optimization capabilities and can generate ERD-like schemas. Try DbVisualizer for free today!

FAQ

Where does the output of mysqldump go?

By default, the output of mysqldump goes to the stdout. In detail, it produces a string in SQL format you can redirect to a dump.sql file with the following instruction:

Copy
        
1 mysqldump [options] > dump.sql

Alternatively, you can achieve the same result with the --result-file flag:

Copy
        
1 mysqldump [options] --result-file=dump.sql

On Windows, the main difference is that > produces a file that has UTF-16 encoding, while --result-file creates an output in ASCII format.

What is the path to mysqldump.exe?

On Windows, you can generally find mysqldump.exe in the bin directory of your MySQL local server installation. The target path should be something like this:

Copy
        
1 C:\Program Files\MySQL\MySQL Server <version>\bin\mysqldump.exe

Replace <version> with the version number of your MySQL server.

Is it possible to run mysqldump on a remote server?

Yes, you can run msqldump on a remote server using the procedure below:

  1. Use the -host or h option to specify the hostname or IP address of the remote MySQL server.
  2. Provide the -user or u option to specify the MySQL username to connect to the server to.

Ensure that the MySQL server allows remote connections. If required, adjust the firewall settings. You will also typically have to provide authentication details, such as a password. Otherwise, consider using advanced methods like SSH tunneling for secure connections.

What are the privileges required to run mysqldump?

mysqldump requires the following privileges:

  • For dumped tables: At least the SELECT privilege.
  • For dumped views: SHOW VIEW privilege.
  • For dumped triggers: TRIGGER privilege.
  • If the --single-transaction option is not used: LOCK TABLES privilege.
  • If the --no-tablespaces option is not used: PROCESS privilege.

Certain options might require other privileges. Check out the official documentation for more information. Note that to reload a MySQL dump file, you must have the privileges required to execute the statements that it contains. That means you need the appropriate CREATE privileges based on the objects created by the SQL statements in the dump.

How to safely access mysqldump?

To securely access mysqldump, consider storing your MySQL authentication details in a configuration file like my.cnf instead of specifying them via the command line. This ensures that sensitive information like usernames and passwords are not exposed in command history logs, which could be accessed by unauthorized users. Find out more in our guide on MySQL security.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13
title

SQL Server DATEADD: The Complete Guide

author Ochuko Onojakpor tags SQL SQL SERVER 7 min 2024-06-10
title

The SQL DELETE Statement Explained

author Leslie S. Gyamfi tags DELETE SQL 4 min 2024-06-03
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL HAVING Clause: The Ultimate Guide

author Antonello Zanini tags SQL 7 min 2024-05-27
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗