MySQL Backup and Replication: A Practical Survival Guide

Two Kinds of Backup, Two Different Jobs

Logical backups export SQL statements; physical backups copy the data files. You need to know which fits your recovery target.

# Logical — portable, slow to restore, great for small/medium DBs
mysqldump --single-transaction --routines --triggers \
  --all-databases > full.sql

# Physical — fast restore of large DBs, near-zero lock with XtraBackup
xtrabackup --backup --target-dir=/backups/full

--single-transaction is critical: it gives a consistent snapshot of InnoDB tables without locking writes. Without it, mysqldump on a busy server produces an inconsistent dump.

Point-in-Time Recovery Needs Binary Logs

A nightly full backup alone means up to 24 hours of data loss. The binary log is MySQL's WAL equivalent — enable it and you can replay transactions up to the second before disaster:

# my.cnf
log_bin = mysql-bin
binlog_format = ROW
server_id = 1

# Restore: load the full backup, then replay binlogs to a target time
mysqlbinlog --stop-datetime="2026-05-10 14:29:59" \
  mysql-bin.000042 | mysql

Replication with GTIDs

Global Transaction Identifiers make replication far easier to reason about than the old file-and-position approach — each transaction has a unique ID, so failover and re-pointing replicas "just work."

# Both servers, my.cnf
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin

# On the replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.internal',
  SOURCE_USER='repl', SOURCE_PASSWORD='secret',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

Always Verify the Replica

SHOW REPLICA STATUS\G
-- Look for:
--   Replica_IO_Running: Yes
--   Replica_SQL_Running: Yes
--   Seconds_Behind_Source: 0

Seconds_Behind_Source is your lag metric — but like PostgreSQL's single lag number, it hides whether the delay is network, disk, or a slow query on the replica. And a backup you have never restored is not a backup: test recovery on a regular schedule.

One Pane for a Polyglot Estate

Most shops run more than one engine. PG Monitoring focuses its deepest intelligence on PostgreSQL, but the discipline is identical everywhere: track replication lag continuously, alert on stalls, and verify backups actually restore — so a silent replica failure never becomes a silent data-loss event.