Backups via Replication

Backup from Replica

Run backups from a replica to avoid locking the primary. Use mysqldump, Percona XtraBackup, or MySQL Enterprise Backup.

Example

# mysqldump from replica (stops SQL thread briefly for consistent point)
mysqldump --single-transaction --master-data=2 \
  --all-databases -u root -p > backup.sql
# XtraBackup (hot backup, no locking)
xtrabackup --backup --target-dir=/data/backups/full
xtrabackup --prepare --target-dir=/data/backups/full
# Restore to new instance
xtrabackup --copy-back --target-dir=/data/backups/full
# Stop SQL thread before mysqldump to freeze replica position
STOP REPLICA SQL_THREAD;
-- backup --
START REPLICA SQL_THREAD;

BASH

Full Editor

Pro Tip

XtraBackup performs non-blocking hot backups of InnoDB — prefer it over mysqldump for large databases.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned