Every MySQL team I have worked with had backups. Fewer had restores. The difference sounds like a joke until the afternoon someone drops a table at 14:37 and you discover the "backup strategy" is a cron job nobody has watched succeed since the person who wrote it left. These are my field notes on the Percona XtraBackup plus binary log workflow I run for production MySQL 8.x, including the parts that only matter when things have already gone wrong.
The short version: weekly full physical backups, daily incrementals, binary logs retained past two full cycles, prepare steps executed in a strict order, and a scheduled restore drill that treats the backup pipeline like untested code. None of this is exotic. All of it fails silently if you skip the boring parts.
Why physical backups carry the pager
Logical dumps have a place, but for InnoDB datasets in the hundreds of gigabytes, restore time is the number that decides your recovery story. A physical backup restores at roughly the speed you can copy files; a logical restore replays SQL and rebuilds every secondary index from scratch. I keep logical exports around for schema archaeology and selective restores, and I compared the two models in more depth in MySQL backups vs PostgreSQL backups. When the business asks how long until we are back, the answer that holds up is the physical one.
One version note that bites people: XtraBackup tracks the server series. XtraBackup 8.0 backs up MySQL 8.0, XtraBackup 8.4 backs up MySQL 8.4, and neither is interchangeable with the other or with the 2.4 tooling that handled 5.7. Pin the tool version next to the server version in the same configuration management change, or your first backup after a server upgrade will fail at 2 a.m. instead of in review.
The full plus incremental cadence
My default cadence is a weekly full backup and daily incrementals. An incremental backup copies only the InnoDB pages whose LSN advanced since a previous backup, so the daily jobs are fast and small on most workloads. The command shape is simple: the full backup stands alone, and each incremental points at a base directory.
xtrabackup --backup --target-dir=/backups/full/2026-07-06
xtrabackup --backup --target-dir=/backups/inc/2026-07-07 --incremental-basedir=/backups/full/2026-07-06There is a real decision hiding in that second command: chain each incremental against the previous incremental, or point every incremental at the weekly full. Chained incrementals are smaller, but a single corrupt or missing link breaks every backup after it. Incrementals taken against the full are larger by midweek but each one is independently restorable with just the full. On systems where I have been burned, I pay the disk cost and base everything on the full. Disk is cheaper than the conversation where you explain why Wednesday is unrecoverable because Tuesday was corrupt.
Whichever shape you pick, verify two things on every run: the process exit code, and the literal "completed OK!" line at the end of the XtraBackup log. I have seen wrapper scripts that checked only one of the two and shipped weeks of unusable backups.
Prepare is where restores die
A raw XtraBackup directory is not a consistent database. It is a copy of tablespace files plus a redo log captured while the server was running. The prepare phase replays that redo and rolls back uncommitted transactions, and with incrementals the ordering is unforgiving: the base and every incremental except the last must be prepared with the apply-log-only flag, so that the rollback phase does not run early and throw away transactions the next incremental would have completed.
xtrabackup --prepare --apply-log-only --target-dir=/restore/full
xtrabackup --prepare --apply-log-only --target-dir=/restore/full --incremental-dir=/backups/inc/2026-07-07
xtrabackup --prepare --target-dir=/restore/fullGet the order wrong and the backup is not slightly wrong, it is unusable, and XtraBackup will not always stop you loudly. This is the single most common way I have seen "we have backups" turn into "we had files." Script the prepare chain, never type it by hand during an incident, and make the script refuse to run if the incremental directories are not in the expected sequence.
Binlog replay to the bad moment
The prepared backup gets you to the consistency point of the last incremental. Point-in-time recovery is the binary logs from that point forward. XtraBackup records the position for you in the xtrabackup_binlog_info file inside the backup directory: the binlog file, offset, and GTID set at the moment the backup became consistent. Guard that file with your life, because without it you are guessing at a starting position.
The prerequisite is that the binlogs still exist. Retention is controlled by binlog_expire_logs_seconds, and I want it to comfortably cover more than one full backup cycle, plus headroom for the weekend when nobody fixes the failed full.
SELECT @@binlog_expire_logs_seconds / 86400 AS retention_days;
SHOW BINARY LOGS;
-- MySQL 8.4 renamed the position statement:
-- SHOW BINARY LOG STATUS (8.4) vs SHOW MASTER STATUS (8.0)The replay itself is mysqlbinlog piped into the restored server, starting from the recorded position and stopping just before the destructive event. Finding that event is usually a grep for the DROP or DELETE in the decoded binlog, then a stop-datetime or stop-position one event earlier. With GTIDs enabled you get a second option: replay everything and exclude only the bad transaction's GTID. That is elegant when the damage is one transaction and dangerous when later transactions depended on the damaged state, so I default to the stop-before approach and treat GTID surgery as a special case.
Restore rehearsals: the backup you never tested
The canonical failure shape in this whole area is not a missing backup. It is a backup that was taken faithfully for months and had never once been through prepare and replay until the day it mattered. Every step above has a silent failure mode: a tool/server version mismatch, a broken incremental chain, expired binlogs, a missing xtrabackup_binlog_info, a prepare script with the flags in the wrong order.
So I schedule the rehearsal. Monthly at minimum, quarterly if the dataset is painful: restore the latest full plus incrementals to a scratch host, replay binlogs to an arbitrary recent timestamp, start the server, and run sanity checks against known reference numbers.
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) AS gb,
SUM(table_rows) AS approx_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
GROUP BY table_schema
ORDER BY gb DESC;Approximate row counts are fine for a smoke test; follow with exact counts on a few business-critical tables and one application-level query. Record the wall-clock time of the whole drill. That number is your honest RTO, and it is the difference between quoting recovery time from evidence and quoting it from hope.
What to monitor around the pipeline
Backups are a production workload and deserve production monitoring. The signals I actually alert on: backup age (hours since the last verified success, not since the last attempt), backup size delta against the previous run (a full backup that shrank 40% overnight is a question, not a victory), binlog disk headroom on the primary, retention versus the full-backup cycle, and offsite copy lag. During the backup window itself, watch for long-running DDL: XtraBackup takes the backup lock, and the interaction between a migration and a backup job is a classic mutual stall.
If you also operate PostgreSQL, the shape of this checklist will feel familiar even though every tool is different. The mapping between the two ecosystems is something I wrote up separately in the MySQL to PostgreSQL monitoring translation notes.
Where MonPG fits today
Full disclosure on positioning: MonPG is a PostgreSQL monitoring platform, and it does not monitor MySQL today. We are actively building MySQL monitoring (coming soon), and backup pipeline visibility of exactly this kind, backup age, binlog retention headroom, restore drill evidence, is part of what we think a credible MySQL story requires. Writing these workflows down is how we make sure the product matches how operators actually work.
If your team also runs PostgreSQL, that side is shipped and battle-tested, and you can start there today. For the MySQL side, the honest advice stands on its own: script the prepare chain, keep the binlogs longer than you think you need, and rehearse the restore before the incident schedules one for you.