intro
Let’s learn how to dump a MySQL database with the mysqldump command-line utility and explore all its options for advanced use.
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:
1
mysqldump [options] <db_name> [table_name_1 ... table_name_n]
Where:
Instead, the mysqldump all databases dump syntax is:
1
mysqldump [options] --all-databases
If you need to export only a few databases, the syntax is:
1
mysqldump [options] --databases db_name_1 ... db_name_n
Where:
mysqldump Options: Complete List
Here is a complete list of all mysqldump options currently available:
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
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:
1
mysql -u admin new_company < company_backup.sql
Dump a Database Into a Directory With a Dump for Table
1
mysqldump -u admin company --tab=company_backup
The result will be a company_backup folder containing:
Export Data Only
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
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:
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:
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:
1
mysqldump [options] > dump.sql
Alternatively, you can achieve the same result with the --result-file flag:
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:
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:
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:
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.
