Getting Started with mysqlpump: Parallel MySQL Backups Explained

If you have ever watched a mysqldump grind through a 200 GB database while your maintenance window ticks down, you already understand the problem mysqlpump was built to solve. Introduced in MySQL 5.7.8, mysqlpump is a drop-in evolution of mysqldump that adds parallel export workers, finer-grained filtering, built-in compression, and real-time progress reporting. The result is logical backups that routinely finish 3–5x faster on multi-core servers — without requiring you to switch to a physical backup tool. This guide covers everything you need to configure, tune, and restore mysqlpump backups in production.

What is mysqlpump and When to Use It

mysqlpump is a logical backup utility — it reads data through the MySQL server and writes SQL statements (CREATE TABLE, INSERT, etc.) to an output file. Like mysqldump, this means the backup is human-readable, portable across MySQL versions, and easy to selectively restore. Unlike mysqldump, mysqlpump distributes the work across multiple threads, each handling a separate table queue in parallel.

Key Improvements Over mysqldump

• Parallel workers: Multiple threads export different tables simultaneously, exploiting multi-core CPUs and I/O bandwidth.
• Per-queue parallelism: You can assign different thread counts to different database queues, so large databases get more workers.
• Built-in compression: LZ4 and zlib compression are available without piping through an external tool.
• User account dumps: The --users flag exports grants as portable CREATE USER / GRANT statements instead of raw mysql.user rows.
• Progress reporting: --watch-progress prints live row counts and estimated completion time to stderr.
• Deferred indexes: Secondary indexes are deferred until after data load, dramatically speeding up restore on InnoDB tables.

When to Choose mysqlpump

mysqlpump is the right choice when your database is in the 10–500 GB range, you need a logical (SQL) backup, and you want faster export without adopting a physical backup tool. It is also a strong choice when you need selective database or table exports, because its filtering flags are more expressive than mysqldump's. For databases larger than 500 GB or where zero-impact hot backups are mandatory, XtraBackup (physical) is usually more appropriate.

Basic Usage and Key Options

The basic invocation mirrors mysqldump closely, so existing scripts require minimal changes.

Full Instance Backup

# Dump all databases to a compressed file
mysqlpump \
  --user=backup_user \
  --password \
  --host=127.0.0.1 \
  --port=3306 \
  --all-databases \
  --compress-output=LZ4 \
  --watch-progress \
  > /backups/full-$(date +%F).sql.lz4

Single Database Backup

mysqlpump \
  --user=backup_user \
  --password \
  --host=127.0.0.1 \
  --databases myapp_production \
  --compress-output=ZLIB \
  > /backups/myapp_production-$(date +%F).sql.zlib

Include User Accounts

# Export all databases AND user/grant definitions
mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --users \
  --exclude-databases=sys,information_schema,performance_schema \
  > /backups/full-with-users-$(date +%F).sql

Commonly Used Flags

Parallel Workers — How to Configure and Tune

Parallelism is the headline feature of mysqlpump and requires a bit of understanding to configure effectively. mysqlpump organizes work into queues. Each queue is assigned a number of threads. By default, a single queue handles all databases with two threads.

Setting Default Thread Count

# Use 4 threads for all databases
mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --default-parallelism=4 \
  > /backups/full.sql

Per-Schema Thread Allocation

The --parallel-schemas flag lets you assign a separate queue with its own thread count to specific databases. This is useful when one large database dominates backup time.

# Give the large analytics DB its own 8-thread queue
# while everything else runs with 2 threads
mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --default-parallelism=2 \
  --parallel-schemas=8:analytics_db \
  --parallel-schemas=4:myapp_production \
  > /backups/full.sql

Tuning Recommendations

• Start with --default-parallelism equal to half the available vCPUs, then benchmark.
• Monitor iostat and CPU during the backup — if CPU is idle but I/O is saturated, adding more threads will not help.
• On a replica server, higher parallelism is safer since there is no live write traffic competing for I/O.
• Each thread opens its own connection to MySQL, so ensure max_connections is high enough to accommodate backup threads plus application connections.
• Do not exceed the number of physical CPU cores for CPU-bound workloads; over-threading increases context switching overhead.

Filtering: Include/Exclude Databases, Tables, Users

