MySQL Backup: mysqldump, mariabackup and Binlog Recovery Tested (2026)

In most cases a MySQL backup is a single command: mysqldump --single-transaction --routines --events mydb > mydb.sql. It produces a consistent copy of one database while your application keeps running. On MariaDB the tool is called mariadb-dump, the options are the same. You restore it with mysql mydb < mydb.sql. For large databases, or when you need to jump back to a specific second, there is a second method: a physical copy with mariabackup or Percona XtraBackup plus the binary log, in other words point-in-time recovery.

That is the short answer. The long one matters more, because with MySQL backups most things fail quietly. A backup script reports success and has written a 401-byte file. A dump taken without the right option contains account balances that no longer add up. And a half-finished dump, restored by mistake, emptied a five-million-row table down to zero rows in our test. We reproduced all of this on November 4, 2026 on our own server with MariaDB 11.8.9 and MySQL 8.4.13, using a test database with five million orders.

This guide shows which method fits which job, how to restore a MySQL backup, how to automate it and how to know it will actually work when you need it. Every number comes from our own runs. If you use PostgreSQL, the same exercise is in our PostgreSQL backup guide.

Video: SimpleBackups walks from a manual mysqldump through cron to full automation (May 2026). Note that the channel belongs to a backup service vendor. The measurements below were made independently.

MySQL backup: the three methods at a glance

There are three fundamentally different ways to back up MySQL and MariaDB. A sensible setup usually combines two of them.

1. Logical backup with mysqldump or mariadb-dump. The tool connects like any other client, reads every table and writes out SQL statements that rebuild the database: CREATE TABLE, INSERT, triggers and, if you ask for them, procedures and events. The result is a text file you can read, grep and load on another server. This is what most people mean by “MySQL backup”.

2. Physical backup with mariabackup or XtraBackup. These tools copy the InnoDB data files directly while the server is running and record the redo log alongside. In a second step (“prepare”) the copy is brought into a consistent state. The result is a data directory you can start a server on. It is much faster than a dump, but only works within the same database family and a matching version.

3. Binary log (binlog) for point-in-time recovery. With the binlog enabled, the server records every change. Combined with a full backup you can replay every change after it, up to a point of your choosing. That gets you to the state one second before an accidental DELETE without a WHERE.

There are also filesystem snapshots (LVM, ZFS, cloud volumes) and copying the data directory with the server stopped. Both work but need care. A cp -r /var/lib/mysql on a running server is not a backup, it is a lottery ticket. We measured exactly this problem with SQLite: the live copy contained 0 instead of 1,000 rows, see SQLite vs. PostgreSQL.

Our test setup

So the numbers make sense: we tested MariaDB 11.8.9 and MySQL 8.4.13 using the official Docker images on our own server (12 vCPUs, NVMe SSD), each in throwaway containers that we deleted afterwards. The MariaDB test database shop held an orders table with 5 million rows (801 MB including indexes), a customers table with 500,000 rows and two small tables konto_a and konto_b (“account A/B”), each with 1,000 balances of 1,000.

The account tables are the consistency test. A load procedure moves one unit from a random account in konto_a to a random account in konto_b in a loop, both updates in one transaction. The total across all accounts therefore always stays exactly 2,000,000. A backup taken at any point during those transfers must also add up to exactly 2,000,000, otherwise it captured a state that never existed. The procedure managed roughly 6,300 transfers per second.

mysqldump and mariadb-dump: the standard backup

The right command

mysqldump --single-transaction --routines --events --triggers \
  mydb > mydb.sql

Since MariaDB 10.5 the tool is called mariadb-dump; mysqldump still exists as an alias on most systems. The options:

• --single-transaction is the single most important option. The dump opens a transaction with a fixed snapshot and reads every InnoDB table as of that moment, without blocking anyone else.
• --routines includes stored procedures and functions. They are not included by default: without the option our dump contained none of our procedures, with it the procedure was there.
• --events includes scheduled events, also not included by default.
• --triggers is on by default, but spelling it out does no harm.

