Almost every website has a backup system. Far fewer know for sure that their backups actually work, that is, that they can be restored without errors on the day they are needed. This article explains the rule that makes the difference, then how to put it in place for a MySQL or MariaDB database.

A backup that has never been tested is not a guarantee

A backup can be incomplete, corrupted, or simply incompatible with the server version at the time of restoring, without anyone noticing as long as it has never been used. The only way to know a backup works is to have already restored it, on a test environment, at least once. That is the rule almost nobody follows.

What needs to be backed up?

A website is made of two things that must stay consistent with each other:

Restoring a database without the matching images, or the other way round, gives you a broken site. Both must be backed up on the same schedule.

How much data can you afford to lose?

Backup frequency is not chosen at random: it depends on what you would accept to lose. For a brochure website that changes once a month, one backup a day is more than enough. For a store that takes orders all day long, losing a day of data means losing orders: you need to back up more often, or rely on the replication offered by your host.

You also need to decide how many versions to keep. A mistake or a hack is not always detected the same day: with only yesterday’s backup, you risk restoring a site that is already damaged. A common schedule: 7 daily, 4 weekly and a few monthly backups.

The 3-2-1 rule

The best-known benchmark: three copies of the data, on two different media, with one kept off-site. In practice, for a website: the production database, a backup on the server for quick restores, and a copy sent automatically somewhere else (storage with another provider), to survive a server failure or a hack.

Backing up a MySQL database properly

With mysqldump, the --single-transaction option gives you a consistent copy of InnoDB tables without locking the site during the backup:

mysqldump --single-transaction --routines --triggers \
  -u user -p database_name | gzip > backup-$(date +%F).sql.gz

The --routines and --triggers options include stored procedures and triggers, which are often forgotten. The password should not be written in plain text in the script: a protected MySQL options file is better.

Testing the restore

The test does not need to be complicated. Once a month, for example:

  1. create an empty test database;
  2. import the latest backup into it;
  3. compare a few figures with production: number of orders, products, articles;
  4. ideally, connect a copy of the site to this database and open it in a browser.
gunzip < backup-2026-11-01.sql.gz | mysql -u user -p test_database

If the import fails, or the figures do not match, you will have found out on a quiet day, not on the day of an outage.

Automate and monitor

A manual backup always ends up being forgotten. It must be scheduled (a cron job or your host’s tool) and monitored: an alert when it fails, and a regular look at the file sizes. A backup that is suddenly much smaller than the day before is a sign of a problem, spotted in seconds.

The most common mistakes

The checklist

  1. Database and files backed up on the same schedule.
  2. Frequency matched to what you can afford to lose.
  3. Several versions kept (daily, weekly, monthly).
  4. One copy off the server, with another provider.
  5. Scheduled backups, alert on failure, size monitored.
  6. A regular restore test, written down with its date.

What it changes in practice

The day a problem occurs — human error, a failed update, a server incident — the difference between a few minutes of downtime and permanent data loss almost always comes down to one thing: has the backup already been tested, or is this the first time anyone tries to use it?

Going further: my expertise: Freelance SQL / MySQL developer · read also: Why your site slows down as the database grows