Architecture

mysqldump vs XtraBackup vs MySQL Shell: Choosing a MySQL Backup That Restores in Time

Every MySQL backup tool works on a 5 GB database. The difference shows up at 500 GB, when the question is not "do we have a backup" but "how many hours until we are back". Here is how the three common options compare, and when to use each.

· 13 min read · AG Data Team

mysqldump vs XtraBackup is usually asked as a question about backups, but it is really a question about restores. Any tool can produce a file. What matters is how long it takes to get a working server back from that file, and whether the data inside is consistent. That depends on the size of your database far more than on the tool's feature list.

There are three realistic choices for self-managed MySQL 8.4: mysqldump, the MySQL Shell dump utilities, and Percona XtraBackup. The first two are logical backups (SQL or data files you reload). XtraBackup is a physical backup (a copy of the data files). Here is what each one does well and where it hurts.

mysqldump: simple, portable, slow to restore

mysqldump --single-transaction --source-data=2 \
  --routines --events --triggers \
  --all-databases | zstd > full-$(date +%F).sql.zst

The mysqldump documentation is honest about its limits: it "is not intended as a fast or scalable solution for backing up substantial amounts of data", and restoring "can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on."

The options that matter:

Use it for small databases, for moving a few schemas between versions or platforms, and as a second, logical copy next to a physical backup.

MySQL Shell dump utilities: logical, but parallel

mysqlsh admin@db1 -- util dump-instance /backups/2026-11-15 --threads=8

# restore into an empty server
mysqlsh admin@db2 -- util load-dump /backups/2026-11-15 --threads=8

util.dumpInstance(), util.dumpSchemas() and util.dumpTables() write the schema as DDL files and the data as chunked .tsv files, and util.loadDump() loads them back. The defaults are sensible: 4 threads, chunking on with about 64 MB per chunk, and zstd compression. The documentation describes "parallel dumping with multiple threads, and file compression, which are not provided by mysqldump."

Consistency is on by default (consistent: true). With the RELOAD privilege the utility briefly takes FLUSH TABLES WITH READ LOCK, starts a consistent snapshot in every thread, then switches to LOCK INSTANCE FOR BACKUP and releases the global lock. That short global lock can still queue behind a long-running query, so watch for it on busy primaries.

The restore is still a logical reload, so indexes are rebuilt and every row goes through the SQL layer again. It is much faster than a single-threaded mysqldump restore, but on large datasets it is usually slower than copying files back. The documentation recommends using the most recent MySQL Shell version for dump and load. One requirement trips up restores: util.loadDump() uses LOAD DATA LOCAL INFILE, so local_infile must be ON on the target for the duration of the import.

Percona XtraBackup: physical, hot, fast to restore

# full backup
xtrabackup --backup --target-dir=/data/backups/base

# incremental: only pages with a newer LSN
xtrabackup --backup --target-dir=/data/backups/inc1 \
  --incremental-basedir=/data/backups/base

# prepare before restore (last incremental without --apply-log-only)
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
xtrabackup --prepare --target-dir=/data/backups/base \
  --incremental-dir=/data/backups/inc1

# restore: server stopped, datadir empty
xtrabackup --copy-back --target-dir=/data/backups/base
chown -R mysql:mysql /var/lib/mysql

XtraBackup copies the InnoDB data files while watching the redo log, then uses InnoDB's crash recovery in a separate --prepare step to make the copy consistent. For an instance that has only InnoDB tables it tries to avoid both backup locks and FLUSH TABLES WITH READ LOCK; non-InnoDB tables are locked only at the end of the copy.

Rules that catch people out:

  1. Always prepare. A backup must be prepared before it can be restored. Prepare right after the backup, or at least test that it prepares, so you do not find out during an incident.
  2. Incrementals have a strict order. An incremental copies pages whose LSN is newer than the previous backup. When preparing, use --apply-log-only on the base and every intermediate incremental, but not on the last one. Without it, the rollback phase runs and further incrementals become unusable.
  3. Versions must match. Use a XtraBackup version equal to your server major version, for example 8.4 for MySQL 8.4. By default a mismatch stops the backup with an error. Upgrade XtraBackup together with the server.
  4. Restore needs a stopped server and an empty datadir. After --copy-back, files keep the ownership of the user who copied them, so in most cases you have to chown them to mysql.

Restoring is mostly file copy speed plus crash recovery, which is why physical backups win as data grows. The trade-off: a physical backup restores to the same major version and a compatible configuration. You cannot use it to move one schema into a different server the way a dump can.

Point-in-time recovery: the part that saves you

A nightly backup alone means losing up to a day of data. The fix is to keep binary logs and replay them on top of the restored backup, up to just before the bad event. The MySQL manual shows the approach:

mysqlbinlog --start-position=157 --stop-datetime="2026-11-15 09:41:00" \
  binlog.000412 binlog.000413 | mysql -u root -p

Start from the coordinates recorded by the backup (--source-data in mysqldump, xtrabackup_binlog_info in XtraBackup) and stop just before the DROP or bad UPDATE. The manual says to use a single connection when you apply several binary logs, so pass them to one mysqlbinlog call as above.

This only works if the logs still exist. binlog_expire_logs_seconds defaults to 2592000 seconds (30 days); your retention must cover at least the time back to the oldest backup you would restore from. Copy binlogs off the server too, because a lost disk takes them with it. If disk pressure tempts you to purge them, read our guide to purging binary logs safely first.

A backup you have not restored is a hope, not a backup. Restore it regularly into a separate server, run checks on the data, and write down how long it took. We describe a practical routine in our backup restore testing guide.

What changes on large databases

Key takeaways

MySQL Backup XtraBackup mysqldump
Work with us

Not sure your MySQL backup restores in time?

We design and test MySQL backup and recovery for production: the right tool for your size, binlog-based point-in-time recovery and a measured restore time.

Get in touch More from the blog