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 the CREATE DATABASE and USE statements, 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.

🔧 BackendSide Tool

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.

Explore MyDBDesk →

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.

  1. 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.
  2. 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=...)
  3. 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
  4. Restore the dump onto a server (ideally a fresh one, not the damaged production instance):
    mysql < shop-2026-11-08.sql
  5. 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
  6. 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:

  1. Take the latest backup from its off-site location, as you would in a real incident.
  2. Restore it to a scratch server.
  3. Check that tables, row counts, routines and triggers are all present, and that the application can read the data.
  4. Time it. That number is your real recovery time, and it is often longer than people assume.
🔧 BackendSide Tool

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.

Explore MyDBDesk →

A sensible baseline strategy

  • Nightly full backup (logical or physical, depending on size), compressed and copied off the server.
  • Binary logs enabled in ROW format, 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-transaction for consistent, non-blocking dumps, and include --routines --events.
  • Record the binary log position in each dump (--source-data on MySQL, --master-data on 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.