Back up and restore MySQL: mysqldump, MySQL Shell and binlog PITR

An accidental DROP TABLE in production, a disk corruption after a power cut, a ransomware that encrypts your data: without a tested backup strategy, these scenarios destroy your business. MySQL offers several complementary methods, from the simple SQL dump to restoring to the exact second, depending on the RPO (how much data you can afford to lose) and the RTO (how long you can stay offline) that your organization accepts.

This guide covers the three backup tools of the MySQL ecosystem, the archiving of the binary logs (the journal where the server records every data change) and Point-In-Time Recovery (PITR), which lets you go back to a precise moment before a human error. It also shows how to prove that a backup is usable, and why some backups are wrong without any error being displayed.

Current version

This guide uses MySQL 8.4 LTS (April 2024). Important change: mysqlpump is deprecated (since MySQL 8.0.34), and MySQL Shell (mysqlsh) is now the recommended tool for parallel logical dumps. The binary log is enabled by default.

What you will learn

• Run logical backups with mysqldump (instance, database, tables)
• Use MySQL Shell for parallel dumps and restores that are much faster
• Understand the role of Percona XtraBackup for hot physical backups
• Configure binary log archiving to keep the history of changes
• Restore to a precise point in time (PITR) after an accidental DROP TABLE
• Spot what a backup omits or distorts without an error: routines missing from a dump, inconsistent MyISAM tables
• Prove that a backup is usable by restoring it, then calling what the application calls
• Choose the right strategy depending on the size of your database and your RPO/RTO requirements

In what context?

MySQL backup is not something you set up the day you need it, which is already too late. You are concerned if:

• You administer a MySQL database in production and do not yet have a formalized backup strategy
• You must guarantee an RPO below a few minutes (finance, e-commerce, healthcare)
• You are migrating from MariaDB and must adapt your backup scripts (the tools differ)
• You are preparing a disaster recovery plan (DRP) and must document the restore procedures
• A recent incident (corruption, human error) revealed that your backups had not been tested

What this guide does not cover

Two neighbouring subjects often come up in the same conversation and deserve their own page. Replication protects against hardware failure but faithfully copies a DROP TABLE, so it never replaces a backup. Monitoring is used to spot drift before the incident, for example a disk filling up with unpurged binary logs.

Prerequisites

• MySQL 8.4 installed and running, see the installation guide
• A user dedicated to backups (accounts and least privilege: see the Security guide), for mysqldump: SELECT, SHOW VIEW, TRIGGER (always required), plus LOCK TABLES (without --single-transaction), RELOAD (with --source-data or GTID), PROCESS (without --no-tablespaces). For MySQL Shell: SELECT, RELOAD, EVENT, SHOW VIEW, TRIGGER (BACKUP_ADMIN is recommended for LOCK INSTANCE FOR BACKUP, but not strictly required)
• Root access or sudo on the server for restore operations and for installing XtraBackup
• Having read the MySQL configuration guide (in particular the section on the binary log)

The two backup families

As with PostgreSQL, MySQL distinguishes two main backup families. Each has its use cases.

Logical backup: SQL or CSV export

A logical backup produces a text file containing the SQL statements (CREATE TABLE, INSERT INTO) that rebuild the database when replayed. Nothing is copied at disk level: the server rereads the tables and regenerates SQL, which explains both the portability of the result and its slowness.

Tools: mysqldump (single-threaded, included with MySQL), MySQL Shell util.dumpInstance() (multi-threaded, compression).

Advantages: portable between versions, selective (one table, one schema), human-readable.

Limits: slow on large databases (SQL serialization), does not allow PITR on its own.

Physical backup: copy of the InnoDB files

A physical backup copies the raw data files (the InnoDB tablespaces and the redo logs, the journal where InnoDB notes every change before writing it to the data files) as they exist in the data directory, the directory where the server keeps its files (/var/lib/mysql on Debian). No SQL query is produced, which makes the operation fast but requires restoring on a server of the same major version of MySQL.

Tool: Percona XtraBackup (open source, hot backup without locking InnoDB).

MySQL Enterprise Backup

MySQL also offers MySQL Enterprise Backup (MEB) in the commercial edition. XtraBackup is the common open source alternative, and the one this guide details.

Advantages: fast (binary copy), hot backup (no locking), basis for physical PITR.

Limits: restores the whole cluster (not selective), not portable between major versions, external tool to install.

Quick comparison

Criterion	Logical (mysqldump / mysqlsh)	Physical (XtraBackup)
Granularity	Table, schema or instance	Whole instance
PITR	No (alone), needs binlogs on top	Yes, with the binlogs
Portability	Between versions, between OSes	Same major version
Backup speed	Slow (serialized SQL), mysqlsh improves it with parallelism	Fast (file copy)
Restore speed	Slow (replays the INSERTs), mysqlsh improves it with loadDump	Fast (file copy)
Server impact	Low with --single-transaction	Low (hot copy)

