Everyone has backups until they need one. Then the file turns out to be from March, or it is half a dump because the disk filled up, or it restores fine but nobody knows how to replay the six hours of changes since it was taken. A good MySQL or MariaDB backup strategy answers two questions in advance: how much data can we afford to lose, and how fast must we be back. This guide covers the tools that answer them — logical dumps, binary logs and point-in-time recovery — and the restore drill that proves they work.
Logical vs physical backups
There are two fundamentally different ways to back up a MySQL-family server:
| Logical (SQL dump) | Physical (file copy) | |
|---|---|---|
| What it is | SQL statements that recreate schema and data | A consistent copy of the data files |
| Tools | mysqldump / mariadb-dump, MySQL Shell dump utilities |
mariabackup (MariaDB), Percona XtraBackup or MySQL Enterprise Backup (MySQL) |
| Speed on large data | Slow to restore — every row is re-inserted and indexes rebuilt | Fast — files are copied back |
| Portability | Across versions, and often between MySQL and MariaDB | Same engine and closely matching version only |
| Granularity | Easy to restore one table or database | Usually all or nothing |
| Best for | Small to medium databases, migrations, extra copies | Large databases where restore time matters |
For databases up to a few tens of gigabytes, logical dumps plus binary logs are simple and dependable. Beyond that, restore time usually pushes you towards physical backups. Either way, the binary-log technique below is the same.
Taking a consistent dump
The single most important flag is --single-transaction. It makes the dump read from one consistent snapshot of InnoDB tables without locking them, so the application keeps running and the dump does not mix data from different moments.
# MySQL 8.x
mysqldump --single-transaction --routines --triggers --events
--source-data=2 --hex-blob
--databases shop > shop-2026-11-08.sql
# MariaDB (mariadb-dump; --master-data is the MariaDB spelling)
mariadb-dump --single-transaction --routines --triggers --events
--master-data=2 --hex-blob
--databases shop > shop-2026-11-08.sql
What the flags buy you:
--routines --triggers --events— include stored procedures, functions, triggers and scheduled events. Triggers are on by default; routines and events are not, and forgetting them is a classic surprise at restore time.--source-data=2/--master-data=2— writes the binary log file name and position at the moment of the snapshot into the dump as a comment. You need this for point-in-time recovery. (MySQL renamed the option in 8.0.26; MariaDB keeps the older name.) It requires binary logging to be enabled on the server.--hex-blob— writes binary columns as hex so they survive any character-set handling.--databases— includes theCREATE DATABASEandUSEstatements, so the file restores on its own.
Avoid passing the password on the command line, where other users can see it in the process list; use an option file with restricted permissions instead. Compress on the fly (| gzip > shop.sql.gz), and copy the result off the server — a backup that lives on the same disk as the database disappears with it.
MyDBDesk — SQL Dumps on a Schedule, From Your Desktop
Not every database has an ops team behind it. MyDBDesk dumps a whole database as SQL — with batched INSERTs and transaction-wrapped dumps — and its dumps restore even when tables reference each other. Scheduled exports repeat on their own, every 15 minutes through weekly, for a whole database or a single table; Keep holds only the newest few files so a schedule cannot fill your disk, Run now tests a job on the spot, and each job shows how its last run went. Jobs run while MyDBDesk is open — a handy extra copy alongside server-side backups.
Binary logs: the changes between dumps
A nightly dump means you can lose up to a day of data. The binary log closes that gap: it records every change to the data, in order, as it happens. Restore last night’s dump, replay the binary log up to a chosen moment, and you are back to that exact point.
Binary logging is on by default in MySQL 8 and off by default in MariaDB. Check, and enable it if needed in the server configuration file:
-- Is it on?
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
# my.cnf / my.ini [mysqld] section
log_bin = binlog
server_id = 1
binlog_format = ROW
binlog_expire_logs_seconds = 1209600 # keep 14 days
Keep binary logs for at least as long as the gap between two full backups — ideally longer — and copy them off the server too. ROW format records the actual changed rows, which is the safest choice for recovery.
Point-in-time recovery, step by step
Scenario: at 14:32 someone runs DELETE FROM orders; without a WHERE. Your last dump was at 02:00. Here is how to get back to 14:31:59 with nothing lost.
- Stop the damage. Keep the application from writing more changes if you can, and copy the current binary logs somewhere safe before anything rotates them.
- Find the starting point. The dump records where it was taken:
grep -m1 "CHANGE" shop-2026-11-08.sql -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000187', SOURCE_LOG_POS=4211; -- (older versions and MariaDB write CHANGE MASTER TO MASTER_LOG_FILE=...) - Find the bad statement. Decode the logs and search near the time:
mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2026-11-08 14:25:00" binlog.000189 | less # note the "# at NNNN" position just before the DELETE - Restore the dump onto a server (ideally a fresh one, not the damaged production instance):
mysql < shop-2026-11-08.sql - Replay everything up to the bad statement, starting at the position from step 2:
mysqlbinlog --start-position=4211 binlog.000187 binlog.000188 > replay.sql mysqlbinlog --stop-position=88342 binlog.000189 >> replay.sql mysql < replay.sql - Verify row counts and recent records, then switch the application over — or copy just the recovered table back into production.
On MariaDB the tools are named mariadb-binlog and mariadb (the mysql* names usually still work as aliases). Note that --start-position applies to the first file listed and --stop-position to the last, which is why the example decodes the final file separately. Using --stop-datetime instead of a position is simpler but less precise if several statements share the same second.
The restore drill nobody does
A backup job that has reported “success” every night for a year can still produce files that do not restore: truncated by a full disk, missing routines, referencing a collation the target server does not know, or encrypted with a key nobody can find. The only proof is a restore. At least monthly:
- Take the latest backup from its off-site location, as you would in a real incident.
- Restore it to a scratch server.
- Check that tables, row counts, routines and triggers are all present, and that the application can read the data.
- Time it. That number is your real recovery time, and it is often longer than people assume.
MyDBDesk — A Throwaway Server for Restore Drills
The hardest part of a restore drill is having somewhere safe to restore to. Click Local Server in MyDBDesk and it runs a portable MariaDB 11.4 on your own PC, listening only on 127.0.0.1, with a connection added automatically. Then run your .sql dump straight from disk — large files never load into the editor — and browse the restored tables in the grid to confirm the data is really there.
A sensible baseline strategy
- Nightly full backup (logical or physical, depending on size), compressed and copied off the server.
- Binary logs enabled in
ROWformat, retained beyond the full-backup interval and shipped off the server regularly. - The 3-2-1 rule: three copies, on two kinds of storage, one off-site.
- A monthly restore drill, timed and written down.
- Extra ad-hoc dumps before risky changes: migrations, bulk updates, version upgrades.
Replication is not on this list for a reason. A replica protects you from a dead server, but it faithfully copies a DELETE without a WHERE within milliseconds. Replicas are for availability; backups are for recovery.
Key takeaways
- Decide how much data you can lose and how fast you must recover before picking tools.
- Use
--single-transactionfor consistent, non-blocking dumps, and include--routines --events. - Record the binary log position in each dump (
--source-dataon MySQL,--master-dataon MariaDB). - Binary logs turn a nightly backup into recovery to any moment; MariaDB needs them switched on.
- Point-in-time recovery = restore the dump, then replay the binlog up to just before the mistake.
- Store backups off the server, and prove them with regular, timed restore drills. Replication is not a backup.

Leave a Reply