Backup & Recovery Interview Questions

mysqlpump parallelizes schema dumps and can exclude tables; it is not a drop-in for --single-transaction consistency in all versions and is deprecated in later 8.0 lines. Parallel threads can see different snapshots if you skip the transaction flags. Prefer mysqldump or a physical backup unless you already standardized on pump. Restore order of routines versus tables still bites.

XtraBackup --prepare applies redo to make pages consistent; skipping prepare leaves a torn copy that InnoDB crash recovery may not finish the way you expect. --prepare --apply-log-only is the incremental chain. After prepare, the directory is a datadir you start with a new auto.cnf uuid. Never prepare the only copy of a backup in place without a clone of the backup files.

Inconsistent dumps happen when you omit --single-transaction and dump InnoDB without a snapshot, so JOINs across tables see mid-commit states. Dumping from a replica without stopping the SQL thread and without a transaction has the same race. --skip-lock-tables without a snapshot is the usual accident. Prefer dumping from a delayed replica that you stop.

Partial backups (per-tablespace XtraBackup or dump of a schema) cannot be applied onto a live server as if they were incremental redo; table IDs, FK parents, and GTID sets will not match. Importing a tablespace (ALTER ... IMPORT TABLESPACE) needs matching .cfg and discarded tables. Prefer logical dump for one schema, physical for the whole instance.

Orchestrating dumps from a replica avoids backup locks on the source, but you must confirm replica lag is zero (or acceptable) and that log_replica_updates kept GTIDs you need. A delayed replica dump is consistently old by design. --dump-replica / source-data coordinates refer to the replica’s view. Do not promote that dump as a source-latest backup without saying so.

mysqldump --single-transaction starts a REPEATABLE READ snapshot and dumps InnoDB tables without holding FTWRL for the whole dump. MyISAM and DDL still need extra locking. Huge dumps keep a long MVCC history, so purge and disk undo grow. It is logical SQL, so restore is slow compared with a physical copy.

Physical backups (Percona XtraBackup or CLONE) copy tablespace pages and redo, so restore is a file copy plus apply-log rather than replaying INSERT SQL. They require InnoDB (and matching version) and more disk. Logical dumps are editable and good for a single schema. Interviewers want RTO as the decision, not tool preference.

Point-in-time recovery replays binary logs with mysqlbinlog --start-datetime after you restore the dump, up to the moment before the bad DROP. You need --source-data or GTID_PURGED from the dump so the first binlog position is known. Expired binlogs break PITR even if the dump is perfect. Test on a scratch instance, not by hoping.

Backup locks (LOCK INSTANCE FOR BACKUP / LOCK TABLES FOR BACKUP) freeze DDL while allowing InnoDB DML, which is what XtraBackup wants. They are lighter than FLUSH TABLES WITH READ LOCK on a pure InnoDB estate. A long backup still holds a consistent snapshot elsewhere. Apps that run online DDL during backup will wait.

GTID restore needs SET @@GLOBAL.GTID_PURGED to the dumped gtid_executed so the replica does not re-apply transactions already in the data files. mysqldump --set-gtid-purged=ON emits that SET. Restoring without it and then starting replication duplicates keys. CLONE copies GTID_EXECUTED with the files, which is simpler.

A dump without --master-data/--source-data lacks the binlog position, so PITR cannot know where to start mysqlbinlog. GTID dumps need the executed set equivalently. Taking a dump from a replica still needs the replica’s executed coordinates, not the source’s current file. Document which server the dump came from.

FLUSH TABLES WITH READ LOCK is still required for mixed MyISAM+InnoDB dumps if you need a globally consistent file position. Pure InnoDB --single-transaction only holds FTWRL briefly to snapshot binlog coords. A long FTWRL stalls writers and replicas. Prefer converting leftover MyISAM to InnoDB over designing around FTWRL.

CLONE INSTANCE (8.0.17+) copies a running donor over the protocol; donor must keep redo until the clone finishes and needs CLONE_ADMIN. The recipient is rebuilt in place. Donor lag and donor disk IO are the production cost. Encryption and keyring must be set up on the recipient first. It is not PITR—it is a current-state copy.

binlog + GTID PITR can skip a bad transaction with SET GTID_NEXT='uuid:id' COMMIT; then resume mysqlbinlog. That punches a hole you must never re-inject from another replica. ROW events around the hole may still reference the dropped table. Practice the injection on a restored copy; it is easy to skip the wrong GTID.

Backup verification means restoring to a scratch instance and running CHECKSUM TABLE or a smoke query, not just ls the file size. Encrypted dumps that cannot decrypt are not backups. Measure restore time against RTO. A replica is not a backup if the bad DROP already replicated.

Point-in-time to a replica: restore the dump, set GTID_PURGED, then START REPLICA; if GTID_PURGED is a subset of the source, auto-position fills the rest. If you restore a clone that already has more GTIDs than the source, you created errant transactions. RESET REPLICA ALL before attaching. Check GTID_SUBSET() before START.

A too-short binlog_expire_logs_seconds after a nightly dump breaks PITR if the dump finished at 01:00 and you need 03:00 events that were purged. Physical backups still need subsequent binlogs for PITR. Align expire with dump duration plus replica lag plus a human-response window. Disk-full PURGE is the silent version of the same bug.

Encrypted tablespaces need the keyring available at restore; XtraBackup copies the redo but not your Hashicorp/AWS key material unless you back that up separately. Starting mysqld without the key fails to open .ibd files. CLONE has the same keyring prerequisite. Document key rotation versus backup age or old backups become paperweights.

mysqlbinlog --base64-output=DECODE-ROWS -v is how you inspect ROW events when PITR needs to stop before one DELETE. Without decode you only see opaque base64. --start-position / --stop-position beat timestamps when multiple events share a second. Never pipe mysqlbinlog into the source; use a restored clone.

Crash recovery on startup replays redo then rolls back uncommitted undo; a missing redo file or a torn doublewrite is corruption, not a PITR scenario. innodb_force_recovery is a data-salvage ladder that can skip undo and lose data. Backups exist because force recovery is not a plan. After salvage, rebuild replicas from a new dump.