To back up everything use --all-databases, for specific ones --databases db1 db2. With --databases the dump also gets CREATE DATABASE and USE lines, so on restore it writes back into the original database. Handy, but it can hit exactly the wrong database when you meant to restore into a copy.

How fast and how big?

For our 840 MB test database we measured three variants:

The dump itself is fast. gzip is the bottleneck and triples the runtime because it only uses one CPU core. zstd compressed better in our test and cost practically no time. If zstd is installed on your server there is little reason to use gzip any more. Our test data contains a lot of repeated strings; real databases usually compress somewhat less.

Restoring the uncompressed file took 43.9 seconds, roughly eight times as long as the backup. That is typical for logical backups: on restore MySQL has to insert every row and rebuild every index. With a 50 GB database you are quickly looking at hours of downtime. Afterwards row count and the sum of all order totals matched the original exactly (5,000,000 rows, 750,007,035.36), and the procedure was back too.

Consistency under load: the difference between two options

Now the most important test. While the load procedure was moving money between accounts, we took three dumps each and then added up the balances inside each dump.

Without a transaction the dump reads konto_a at one moment and konto_b a few seconds later, with thousands of transfers in between. The dump then contains more than 33,000 units of money that never existed. Nothing in the file tells you; it looks completely normal. In an online shop that would be orders without customers, or stock levels that do not match the orders.

And with no option at all? Then the default --lock-tables applies: the dump locks all tables of a database against writes while it runs. The result is consistent within that database (our total matched), but your application stalls. A single UPDATE on one account took 2.82 seconds during a default dump and 0.086 seconds with --single-transaction. Over 20 seconds of load the procedure completed 93,043 transfers with the default lock and 126,804 with --single-transaction, a quarter less throughput. With a dump that runs for half an hour, your shop is effectively read-only for half an hour.

One limitation: --single-transaction only works for InnoDB tables. Old MyISAM tables have no transactions and are still read inconsistently. Check whether you have any with SELECT table_schema, table_name FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys');. Also, nobody should run ALTER TABLE or TRUNCATE during the dump, as that breaks the snapshot.

Users and privileges are not in the database dump

A dump of the shop database contains its tables, but not the database users. We created a user shopapp with privileges on shop and searched for it: in the dump taken with --databases shop it appeared zero times. Restore that backup on a new server and all data is there, but your application cannot log in.

Since 10.11, MariaDB has a dedicated option that exports users and grants as portable SQL:

mariadb-dump --system=users > users.sql

For us that produced CREATE USER and GRANT lines including the password hash. On MySQL the clean route is MySQL Shell (util.dumpUsers or util.dumpInstance) or a script that prints SHOW GRANTS for every user. A dump of --all-databases does include the mysql system schema, but it only restores cleanly onto the same major version.

The traps that never throw an error

The pipe reports success although nothing was backed up

This is the classic line in nearly every backup script on the internet:

mysqldump mydb | gzip > backup.sql.gz

We deliberately misspelled the database name (shpo instead of shop). mariadb-dump printed “Unknown database”, but the exit code of the whole line was 0, success. The reason: the shell reports the exit code of the last command in the pipe, and gzip successfully compressed nothing. The result was a 401-byte file that gets rotated every night. If you never open the backup, you find out the day you need it.

The fix is one line at the top of the script:

set -o pipefail

With it the same command returned exit code 2, and your cron job or monitoring can react. We got the identical result with pg_dump on PostgreSQL; it is a property of the shell, not the database.

A half-finished dump empties your database

A dump that dies halfway (full disk, dropped connection, timeout) looks like a normal backup at first glance. We killed a dump after two seconds: 310 MB, the last line in the middle of an INSERT. We then loaded that partial dump into a copy of the database that had been complete before, 5 million orders.

The import stopped with exit code 1, so far so good. But afterwards the orders table contained 0 rows. For every table the dump starts with DROP TABLE IF EXISTS, recreates it empty and then fills it in chunks inside a transaction. The abort came before the COMMIT, so the rows of the running chunk were discarded, but the original table was already gone.

