MySQL Backup, Restore, and Recovery

A backup is only useful when it can be restored within the required recovery time and data-loss tolerance. Recovery planning must include the application, configuration, files, and database together.

Define RPO and RTO

Logical Backup Example

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --databases customer_portal \
  | gzip > customer_portal-2026-07-16.sql.gz

--single-transaction provides a consistent snapshot for transactional tables without long table locks. It does not make non-transactional tables magically consistent.

Restore Example

gunzip -c customer_portal-2026-07-16.sql.gz \
  | mysql --host=127.0.0.1 --user=restore_operator --password

Point-in-Time Recovery

A full backup plus binary logs can restore to a moment after the backup and before an accidental change. This requires binary logging, retention, secure backup of logs, and a practiced restoration procedure.

Recovery Drill

1. Provision an isolated database instance.
2. Restore the most recent full backup.
3. Apply incremental or binary-log recovery to the target time.
4. Run integrity checks and application smoke tests.
5. Measure actual recovery time and data gap.
6. Record failures and update the runbook.

What Must Be Backed Up Together

• Database data and required binary logs
• Application releases and dependency lock files
• Environment configuration and secret-recovery process
• User uploads and generated documents
• Web server, PHP-FPM, scheduler, and queue configuration
• Migration history and operational runbooks