MySQL Backup and Restore

Start with Recovery Goals, Not a Command

A database backup is useful only when it can restore the required service within an agreed loss and time window. Define these two goals before selecting tools:

A nightly dump cannot satisfy a 15-minute RPO by itself. A 2 TB logical dump that takes eight hours to import cannot satisfy a one-hour RTO. Measure real backup and restore durations, growth, network throughput and dependency time.

Write the owner, target system, recovery point, escalation path and decision authority in the runbook. Never discover them during an outage.

Choose the Right Backup Types

mysqldump is a logical backup program. For large databases or strict RTOs, evaluate a supported physical backup, storage snapshot coordinated with MySQL, replica-based workflow or MySQL Shell dump utilities. A replica is not a backup by itself: destructive or malicious changes can replicate.

Inventory Everything Needed for Recovery

Database rows are only one part of a working service. Record and protect the appropriate items:

• schemas, tables, views, triggers, routines and Event Scheduler events;
• users, roles and grants, with secrets handled through the approved vault;
• MySQL configuration, time zones, character sets, plugins and version;
• binary logs and replication metadata needed for point-in-time recovery;
• encryption keys, certificates and key-management recovery procedure;
• application migrations, uploaded files, object storage and external dependencies;
• backup manifests, checksums, retention policy and restore instructions.

Classify data before copying it. Backups frequently contain production personal data and credentials, so access controls, encryption and deletion obligations apply to them too.

Record prerequisites in a manifest:

service: school-api
mysql_source: mysql-8.4 / InnoDB
backup_utc: 2026-08-15T01:00:00Z
rpo: 15m
rto: 60m
includes: schema,data,views,triggers,routines,events
separate_assets: users-grants,config,binlogs,kms-runbook
restore_test: required

Create a Consistent Logical Backup Lab

Store credentials outside the command history. An administrator can create an encrypted login path interactively on the backup host:

mysql_config_editor set --login-path=backup \
  --host=db.example.internal --user=backup_operator --password

mysql --login-path=backup -e "SELECT VERSION();"

Use an account with only the privileges required by the selected dump options. Never put a production password directly in a command or tutorial script.

mysqldump --login-path=backup \
  --single-transaction --quick \
  --routines --events --triggers \
  --databases school \
  --result-file=school_full.sql

--single-transaction provides a consistent snapshot for transactional tables such as InnoDB without locking them for the entire dump. It does not make nontransactional tables consistent, and DDL such as ALTER TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE during the dump can break consistency. --quick streams rows instead of buffering a whole table.

Triggers are dumped by default, but stating --triggers documents intent. Stored routines and events require --routines and --events. --databases adds database creation/selection statements. On Windows PowerShell, prefer --result-file; some PowerShell redirection versions can create a UTF-16 file that cannot be loaded correctly.

Restore into an Isolated Target and Verify

Never test a restore over the only production database. Provision a compatible isolated server with enough disk space, keep application traffic blocked and scan the dump before executing it. Because the example was created with --databases school, the dump selects its database:

mysql_config_editor set --login-path=restore \
  --host=restore.example.internal --user=restore_operator --password

mysql --login-path=restore < school_full.sql

For an interactive client, SOURCE /approved/path/school_full.sql; is another option. Treat SQL dumps as executable code; use trusted artifacts and least-privileged restore accounts.

1. Confirm the client exit status and review the complete error log.
2. Check schema objects: tables, views, triggers, routines and events.
3. Compare expected row counts and business totals, not only file size.
4. Run foreign-key, nullability and application migration checks.
5. Use CHECKSUM TABLE only where operationally suitable; it is not a universal integrity proof.
6. Run read-only application smoke tests with outbound email, payments and jobs disabled.
7. Measure achieved restore time against RTO and record the recovery point.

SELECT COUNT(*) AS students FROM school.students;
SHOW TRIGGERS FROM school;
SHOW PROCEDURE STATUS WHERE Db='school';
SELECT EVENT_NAME,STATUS FROM information_schema.EVENTS
WHERE EVENT_SCHEMA='school';

Plan Point-in-Time Recovery with Binary Logs

A full backup recovers its snapshot time. To reduce data loss after that point, retain the required binary logs and use them to replay approved changes up to a time or position before the incident. This is point-in-time recovery (PITR).

1. Identify the exact incident timeline in UTC and stop unreviewed writes.
2. Preserve the affected server, backup, binary logs and audit evidence.
3. Restore the last valid full backup to a replacement isolated target.
4. Select the correct binary-log sequence beginning after that backup.
5. Review and replay only through the approved stop time/position.
6. Validate data and application invariants before cutover.

Keep binary logs long enough to cover the RPO and the time needed to discover an incident. Confirm log continuity, server identifiers, GTID policy and time-zone interpretation in every drill. An inaccurate stop time can reapply the damaging statement or discard legitimate work.

Protect, Retain and Dispose of Backups

A practical target is 3-2-1-1-0: three copies, two storage types, one offsite, one offline or immutable copy, and zero unverified restore errors. Adapt it to regulation and threat model; the numbers do not replace risk analysis.

• Encrypt in transit and at rest; manage keys separately and test key recovery.
• Use dedicated backup identities, least privilege, MFA for operators and audit logs.
• Make at least one copy immutable against ransomware and compromised administrators.
• Separate production credentials from backup and restore credentials.
• Define daily/weekly/monthly retention, legal holds and secure expiration.
• Monitor missed jobs, unexpected size changes, checksum failures and aging copies.
• Redact or mask sensitive production data before nonproduction use.

Do not retain data forever “just in case.” A documented retention schedule balances recovery, legal and privacy requirements. Securely expire both the object and any replicated copies when retention ends.

Production Backup and Restore Runbook

1. Design: approve RPO/RTO, scope, technology, owners and budget.
2. Prepare: inventory objects, versions, keys, grants and dependencies.
3. Back up: create a consistent copy and record immutable metadata.
4. Transfer: encrypt, checksum and place copies in independent failure domains.
5. Monitor: alert on failure, duration, size, retention and missing logs.
6. Restore: rebuild in isolation using documented commands.
7. Validate: compare technical and business acceptance criteria.
8. Exercise: time a disaster drill, record gaps and update the runbook.

For command-level logical dumps, continue to mysqldump. Review Event Scheduler so restored jobs do not run prematurely, and apply least-privilege access to backup identities.

Official References

• MySQL 8.4: Backup and Recovery
• MySQL 8.4: Backup Types
• MySQL 8.4: The Binary Log

The backup classifications, logical-dump limits and binary-log role in recovery were verified against the official MySQL manual. Test all procedures on the deployed MySQL edition, version and topology.