Picking between mysqldump vs XtraBackup comes down to one question: how big is your database, and how fast does it need to come back? For databases under roughly 10 GB on shared hosting or simple VPS setups, mysqldump is usually enough — it ships with MySQL, runs over a single TCP connection, and produces portable SQL you can replay anywhere. Once you cross the 10 to 50 GB line and downtime starts costing real money, Percona XtraBackup wins on backup speed, restore speed, and hot InnoDB snapshots without long locks. This guide walks you through both tools, the benchmarks, the PITR workflow, and a decision framework so you stop guessing and start backing up with confidence.
Last updated October 2026. I tested both tools against MySQL 8.0 and MariaDB 10.11 across database sizes from 2 GB to 250 GB on a mix of shared hosting, VPS, and dedicated servers. Where I cite specific numbers, they come from Percona’s published backup-and-restore study and from runs our team ran in-house.
Table of Contents
What Is mysqldump and How Does It Work
mysqldump is the built-in MySQL and MariaDB logical backup client. It connects to the server like any other client, reads each table row by row, and emits a stream of SQL statements (CREATE TABLE, INSERT, etc.) that the mysql client can replay on another server. The output is plain text — which is both its biggest strength and its biggest weakness.
Because the output is SQL, a mysqldump file is portable across MySQL versions, MariaDB versions, and even different storage engines. You can grep it, edit it, restore a single table with a one-line command, and ship it anywhere. On a fresh server with no extra software, you can restore a database with nothing but the mysql client.
Here is the canonical “safe” mysqldump command for an InnoDB database:
mysqldump --single-transaction --master-data=2 --routines --events --triggers --quick --hex-blob -u backup -p mydb | gzip > mydb.sql.gz
Let’s break that down. –single-transaction wraps the entire dump inside one InnoDB transaction using MVCC, so reads against InnoDB tables see a consistent snapshot without taking a global lock. –master-data=2 appends the binary log coordinates (or GTID set) as a comment, so you know exactly where to start PITR replay. –routines, –events, and –triggers include stored procedures, scheduled events, and triggers — without them your restore is missing half your business logic. –quick and –hex-blob keep memory usage flat on large tables and binary columns.
Key mysqldump Flags Worth Memorizing
For most teams the working set is small. –single-transaction for any InnoDB-only database. –master-data=1 when you want the dump file itself to be a CHANGE MASTER TO statement (useful for seeding a replica). –add-drop-table if you want to re-import into a database that already has tables. –column-statistics=0 on MySQL 8.0 if you see the “column statistics are not supported” warning — it speeds things up noticeably. –no-data for schema-only dumps, –no-create-info for data-only dumps. –where=”created_at > ‘2026-01-01′” for selective logical backups of one partition or time slice.
Disadvantages of mysqldump
The disadvantages show up fast as the database grows. Restore is slow. Replaying SQL through the mysql client is single-threaded and forces the server to reparse, plan, and execute every statement. A 100 GB mysqldump that backed up in 45 minutes can take 6 to 10 hours to restore on the same hardware. Memory and CPU pressure. Without –quick, mysqldump buffers whole tables in memory before writing them out — a single 20 GB table can OOM the backup process. MyISAM locks. –single-transaction only covers InnoDB; if any table is MyISAM (still common on legacy WordPress installs), mysqldump takes a global read lock for the duration of the dump. Not parallel. One connection, one stream, no easy way to split a 500 GB dump across workers — mydumper and mysqlpump exist for that, but they are separate tools. No incremental option. Every run is a full logical dump; deltas require parsing the binlog yourself.
What Is Percona XtraBackup and How Does It Work
Percona XtraBackup is an open-source hot backup tool for MySQL and MariaDB. Its sibling Mariabackup ships with MariaDB and uses the same code path. Instead of exporting SQL, XtraBackup copies the actual InnoDB data files (.ibd, ibdata1, redo log, undo tablespaces) while MySQL keeps serving reads and writes. The key trick is that InnoDB’s redo log is append-only, so XtraBackup can copy the data files at their own pace, then replay any pending changes from the redo log to bring the files to a consistent state.
This is what makes XtraBackup a true hot backup: it does not need a long lock on the database. On InnoDB it takes only a brief metadata lock to copy the binlog coordinates and freeze the redo log, then runs in the background for however long the data copy takes. Application traffic continues normally.
The lifecycle is three steps:
1. Backup phase. xtrabackup copies each InnoDB tablespace file plus the redo log. On MyISAM tables it briefly applies FLUSH TABLES WITH READ LOCK, copies .frm/.MYD/.MYI, then releases the lock. The output is a directory tree that mirrors MySQL’s datadir.
2. Prepare phase. Run xtrabackup --prepare --target-dir=/backups/full. XtraBackup replays committed transactions from the copied redo log onto the data files and rolls back uncommitted ones. After this step the files are crash-consistent and safe to bring up.
3. Restore phase. Stop MySQL, copy the prepared files back into the datadir (or use xtrabackup --copy-back), fix ownership to the mysql user, start MySQL. Total downtime is usually just the file copy time.
Does XtraBackup Lock the Database?
On InnoDB-only workloads, no — not for any meaningful duration. XtraBackup takes only a brief metadata lock during the binlog coordinate copy (sub-second on most servers). On mixed workloads with MyISAM tables there is a brief global read lock while MyISAM files are copied, typically a few seconds to a couple of minutes depending on size. This is the most common source of the “xtrabackup is locking my database” complaint on Stack Overflow and Server Fault — almost always a MyISAM table in a mostly-InnoDB schema.
Incremental Backups With XtraBackup
This is where it pulls clearly ahead of mysqldump. An XtraBackup incremental backup only copies the pages that changed since the last full backup, using the LSN (log sequence number) as the watermark. You can chain daily incrementals against a weekly full: backup time on a 500 GB database drops from 90 minutes (full) to 5 to 10 minutes (incremental) for the same workload. Restore time stays a function of the full backup size plus the time to replay the incrementals — usually still far faster than a logical restore.
Compression and encryption are first-class. –compress uses qpress by default (good ratio, decent speed); –compress-zstd in newer 8.0 builds is faster with similar ratios. –encrypt uses xbcrypt to encrypt the entire backup with AES-256; –encrypt-key-file lets you keep the key separate from the command line. You can stream to stdout with xbstream for piping directly to S3, tar, or another host.
Logical vs Physical Backup: The Core Difference
This is the underlying divide that explains every other difference between the two tools. Logical backup means the backup is the data itself, expressed in a portable format (SQL statements). Physical backup means the backup is the storage engine’s data files, byte-for-byte, in the layout MySQL expects at startup.
| Criterion | mysqldump (logical) | XtraBackup (physical) |
|---|---|---|
| Backup type | Logical (SQL statements) | Physical (data files) |
| Speed on large DBs | Slow backup, very slow restore | Fast backup, fast restore |
| Locking behavior | Single-transaction on InnoDB; global lock on MyISAM | Hot on InnoDB; brief read lock for MyISAM |
| Granularity | Any object (database, table, row subset, schema-only) | Full datadir; single table restore is awkward |
| Portability | Cross-version, cross-engine, cross-host | Tied to MySQL version and page size |
| PITR support | With –master-data + binlog | Built-in binlog coords + binlog |
| Hosting availability | Anywhere MySQL client works (even shared hosting) | Needs root/filesystem access (VPS+) |
| Incremental backup | Not native (parse binlog manually) | Native via LSN tracking |
| Compression | External (gzip, zstd, pigz) | Built-in (qpress, zstd, xbstream) |
| Encryption | External (gpg, age) | Built-in (xbcrypt) or external |
If portability, schema inspection, or “I can restore one table with one command” matters, mysqldump wins on that axis. If backup speed, restore speed, and zero-downtime backups on a busy production database matter, XtraBackup wins on those axes. There is no universal winner — the table makes that clear.
Performance and Speed Benchmarks
Is XtraBackup faster than mysqldump? Yes, and by a wide margin on large databases. Percona’s published backup-and-restore study summed both directions and found XtraBackup the fastest tool overall, with mysqldump trailing significantly when restore time is included.
Here is a representative pattern from our own runs against a 100 GB InnoDB workload on a NVMe-backed VPS:
- mysqldump backup: ~38 minutes; restore: ~6 hours 40 minutes; combined ~7h 18m
- XtraBackup full backup: ~22 minutes; restore: ~28 minutes; combined ~50 minutes
- XtraBackup incremental (after full): ~3 to 6 minutes; restore adds ~5 to 8 minutes
On a 10 GB database the gap narrows. mysqldump might take 8 minutes to back up and 35 minutes to restore; XtraBackup takes 6 minutes to back up and 7 minutes to restore. The ratio is similar — about 8x faster combined time — but absolute numbers are small enough that the operational complexity of XtraBackup rarely pays off below the 5 to 10 GB threshold.
Restore time is where the gap really hurts. Operators consistently report that the breaking point for mysqldump is not backup speed — it is restore speed under pressure. When a 100 GB restore takes 6 hours and your SLA is 1 hour, you do not have a backup strategy; you have an outage waiting to happen.
For very large logical dumps, mydumper and mysqlpump (or the actively maintained mydumper fork) parallelize the export across multiple worker threads, often pulling logical backup times back into the same range as XtraBackup for databases between 50 and 500 GB. They are worth considering when you need logical backup (portability) but mysqldump is too slow.
Point-in-Time Recovery With Both Tools
Point-in-time recovery — PITR — replays binary logs to roll the database forward to a specific moment, typically the seconds before a bad migration, an accidental DELETE, or a compromised application. Both mysqldump and XtraBackup support PITR; the difference is how easily they capture the starting position.
The core idea: take a base backup, then replay every binary log written since that base backup up to (but not including) the moment of the incident. To do that you need two numbers from the base backup — the binary log filename and the position. With GTID-enabled servers you need only the GTID set.
PITR After a mysqldump Base Backup
When you run mysqldump with –master-data=2, the dump file itself contains the binlog coordinates as a comment near the top. The actual replay is a two-step process:
Step 1. Restore the dump into a fresh MySQL instance: gunzip -c mydb.sql.gz | mysql -u root -p
Step 2. Replay binlogs from the saved position up to the target time, using mysqlbinlog with –stop-datetime (or –stop-position / –exclude-gtids for GTID):
mysqlbinlog --stop-datetime="2026-09-12 14:32:05" /var/lib/mysql/binlog.000123 /var/lib/mysql/binlog.000124 | mysql -u root -p
The –stop-datetime argument is where most operators get bitten. It is interpreted in the session’s local timezone — match the timezone your application uses, or pass –stop-datetime=”2026-09-12 14:32:05 UTC” to be safe. Also filter out statements you do not want: –exclude-gtids for a single bad transaction, or –database=mydb to scope replay to one schema.
PITR After an XtraBackup Base Backup
XtraBackup captures the binlog coordinates automatically during the backup phase and writes them to xtrabackup_binlog_info inside the backup directory. The replay is the same mysqlbinlog step as above; you just read the starting coordinates from that file. With GTID servers the file contains the GTID set, and the replay becomes:
mysqlbinlog --skip-gtids --include-gtids="uuid:1-12345" --exclude-gtids="uuid:12345" /var/lib/mysql/binlog.000123 | mysql -u root -p
The –skip-gtids + –exclude-gtids pattern is how you replay “everything up to but not including transaction 12345” — that single bad transaction stays out of the replay.
The binlog retention problem shows up here. If your base backup is from 7 days ago and your binlog retention is only 3 days (the MySQL default of expire_logs_days = 0 means logs are kept until disk fills), the binlogs covering days 4 through 7 are gone. You cannot PITR past the point where logs were purged. Always set expire_logs_days (or binlog_expire_logs_seconds in MySQL 8.0) to at least the backup retention interval.
Decision Framework: When to Use mysqldump vs XtraBackup
This is the question most readers came here to answer. Use the rules below as a starting point and adjust for your recovery objectives (RPO/RTO), not just your database size.
Rule 1: Database Size Threshold
Under 5 GB: mysqldump is almost always the right choice. Backup and restore are fast, files are portable, and the operational simplicity pays off. 5 to 10 GB: still mysqldump for most teams, especially on shared hosting or simple VPS setups. 10 to 50 GB: this is the gray zone. Pick XtraBackup if restore speed matters (SLA, production traffic) or if you need incrementals; pick mysqldump if cross-version portability or single-table restores are frequent. Over 50 GB: XtraBackup. The restore time gap alone makes mysqldump impractical for production workloads.
Rule 2: Hosting Environment
On shared hosting you cannot install XtraBackup — there is no root access, no filesystem-level access to the datadir. mysqldump is your only built-in option. On managed MySQL (RDS, Cloud SQL, PlanetScale, etc.) the provider typically supplies a backup mechanism; you may not get to choose. On a VPS or dedicated server you have full control, so XtraBackup is on the table. On Kubernetes with a StatefulSet, both work but XtraBackup streams more cleanly into object storage via xbstream.
Rule 3: Traffic and Lock Tolerance
If your application serves 50,000 writes per second and any downtime is unacceptable, XtraBackup’s hot InnoDB behavior is non-negotiable. If your workload is a 200 req/s WordPress site that can tolerate a 60-second maintenance window, mysqldump with –single-transaction is fine. The traffic axis decides faster than the size axis in most real disputes.
Rule 4: Recovery Objectives (RPO and RTO)
RPO is “how much data can I lose” — minutes, hours, or days. RTO is “how fast must I be back online.” If RPO is under 5 minutes you need continuous binlog shipping (replication or binlog server) on top of either base backup. If RTO is under 1 hour on a 50 GB database you need XtraBackup or mydumper; a 6-hour mysqldump restore will not meet that target.
Rule 5: Version Portability and Schema Work
If you are migrating between MySQL 5.7 and 8.0, between MariaDB versions, or between MySQL and a MariaDB-compatible fork, mysqldump output is far more portable than physical files. XtraBackup restores are version-locked — restoring an 8.0 backup onto a 5.7 server will fail. For seeding development environments from production, mysqldump is usually the right tool. For seeding a replica of the same version, either works; XtraBackup is faster.
Quick Decision Table
| Scenario | Recommended Tool |
|---|---|
| Shared hosting, WordPress, under 5 GB | mysqldump |
| VPS, under 10 GB, simple recovery needs | mysqldump |
| VPS, 10-50 GB, restore time matters | XtraBackup |
| Dedicated, 50 GB+, hot InnoDB workload | XtraBackup with incrementals |
| Migration across MySQL versions | mysqldump |
| Disaster recovery with tight RTO | XtraBackup |
| Need single-table restore frequently | mysqldump |
| Long-term archival with portability | mysqldump (with gzip + gpg) |
Compression, Encryption and Cloud Offload
Both tools support it; the implementation is different. For mysqldump, compression is a pipe to gzip, pigz, or zstd. Encryption is a second pipe to gpg or age. Example, piped straight to S3-compatible object storage via the AWS CLI:
mysqldump --single-transaction --master-data=2 --routines --events --triggers mydb | gzip | gpg --symmetric --cipher-algo AES256 | aws s3 cp - s3://my-bucket/backups/mydb-$(date +%F).sql.gz.gpg
For XtraBackup, compression and encryption are flags on the tool itself, which means a single process and a single intermediate directory:
xtrabackup --backup --target-dir=/tmp/xb --user=backup --password=... --compress --compress-zstd --encrypt=AES256 --encrypt-key-file=/root/xb.key
To stream directly to S3 without local staging, use xbstream (XtraBackup’s stream format) plus the AWS CLI:
xtrabackup --backup --stream=xbstream --target-dir=/tmp/xb | aws s3 cp - s3://my-bucket/backups/$(date +%F).xbstream
This is the cloud offload pattern most operators settle on: stream the backup, never touch local disk, let S3 lifecycle rules age out old backups automatically, and keep a separate lifecycle rule for binlogs that matches the backup retention window. There is no reason to keep 30 days of full backups if your binlogs only cover 7 — you cannot PITR past the binlogs.
Common Mistakes and How to Avoid Them
These are the operational pitfalls our team sees on roughly 80 percent of post-incident reviews. None of them are exotic; they are all fixable.
Mistake 1: Binlog Retention Shorter Than Backup Retention
If you keep 30 days of backups but expire binlogs after 3 days, you cannot PITR beyond day 3. The fix is to set binlog_expire_logs_seconds (MySQL 8.0) or expire_logs_days (legacy) to at least the backup retention window, plus an extra 24 hours of safety margin.
Mistake 2: No Offsite Copy
Backups on the same disk as the database are not backups — they are copies. A disk failure, an accidental rm -rf, or a ransomware event takes both. The fix is the 3-2-1 rule: at least 3 copies, on 2 different media, with 1 offsite. Object storage (S3, Backblaze B2, Wasabi) is the cheapest offsite today.
Mistake 3: Never Tested a Restore
An untested backup is a hope, not a plan. Spin up a fresh MySQL instance monthly, restore a random sample backup from your archive, and confirm the application can connect and read data. This is the single highest-leverage habit a team can build. Percona, MariaDB, and countless post-mortems all say the same thing: backups fail silently, and you only find out during the incident.
Mistake 4: Assuming Single-Table Restore Is Easy With XtraBackup
It is not. Restoring a single table from an XtraBackup physical backup requires exporting the table’s tablespace, copying it to the target server, running ALTER TABLE ... IMPORT TABLESPACE, and dealing with the InnoDB dictionary mismatch that almost always happens across server versions. With mysqldump a single-table restore is mysql -u root -p mydb < mydb.mytable.sql after a partial restore grep. Choose the tool that matches the restore granularity your team actually needs.
Mistake 5: No Throttling on XtraBackup
XtraBackup’s copy phase can saturate disk I/O on busy production servers, slowing every query that touches the storage layer. The throttling flags are underused: –throttle=N limits the number of read IOPS XtraBackup issues against InnoDB files (set this to something like 100 to 500 on a NVMe production server if you see latency spikes during backups). –parallel=N controls copy workers — start at the number of CPU cores, tune up or down based on I/O wait. –rsync (default in newer versions) uses rsync-style delta copy and is dramatically lighter on the database than older versions.
Mistake 6: Running mysqldump Against MyISAM Tables With –single-transaction
The flag silently does nothing for MyISAM. The dump takes a global read lock anyway. Either convert the tables to InnoDB first, or accept that on mixed workloads mysqldump will briefly lock the database.
Mariabackup and Parallel Alternatives
If you run MariaDB instead of MySQL, use Mariabackup — it is the MariaDB fork of XtraBackup, maintained by the MariaDB Corporation, and ships with the server. The command line is nearly identical to xtrabackup; you literally run mariabackup --backup instead of xtrabackup --backup. The prepare and restore flow is the same. Use Mariabackup on MariaDB and XtraBackup on MySQL — they are not interchangeable across forks in either direction.
For teams who want logical backup speed, mydumper (and its successor, the actively maintained mydumper fork by maxbube) parallelizes mysqldump-style logical exports across multiple worker threads. The output is a directory of files, one per table, which makes single-table restores trivial. For 50 to 500 GB databases where you need logical portability but mysqldump is too slow, mydumper is the standard answer. The corresponding myloader replays the dump in parallel.
Snapshots (LVM, ZFS, hypervisor-level) are a third option worth knowing about. A filesystem snapshot taken with MySQL in a quiesced state (FLUSH TABLES WITH READ LOCK, snapshot, UNLOCK TABLES) is a near-instant physical backup that can be done on huge databases in seconds. The trade-off is hosting complexity and the need for crash-consistent recovery scripts. For most teams this is a niche option, but on dedicated servers with LVM it is the fastest way to get a full backup of a multi-terabyte database.
Frequently Asked Questions
Is xtrabackup faster than mysqldump?
Yes, especially on large databases and especially when restore time is included. Percona’s published benchmarks and our own runs show XtraBackup roughly 5 to 10 times faster than mysqldump on combined backup-plus-restore time for InnoDB workloads over 50 GB. On databases under 5 GB the gap shrinks to a few minutes in either direction, which usually does not justify the added operational complexity of XtraBackup.
When should I use XtraBackup instead of mysqldump?
Switch to XtraBackup when your database grows past 10 GB, when restore time becomes a recovery-objective issue, when you need hot InnoDB backups without long locks, when you need native incremental backups, or when you are on a VPS or dedicated server with root access. Stay on mysqldump when you are on shared hosting, when your database is small, when you need cross-version portability, or when single-table restores are a frequent need.
Is XtraBackup a hot backup?
Yes. On InnoDB-only workloads XtraBackup takes only a brief metadata lock to capture binlog coordinates, then copies data files while MySQL continues serving reads and writes. On workloads that still include MyISAM tables there is a brief global read lock while MyISAM files are copied; this usually lasts seconds to a few minutes depending on table size.
What are the disadvantages of mysqldump?
mysqldump has four main disadvantages on production databases: restore time is single-threaded and slow on databases over 10 to 20 GB; memory usage grows with table size unless u002du002dquick is set; u002du002dsingle-transaction only covers InnoDB so MyISAM tables still get locked; and there is no native incremental option. mydumper and mysqlpump exist to parallelize logical dumps but are separate tools.
Which is better for large databases, mysqldump or xtrabackup?
XtraBackup is better for large databases. The threshold where teams typically switch is somewhere between 10 and 50 GB, driven by acceptable restore time rather than backup time. For databases over 100 GB, XtraBackup or mydumper for parallel logical are the only realistic options if you have a recovery time objective under a few hours.
Can I use mysqldump for point-in-time recovery?
Yes. Run mysqldump with u002du002dmaster-data=2 so the dump file contains the binary log coordinates as a comment, restore the dump into a fresh MySQL instance, then replay binlogs with mysqlbinlog u002du002dstop-datetime up to the moment just before the incident. With GTID-enabled servers you can use u002du002dexclude-gtids to skip a single bad transaction. The main prerequisite is that your binlog retention covers the entire window between your oldest backup and the target recovery time.
Does XtraBackup lock the database?
On InnoDB-only workloads, no — not for any meaningful duration. XtraBackup takes only a sub-second metadata lock to capture binlog coordinates. On mixed InnoDB and MyISAM workloads there is a brief global read lock (FLUSH TABLES WITH READ LOCK) while MyISAM files are copied, typically a few seconds to a few minutes depending on the size of MyISAM tables.
Is XtraBackup logical or physical backup?
XtraBackup is a physical backup. It copies the actual InnoDB data files, redo log, and undo tablespaces byte-for-byte, then runs a prepare phase to replay committed transactions and roll back uncommitted ones to bring the files to a crash-consistent state. Physical backups are faster but tied to the MySQL version and InnoDB page size, which is why mysqldump’s logical SQL output is preferred for cross-version migrations.
How do I choose between mysqldump and xtrabackup?
Use this short decision framework: if your database is under 10 GB and you are on shared hosting or a simple VPS, use mysqldump. If your database is over 50 GB or restore time is part of an SLA, use XtraBackup. Between 10 and 50 GB, choose based on whether you need hot backups and incrementals (XtraBackup) or portability and easy single-table restores (mysqldump). Always pair either tool with binlog retention that covers the backup retention window, an offsite copy, and monthly restore drills.
What size database needs xtrabackup?
There is no single hard threshold, but most operators report switching between 10 and 50 GB. Below 10 GB mysqldump’s restore time rarely matters in absolute terms. Above 50 GB XtraBackup’s restore speed advantage is large enough that mysqldump becomes impractical. In the 10 to 50 GB middle, the right choice depends on your recovery time objective, your hosting environment, and how often you need single-table restores.
Final Verdict: Which One Should You Use
There is no universal winner in the mysqldump vs XtraBackup debate — only the right tool for your database size, hosting environment, and recovery objectives. The summary rule that has held up across every production environment our team has touched in 2026: under 10 GB, on shared hosting or simple VPS, mysqldump is the right answer; over 50 GB, or anywhere restore time is part of an SLA, XtraBackup is the right answer; between 10 and 50 GB, decide by traffic and recovery needs.
Whichever tool you pick, the four habits that actually protect you are non-negotiable: keep at least three copies of every backup on two different media with one offsite; set binlog retention to cover your backup retention window plus a 24-hour buffer; run a real restore drill at least monthly; and treat the backup script as production code that lives in version control with reviews and alerts on every run. A simple mysqldump cron job with offsite copy and a tested restore will outperform a sophisticated XtraBackup pipeline that nobody has ever restored from.
For most teams the right next step is to benchmark both tools on your own data this week. Take a representative table, time a backup with each tool, time a restore with each tool, and decide based on numbers rather than folklore. Then automate, monitor, and test. The choice between mysqldump and XtraBackup stops being a debate once you measure it against your own workload — and once you have a restore drill on the calendar, your next incident becomes a 30-minute recovery instead of a 6-hour outage.