Most companies only discover the real state of their database backup on the day they need it: the dump job stopped months ago, the file is on the same disk that failed, or nobody knows the restore command. This guide explains how to design a database backup that actually restores, for MySQL, MariaDB, PostgreSQL and other common engines.
Why database backup is different from file backup
A database is not a folder of independent files. The engine keeps data in several files, in memory buffers and in transaction logs, and it changes them constantly. If you copy the data directory with a normal file backup tool while the server is running, you get files captured at different moments. That copy may refuse to start, or worse, start and silently contain broken tables.
The consequence of getting this wrong is not a slower restore. It is lost orders, lost invoices, lost customer accounts, and in many countries a reportable incident under data protection laws such as LGPD or GDPR. A database backup therefore needs its own method, its own schedule and its own restore test.
The two families of database backup
| Type | What it copies | Strengths | Limits |
|---|---|---|---|
| Logical backup (dump) | SQL statements or an archive that rebuilds schema and data | Portable between versions and servers, easy to restore a single table, easy to inspect | Slower to create and restore on large databases; restore rebuilds indexes |
| Physical backup | The engine's data files, copied in a consistent way | Fast for large databases, enables point-in-time recovery with transaction logs | Tied to the same major version and platform; restores the whole instance |
For databases up to a few tens of gigabytes, a daily logical dump is usually enough and much simpler. Above that, or when losing a day of data is unacceptable, combine a physical backup with continuous transaction log archiving.
Recommended tools by engine
| Engine | Logical | Physical / point-in-time |
|---|---|---|
| MySQL | mysqldump --single-transaction, MySQL Shell dump utilities | Percona XtraBackup or MySQL Enterprise Backup, plus binary logs |
| MariaDB | mariadb-dump --single-transaction | mariadb-backup, plus binary logs |
| PostgreSQL | pg_dump (one database), pg_dumpall --globals-only (roles) | pg_basebackup with WAL archiving, or pgBackRest |
| MongoDB | mongodump | Filesystem snapshots with journaling, or replica set based backups |
| SQLite | sqlite3 app.db ".backup backup.db" or VACUUM INTO | Not applicable |
| Redis | RDB snapshot (BGSAVE) | AOF persistence |
Each engine has detailed guides: MySQL backup best practices, MariaDB backup strategies and how to automate PostgreSQL backups.
A minimal, consistent dump for each engine
# MySQL / MariaDB (InnoDB): consistent without locking writes
mysqldump --single-transaction --routines --triggers --events \
--databases shop | gzip > shop-$(date +%F).sql.gz
# PostgreSQL: compressed custom format, restorable with pg_restore
pg_dump -Fc -d shop -f shop-$(date +%F).dump
pg_dumpall --globals-only > globals-$(date +%F).sql
# SQLite: online, consistent copy
sqlite3 /srv/app/app.db ".backup '/backups/app-$(date +%F).db'"Never write passwords on the command line, where they appear in the process list and shell history. Use ~/.my.cnf or mysql_config_editor for MySQL, and ~/.pgpass for PostgreSQL, with permissions set to 600.
How often should you back up a database?
The answer comes from your recovery point objective (RPO): how much data, measured in time, the business can afford to lose. If the answer is "one day", a nightly dump is enough. If it is "fifteen minutes", you need transaction log archiving (MySQL binary logs, PostgreSQL WAL) shipped off the server continuously. The RPO vs RTO guide shows how to define these numbers with the business.
A practical starting point for small and medium systems:
- full logical dump every night, outside peak hours;
- binary logs or WAL archived every few minutes for critical databases;
- retention of 7 daily, 4 weekly and 6 to 12 monthly copies;
- an extra dump before every migration or application upgrade.
Where to keep database backups
A dump stored on the database server protects only against a mistaken DELETE. It does not survive disk failure, ransomware or the loss of the machine. Follow the 3-2-1-1-0 rule: at least three copies, on two types of media, one offsite, one immutable or offline, and zero errors after verification. Encrypt dumps before they leave your network, because they contain every customer record in readable form.
Testing the restore
A backup is only proven when it has been restored. At least once a month, restore the latest dump into a separate server or container and check it:
# PostgreSQL example on a test instance
createdb shop_restore_test
pg_restore -d shop_restore_test --no-owner shop-2026-10-25.dump
psql -d shop_restore_test -c "SELECT count(*) FROM orders;"Compare row counts of key tables with production, measure how long the restore took, and write the procedure down. The time you measure is your real recovery time, not the one in the contract.
Common database backup mistakes
- Copying the data directory of a running server with
cp,rsyncor a file backup agent. - Using
mysqldumpwithout--single-transactionon InnoDB, which locks tables or produces inconsistent dumps. - Backing up the database but not the users, roles and grants.
- Scripts that fail silently because nobody checks the exit code or the file size.
- Keeping the only copy on the same server or in the same cloud account as production.
- Never testing a restore until a real incident.
Database backup checklist
- List every database, including those inside Docker containers and SaaS add-ons.
- Choose logical, physical or both for each one, based on size and RPO.
- Automate the job and alert on failure, not only on success.
- Copy backups offsite, encrypted, with a retention policy.
- Restore a backup every month and record the time it took.
Backnix is being built to run consistent MySQL, MariaDB and PostgreSQL backups with native tools and store them encrypted offsite. See the database backup page for the current status and the plans and prices.