Two rules follow:

1. Check the last line of every dump. A complete dump ends with -- Dump completed on …. If that line is missing, it is incomplete: tail -n 1 backup.sql | grep -q "Dump completed" || echo "INCOMPLETE". For compressed dumps use zstdcat or zcat accordingly.
2. Never test a restore on top of the production database. Use a new, empty database or a separate server.

—force turns errors into success

If you load the same dump twice or data collides, the mysql client stops at the first error. In our test: “Duplicate entry ‘1’ for key ‘PRIMARY’”, exit code 1. That is good behaviour, and here MySQL is stricter than psql, which simply keeps going unless told otherwise.

Many guides, however, recommend mysql --force so that the import “gets through”. With --force the same error appeared and the exit code was 0. If you use --force in a script, the exit code tells you nothing any more. You then have to parse the error output, or you end up with a restore that half worked.

MariaDB dumps do not necessarily fit into MySQL

MariaDB and MySQL were once the same program, but they have drifted far apart. We loaded our MariaDB 11.8 dump into MySQL 8.4 as a test. It failed on line 26: “Unknown collation: ‘utf8mb4_uca1400_ai_ci’”, exit code 1. Since 11.x MariaDB uses a collation that MySQL does not know. If you want to move from MariaDB to MySQL (or the other way round), you have to adjust character sets in the dump and should rehearse the migration on a copy first. For the broader differences between the systems, see our PostgreSQL vs. MySQL comparison.

The password does not belong on the command line

mysqldump -uroot -pSecret works, and the client now masks the password in the process list (ours showed up as -px xxxx). It still ends up in shell history, cron logs and scripts committed to Git. A better approach is an option file that only the backup user can read:

# /root/.my.cnf  (chmod 600)
[client]
user=backup
password=ALongRandomPassword

For mysqldump the backup user only needs read access: GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS ON *.* TO 'backup'@'localhost'; (on MySQL 8, depending on version, also RELOAD or FLUSH_TABLES). Using root for backups is convenient but unnecessary.

Physical backup with mariabackup and XtraBackup

When the dump takes too long or the restore is too slow, physical backups come in. For MariaDB that is mariabackup (new name mariadb-backup), for MySQL it is Percona XtraBackup. Both come from the same code base and work the same way: copy data files, capture the redo log, then “prepare”.

# 1. Take the backup (server keeps running)
mariabackup --backup --target-dir=/backup/full

# 2. Prepare: makes the copy consistent
mariabackup --prepare --target-dir=/backup/full

# 3. Restore: stop server, empty data dir, copy back
systemctl stop mariadb
mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Our measurement

Under the same load as the dump test (about 6,300 transfers per second):

• Backup: 4.3 seconds, exit code 0, “completed OK!”
• Prepare: 0.23 seconds
• Restore: 3.2 seconds, measured from starting a new server on the prepared copy to the first successful query
• Checksum after restore: exactly 2,000,000, all 5 million orders present
• The load kept running practically unhindered during the backup (152,484 transfers in 25 seconds)

So the difference is not in the backup (4.3 s vs. 5.4 s) but in the restore: 3.2 seconds instead of 43.9 seconds. For large databases that is the difference between a few minutes and half a day of downtime.

The price: the backup is bigger. Our copy took 2.1 GB versus 815 MB for the uncompressed dump, because indexes and free space inside the table files are copied too. With --stream=mbstream | zstd you can write it compressed directly.

Incremental backups

Physical backups can be incremental, copying only the pages that changed since the last backup:

mariabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

After ten seconds of load our full backup was 1.6 GB and the incremental one 50 MB, taken in about 1.4 seconds. To restore, you prepare the full backup first and then apply each increment in order (--prepare --incremental-dir=…). That worked without errors for us. If one link in the chain is missing, everything after it is worthless, so each chain should regularly start over with a new full backup.

The file permission trap

