Skip to content
Tutorials Center
My learning

Topics

Lesson 61 of 75 ยท Security and Administration

Backup and Restore

Back up MySQL with mysqldump, MySQL Shell dump utilities, XtraBackup and the Clone plugin, recover to a point in time with the binary log, and test restores.

A backup you have never restored is a hope, not a backup. This lesson covers the main ways to copy a MySQL server's data, how to combine a full backup with the binary log to recover to an exact moment, and how to prove that your backups work. Everything with a real server (mysqldump, mysqlbinlog, MySQL Shell, XtraBackup) is shown as read-only commands to run in your own terminal. The live examples on this page are honest stand-ins that model the ideas with SQLite: generating a dump file and replaying a change log.

Two words matter throughout. A logical backup is a set of SQL statements (or text files) that recreate the data. It is portable and readable, but slow to restore on big databases. A physical backup copies the data files themselves. It is much faster for large servers, but tied to the same MySQL version family and file layout.

Logical or physical?

Logical SQL or text files. Portable and easy to inspect.
Physical Copies of data files. Fast for terabytes.
Binary log Every change since the backup. Enables point in time.

Real plans combine a full backup with the binary log.

1 / 2

Logical backups with mysqldump

mysqldump ships with the server. It connects like any client, reads the data and prints SQL statements. The most important flags:

Terminal
mysqldump --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF -u backup_user -p shop > shop-2026-03-01.sql
  • --single-transaction starts a consistent-snapshot read inside one REPEATABLE READ transaction, so InnoDB tables are dumped as of one moment without blocking writers. It only protects InnoDB tables, and no other session should run DDL such as ALTER TABLE while the dump runs.
  • --routines, --triggers and --events are needed to include stored routines and events. Triggers are on by default, routines and events are not.
  • --source-data=2 writes the binary log file and position as a comment at the top of the dump. You will need it for point-in-time recovery. In versions before 8.4 this option was called --master-data.
  • --databases shop crm or --all-databases choose what to dump. A dump of a single database named on the command line does not include a CREATE DATABASE statement, while --databases does.
  • --set-gtid-purged=OFF leaves GTID bookkeeping out of the file. Use it when the dump will be loaded into a server that already has its own history.

Restore by feeding the file back to the client:

Terminal
mysql -u root -p shop < shop-2026-03-01.sql

The dump account needs SELECT, SHOW VIEW, TRIGGER and EVENT on what it dumps, plus PROCESS (or the --no-tablespaces flag) in recent versions, and RELOAD if you use --source-data. Give the backup user a dedicated, least-privilege account; see Users, Privileges and Roles.

mysqlpump, the parallel dump tool, is deprecated in MySQL 8.4 and removed in 9.0. Do not start new work with it. Use MySQL Shell dump utilities for parallel logical backups instead.

MySQL Shell dump utilities

MySQL Shell (mysqlsh) has dump and load utilities that run in parallel, compress the data and write many files, so they are far faster than a single mysqldump stream on large schemas:

JavaScript
// in MySQL Shell, JavaScript mode
util.dumpInstance("/backups/full-2026-03-01", { threads: 4 })
util.dumpSchemas(["shop"], "/backups/shop-2026-03-01")

// restore into a (possibly new) server
util.loadDump("/backups/shop-2026-03-01", { threads: 4 })

loadDump can resume after a failure and checks the dump for completeness. A dump written by MySQL Shell is not a single SQL script, so you restore it with loadDump, not with the mysql client. Loading uses LOAD DATA, which needs local_infile enabled on the target server; the utility tells you when it is missing. See Bulk Loading and Exporting Data for the background.

Physical backups

For a server with hundreds of gigabytes, copying files is the practical way.

Percona XtraBackup is a free tool that copies InnoDB files while the server runs, keeps a record of the redo log written meanwhile, and then makes the copy consistent:

Terminal
xtrabackup --backup --target-dir=/backups/full --user=backup_user --password
xtrabackup --prepare --target-dir=/backups/full
# server stopped, empty data directory:
xtrabackup --copy-back --target-dir=/backups/full
chown -R mysql:mysql /var/lib/mysql

--prepare replays the redo log so the files are crash-consistent. Incremental backups are supported, and the tool must match the server's major version.

The Clone plugin is built into MySQL Community Server. It copies a whole instance from a running server (the donor) to another (the recipient) over the network, which is also the fastest way to seed a replica:

