MySQL backups that actually restore: point-in-time recovery for small business applications

Every small business application faces the same question: if the database disappeared right now, how much work would be lost, and how long would it take to get it back? Most teams have a backup job and a folder of dump files, but few have ever restored one into a working application. A backup you have never restored is not a safety net. It is a hope, dressed up as a file.

MySQL gives small teams everything they need, without enterprise tooling: regular full backups, binary logs recording every change since the last backup, copies stored where the failure cannot reach them, and a restore proven on a schedule.

A backup is not a restore plan

The most common setup in a small business is a nightly dump, kept on the same server, never tested. It fails in exactly the ways that matter. If the server disk dies, the backup dies with it. If the dump job has silently failed for three weeks, nobody finds out until the day it is needed. If the dump is six hours old, the team must explain why a morning of orders is gone.

A restore plan answers four questions in advance: how much data can we afford to lose, in minutes? How long can the application be down? Where does a clean copy live if production is gone? Who runs the restore when its author is away? As with code — a deployment pipeline should prove what it ships — a backup routine should prove what it can restore.

The two pieces: a full backup and the binary logs

Point-in-time recovery rests on two pieces. The first is a full backup, a consistent snapshot of the whole database at a known moment. For most small applications a logical dump with mysqldump is enough, provided it is taken with a single transaction so tables do not drift apart mid-dump. The second is the binary log, MySQL's record of every change after that snapshot, in order and with timestamps.

Recovery is then two stages: restore the full backup, then replay the binary logs up to the moment just before the damage. Deleted the wrong table at 10:42? Replay up to 10:41. A migration corrupted data at noon? Restore to 11:59 and replay only the good transactions. Without binary logs, the gap is whatever your customers did since the last backup.

# my.cnf — enable binary logging with a 7-day expiry
[mysqld]
log_bin = mysql-bin
binlog_expire_logs_seconds = 604800
binlog_format = ROW

# Nightly full backup, consistent and compressed
mysqldump --single-transaction --routines --events \
  --all-databases | gzip > /backups/mysql-full-$(date +%F).sql.gz

# Copy the backup AND the binary logs off the server, every night
rsync -a /backups/ backup-host:/backups/mysql/
rsync -a /var/lib/mysql/mysql-bin.* backup-host:/backups/mysql-binlogs/

How often, how long, and where

Three decisions determine whether the system fits the business. Frequency: nightly full backups are the right default, because binary logs cover the hours between them. Retention: at least two weeks of nightly backups, plus a monthly copy for several months, so a late discovery still has a clean restore point. Location: one copy off the production server, so a failed server cannot take the backups with it.

A written policy — nightly for fourteen days, monthly for six months, one pinned copy before every major migration — beats any ad-hoc arrangement, for the same reason that deciding what deployment artifacts to keep, and for how long, beats keeping everything or nothing.

Test the restore, on a schedule

This is the step most teams skip, and the only one that turns a backup into a guarantee. A restore test means restoring a backup onto a separate server, pointing a copy of the application at it, and confirming it actually works: logins succeed, recent orders are present, reports return sane numbers. Checking that the dump file exists is not a test — files can be truncated and permissions wrong, and only a real restore finds that, on a quiet Tuesday instead of during an outage.

Run the test monthly, and after every change to the backup routine, the MySQL version, or the server:

• Restore the most recent full backup to a separate server — never over production.
• Replay the binary logs to a chosen point in time and confirm the data lands where you expect.
• Start the application against the restored database and run its workflows end to end.
• Time the whole restore and write the number down; that is your recovery time, not a guess.
• Confirm the off-server copy restores too, not just the copy sitting on the production host.

What point-in-time recovery changes in an incident

Consider the two incidents small teams have. The first is obvious: a destructive query, or a migration that drops a column it should not have. Without point-in-time recovery, the team restores last night's backup and loses a day. With it, the team restores to the minute before the mistake — “we lost twenty minutes,” not “we lost today.”

The second incident is quieter: bad data discovered days later, from a faulty import or a bug that wrote wrong values for a week. Here binary logs alone are not enough, because replaying them reproduces the bad data too. What saves you is retention — an older full backup, restored separately so good rows can be extracted carefully. This is why deploying database changes without a maintenance window pairs so naturally with a tested restore.

Failure modes worth designing against

Most backup failures are boring, which is why they are missed: the job fails silently when a password expires, the disk fills because binary logs were never given an expiry, the dump excludes stored procedures because nobody passed the right flag, or the restore works on a laptop but not the server because MySQL versions differ. Counter each one cheaply — alert on job failure, monitor backup disk usage, include routines and events, and test on production's MySQL version. Observability for asynchronous workflows makes the same point: if nobody is watching it, assume it is broken until proven otherwise.

Answer the performance question early, too: a dump on a busy database can slow the application, so use the single-transaction flag and off-peak scheduling. As with treating a MySQL index as a hypothesis, measure backup and restore times and revisit the approach when they change.

Start with the restore, work backwards

If you do one thing this week, do the restore test. Take last night's backup, restore it somewhere safe, and time it. Whatever you learn — a missing flag, a stale password, a four-hour restore instead of forty minutes — is the most valuable database knowledge your team will gain all month. Then write the policy down: nightly backups, binary logs with a sane expiry, an off-server copy, monthly restore tests, and a named owner for each step. That document, plus the tested commands, is the real backup system.

For teams that would rather have this set up once than improvised under pressure, our cloud and DevOps work covers backup, recovery, and deployment pipelines. If your database is the part you think about least, it deserves attention first — and talk to us before the day you need the restore, not after.