With MySQL 8.4 and XtraBackup 8.4 in Docker our first attempt failed immediately: “Operating system error number 13” and “Failed to list redo log files”. The cause: in the XtraBackup image the mysql user has UID 1001, while in the MySQL image the data files belong to UID 999. The tool simply was not allowed to read them. Run as root, the backup went through (3.7 s, 509 MB for the 2-million-row database, prepare 2.4 s). This rarely happens on a plain server, but constantly in Docker setups.

Important with XtraBackup: the version must match the MySQL version. MySQL 8.4 needs XtraBackup 8.4, MySQL 8.0 needs XtraBackup 8.0. The same applies to MariaDB: use mariabackup from the same major version as your server. A mariabackup from 11.8.8 prepared our 11.8.9 backup without issues; across major versions you should not expect that.

Point-in-time recovery with the binlog

A daily backup means that in the worst case you lose one day of changes. And the typical disaster is not a dead disk but a person or a script deleting the wrong data. That is what the binary log is for.

Enable the binlog

On MySQL 8 the binlog is on by default, on MariaDB it is not. In the configuration:

[mysqld]
log_bin = binlog
server_id = 1
binlog_format = ROW
binlog_expire_logs_seconds = 1209600   # 14 days

Our accident test

After the physical backup we kept working: inserted two new orders, then staged the accident, DELETE FROM orders WHERE status='storniert' (“cancelled”), which deleted 1,250,000 rows, and then inserted one more order. Goal: get everything back except the DELETE.

The physical backup records its own position in the binlog. Ours had binlog.000005 339635677 in the file mariadb_backup_binlog_info. That is where you start replaying. You find the position of the accident with mysqlbinlog or mariadb-binlog by looking for the timestamp or the large Delete_rows event. Then you replay everything up to just before it, skip the bad transaction and replay the rest:

mariadb-binlog --start-position=339635677 binlog.000005 binlog.000006 | mariadb
mariadb-binlog --stop-position=339 binlog.000007 | mariadb
mariadb-binlog --start-position=159398849 binlog.000007 | mariadb

Result after 38.8 seconds of replay: 5,000,003 orders, all 1,250,000 deleted rows back, both orders from before the accident and the one from after it as well. The account total matched at 2,000,000, so every transfer from the load procedure after the backup was replayed correctly too.

Instead of positions you can use --stop-datetime="2026-11-04 08:10:57". That is more convenient but imprecise with many transactions per second. With GTIDs (recommended on MySQL 8) it is more elegant via --exclude-gtids.

Binlogs grow bigger than the database

A detail that surprises many people: the single DELETE produced 159 MB of binlog, because in ROW format every deleted row is logged individually. Filling the test database and a test restore had together already produced more than 2 GB of binlog, more than twice the size of the database itself. Without binlog_expire_logs_seconds your disk fills up. Also, binlogs on the same disk as the database do not help when that disk dies. For real point-in-time recovery they need to be copied continuously to another machine, for example with mysqlbinlog --read-from-remote-server --raw --stop-never on a backup server.

Faster dumps with MySQL Shell and mydumper

mysqldump works with a single thread. For larger databases there are parallel alternatives. On MySQL that is MySQL Shell with util.dumpSchemas() or util.dumpInstance(). We compared it on MySQL 8.4 with our 2-million-row table:

The restore is three times as fast because MySQL Shell loads in parallel. One catch: loading requires local_infile to be enabled on the server, otherwise util.loadDump aborts. For both MariaDB and MySQL there is mydumper/myloader, an open-source tool built on the same idea. The old mysqlpump, by the way, has been removed in MySQL 8.4.

Automating MySQL backups: a script that reports failures

A backup you have to run by hand will eventually be forgotten. Here is a script that covers all the traps above:

#!/usr/bin/env bash
# /usr/local/bin/mysql-backup.sh
set -euo pipefail

TARGET=/backup/mysql
STAMP=$(date +%F_%H%M)
DB=mydb
FILE="$TARGET/${DB}_${STAMP}.sql.zst"