mysqlpump's filtering system is more powerful than mysqldump's. You can include or exclude databases, tables, and users with granular control — and patterns support wildcards.

Exclude System Databases

mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --exclude-databases=sys,information_schema,performance_schema,mysql \
  > /backups/user-data.sql

Include Specific Databases Only

mysqlpump \
  --user=backup_user \
  --password \
  --include-databases=myapp_production,payments_db \
  > /backups/app-databases.sql

Exclude Specific Tables

# Exclude all audit log tables and a large ephemeral table
mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --exclude-tables=myapp_production.audit_events,myapp_production.job_queue \
  > /backups/full-no-audit.sql

Wildcard Table Exclusions

# Exclude all tables matching the pattern in any database
mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --exclude-tables=%.tmp_% \
  > /backups/full-no-tmp.sql

Users-Only Backup

# Export only user accounts and grants — no table data
mysqlpump \
  --user=backup_user \
  --password \
  --exclude-databases=% \
  --users \
  > /backups/mysql-users-$(date +%F).sql

Compression and Output Options

mysqlpump supports two built-in compression algorithms, eliminating the need to pipe through gzip or lz4 separately.

LZ4 Compression (Faster, Lower CPU)

mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --compress-output=LZ4 \
  > /backups/full-$(date +%F).sql.lz4

# Decompress when needed
lz4 -d /backups/full-2026-02-23.sql.lz4 /backups/full-2026-02-23.sql

ZLIB Compression (Smaller Files, More CPU)

mysqlpump \
  --user=backup_user \
  --password \
  --all-databases \
  --compress-output=ZLIB \
  > /backups/full-$(date +%F).sql.zlib

# Decompress with zlib-flate or python
python3 -c "import zlib, sys; sys.stdout.buffer.write(zlib.decompress(sys.stdin.buffer.read()))" \
  < /backups/full-2026-02-23.sql.zlib \
  > /backups/full-2026-02-23.sql

Practical Recommendation

Use LZ4 for backups that need fast restore access and ZLIB for long-term archival where storage cost matters more than decompression speed. LZ4 typically compresses at 400–600 MB/s with modest CPU overhead; ZLIB offers 2–4x better compression ratios at 3–5x higher CPU cost.

mysqlpump vs mysqldump vs XtraBackup

Restoring from a mysqlpump Backup

Restoration from a mysqlpump backup uses the standard mysql client — the output format is plain SQL. The deferred index feature built into mysqlpump means indexes are added after all data rows are inserted, which significantly speeds up restoration on large InnoDB tables.

Restore a Plain SQL Backup

mysql \
  --user=root \
  --password \
  --host=127.0.0.1 \
  < /backups/full-2026-02-23.sql

Restore a Compressed LZ4 Backup

lz4 -d -c /backups/full-2026-02-23.sql.lz4 \
  | mysql --user=root --password --host=127.0.0.1

Restore a Compressed ZLIB Backup

python3 -c "import zlib, sys; sys.stdout.buffer.write(zlib.decompress(sys.stdin.buffer.read()))" \
  < /backups/full-2026-02-23.sql.zlib \
  | mysql --user=root --password --host=127.0.0.1

Restore a Single Database

# First create the target database if it does not exist
mysql --user=root --password -e "CREATE DATABASE IF NOT EXISTS myapp_production;"

mysql \
  --user=root \
  --password \
  --host=127.0.0.1 \
  myapp_production \
  < /backups/myapp_production-2026-02-23.sql

Speeding Up Restore

# Apply these session variables before restoring to maximize InnoDB load speed
mysql --user=root --password --host=127.0.0.1 <<'EOF'
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;
SET GLOBAL foreign_key_checks = 0;
EOF

# Then run the restore
mysql --user=root --password --host=127.0.0.1 < /backups/full-2026-02-23.sql

# Restore safe defaults afterward
mysql --user=root --password --host=127.0.0.1 <<'EOF'
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;
SET GLOBAL foreign_key_checks = 1;
EOF

Working with JusDB on MySQL Backup Strategy

JusDB designs and manages MySQL backup strategies for engineering teams — combining mysqlpump for logical backups, XtraBackup for physical hot backups, and S3 archival with automated restore testing.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• XtraBackup: Physical MySQL Backups for Production
• Database Backup Strategies: RTO and RPO
• MySQL Binary Log Management