A Destructive DELETE and a Backup Window That Cannot Meet the RPO

Do not restore directly over production. Build a separate recovery target first.

1. Freeze and preserve. Put the application in maintenance mode or revoke writes. Copy the current binary logs and the dump to a safe location before anything else; do not purge logs. Record the current master status and the exact time of the DELETE.

2. Restore the base image to staging. Load the previous night's dump into a new database instance or a separate schema on a staging host: gunzip < /backup/finapp.sql.gz | mysql -u root -p finapp_restore. If the dump was taken with --source-data=2, read the binlog file and position from its header; if not, use the dump start time to choose the starting binlog position, and document that approximation.

3. Replay binlogs up to just before the DELETE. Use mysqlbinlog --start-position=... --stop-position=... binlog.000123 | mysql -u root -p finapp_restore. Find the stop position by inspecting the decoded events with mysqlbinlog --base64-output=DECODE-ROWS -v and locating the transaction that contains the DELETE. Stop before its BEGIN, not after its COMMIT, so the bad transaction never enters the staging copy.

4. Validate staging. Compare row counts with expected values, check a sample of known rows, run referential-integrity queries, and confirm that the 2.3 million rows are present. Do not proceed on row count alone.

5. Move only the missing rows. Extract them from staging with a WHERE clause or a primary-key range, load them into a temporary table in production, validate, then insert into ledger_entry in batches inside a transaction. Use INSERT ... SELECT with a controlled batch size to avoid a long lock. Reconcile totals and have the finance owner sign off before re-enabling writes.

6. Record and review. Keep the staging instance until the incident is closed, document the exact positions used, and preserve the binlogs for audit.

The current design cannot meet either objective. The dump completes at 06:38, so a failure at 10:15 has a recovery point of nearly four hours, not 15 minutes. A full logical restore of 210 GB plus binlog replay is unlikely to finish within four hours, especially while the dump is competing with the 07:00 batch. Keeping the only backup on the same RAID-1 volume as the data means a volume or controller failure destroys both copies. There is no offsite copy, no immutable copy and no restore test.

A revised design should separate the backup path from the primary storage and shorten the recovery point:

• Physical online backups: Percona XtraBackup or MySQL Enterprise Backup, weekly full on Sunday at 01:00 and daily incremental at 01:00. These avoid a 5-hour logical dump and reduce load.
• Continuous binlog shipping: run mysqlbinlog --read-from-remote-server --raw --stop-never on a backup host, or ship binlogs every 5 minutes. This gives an RPO below 15 minutes. Retain at least 14 days and copy to object storage.
• A delayed replica: configure a replica with a 15-minute SOURCE_DELAY as a human-error safety net, but do not treat it as the only backup.
• Storage: follow 3-2-1: primary backup storage, a second media type, and an offsite or object-lock copy that the database account cannot delete.
• RTO engineering: pre-build a recovery VM, automate restore with scripts, and rehearse monthly. Measure actual restore time and keep it under four hours with margin.
• Verification: checksum the backups, run a monthly restore to staging, and alert on job failure or binlog shipping lag. Document retention and test results.

The root cause is not only a typing mistake; it is an environment that allowed an unreviewed, unbounded DELETE on a critical table.

Database controls.

• Remove DELETE from application and routine operator accounts. Grant it only to a controlled maintenance role, and require a second approver for that role.
• Enable sql_safe_updates for interactive sessions and use the --safe-updates client option. This forces a key-based WHERE clause and limits mass updates.
• Prefer append-only or soft-delete design: add deleted_at and revoke hard deletes, or archive old rows to a history table.
• Run destructive DML inside an explicit transaction: first run the same WHERE as a SELECT COUNT(*), inspect the rows, use LIMIT, then START TRANSACTION, delete, verify the affected count, and only then COMMIT. A ROLLBACK must be a normal step, not an afterthought.
• Keep binlog_row_image=FULL and enable the audit plugin so every DML statement and account is recorded.

Process controls. Store DML scripts in version control, not in a chat window. Require a change ticket with the exact SQL, the expected row count, a dry-run result, a backup reference and a rollback plan. Apply a two-person rule: the person who writes the statement cannot be the only person who executes it. Schedule destructive maintenance outside batch windows. For emergencies, use a break-glass procedure that still captures the approval and the SQL.

Detection. Alert on unusually large row-count changes, on deletes against ledger_entry, and on replication lag. After any incident, replay the binlog into staging and compare counts. Test the restore procedure quarterly; an untested rollback plan is only a document.