Mastering MySQL Backups: Strategies, Tools, and RTO/RPO Optimization

Why Database Backup Matters

Database backup is the last line of defense for data security. Requirements include multi‑region deployment, multi‑media deployment, and regular validation of backup effectiveness.

Common Backup Classifications

Physical Backup vs Logical Backup

Physical backup copies raw data files. Advantages: fast backup and restore, no instance required, can copy files while the instance is down (cold backup). Disadvantages: large backup files, strict version compatibility, local‑only, cannot shrink space, cannot back up MEMORY tables.

Logical backup backs up logical records. Advantages: high portability, flexible (restore per database or table), no engine restrictions, smaller files, remote backup, space can be reclaimed after restore. Disadvantages: slower backup and restore.

Offline Backup vs Online Backup

Offline (cold) backup is performed with the instance stopped and only supports physical backup. Online (hot) backup runs while the instance is active and can be either physical or logical.

Full Backup vs Incremental Backup

Full backup copies the entire instance. Incremental backup copies only the data pages that changed since the last backup. Physical incremental backup is simple (check LSN changes). Logical incremental backup is harder because it often relies on time‑based columns.

Backup Tools in MySQL

Physical Backup Tools

XtraBackup – open‑source tool from Percona for MySQL, MariaDB, Percona Server. Supports 2.4 (MySQL 5.6/5.7) and 8.0 versions.

mysqlbackup – MySQL Enterprise Backup for MySQL Enterprise Edition.

Clone Plugin – introduced in MySQL 8.0.17 for fast cloning of instances.

Logical Backup Tools

mysqldump – single‑threaded tool bundled with MySQL.

mydumper – multi‑threaded backup tool from Facebook, SkySQL, Oracle, Percona.

mysqlpump – parallel backup tool introduced in MySQL 5.7.

MySQL Shell – util.dumpInstance() for row‑level parallel backup.

SELECT … INTO OUTFILE – SQL command to export table data.

Comparison of mysqlbackup and mysqldump

Official MySQL data shows that mysqldump is 49× slower for backup and 80× slower for restore compared with mysqlbackup.

Logical backup tools are generally slower than physical ones, but for large data sets mydumper is preferred over mysqldump.

How to Verify Backup Effectiveness

Restore the backup to a test instance and run random queries.

Set up replication from the restored instance; if the replica catches up without errors, the backup is likely consistent.

Use pt‑table‑checksum to compare master‑slave data; matching checksums indicate a valid backup.

Method 2 is usually sufficient for production; method 3 is more thorough but time‑consuming.

RTO and RPO

RTO (Recovery Time Objective) is the maximum acceptable downtime after a disaster. RPO (Recovery Point Objective) is the maximum acceptable data loss.

To reduce RTO: increase backup frequency, use physical backup, add delayed replica, automate recovery.

To reduce RPO: increase backup frequency, back up binlog (Binlog Server), add delayed replica.

Both improvements increase storage cost.

Summary

From an RTO perspective, prefer physical backup or multi‑threaded logical backup tools with parallel restore. From an RPO perspective, increase backup frequency. Consider storage cost and deploy delayed replicas for critical online workloads.

<code>INSERT INTO `t1` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');</code>

<code>1       aaa
2       bbb
3       ccc</code>