Database

Build Reliable MySQL Backup and Restore Procedures

Design encrypted, tested backups and repeatable restore procedures for application databases.

Overview

A backup is not a recovery plan until the team has restored it successfully. A scheduled dump can be incomplete, corrupted, inaccessible, or too old to meet recovery objectives. Define the acceptable recovery point objective (RPO) and recovery time objective (RTO) before selecting a backup schedule.

Start with the workload and the invariant

For smaller databases, logical dumps are convenient and portable. Use options appropriate to the server version and storage engine, and ensure the dump is consistent. For InnoDB, a transactional consistent snapshot can avoid blocking normal writes for the duration of the dump. Large databases may require physical backups or managed backup tooling.

Practical example

mysqldump --single-transaction --routines --triggers \
  --events --default-character-set=utf8mb4 \
  -u backup_user -p app_database > app_database.sql

Do not put passwords directly in shell history or process arguments. Use a protected client option file or a secrets manager with restrictive permissions. Encrypt backups before transferring them off the server. Store copies in a separate failure domain, restrict who can read them, and define retention so old backups do not accumulate indefinitely.

Trade-offs and failure modes

Restore into an isolated database first. Verify that tables, routines, triggers, events, character sets, and expected row counts are present. Run application smoke tests and check important business records. A successful command exit is necessary but not sufficient evidence of a usable recovery.

Verification and rollout

Document the exact restore sequence, required credentials, DNS or application cutover steps, and rollback plan. Include a clean-room restore test that does not rely on files remaining on the original host. Measure the time from starting restore to a verified application being available.

Engineering checklist

Review backup logs and alert on missed or unusually small backups. Perform restore drills periodically and after major database changes. Treat backup encryption keys as critical recovery dependencies: if the key is lost, the backup may be unusable.

Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.

Share this page Help other developers discover this resource.