How to back up MySQL from a replica

Why back up from a replica

mysqldump reads every row. On a busy primary those reads compete with your application, and options that record a binary log position take a brief global read lock that can wait behind a long write. A replica holds the same data a moment later and serves nobody. The price is a second server on the same MySQL version. Examples here use MySQL 8.4 on Ubuntu, a primary at 10.0.0.11 and a replica at 10.0.0.12 on a private network; MariaDB's names follow below.

Prepare the primary

Every server needs its own server_id (the default is 1) and GTIDs on; MySQL 8.4 has binary logging on by default but gtid_mode off. On the primary, add these to the [mysqld] section, changing Ubuntu's bind-address of 127.0.0.1 to the private address:

server_id                = 1
gtid_mode                = ON
enforce_gtid_consistency = ON
bind-address             = 10.0.0.11

Restart MySQL to apply it. If the primary already listens on the network and can't restart, switch GTIDs on live, one statement at a time. Leave the first running for a while under normal load and fix whatever it warns about in the error log; run the last only once the count reads 0:

SET GLOBAL enforce_gtid_consistency = WARN;
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
SET GLOBAL gtid_mode = ON;

Keep the config lines for restarts, and take a fresh backup: binary logs from before GTIDs can't be used afterwards. Then create the replication account. It needs only REPLICATION SLAVE, and the replica stores its password in plain text, so don't reuse an admin account:

CREATE USER 'repl'@'10.0.0.12' IDENTIFIED BY 'long-random-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.12';

sudo ufw allow proto tcp from 10.0.0.12 to any port 3306

Seed the replica

Install the same MySQL version on the replica, give it its own ID, and restart it:

server_id                = 2
gtid_mode                = ON
enforce_gtid_consistency = ON

Then dump the primary:

sudo mysqldump --all-databases --single-transaction --source-data=2 --set-gtid-purged=ON --routines --events --triggers --flush-privileges --result-file=/root/seed.sql

• --source-data=2 records the binary log file and position as a comment. It needs RELOAD and takes a global read lock for a moment at the start. --master-data is its deprecated old name.
• --set-gtid-purged=ON writes a SET @@GLOBAL.gtid_purged line listing every transaction the primary had run at that point. With GTIDs, that line is the replication position.
• --flush-privileges puts the copied accounts into effect once loaded.

Copy seed.sql to the replica. A fresh server can have GTIDs of its own, which makes the load fail, so clear them first:

RESET BINARY LOGS AND GTIDS;

sudo sh -c 'mysql < /root/seed.sql'

For a large database, a physical copy with XtraBackup is faster. After restoring it on the replica, delete auto.cnf from the data directory if present (so the replica gets its own server UUID), start MySQL, and run RESET BINARY LOGS AND GTIDS then SET GLOBAL gtid_purged = '<gtid-set>' with the set from the backup's xtrabackup_binlog_info.

Start replication

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.11',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'long-random-password',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;
START REPLICA;

• SOURCE_AUTO_POSITION = 1: the replica sends the GTIDs it has, and the primary sends the rest. No file names or positions to copy.
• SOURCE_SSL = 1 encrypts the link with the certificate MySQL generates for itself. Accounts on MySQL 8.4's default caching_sha2_password can't replicate unencrypted without RSA key exchange (GET_SOURCE_PUBLIC_KEY = 1).

START REPLICA returns before it knows whether replication works. Check:

SHOW REPLICA STATUS\G

You want Replica_IO_Running: Yes (the receiver, which downloads from the primary), Replica_SQL_Running: Yes (the applier) and Seconds_Behind_Source falling to 0. Otherwise Last_IO_Error or Last_SQL_Error says why.

Make the replica read-only

SET GLOBAL super_read_only = ON;

Add super_read_only = ON to the replica's config too, for restarts. Replication keeps applying changes; everyone else, root included, gets The MySQL server is running with the --super-read-only option so it cannot execute this statement. Create users, the backup user included, on the primary: they replicate.

MariaDB names

Back up the replica

Create the backup user on the primary; replication carries it to the replica, where the dump logs in:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, RELOAD, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

RELOAD is required for --single-transaction when GTIDs are on; REPLICATION CLIENT lets the script below read SHOW REPLICA STATUS. Put the credentials in /etc/mysql/backup.cnf on the replica, mode 600, as in the mysqldump guide. Then:

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events --triggers --all-databases | gzip > /var/backups/mysql/all-$(date +%F).sql.gz

--single-transaction reads every InnoDB table from one snapshot while replication carries on. The default --set-gtid-purged=AUTO writes the replica's GTID set into the dump: exactly which primary transactions it holds. Two variants:

• MyISAM tables, or every engine frozen at one instant: stop only the applier, dump, start it again (the backup user then needs REPLICATION_SLAVE_ADMIN). With super_read_only on, nothing else writes; the receiver keeps downloading, so catching up is quick.
• File-and-position replication: --dump-replica=2 records the primary's binary log file and position as a comment, and stops the applier for the whole dump. MySQL warns against it when the restored server will use SOURCE_AUTO_POSITION = 1; with GTIDs, the gtid_purged line does the job.

mysql --defaults-extra-file=/etc/mysql/backup.cnf -e "STOP REPLICA SQL_THREAD"
trap 'mysql --defaults-extra-file=/etc/mysql/backup.cnf -e "START REPLICA SQL_THREAD"' EXIT

