Backup & Replication

Backup Methods

Logical backups with mysqldump export SQL statements portable across versions. Physical backups (Percona XtraBackup, MySQL Enterprise Backup) copy InnoDB files for faster restore on large databases.

Combine full backups with binary log retention for point-in-time recovery. Encrypt backups at rest and restrict access to backup storage.

mysqldump --single-transaction --routines --triggers shop > shop_backup.sql
mysql shop < shop_backup.sql

Restoration

Test restore procedures quarterly on isolated infrastructure. Restoring to a new instance validates backup integrity better than checksums alone.

For PITR, replay binary logs from backup timestamp to target time. Document RTO and RPO targets so backup frequency matches business requirements.

mysqlbinlog --start-datetime="2024-06-01 10:00:00" \
  --stop-datetime="2024-06-01 11:00:00" binlog.000042 | mysql shop

Replication

MySQL replication copies changes from primary to replicas asynchronously (traditionally) or with Group Replication for multi-primary consensus. Read replicas scale read traffic; lag monitoring is critical.

Applications reading replicas must tolerate stale data or use primary for read-after-write consistency.

• Use GTID-based replication for simpler failover topology
• Monitor Seconds_Behind_Source or replication lag metrics
• Filter replication with replicate-do-db only when necessary—prefer full copies

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='...',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

High Availability

Orchestrate failover with Orchestrator, MHA, or cloud-managed failover. Virtual IP or proxy (ProxySQL, HAProxy) routes traffic to current primary.

Split-brain risks require odd-number quorum or STONITH mechanisms. Practice failover drills including application reconnection behavior.

• InnoDB Cluster provides integrated Group Replication management
• Semi-sync replication reduces data loss risk at latency cost
• Document promotion steps and rollback if new primary is unhealthy

Disaster Recovery

Replicate to a secondary region for geographic redundancy. Cross-region lag and cost trade off against RPO for regional outages.

Maintain runbooks for restoring from backups when replication topology is corrupted. Keep binlog expiration long enough to bridge backup gaps.

SHOW BINARY LOGS;
SHOW REPLICA STATUS\G