mkdir -p "$TARGET"

# user/password come from /root/.my.cnf
mysqldump --single-transaction --routines --events --triggers \
  "$DB" | zstd -3 -q > "$FILE.tmp"

# completeness check: last line must be "Dump completed"
zstdcat "$FILE.tmp" | tail -n 1 | grep -q "Dump completed" \
  || { echo "Dump incomplete: $FILE" >&2; exit 1; }

mv "$FILE.tmp" "$FILE"

# users and grants separately (MariaDB 10.11+)
mariadb-dump --system=users | zstd -3 -q > "$TARGET/users_${STAMP}.sql.zst"

# delete backups older than 14 days (-mmin is more precise than -mtime)
find "$TARGET" -name '*.sql.zst' -mmin +20160 -delete

A few details that matter: set -euo pipefail aborts the script on any error, including inside the pipe. The file is written as .tmp first and only renamed after the check passes, so a partial dump never sits under the real name. And find … -delete comes last, so it only runs if the new backup succeeded. How treacherous option order is with find, we measured in our Linux commands list.

Schedule the script with a cron job, for example every night at 3:15:

15 3 * * * /usr/local/bin/mysql-backup.sh || echo "MySQL backup failed" | mail -s "Backup error" admin@example.org

How cron jobs work and why they run in a different environment than your shell is covered in create a cron job. An alternative with better logging is a systemd timer, see create a systemd service.

Video: Chirag Mahto runs a full mysqldump backup-and-restore lab on Ubuntu 24.04 with MySQL 8.4 LTS (June 2026).

MySQL backup in Docker

If your database runs in a container, everything works the same way, just through docker exec:

docker exec my-db sh -c 'exec mariadb-dump --single-transaction --routines --events -uroot -p"$MARIADB_ROOT_PASSWORD" mydb' \
  | zstd -3 -q > mydb.sql.zst

The sh -c makes sure the environment variable with the password is evaluated inside the container, not on your host. Restoring works the other way round with docker exec -i. Backing up the Docker volume with tar while the container runs, on the other hand, is the same lottery as cp -r and not a consistent backup. More on containers and volumes in What is Docker?

A backup is only a backup once the restore worked

All the measurements above lead to the same point: a backup script that returns exit code 0 proves nothing. The 401-byte file had exit code 0. The import with --force had exit code 0. The dump without a transaction ran without errors and was still wrong.

The only real test is the restore. Do it regularly, for example once a month, like this:

1. Start a new empty server or container (same version as production).
2. Load the latest backup and time it.
3. Compare row counts of the most important tables with production. Checksums such as CHECKSUM TABLE or SELECT COUNT(*), SUM(amount) help.
4. Start the application against the restored database. Can it log in? Do procedures and triggers work?
5. Write down the restore time. That is your real downtime in an emergency.

The duration matters at least as much as the result. Our dump took 5 seconds to back up but 44 seconds to restore. Extrapolated to a 100 GB database that is more than an hour during which your shop is down. If that is too long, you need a physical backup.

Which backup strategy fits you?

Small project, blog, WordPress (up to a few GB): daily mysqldump --single-transaction --routines --events with zstd, keep 14 days, copy to another machine. Possible data loss: up to one day.

Online shop or application with paying customers: nightly physical backup with mariabackup or XtraBackup, binlog enabled and streamed continuously to a second server. Plus a weekly logical dump as a version-independent fallback. Possible data loss: seconds to minutes.

Large database, fast restore required: physical full backups plus incrementals, parallel dumps with MySQL Shell or mydumper for single databases, a replica as standby. But: replication is not a backup. An accidental DELETE reaches the replica within milliseconds.

The 3-2-1 rule applies to everyone: three copies, two different media, one off-site. A backup on the same disk as the database protects you from a wrong DROP TABLE, but not from a dead disk, a hacked server or a fire in the data centre. How to set this up with restic is in our 3-2-1 backup strategy. And a RAID is not a backup either.