The trap restarts the applier even when the dump fails; a forgotten stopped applier is a common way for a replica to go stale.

Refuse to back up a stale replica

A broken replica still answers queries, so every nightly dump succeeds, with data frozen at the moment it broke. START REPLICA doesn't warn you when its threads stop later, and Seconds_Behind_Source isn't proof: MySQL 8.4 reports 0 whenever the applier has applied everything the receiver fetched, even if the receiver is behind. MariaDB's Seconds_Behind_Master reads 0 after a replica restart until a transaction runs (MDEV-17516). The reliable test is a timestamp the primary writes every minute:

CREATE DATABASE ops;
CREATE TABLE ops.heartbeat (id TINYINT PRIMARY KEY, ts DATETIME(6) NOT NULL);
INSERT INTO ops.heartbeat VALUES (1, UTC_TIMESTAMP(6));
CREATE EVENT ops.heartbeat_tick ON SCHEDULE EVERY 1 MINUTE
  DO UPDATE ops.heartbeat SET ts = UTC_TIMESTAMP(6) WHERE id = 1;

MySQL 8.4 runs events by default and disables the replicated copy on replicas, so only the primary ticks. Keep both clocks on NTP. This script checks both threads and the heartbeat's age before it dumps:

#!/usr/bin/env bash
set -euo pipefail

CNF="/etc/mysql/backup.cnf"
MAX_AGE=300
DIR="/var/backups/mysql"
OUT="$DIR/all-$(date +%Y-%m-%d_%H%M).sql.gz"

STATUS=$(mysql --defaults-extra-file="$CNF" -e "SHOW REPLICA STATUS\G")
grep -q "Replica_IO_Running: Yes" <<< "$STATUS" || { echo "receiver not running"; exit 1; }
grep -q "Replica_SQL_Running: Yes" <<< "$STATUS" || { echo "applier not running"; exit 1; }

AGE=$(mysql --defaults-extra-file="$CNF" -N -e "SELECT TIMESTAMPDIFF(SECOND, ts, UTC_TIMESTAMP()) FROM ops.heartbeat WHERE id = 1")
if [ -z "$AGE" ] || [ "$AGE" -gt "$MAX_AGE" ]; then
  echo "replica data is ${AGE:-?} seconds old"; exit 1
fi

mkdir -p "$DIR"
trap 'rm -f "$OUT.partial"' EXIT
mysqldump --defaults-extra-file="$CNF" --single-transaction \
  --routines --events --triggers --all-databases | gzip > "$OUT.partial"
gunzip -c "$OUT.partial" | tail -n 1 | grep -q "Dump completed"
mv "$OUT.partial" "$OUT"

30 2 * * * root /usr/local/bin/mysql-replica-backup.sh >> /var/log/mysql-replica-backup.log 2>&1

Make it executable with chmod 700 and treat a failed run as an alert (backup failure alerts). If the primary is down and you want the replica's last state anyway, dump by hand. On MariaDB, match Slave_IO_Running and Slave_SQL_Running.

What a replica does not protect against

Replication copies mistakes as faithfully as data: a DROP TABLE, a DELETE without WHERE or a bad migration reaches the replica within seconds. That's why you back up the replica rather than calling it the backup. A delayed replica, a second one that applies each transaction an hour late, gives you a window:

STOP REPLICA SQL_THREAD;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
START REPLICA SQL_THREAD;

When someone drops a table, stop its applier at once, find the bad transaction's GTID in the primary's binary log (see point-in-time recovery), remove the delay, and apply everything before it:

STOP REPLICA SQL_THREAD;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
START REPLICA SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '3E11FA47-71CA-11E1-9E33-C80AA9429562:23';

The applier stops just before that transaction. Dump the table with --set-gtid-purged=OFF, since the primary has its own GTIDs, and load it into the primary. Keep the delayed replica out of the nightly backup, or its heartbeat check always fails.

Restore and test

A replica's dump restores like any other, on a fresh server or after RESET BINARY LOGS AND GTIDS:

gunzip -c all-2026-10-04_0230.sql.gz | mysql -u root -p

Then SELECT ts FROM ops.heartbeat; gives the moment, in UTC, the backup really represents; hours older than the file name means the replica was behind. To rebuild a lost replica, load the dump and run the CHANGE REPLICATION SOURCE TO and START REPLICA above: it fetches everything since the dump, as long as the primary still has those binary logs (binlog_expire_logs_seconds, 30 days by default). Restore into a scratch server on a schedule and time it (testing a restore).

Common errors

Frequently asked questions

Is it safe to run mysqldump on a replica?

Yes. mysqldump only reads, and with super_read_only on, only replication changes the replica. Check that it is current first, or the backup is old.

Does mysqldump --single-transaction stop replication?

No. The applier keeps running; the dump reads from the snapshot taken when it started. --dump-replica and stopping the applier yourself do pause it.

What is Seconds_Behind_Source?

MySQL 8.4's name for Seconds_Behind_Master: how far the applier's current event lags its timestamp on the primary. It reads 0 when the applier is idle and NULL when it is stopped, so it can look fine while the receiver is behind.

Is a MySQL replica a backup?

No. It copies a DROP TABLE or a bad UPDATE within seconds. Back up the replica, keep the dumps on other storage, and add a delayed replica for a window to undo mistakes.