MySQL Replication as a Backup Strategy

MySQL replication continuously synchronizes data from a primary (master) server to one or more replica (slave) servers. While replication is primarily for high availability and read scaling, it also provides a form of backup protection.

How Replication Works

The primary server writes changes to its binary log. Each replica reads the binary log and applies the same changes to its own data. Replication is asynchronous by default — the replica may lag slightly behind the primary.

Using a Replica for Backups

You can run mysqldump on the replica without impacting the primary server. Pause replication on the replica before taking the dump for a consistent snapshot, then resume it. This offloads backup I/O from the production primary.

Limitations

Replication is not a substitute for backups — accidental deletes replicate to all replicas
Replica lag means data may not be 100% current
Use delayed replication (MASTER_DELAY) to keep a replica N hours behind as a safety net

Example

-- Set up a delayed replica (stays 1 hour behind primary)
-- Run on the replica:
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 3600;
START SLAVE;

-- Take a backup from the replica (pause replication first)
STOP SLAVE SQL_THREAD;
-- Run mysqldump here (from shell)
-- mysqldump --single-transaction myapp_db > backup.sql
START SLAVE SQL_THREAD;

-- Check replication status
SHOW SLAVE STATUS\G

SQL

Full Editor

Pro Tip

A delayed replica is your best protection against accidental mass DELETE or DROP — it gives you a window to recover.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned