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.