Swipe to see more

Rule of thumb: start with mysqldump or MySQL Shell. Move to XtraBackup when restoring a dump, timed on your hardware, exceeds the RTO you have announced (the orders of magnitude by database size are in the "Recommended backup strategy" section).

Logical backup with mysqldump

mysqldump is MySQL's historical tool. It is included in the standard installation and works in single-threaded mode.

Full instance dump

To back up all databases, including the system databases (mysql, sys):

mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events \
  > /tmp/full_instance.sql

-- MySQL dump 10.13  Distrib 8.4.5, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version    8.4.5

-- ...
-- Dumping data for table `clients`
-- ...

The file produced is plain, readable SQL; you can inspect it with head or grep.

Dump of a specific database

To back up only the lab_mysql database:

mysqldump -u root -p --single-transaction \
  --routines --triggers --events \
  lab_mysql > /tmp/lab_mysql.sql

Quick check:

head -30 /tmp/lab_mysql.sql

-- MySQL dump 10.13  Distrib 8.4.5, for Linux (x86_64)
--
-- Host: localhost    Database: lab_mysql
-- ------------------------------------------------------
-- Server version    8.4.5

This dump of a single database carries neither CREATE DATABASE nor USE (measured on MySQL 8.4). The destination database is the one you name to the client during the restore, for example mysql lab_mysql_restauree < /tmp/lab_mysql.sql, and it must exist beforehand. With --databases, the dump adds these two statements and then imposes its own database.

Dump of selected tables

A partial dump is mostly used to extract a reference table to copy to a test environment, or to leave out a log table that weighs more than the rest of the database. The trap is silent: mysqldump disables FOREIGN_KEY_CHECKS in the file header, so restoring a single table succeeds without error even if the table referenced by its foreign key is missing. The constraint stays declared and orphaned, and it is the first INSERT into the child table that fails with ERROR 1452 (measured on MySQL 8.4.10). Always export tables that are linked together, or remove the constraint on the copy.

To back up only certain tables:

# A single table
mysqldump -u root -p --single-transaction lab_mysql clients > /tmp/clients_only.sql

# Several tables
mysqldump -u root -p --single-transaction lab_mysql clients commandes > /tmp/clients_commandes.sql

# Everything except certain tables
mysqldump -u root -p --single-transaction \
  --ignore-table=lab_mysql.logs_acces \
  lab_mysql > /tmp/lab_sans_logs.sql

Essential options: --single-transaction, --routines, --triggers

The column to read first is the third one: it separates the options that are on by default from those you must add yourself. On MySQL 8.4, mysqldump --help shows routines FALSE and events FALSE in the summary of effective values, while triggers is TRUE. In other words, a dump run with no options does copy the triggers but silently forgets the stored procedures, the functions and the scheduled events. The loss only shows at restore time, when an application calls a procedure that no longer exists.

Option	Role	Essential?
--single-transaction	Starts a REPEATABLE READ transaction for a consistent dump without locking the tables (consistency guaranteed for InnoDB tables only, see the box below)	Yes on InnoDB: without this option, mysqldump takes LOCK TABLES that block writes
--routines	Includes stored procedures and functions	Yes, often forgotten
--triggers	Includes triggers (enabled by default since MySQL 5.x)	Yes (by default)
--events	Includes the event scheduler (MySQL's cron)	Yes if you use EVENT SCHEDULER
--set-gtid-purged	Controls whether GTID statements are included in the dump, AUTO (default): adds SET @@GLOBAL.gtid_purged if GTIDs (global identifiers assigned to each transaction) are enabled; ON: forces it (error if GTID is disabled); OFF: omits the GTID statements; COMMENTED: adds them as an SQL comment	Leave AUTO by default, use OFF only for a restore on an isolated server without replication
--result-file	Writes to a file instead of stdout (avoids encoding problems on Windows)	Optional

Swipe to see more

--single-transaction and MyISAM tables: a wrong dump, without an error

--single-transaction only freezes InnoDB tables. The MySQL documentation says it this way: only InnoDB tables are dumped in a consistent state, a MyISAM or MEMORY table may still change during the dump. Nothing freezes them and nothing is displayed: mysqldump exits with code 0 (measured on MySQL 8.4.10 with a MyISAM table), the file looks complete, and the inconsistency is only discovered at restore time, or never.

Two remedies: convert these tables to InnoDB, or give up --single-transaction for --lock-all-tables, which blocks writes for the whole dump. The default engine of MySQL 8.4 is InnoDB, but inherited (legacy) tables may remain: find them with SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' AND ENGINE != 'InnoDB';.

Limits of mysqldump on large databases

mysqldump is single-threaded: it exports tables one by one, sequentially. On a 100 GB database:

• The backup can take several hours
• The restore (mysql < dump.sql) is even slower because it re-executes each INSERT one by one
• The SQL file produced is large (no native compression)

When to switch to MySQL Shell: as soon as your dumps take more than 30 minutes or the database exceeds a few tens of GB.

Backup with MySQL Shell (mysqlsh)

MySQL Shell (mysqlsh) is MySQL's next-generation client. Its dump and load utilities are multi-threaded and produce compressed files; it is the recommended successor of mysqlpump (deprecated since MySQL 8.0.34).

util.dumpInstance() and util.dumpSchemas()

Connect to MySQL Shell, then run the dump:

mysqlsh root@localhost -- util dump-instance /tmp/full_dump

Or in interactive mode:

// In JavaScript mode in mysqlsh
util.dumpInstance("/tmp/full_dump", {
  threads: 4,
  compression: "zstd"
})

Acquiring global read lock
Global read lock acquired
Initializing - done
4 out of 6 schemas will be dumped and within them 12 tables, 0 views.
2 out of 4 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values...
120% (856 rows / ~712 rows), 856 rows/s, 52.34 KB/s
Duration: 00:00:01s
Schemas dumped: 4
Tables dumped: 12
Data size: 52.34 KB
Rows written: 856
Bytes written: 18.72 KB
Average throughput: 52.34 KB/s

For a single database:

util.dumpSchemas(["lab_mysql"], "/tmp/lab_dump", {
  threads: 4,
  compression: "zstd"
})

The result is a directory containing one file per table (DDL and data separated):

ls /tmp/full_dump/

lab_mysql@clients@@0.tsv.zst
lab_mysql@clients@@0.tsv.zst.idx
lab_mysql@clients.json
lab_mysql@clients.sql
lab_mysql@commandes@@0.tsv.zst
lab_mysql@commandes.json
lab_mysql@commandes.sql
@.done.json
@.json
@.post.sql
@.sql
@.users.sql

Each table has its DDL file (.sql) and its data (.tsv.zst, zstd-compressed). This separation allows parallel and selective restores.

Parallelism and compression

The four options below are tuned together: threads sets the number of simultaneous export streams, and chunking determines whether there is enough work to distribute among them. Without chunking, a 200 GB table monopolizes a single thread while the others wait, and parallelism brings nothing. Watch the server's disk load during the first trials: raising threads above the number of cores saturates I/O with no gain in duration.

Option	Default	Recommendation
threads	4	Number of available CPU cores (do not exceed)
compression	zstd	Keep zstd, best compression/speed ratio
chunking	true	Splits large tables into chunks to parallelize their export
bytesPerChunk	256M	Size of each chunk, reduce it if RAM is limited

Swipe to see more

The combination of parallelism and compression makes MySQL Shell much faster than mysqldump on large databases. The gain depends on the number of cores and the disks: time a real dump before promising an RTO.

util.loadDump(): parallel restore

The restore benefits from the same parallelism as the dump: each chunk is loaded by a separate thread, whereas mysql < dump.sql replays a single SQL stream. Two parameters decide the behaviour when resuming. resetProgress: true erases the progress log and reloads everything from the start, to be reserved for an initial load into an empty database. ignoreExistingObjects: false makes the load fail if a target table already exists, which avoids accidentally overwriting a database still in service.

util.loadDump("/tmp/full_dump", {
  threads: 4,
  resetProgress: true,
  ignoreExistingObjects: false
})

Loading DDL and Data from '/tmp/full_dump' using 4 threads.
Opening dump...
Target is MySQL 8.4.5. Dump was produced from MySQL 8.4.5
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Loading data using 4 threads...
120% (856 rows / ~712 rows), 0.00 rows/s, 0.00 B/s
Recreating indexes - done
12 chunks (856 rows, 52.34 KB) for 12 tables in 4 schemas were loaded in 2 sec
0 warnings were reported during the load.

loadDump: resuming after an error

util.loadDump() records its progress in a load-progress*.json file. If the restore is interrupted, rerun the same command: it resumes where it stopped (without resetProgress: true).

mysqlpump is deprecated, use mysqlsh

mysqlpump was introduced in MySQL 5.7 as a parallel alternative to mysqldump, but it suffered from limitations (no reliable transactional consistency, inconsistent options). MySQL 8.0.34 deprecated it (it will be removed in a future version). If your scripts still use mysqlpump, migrate to MySQL Shell:

Old (mysqlpump)	New (mysqlsh)
mysqlpump --all-databases	util.dumpInstance("/path/dump")
mysqlpump --databases db1 db2	util.dumpSchemas(["db1","db2"], "/path/dump")
mysqlpump --default-parallelism=4	util.dumpInstance("/path/dump", {threads: 4})

Swipe to see more

Physical backup with Percona XtraBackup

Principle: hot copy of the InnoDB files

Percona XtraBackup copies the InnoDB data files (tablespaces) while the server is running, without locking InnoDB tables. For non-InnoDB tables such as MyISAM, XtraBackup 8.4 takes a backup lock (LOCK INSTANCE FOR BACKUP), lighter than a global read lock (FLUSH TABLES WITH READ LOCK), and tries to avoid both when the instance contains only InnoDB. The global lock is only required with --slave-info. It is the MySQL equivalent of pg_basebackup for PostgreSQL.

The process has three steps:

1. Backup: copies the InnoDB files and continuously captures the redo log
2. Prepare: applies the redo log to make the backup consistent (like recovery after a hard stop, crash recovery)
3. Restore: copies the prepared backup in place of the data directory

Percona XtraBackup 8.4

Use Percona XtraBackup 8.4 for MySQL 8.4: XtraBackup versions must match the major version of MySQL. XtraBackup 8.0 does not work with MySQL 8.4.

Installing Percona XtraBackup

XtraBackup does not live in the distribution repositories: you must first install the percona-release package, which declares the signed Percona repositories, then explicitly enable the pxb-84 channel. That last command decides the major version installed, and forgetting it brings back an XtraBackup 8.0 that is incompatible with MySQL 8.4. The two tabs below cover the Debian and RHEL families.

Debian / Ubuntu

# Add the Percona repository
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release setup pxb-84
sudo apt update
sudo apt install percona-xtrabackup-84

Rocky / RHEL

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup pxb-84
sudo yum install percona-xtrabackup-84

Check:

xtrabackup --version

xtrabackup version 8.4.0-2 based on MySQL server 8.4.5

Full backup

The --target-dir must point to an empty or nonexistent directory: XtraBackup refuses to write into a directory that already contains files. A password passed in cleartext on the command line shows up in the shell history and in ps; in production, prefer an options file with 0600 permissions or the mysql_config_editor command. The backup consumes disk I/O proportional to the size of the data directory, so schedule it outside traffic peaks.

sudo xtrabackup --backup \
  --user=root --password='YourPassword' \
  --target-dir=/var/backups/mysql/full_$(date +%Y%m%d)

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: Starting backup threads (using 1 thread)...
xtrabackup: Redo log capacity 104857600 bytes
...
xtrabackup: Transaction log of lsn (38948302) to (38948312) was copied.
xtrabackup: completed OK!

The completed OK! message confirms success. The target directory contains a copy of the InnoDB files plus the redo logs captured during the backup.

Incremental backup

After a full backup, you can back up only the pages modified since:

# Incremental backup based on the full one
sudo xtrabackup --backup \
  --user=root --password='YourPassword' \
  --target-dir=/var/backups/mysql/incr_$(date +%Y%m%d) \
  --incremental-basedir=/var/backups/mysql/full_20260413

The incremental backup contains only the InnoDB pages modified since full_20260413, so the volume is much smaller than a full backup.

Prepare and restore

After the backup, putting back into service takes five steps, the second of which only applies if you took incrementals. Order matters: preparing the copy comes before stopping MySQL, so that the service interruption is as short as possible.

1. Prepare the full backup (apply the redo log):

   sudo xtrabackup --prepare --target-dir=/var/backups/mysql/full_20260413

   InnoDB: Starting crash recovery.
   ...
   xtrabackup: completed OK!

2. If incremental, apply each incremental on top of the full one:

   sudo xtrabackup --prepare \
     --target-dir=/var/backups/mysql/full_20260413 \
     --incremental-dir=/var/backups/mysql/incr_20260414

3. Stop MySQL:

   sudo systemctl stop mysql

4. Replace the data directory:

   sudo mv /var/lib/mysql /var/lib/mysql_damaged
   sudo xtrabackup --copy-back --target-dir=/var/backups/mysql/full_20260413
   sudo chown -R mysql:mysql /var/lib/mysql

5. Restart MySQL:

   sudo systemctl start mysql

Permissions after the restore

After --copy-back, the restored files belong to root. Do not forget the chown -R mysql:mysql /var/lib/mysql, otherwise MySQL will not start, with a permission error on the tablespaces.

Archiving the binary logs

Enable the binary log (enabled by default in 8.4)

In MySQL 8.4, the binary log is enabled by default (log_bin = ON). Check:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'log_bin_basename';
SHOW BINARY LOGS;

+-----------+-------+
| log_bin   | ON    |
+-----------+-------+

+------------------+---------------------------+
| log_bin_basename | /var/lib/mysql/binlog      |
+------------------+---------------------------+

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       180 | No        |
| binlog.000002 |      4521 | No        |
| binlog.000003 |       180 | No        |
+---------------+-----------+-----------+

The binlog files are in /var/lib/mysql/ by default. Each file contains the change events (INSERT, UPDATE, DELETE, DDL).

Important configuration in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# Binlog retention (30 days by default in 8.4)
binlog_expire_logs_seconds = 2592000

# ROW format for consistency (default in 8.4)
binlog_format = ROW

# Durability: flush at every commit
sync_binlog = 1

These settings can also be changed on the fly, and survive a restart with SET PERSIST: the Configuration guide shows where the binary log lives and how to make this persistent.

Difference with PostgreSQL

MySQL's binary logs combine the role of PostgreSQL's WAL (Write-Ahead Log) for replication and PITR, but are a distinct mechanism from the InnoDB redo log (which serves internal crash recovery). PostgreSQL has a single mechanism (the WAL) for both. The Discover MySQL guide details this distinction.

mysqlbinlog: read and filter events

mysqlbinlog is the command-line tool for reading binary logs:

# Read a whole binlog
mysqlbinlog /var/lib/mysql/binlog.000002

# at 4
#260413 10:15:00 server id 1  end_log_pos 126 ...
# at 126
#260413 10:15:00 server id 1  end_log_pos 197 ...
### INSERT INTO `lab_mysql`.`clients`
### SET
###   @1=1
###   @2='Alice Martin'
###   @3='alice@example.com'
###   @4='Paris'

To filter by database and by period:

# Events of one database between two instants
mysqlbinlog /var/lib/mysql/binlog.000002 \
  --database=lab_mysql \
  --start-datetime="2026-04-13 10:00:00" \
  --stop-datetime="2026-04-13 10:20:00" \
  --verbose

Useful options:

Option	Role
--database	Filter by database (caution: does not work for all cross-db statements)
--start-datetime / --stop-datetime	Filter by date
--start-position / --stop-position	Filter by position in the binlog
--verbose (-v)	Decode ROW events into readable pseudo-SQL
--base64-output=DECODE-ROWS	Display the data without the raw base64

Swipe to see more

Copy the binlogs to external storage

The binlogs sit on the same disk as the data. In production, move them off the machine:

# Manual copy to external storage
cp /var/lib/mysql/binlog.* /mnt/backup/mysql-binlogs/

# Or with rsync to a remote server
rsync -avz /var/lib/mysql/binlog.* backup-server:/backups/mysql-binlogs/

Do not copy the binlogs during an active write

Use FLUSH BINARY LOGS; before copying to force the rotation: the binlog currently being written is not guaranteed to be consistent while MySQL writes into it. Then copy the previous files (not the last, active one).

To automate continuous archiving, use mysqlbinlog in streaming mode:

# Continuous streaming of the binlogs to a remote file
mysqlbinlog --read-from-remote-server --host=localhost \
  --user=repl_user --password='xxx' \
  --raw --stop-never \
  binlog.000001 \
  --result-file=/mnt/backup/mysql-binlogs/

Thanks to --stop-never, the process stays connected to the server and keeps writing new events as they arrive, instead of stopping at the end of the current file. Run it under a systemd service with automatic restart: if it dies without anyone noticing, the RPO goes back to the time elapsed since the last full backup.

Point-In-Time Recovery (PITR)

PITR is the ability to restore your database to a precise moment in the past. It is a safety net against human error: a DROP TABLE at 14:32 can be undone by restoring the state at 14:31.

The principle: restore a dump + replay the binlogs

MySQL PITR combines two elements:

1. A reference dump (mysqldump or MySQL Shell), the starting point
2. The binary logs between the dump and the target instant, the changes to replay

The process:

[Dump at 02:00] ──── binlogs ──── [DROP TABLE at 14:32] ──── binlogs ── [now]
                         │                    │
                   We replay           We stop
                   these binlogs       BEFORE this position

Demonstration: simulate a DROP TABLE and restore

The lab_mysql database contains customers and orders. We will simulate an accident, then restore.

1. Create a reference dump:

   mysqldump -u root -p --single-transaction \
     --routines --triggers --events \
     --flush-logs --source-data=2 \
     lab_mysql > /tmp/lab_mysql_backup.sql

   The --flush-logs option forces a binlog rotation. The --source-data=2 option writes the binlog position into a comment of the dump (--master-data is a deprecated alias):

   head -30 /tmp/lab_mysql_backup.sql | grep "CHANGE"

   -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=157;

   The dump is consistent at position binlog.000004:157.

2. Insert a row (which must survive the restore):

   INSERT INTO clients (nom, email, ville)
   VALUES ('PITR Test', 'pitr@example.com', 'Nantes');
   
   SELECT COUNT(*) FROM clients;

   +----------+
   | COUNT(*) |
   +----------+
   |        6 |
   +----------+

3. Note the reference instant (the "safe point"):

   SELECT NOW() AS safe_timestamp;

   +---------------------+
   | safe_timestamp      |
   +---------------------+
   | 2026-04-13 10:19:49 |
   +---------------------+

4. Simulate the disaster (a few seconds later):

   DROP TABLE commandes;
   DROP TABLE clients;

   The tables are gone.

The accident has happened: the database no longer has its two tables, but the reference dump and the binlog still exist. Recovery takes two stages, in this order: the dump brings the database back to the state it was in when taken, then the binlogs replay everything that happened since, stopping before the DROP TABLE. The steps continue the numbering of the first series.

5. Force the binlog rotation:

   FLUSH BINARY LOGS;
   SHOW BINARY LOGS;

   +---------------+-----------+
   | Log_name      | File_size |
   +---------------+-----------+
   | binlog.000004 |     12847 |
   | binlog.000005 |       180 |
   +---------------+-----------+

   The DROP TABLE is in binlog.000004. Events after the rotation are in binlog.000005.

6. Restore the reference dump:

   mysql -u root -p lab_mysql < /tmp/lab_mysql_backup.sql

   The database is back to the state of the dump (5 customers, without "PITR Test").

7. Replay the binlogs up to the safe point:

   mysqlbinlog /var/lib/mysql/binlog.000004 \
     --start-position=157 \
     --stop-datetime="2026-04-13 10:19:49" \
     --database=lab_mysql \
     | mysql -u root -p lab_mysql

   This command reads the binlog from the position of the dump (157) and stops before the DROP TABLE.

8. Check the restore:

   SELECT nom, email FROM clients ORDER BY id;

   +---------------+--------------------+
   | nom           | email              |
   +---------------+--------------------+
   | Alice Martin  | alice@example.com  |
   | Bob Dupont    | bob@example.com    |
   | Claire Durand | claire@example.com |
   | David Moreau  | david@example.com  |
   | Eva Bernard   | eva@example.com    |
   | PITR Test     | pitr@example.com   |
   +---------------+--------------------+

   6 customers: the 5 original ones plus the row inserted before the DROP. PITR worked.

Identify the restore point (position or GTID)

Two methods to target the restore point:

By date/time (the most common):

mysqlbinlog binlog.000004 --stop-datetime="2026-04-13 10:19:49"

By position (more precise, useful when several transactions share the same second):

# Find the position of the DROP TABLE
mysqlbinlog binlog.000004 --verbose | grep -B5 "DROP TABLE"

# at 12534
#260413 10:20:03 server id 1  end_log_pos 12639
# at 12639
### DROP TABLE `lab_mysql`.`commandes`

# Replay up to the position BEFORE the DROP
mysqlbinlog binlog.000004 --start-position=157 --stop-position=12534 \
  | mysql -u root -p lab_mysql

By GTID (if enabled, recommended for replicated environments):

mysqlbinlog binlog.000004 \
  --exclude-gtids="7c1b9e1a-...:15" \
  | mysql -u root -p lab_mysql

Check consistency after the restore

A PITR can succeed technically and leave the database in a functionally inconsistent state: orders without a customer, desynchronized application counters, AUTO_INCREMENT sequences repositioned. The commands below cover the structural level, to be completed by business checks specific to your application, and first of all by an explicit search for orphans: a LEFT JOIN from commandes to clients that returns the rows with no match. Re-enabling the foreign key check does not detect them, because it does not reread the rows already present. CHECK TABLE really reads the InnoDB pages, it is not a simple metadata read, so allow time on large tables.

After a PITR, check data integrity:

-- Re-enable foreign key checking for this session
-- (rows already present are not rechecked)
SET FOREIGN_KEY_CHECKS = 1;

-- Count rows in the critical tables
SELECT
  (SELECT COUNT(*) FROM clients) AS nb_clients,
  (SELECT COUNT(*) FROM commandes) AS nb_commandes;

-- Check the tables for internal errors
CHECK TABLE clients, commandes;

+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| lab_mysql.clients      | check | status   | OK       |
| lab_mysql.commandes    | check | status   | OK       |
+------------------------+-------+----------+----------+

Recommended backup strategy

The three profiles below give starting orders of magnitude, not commitments: the size thresholds and the announced RPO/RTO depend on your hardware and your data volume, and only the timed restore of a real backup validates them (see "Testing your backups").

Small database (< 10 GB): mysqldump + binlogs

At this volume, the full dump takes a few minutes and the historical tool is enough. The effort goes elsewhere: into binlog archiving, which brings the RPO from 24 hours down to a few minutes for a negligible storage cost, and into the weekly restore test, which is the only way to know that the file produced every night is usable.

• Daily mysqldump through cron (--single-transaction --routines --triggers --events --flush-logs --source-data=2)
• Binlogs continuously archived to external storage
• Weekly automated restore test
• Retention: 7 rolling dumps + 1 monthly
• RPO: a few minutes (archived binlogs)
• RTO: a few minutes (small dump + fast binlog replay)

Medium database (10-500 GB): mysqlsh dump + binlogs

This is the range where mysqldump starts to overflow the night window and where MySQL Shell's parallelism really changes the game. The tipping point is decided not by the displayed size but by the measured duration: time a real dump, and if the restore exceeds the RTO announced to the business, change tools rather than add threads.

• Daily MySQL Shell util.dumpInstance() (parallel + zstd compression)
• Binlogs continuously archived
• Between two dumps, the binlogs fill the gap up to the desired instant
• Monthly PITR test
• RPO: a few minutes
• RTO: 15-60 minutes depending on volume

Large database (> 500 GB): XtraBackup + binlogs

Beyond a few hundred gigabytes, a logical restore is counted in hours because it re-executes each INSERT and rebuilds the indexes row by row. The physical backup replaces this work with a file copy, hence the gain on the RTO. In exchange, you inherit an operational constraint: the full plus incrementals chain must be complete and prepared in order, and one missing incremental makes all those that follow it unusable.

• Percona XtraBackup weekly full + daily incremental
• Binlogs continuously archived (streaming with mysqlbinlog --read-from-remote-server)
• MySQL Shell for selective dumps of critical tables
• Automated restore test in CI
• RPO: a few seconds (streamed binlogs)
• RTO: 30 minutes to a few hours depending on volume

Testing your backups

A backup that has not been tested is not a backup. The check must be automated and regular.

Three levels of verification:

Level	Method	What it proves
1. File integrity	Check that the dump is not empty or truncated	The file was produced without error
2. Restore	Restore into a disposable database	The dump is readable and replays without error
3. Consistency	Check queries on the restored database	The data is usable

Swipe to see more

Level 2 alone is not enough: a dump taken without --routines restores without error, with a return code of 0 and all the rows in place, while the procedure is missing. Only level 3 catches it, by calling what the application calls (a procedure, a trigger). The script below therefore also checks the routines.

Example of an automated test script:

#!/bin/bash
set -euo pipefail

DUMP="/var/backups/mysql/lab_mysql_$(date +%Y%m%d).sql"
TEST_DB="verify_$(date +%Y%m%d)"

# Dump
mysqldump -u root -p"${MYSQL_ROOT_PASSWORD}" \
  --single-transaction --routines --triggers --events \
  lab_mysql > "$DUMP"

# Check that the file is not empty
if [ ! -s "$DUMP" ]; then
  echo "ERROR: empty dump" >&2; exit 1
fi

# Restore into a temporary database
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -e "CREATE DATABASE $TEST_DB;"
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" "$TEST_DB" < "$DUMP"

# Check the content
COUNT=$(mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -N -e \
  "SELECT COUNT(*) FROM $TEST_DB.clients;")

if [ "$COUNT" -gt 0 ]; then
  echo "OK: $COUNT clients restored"
else
  echo "ERROR: empty database after restore" >&2
  exit 1
fi

# Check that the routines came back (a dump taken without --routines omits them without an error)
ROUTINES=$(mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -N -e \
  "SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = '$TEST_DB';")
echo "Routines restored: $ROUTINES"

# Cleanup
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -e "DROP DATABASE $TEST_DB;"

Put it into practice

The lab gives you a database that carries two tables, a stored procedure and a trigger, and asks for a complete logical backup, its restore into a new database, and the proof that the copy is usable. The validation looks at two objects: the backup file, because that is what you restore from the day the machine has burned, and the restored database, because a complete dump does not prove that it restores.

The trap is the one this guide announced above, and the lab makes you meet it for real. Measured on MySQL 8.4: a dump taken without --routines omits the stored procedures while keeping the triggers, then its restore succeeds with return code 0, with all the rows in place. The failure comes afterwards, at the first page that calls the procedure, with an ERROR 1305 ... does not exist, and the backup file looks beyond suspicion since it "restored without error".

One point the lab does not grade, and it tells the learner rather than hiding it: --single-transaction leaves no verifiable trace in the file produced, a dump taken with it and a dump taken without it both carrying a LOCK TABLES. A test that claimed to check it would grade a command line instead of a state. The option remains the good practice on a database in service whose tables are InnoDB, where it avoids blocking writes.

Guided lab Practice

Learn how to do it

Produce a logical backup that is complete, and prove it by restoring it

$ dsoxlab run mysql-05-sauvegarder

Environment: VM·Duration: ~35 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-05-sauvegarder checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-05-sauvegarder
dsoxlab check mysql-05-sauvegarder

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Troubleshooting

Most MySQL backup incidents fall into three families: a missing privilege on the backup account, a version incompatibility between the tool and the server, and a size limit reached (network packet, binlog retention). Read the middle column first: the raw error message is often misleading, whereas the real cause is fixed with one command. Be especially wary of the lines without an error code: a backup can be wrong without anything being displayed, and only a restore test reveals it.

Symptom	Likely cause	Fix
mysqldump: Got error: 1044: Access denied ... when using LOCK TABLES	User without the LOCK TABLES privilege, for a dump taken without --single-transaction	Add --single-transaction if the tables are InnoDB, or grant the privilege: GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
No error, but the dump is almost empty	User without SELECT on the database: its tables are invisible to it (return code 0, measured on 8.4.10)	Grant SELECT to the user, and test the restore rather than the file size
The application's writes block during the dump	Dump taken without --single-transaction: mysqldump locks the tables for reading	Add --single-transaction if the tables are InnoDB
No message, but MyISAM or MEMORY tables change during a dump taken with --single-transaction: the restore is inconsistent	--single-transaction only protects transactional tables: a MyISAM or MEMORY table may change during the dump	Convert the tables to InnoDB, or, if they must stay MyISAM, use --lock-all-tables (global read lock for the whole duration of the dump)
Very slow dump (> 1h for < 50 GB)	mysqldump is single-threaded	Switch to MySQL Shell util.dumpInstance() with threads: 4
A mysqldump 8.x refuses to dump an older server (5.7 for example), complaining about an unknown COLUMN_STATISTICS table	The --column-statistics option is on by default in mysqldump 8.4 (mysqldump --help shows column-statistics TRUE) and queries a table the older server does not have	Add --skip-column-statistics (or --column-statistics=0) to the dump
mysqlbinlog no longer finds the requested file, or the PITR replay stops for lack of events	The binlogs expired and were purged before being archived	Check binlog_expire_logs_seconds, increase retention, archive earlier
Got a packet bigger than 'max_allowed_packet' bytes	Row or BLOB too large for the packet size	Add --max-allowed-packet=512M to the dump and to the restore
XtraBackup stops as soon as it reads the redo log, or reports an unsupported server	XtraBackup version that does not match the major version of MySQL	XtraBackup 8.4 is mandatory for MySQL 8.4
PITR restore: missing data	--stop-datetime too early or missing binlog	Check the exact timestamp in the binlog with mysqlbinlog --verbose

Swipe to see more

Key takeaways

• Two families: mysqldump/mysqlsh (logical, portable, selective) and XtraBackup (physical, fast, physical PITR).
• --single-transaction is essential with mysqldump on InnoDB: without this option, writes are blocked.
• That same --single-transaction freezes only InnoDB: a MyISAM or MEMORY table is dumped without an error and may be inconsistent. Convert it to InnoDB, or use --lock-all-tables.
• MySQL Shell replaces mysqlpump (deprecated since 8.0.34), and its parallel dumps are much faster than mysqldump's.
• XtraBackup is the reference tool for hot physical backups; its version must match the major version of MySQL.
• A dump taken without the --routines option omits procedures and functions, and its restore succeeds with code 0: only a test that calls the procedure reveals it.
• The binary log is enabled by default in MySQL 8.4; watch the retention (binlog_expire_logs_seconds) to keep the disk from filling up.
• PITR combines a reference dump with the replay of the binlogs up to a precise instant; it is the safety net against accidental DROP TABLEs.
• --flush-logs --source-data=2 in mysqldump records the exact binlog position in the dump, essential for PITR (--master-data is a deprecated alias).
• Test your restores regularly: an untested backup is a false sense of security.
• For databases > 500 GB, combine XtraBackup (incremental backups) with continuous binlog archiving for an RPO of the order of a few seconds.

Knowledge check

Eight questions on what a dump omits by default, the restore that succeeds with code 0 without the procedures, the destination of a single-database dump, the proof by restoring, and the strategy that the size of the database imposes.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →