mysqldump vs mysqlpump vs mydumper: Choosing the Right MySQL Backup Tool

Your 2TB MySQL database has a 4-hour maintenance window, but mysqldump takes 6 hours just to complete the export. This is not a hypothetical — it is a real constraint engineering teams hit as databases scale beyond a few hundred gigabytes. The single-threaded, sequential nature of mysqldump made it the universal standard for portability, but it becomes a liability when backup windows compress and restore RTOs tighten. MySQL's ecosystem now includes three distinct logical backup tools — mysqldump, mysqlpump, and mydumper — each designed with different assumptions about parallelism, compatibility, and operational complexity. Understanding those trade-offs determines whether your next backup window fits inside your maintenance window or blows straight through it.

mysqldump — The Universal Standard

mysqldump ships with every MySQL and MariaDB installation and has been the default logical backup tool for over two decades. It reads rows from each table sequentially and emits SQL INSERT statements or CSV output to a single file. Its simplicity is its greatest strength: the output is a plain SQL file that any MySQL-compatible system can restore without special tools.

How It Works

mysqldump acquires a global read lock or uses --single-transaction with InnoDB to get a consistent snapshot. With --single-transaction, it opens a repeatable-read transaction before dumping begins, allowing other writes to continue without blocking. For mixed-engine databases that include MyISAM tables, it falls back to FLUSH TABLES WITH READ LOCK, which blocks writes for the duration of the dump — a significant concern on busy systems.

# Full database dump with InnoDB consistency
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --compress \
  -h db-primary.internal \
  -u backup_user -p \
  --all-databases \
  | gzip > /backups/full-$(date +%F).sql.gz

# Restore
zcat /backups/full-2025-11-15.sql.gz | mysql -u root -p

# Dump a single schema
mysqldump --single-transaction -u backup_user -p myapp_db \
  | gzip > /backups/myapp_db-$(date +%F).sql.gz

Key Options

• --single-transaction: Consistent InnoDB dump without global lock
• --master-data=2: Writes binary log coordinates as a comment — essential for replica setup and PITR
• --routines, --triggers, --events: Include stored procedures, triggers, and scheduled events
• --where: Partial table export by filtering rows
• --tab: Export as CSV + schema SQL for faster LOAD DATA INFILE restores

mysqlpump — Parallel Export, Limited Use

mysqlpump was introduced in MySQL 5.7.8 as a parallel replacement for mysqldump. It can dump multiple databases and tables concurrently using a configurable thread pool, and it adds built-in compression and progress reporting. On paper, it addresses mysqldump's core limitation. In practice, its adoption has been limited by a critical consistency caveat.

How It Works

mysqlpump spawns multiple worker threads, each dumping separate tables in parallel. It uses a queue system where databases and tables are distributed across workers. Output goes to a single compressed stream by default, or to multiple files with --result-file. Progress is reported in real time — a welcome improvement over mysqldump's complete silence.

# Parallel dump with 4 threads
mysqlpump \
  --default-parallelism=4 \
  --single-transaction \
  --compress-output=LZ4 \
  --all-databases \
  > /backups/full-pump-$(date +%F).sql

# Parallel dump with per-database thread allocation
mysqlpump \
  --default-parallelism=2 \
  --parallel-schemas=4:myapp_db,analytics_db \
  --exclude-databases=information_schema,performance_schema \
  --compress-output=ZLIB \
  > /backups/schemas-$(date +%F).sql.zlib

# Restore (mysqlpump output is compatible with mysql client)
mysql -u root -p < /backups/full-pump-2025-11-15.sql

Parallelism Configuration

--default-parallelism=N sets the worker thread count for all queues. --parallel-schemas=N:db1,db2 overrides the thread count for specific databases. This lets you prioritize throughput for your largest schemas while limiting concurrency on smaller ones to avoid overwhelming the server.

mydumper — High-Performance Multi-Threaded Backup

mydumper is an open-source, community-maintained tool written in C that was purpose-built for high-throughput logical backups. It pairs with myloader for restore. Unlike mysqlpump, mydumper achieves genuine cross-table consistency by acquiring a global read lock briefly at startup to record binary log coordinates, then releasing it while all worker threads operate inside a single consistent snapshot using repeatable-read transactions.

How It Works

mydumper's architecture separates the dump into per-table files, each stored independently in an output directory. A metadata file records the binary log position at dump start. Multiple worker threads chunk large tables and dump segments concurrently. myloader then restores in parallel, reading multiple files simultaneously. This file-per-table structure also enables selective restores: you can restore a single table without replaying the entire dump.

# Install mydumper (macOS)
brew install mydumper

# Install mydumper (Ubuntu/Debian)
apt-get install mydumper

# Full parallel dump — 8 threads, chunk large tables at 50000 rows
mydumper \
  --threads=8 \
  --rows=50000 \
  --compress \
  --trx-consistency-only \
  --host=db-primary.internal \
  --user=backup_user \
  --password="${DB_PASSWORD}" \
  --outputdir=/backups/$(date +%F) \
  --logfile=/var/log/mydumper.log \
  --verbose=3

# Dump a single database
mydumper \
  --threads=8 \
  --database=myapp_db \
  --compress \
  --trx-consistency-only \
  --host=db-primary.internal \
  --user=backup_user \
  --password="${DB_PASSWORD}" \
  --outputdir=/backups/myapp_db-$(date +%F)

# Parallel restore with myloader — 8 threads
myloader \
  --threads=8 \
  --database=myapp_db \
  --directory=/backups/myapp_db-2025-11-15 \
  --host=db-restore.internal \
  --user=root \
  --password="${DB_PASSWORD}" \
  --verbose=3

# Restore a single table
myloader \
  --threads=4 \
  --database=myapp_db \
  --directory=/backups/myapp_db-2025-11-15 \
  --tables-list=orders,order_items \
  --host=db-restore.internal \
  --user=root \
  --password="${DB_PASSWORD}"

Key mydumper Options

• --threads=N: Worker thread count for parallel dump/restore
• --rows=N: Chunk large tables into N-row segments for parallelism within a single table
• --trx-consistency-only: Skip the global read lock entirely for all-InnoDB databases — consistent snapshot via transaction only
• --compress: Gzip compress each output file
• --regex: Include/exclude tables by regular expression
• --chunk-filesize: Split files at a target size rather than row count
• --less-locking: Minimize lock time by dumping non-InnoDB tables in a separate phase

Side-by-Side Comparison

Performance Benchmarks

The following throughput figures are based on InnoDB databases on dedicated hardware (32-core, 128GB RAM, NVMe storage) with network backup to a separate storage host. Results vary significantly based on row size, index density, and network bandwidth.

200GB InnoDB Database (mixed table sizes)

1TB InnoDB Database (large tables, 500M+ rows)

The asymmetry between mysqlpump dump and restore is important: mysqlpump's parallel dump writes a single output file, so restore still uses the single-threaded mysql CLI. mydumper's per-file output enables parallel restore through myloader, compressing both sides of the backup window.

When to Use Each Tool

Use mysqldump when:

• Database size is under 50GB and backup windows are not a constraint
• You need maximum portability — the output SQL must run on MySQL, MariaDB, Percona Server, or cloud RDS/Aurora without modification
• You are exporting a subset of tables or using --where for partial exports
• The backup pipeline uses simple shell scripts without external tool dependencies
• You are seeding a development database or migrating between environments

Use mysqlpump when:

• Database is 50–200GB, you are on MySQL 5.7+, and cross-table consistency is not a hard requirement
• You need built-in progress reporting without installing external tools
• The environment restricts third-party tool installation but you need some parallelism
• You understand and accept the consistency limitation for your specific workload

Use mydumper when:

• Database exceeds 100GB and backup/restore time is operationally significant
• You need both fast dumps and fast restores — myloader's parallel restore is critical for low-RTO recovery
• You require selective table restores without parsing a monolithic SQL file
• The team can maintain a slightly more complex backup pipeline (directory output, myloader dependency)
• You are on MariaDB and need parallelism (mysqlpump is not an option)

Complementing Logical with Physical Backups

Logical backups — regardless of tool — replay SQL statements at restore time. For databases exceeding 500GB, even mydumper's parallel restore can take 60–90 minutes. Physical backups copy raw InnoDB data files and can restore in minutes rather than hours by bypassing SQL replay entirely.

Percona XtraBackup provides hot physical backups for InnoDB without locking. A mature backup strategy for large MySQL databases combines both approaches:

• Daily physical backup with XtraBackup for fast full-database RTO (5–20 minutes for 500GB)
• Weekly logical backup with mydumper for schema portability, selective table restores, and long-term archival
• Binary log backups every 15 minutes for point-in-time recovery between full backup windows

# XtraBackup hot physical backup (no lock, no downtime)
xtrabackup \
  --backup \
  --parallel=8 \
  --compress \
  --compress-threads=4 \
  --target-dir=/backups/xtrabackup-$(date +%F) \
  --host=db-primary.internal \
  --user=backup_user \
  --password="${DB_PASSWORD}"

# Prepare and restore
xtrabackup --prepare --target-dir=/backups/xtrabackup-2025-11-15
xtrabackup --copy-back --target-dir=/backups/xtrabackup-2025-11-15

Physical backups are version-specific and engine-specific — XtraBackup output cannot be restored to a different major MySQL version or to MariaDB without conversion. Logical backups remain the authoritative choice for cross-version migration, cloud RDS import/export, and schema-level archival. Neither approach eliminates the other.

Working with JusDB on MySQL Backup Strategy

JusDB designs comprehensive MySQL backup strategies for engineering teams — combining logical and physical backups, automated restore testing, S3 archival, and RTO/RPO-aligned recovery procedures.

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