MySQL backup and restore test plan for a business application

A logical dump is a text file of SQL statements that rebuilds your tables and rows. The mysqldump tool writes it. Run it every night, at an hour when billing and dispatch are quiet.

Use the single transaction option for InnoDB tables, so the dump reads one consistent snapshot without locking staff out. Name each file with the database and the date, such as erp_2026-10-17.sql.gz.

Keep a written retention rule. Seven daily files, four weekly files and a handful of monthly files is a common pattern. Write down who owns the schedule and who is told when a run fails.

A nightly dump leaves a gap. If the database is damaged at three in the afternoon, you lose every order entered since midnight. Binary logs close that gap.

The binary log records each change as it happens. To recover, you restore last night’s dump, then replay the logs up to the minute before the damage. The mysqlbinlog tool does the replay.

Decide how long to keep the logs and where they live. Logs stored on the same disk as the database are lost together with it. Copy them away every hour.

Fire, theft, ransomware and a mistaken delete can all take out a server and the backup folder beside it. At least one copy must sit in another building or another cloud account.

Encrypt dumps before they leave the server. Give the upload job a credential that can add files but not delete old ones. An attacker who steals it then cannot wipe your history.

Check the off site folder weekly. Someone should open it and confirm that yesterday’s file is present and has a sensible size. A zero byte file passes silently otherwise.

Backup finished is not the same as restore proven. Corrupt files, missing grants, full disks and wrong character sets only show up when you restore. So restore, at least once a quarter.

Use a separate test server, never the live one. Load the latest dump, replay the binary logs to a chosen minute, and time the whole exercise. That time is your real recovery time, which owners often guess wrongly.

Record who ran it, how long it took and what went wrong. Fix the faults the same week. A restore drill that finds nothing is rare, and each fault found is a disaster avoided.

A restore that finishes without errors can still be incomplete. Compare row counts for the main tables, such as customers, invoices, invoice_lines and payments, against the live database at the same time.

Then check totals a business person understands. Does the sum of yesterday’s invoices match the sales register? Does the newest invoice number match? Do a few known customers open with the right balance?

Sign the drill off on one sheet, with the checker’s name and the day it was done. Then book the next drill in the calendar before closing the document.