Frequently asked questions

How do I create a MySQL backup?

With mysqldump --single-transaction --routines --events dbname > backup.sql. The same command works on MariaDB, where the tool is also available as mariadb-dump. --single-transaction makes the backup consistent while the database stays in use. For very large databases a physical backup with mariabackup or Percona XtraBackup is faster.

What is the difference between mysqldump and mariabackup?

mysqldump creates a logical backup as SQL text that can be loaded on other servers and usually other versions. mariabackup copies the data files physically. In our test both were similarly fast at backing up (5.4 s and 4.3 s), but mariabackup restored more than ten times faster (3.2 s instead of 43.9 s). In return the physical backup is larger and tied to the version.

How do I restore a MySQL backup?

Load a dump with mysql dbname < backup.sql, or for compressed files zstdcat backup.sql.zst | mysql dbname. The target database has to exist unless the dump was created with --databases. Check first that the dump is complete (last line “Dump completed”), and never test a restore on top of the production database.

How do I back up all MySQL databases at once?

With mysqldump --all-databases --single-transaction --routines --events > all.sql. That also includes the system schema with the users, which only restores cleanly onto the same major version. Often clearer is a loop that dumps each database into its own file, plus a separate export of the users.

Can I run mysqldump on a live database?

Yes, with --single-transaction. In our test with about 6,300 write transactions per second every checksum in the dump matched exactly. Without that option mysqldump locks the tables by default: a single update then waited 2.8 seconds instead of 0.09 seconds. With --skip-lock-tables and no transaction the dump was inconsistent.

Does mysqldump include users and passwords?

Not if you only back up a specific database. Users live in the mysql system schema. On MariaDB you export them with mariadb-dump --system=users, on MySQL with MySQL Shell (util.dumpUsers) or via SHOW GRANTS. Without this step your application cannot log in after a migration.

How often should I back up MySQL?

As often as you can afford to lose data. A daily backup means that in the worst case one day of changes is gone. If that is too much, you need the binlog copied continuously to another server. That lets you restore to within seconds.

What is point-in-time recovery in MySQL?

Point-in-time recovery restores the database to a specific moment. You need a full backup and every binlog since. You restore the backup and then replay the changes from the binlog up to just before the incident. In our test we recovered 1,250,000 accidentally deleted rows this way without losing the changes before and after.

Can I load a MariaDB dump into MySQL?

Not necessarily. Our dump from MariaDB 11.8 failed on MySQL 8.4 because of the collation utf8mb4_uca1400_ai_ci, which MySQL does not know. Moving between the two systems means adjusting character sets and sometimes syntax, and you should rehearse it on a copy first.

What is the best way to compress a MySQL backup?

With zstd: mysqldump … | zstd -3 > backup.sql.zst. In our test the dump shrank from 815 MB to 163 MB without the backup taking any longer. gzip reached 187 MB and tripled the runtime because it only uses one CPU core. Either way you need set -o pipefail, otherwise your script will not notice mysqldump failing.

Is replication a backup?

No. A replica applies every change within milliseconds, including an accidental DELETE or DROP TABLE. It protects against hardware failure, not against mistakes. For that you still need real backups, ideally with the binlog for point-in-time recovery.

Conclusion: the command is easy, verification is the work

A MySQL backup is a one-liner. mysqldump --single-transaction --routines --events with zstd is exactly right for most projects: fast, consistent, small and loadable on almost any server. When the database grows or the restore has to be fast, add mariabackup or XtraBackup, and with the binlog you can go back to the second.

The real work is elsewhere, and our measurements show it more clearly than any theory: a pipe that reports a 401-byte file as success. A dump without a transaction that contains 33,000 units of money that never existed. A partial dump that empties a full table. --force turning every error into success. None of these wakes you up at night with an error message.

So: set -o pipefail, --single-transaction, check the last line, back up users separately, keep a copy off-site. And above all: do a real restore once a month and time it. A backup you have never restored is not a backup, it is a hope.