Backup Strategy: RPO, RTO and the 3-2-1 Rule

Every database eventually loses data: a mistaken DELETE, a bad deployment, a failed disk, a hacked server, a ransomware attack. A backup you have never restored is only a hope. This chapter shows real backup and recovery commands, run against real MySQL servers. This first lesson is about planning.

Two numbers decide your strategy

A nightly dump gives an RPO of up to 24 hours: everything since the last backup is lost. To lower the RPO to minutes, you add the binary log (Point-in-Time Recovery). To lower the RTO, you need faster restores: physical backups, a standby replica, and a practised procedure (Replication Basics).

Ask the business for the numbers. “We want zero data loss and zero downtime” has a price, and people reconsider when they see it.

Logical and physical backups

Most teams use both: a physical (or replica-based) backup for fast recovery and a logical dump as a portable second copy.

What to back up

• All your databases, including views, stored routines, triggers and events.
• The binary logs since the last full backup (for point-in-time recovery).
• User accounts and grants. A dump of one database does not include them. Export with SHOW CREATE USER and SHOW GRANTS, or take the mysql system schema carefully.
• Configuration: my.cnf, certificates, keyring files. Without the keyring or master key, encrypted data cannot be read.
• Application files that belong to the data (uploads), because the database stores only the names.

Look at what binary logging is configured:

1

SELECT @@log_bin AS binary_logging, @@binlog_format AS format, @@binlog_expire_logs_seconds / 86400 AS keep_days;

1
2
3
4
5
6

+----------------+--------+-----------+
| binary_logging | format | keep_days |
+----------------+--------+-----------+
|              1 | ROW    |   30.0000 |
+----------------+--------+-----------+
1 row in set

Binary logging is on by default in MySQL 8.x, in ROW format, and logs are kept for 30 days (binlog_expire_logs_seconds = 2592000). Check that your retention is longer than the time between full backups, or point-in-time recovery will have gaps. (On a managed service such as RDS or Cloud SQL, the provider configures this: check it.)

The 3-2-1 rule

• 3 copies of your data (the live one and two backups),
• on 2 different kinds of storage,
• with 1 copy off-site (another region or provider), preferably one that the server cannot delete (write-once or a separate account), because ransomware that reaches your server deletes the backups it can reach.

A backup account

The backup job needs read access, not write access, and never the root account (Application Users). The privileges that mysqldump really needed, tested on a MySQL 8.4 server, are:

1
2

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'use-a-long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, SHOW_ROUTINE, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

What each is for, from the real error messages the server gave when a privilege was missing:

Keep the password out of the command line: use an option file with chmod 600 or mysql_config_editor (Password Security).

Schedule, retention, monitoring

The restore drill

The only proof that a backup works is a restore:

1. Pick a backup at random.
2. Restore it on a different server.
3. Check it: row counts, checksums, an application smoke test (Restoring and Verifying).
4. Write down how long it took. That is your real RTO.
5. Repeat on a schedule (every quarter), and after any change to the backup process.

Write the procedure as a runbook that someone who is not you can follow at 3 a.m.

Replication is not a backup

A replica copies every change, including the accidental DROP TABLE, within a second. It protects against hardware failure, not against mistakes or attacks. A delayed replica (one that runs, say, one hour behind) is a useful safety net, but it does not replace backups.