How to Use mysqlpump for Efficient MySQL Backups

Use mysqlpump to export MySQL databases by running it from the command line with options like --user, --password, and --databases. It creates logical backups faster than mysqldump by using parallel processing and supports exporting multiple databases at once.

Syntax

The basic syntax of mysqlpump is:

• mysqlpump [options] [databases]

Key parts explained:

• --user: MySQL username
• --password: Password for the user (can be prompted)
• --host: Server address (default is localhost)
• --databases: List of databases to dump
• --result-file: File to save the dump output
• --default-parallelism: Number of parallel threads for faster export

bash

mysqlpump --user=root --password --databases db1 db2 --result-file=backup.sql --default-parallelism=4

Example

This example exports two databases shop and sales to a file named backup.sql using 4 parallel threads. It prompts for the password securely.

bash

mysqlpump --user=root --password --databases shop sales --result-file=backup.sql --default-parallelism=4

Output

Enter password: ********
Dump completed successfully, output saved to backup.sql

Common Pitfalls

Common mistakes when using mysqlpump include:

• Not specifying --databases or database names, which causes no output.
• Using --password without a value causes a prompt; avoid putting password directly for security.
• Expecting mysqlpump to replace physical backups; it only creates logical dumps.
• Not setting --default-parallelism can lead to slower dumps on large data.

Wrong way (no databases specified):

mysqlpump --user=root --password --result-file=backup.sql

Right way:

mysqlpump --user=root --password --databases mydb --result-file=backup.sql

Quick Reference

Option	Description
--user	MySQL username
--password	Prompt for password or specify it (not recommended)
--host	MySQL server host
--databases	List of databases to export
--result-file	File to save the dump output
--default-parallelism	Number of parallel threads for faster export
--exclude-databases	Databases to exclude from dump
--include-tables	Specific tables to include
--exclude-tables	Specific tables to exclude

Key Takeaways

Use mysqlpump with --databases to export one or more databases.

Set --default-parallelism to speed up large backups using multiple threads.

Avoid putting passwords directly in the command line for security.

mysqlpump creates logical backups, not physical copies of data files.

Always specify output file with --result-file to save the dump.