SQL
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/backups/clone-2026-03-01';
-- or, on a new server:
CLONE INSTANCE FROM 'clone_user'@'donor-host':3306 IDENTIFIED BY '...';

Clone copies InnoDB data only. Other engines are not included, and it does not copy configuration files or the binary log. The donor and recipient must run the same MySQL version.

A third option is a filesystem or volume snapshot (LVM, ZFS, a cloud disk snapshot). It is quick and reliable only if the snapshot is atomic across the data directory and redo log, or if you flush and lock tables first.

Point-in-time recovery

Backups are taken once a night, but mistakes happen at 10:15. The binary log records every change since the last backup, so you can restore the backup and then replay the log up to the moment before the mistake. Check that binary logging is on (it has been on by default since 8.0):

SQL
SHOW BINARY LOG STATUS;
SHOW BINARY LOGS;

The recovery steps are always the same:

  1. Restore the last full backup to a separate server first.
  2. Find the binary log file and position that the backup recorded (the --source-data=2 comment, or the Shell dump metadata).
  3. Replay the log with mysqlbinlog, stopping just before the bad statement:
Terminal
mysqlbinlog --start-position=157 --stop-datetime="2026-03-01 10:15:00" \
  binlog.000042 binlog.000043 | mysql -u root -p

Use --stop-position when you know the exact position of the bad statement, and --start-position after it if you want to continue and skip it. Binary logs are deleted after binlog_expire_logs_seconds (30 days by default), so keep copies for as long as your recovery window needs. The Binary Log and Replication explains the log's formats.

The live demo below models the logic. A snapshot holds balances at 02:00, and a log table holds what happened afterwards. One entry is a mistake made by a bad script.

SQL Stand-in: replay a change log up to a moment

Press Run (or Ctrl + Enter) to see the result here.

Account A is 120 in both recovery columns, because the bad entry is excluded. Account B differs: stopping at 10:15 gives 40 and loses the later deposit, while skipping only the mistake gives 45. In real life, that is the difference between --stop-position and a second replay that starts after the bad statement.

Making a dump file

A logical dump is just statements. This stand-in builds INSERT lines from a table with SQLite's quote(), which escapes text the way a dump tool must. A title with an apostrophe is the classic case:

SQL Stand-in: generate INSERT statements like a dump

Press Run (or Ctrl + Enter) to see the result here.

The output has three lines, and the missing price becomes the keyword NULL, not a quoted word. Real mysqldump also writes DROP TABLE IF EXISTS, CREATE TABLE, LOCK TABLES and multi-row INSERT statements, and sets character set and foreign key variables at the top and bottom of the file, which is why you should restore a dump with the same mysql client rather than editing it by hand.

Testing your restores

Schedule the restore test, not just the backup:

  • Restore the newest backup to a scratch server every week, and run a few row-count and checksum queries against the result.
  • Time the restore. If it takes six hours and your business can wait one, change the plan now.
  • Store backups off the server, in another account or region, and protect them with the same care as the live data, because a dump contains everything. Encrypt them.
  • Keep several generations. A silent corruption is often found days later.
  • Write the restore procedure down, including the commands and who runs them.

Replicas are not backups. A DROP TABLE or a wrong UPDATE is copied to every replica within seconds. A delayed replica or a point-in-time-capable backup is what saves you.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does --single-transaction do for mysqldump?

    The dump runs in one REPEATABLE READ transaction, so it sees the data as of one moment and writers keep working. It only protects InnoDB tables.
  2. 2. Which pair is needed for point-in-time recovery?

    You restore the full backup, then replay the binary log up to the moment before the mistake with mysqlbinlog.
  3. 3. Why is a replica not a replacement for a backup?

    A DROP TABLE or a bad UPDATE is applied to the replica within moments, so both copies lose the data.
  4. 4. What is true about mysqlpump in MySQL 8.4?

    Use MySQL Shell dump utilities for parallel logical backups instead.

Summary

  • Logical backups (mysqldump, MySQL Shell dumps) are portable; physical ones (XtraBackup, Clone, snapshots) are fast for large data.
  • Use mysqldump --single-transaction --routines --triggers --events for InnoDB, and record the binary log position with --source-data=2.
  • mysqlpump is deprecated; use util.dumpInstance and util.loadDump instead.
  • Point-in-time recovery means restoring a full backup and replaying the binary log with mysqlbinlog to a stop position.
  • Replicas are not backups. Keep copies off the server, encrypted, and test a full restore regularly.

Next: The Binary Log and Replication.