A backup you have never restored isn't a backup: it's hope with a timestamp. In this guide you'll see how to design a complete MySQL and MariaDB backup strategy to S3: from the classic mysqldump to hot physical backups with Mariabackup / Percona XtraBackup and binary logs for point-in-time recovery (PITR), with encryption, cron automation, ransomware-proof immutability via Object Lock and, most importantly, how to actually test that your copies restore.
Logical vs. physical: two backup philosophies
Before touching a command it helps to understand that MySQL and MariaDB have two backup families, and they complement each other more than they compete.
- Logical backup (
mysqldump,mariadb-dump): exports the contents as SQL statements. It's portable across major versions and architectures, ideal for cloning a specific database or migrating. Its Achilles heel is time: on large databases, dumping and restoring can take hours and, on its own, it can't recover to an exact second. - Physical backup (hot copy of the InnoDB data files + binary logs): copies data at the file level with Mariabackup (MariaDB) or Percona XtraBackup (MySQL) without stopping the server. Combined with the binlogs it enables Point-in-Time Recovery (PITR), much faster restores on large volumes and incremental backups. It's the recommended approach in production.
A practical rule: under a few GB, mysqldump to S3 solves your life; beyond that, or if you need a recovery point objective (RPO) of minutes, go straight to Mariabackup/XtraBackup with binlog archiving.
mysqldump / mariadb-dump: the logical dump
In MariaDB 10.x+ the command is called mariadb-dump (mysqldump remains as an alias); in MySQL it's mysqldump. The key to a consistent dump without locking on InnoDB tables is --single-transaction, which takes a transactional snapshot. Add --routines, --triggers and --events so you don't leave behind stored procedures, triggers or scheduled events.
You can compress and upload it straight to your bucket in streaming, with no intermediate disk:
#!/usr/bin/env bash
set -euo pipefail
DATE=$(date +%F)
ENDPOINT=https://es-mad-1.s3.otterstorage.io
BUCKET=backups-mysql
# Consistent dump of an InnoDB database, compressed, straight to S3
mysqldump --single-transaction --quick --routines --triggers --events mydb \
| gzip -6 \
| aws s3 cp - "s3://${BUCKET}/mydb/mydb-${DATE}.sql.gz" \
--endpoint-url "${ENDPOINT}"
# All databases (include users/grants on recent MariaDB with --system=all)
mysqldump --single-transaction --quick --all-databases \
| gzip -6 \
| aws s3 cp - "s3://${BUCKET}/full/all-${DATE}.sql.gz" \
--endpoint-url "${ENDPOINT}"
Heads-up: --single-transaction only guarantees consistency on transactional (InnoDB) tables. If you have MyISAM tables you'll need --lock-tables (which does lock writes during the dump). To set up the client credentials, follow the per-bucket isolated access keys guide: ideally the key this script uses only has permission over backups-mysql and nothing else; lean on the AWS CLI documentation for the profile. Don't put the password in the command: use a ~/.my.cnf file with a [client] section in mode 600.
Encrypt before uploading
Your backups contain sensitive data, so encrypt them at the source (client) so they travel and rest encrypted. The simplest way is to encrypt the stream with GnuPG before sending it:
# Symmetric AES-256 encryption before uploading
mysqldump --single-transaction --quick mydb \
| gzip -6 \
| gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /etc/mysql/backup.key \
| aws s3 cp - s3://backups-mysql/mydb/mydb-$(date +%F).sql.gz.gpg \
--endpoint-url https://es-mad-1.s3.otterstorage.io
If you'd rather not manage the cryptography yourself, restic encrypts out of the box with AES-256 and Poly1305 authentication, and deduplicates. We use it as a target below. Also check the security guide for the full model.
Mariabackup / Percona XtraBackup with an S3 target
For production, the hot physical backup is the standard: it copies the InnoDB files without stopping the database, supports incrementals and restores much faster than re-importing a dump. On MariaDB use Mariabackup; on MySQL, Percona XtraBackup. Both can emit the backup as a stream (xbstream) that you upload directly to S3.
Full backup in streaming
# MariaDB: full backup, streamed, compressed and straight to S3
mariabackup --backup --stream=xbstream --user=bkp --password=*** \
| zstd -3 \
| aws s3 cp - "s3://backups-mysql/phys/full-$(date +%F).xb.zst" \
--endpoint-url https://es-mad-1.s3.otterstorage.io
# MySQL with Percona XtraBackup is equivalent:
# xtrabackup --backup --stream=xbstream --target-dir=/tmp | zstd | aws s3 cp - s3://...
Incremental backup
Incrementals only copy the pages changed since a reference, so they're fast and small. They need to know the LSN of the base backup (Mariabackup stores it in xtrabackup_checkpoints).
# Incremental against the last local base backup
mariabackup --backup --target-dir=/var/backups/mysql/inc \
--incremental-basedir=/var/backups/mysql/base --user=bkp --password=***
# And sync it to S3 (see rclone below)
rclone copy /var/backups/mysql/inc otter:backups-mysql/phys/inc-$(date +%F)
Binary logs for PITR
A physical backup gives you a snapshot; the binary logs give you the movie. With a base backup + the later binlogs you can restore to an exact second (right before an accidental DROP TABLE). Enable them in my.cnf:
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800 # 7 days on local disk
sync_binlog = 1
Archive the binlogs continuously to S3 with mysqlbinlog in stream mode, which writes the segments as they're generated; then an rclone syncs them to the bucket:
# Read the server's binlogs raw and keep saving them locally
mysqlbinlog --read-from-remote-server --host=127.0.0.1 --user=repl --password=*** \
--raw --stop-never --result-file=/var/backups/mysql/binlog/ mysql-bin.000001 &
# Sync the binlog directory to S3 every minute (cron)
rclone sync /var/backups/mysql/binlog otter:backups-mysql/binlog --checksum
Schedule with cron
Automation is what turns a script into a backup policy. A typical scheme combines a weekly full, daily incrementals and continuous binlog archiving:
# /etc/cron.d/mysql-backup
# m h dom mon dow user command
0 2 * * 0 mysql /opt/scripts/mariabackup-full.sh
0 2 * * 1-6 mysql /opt/scripts/mariabackup-incremental.sh
*/1 * * * * mysql rclone sync /var/backups/mysql/binlog otter:backups-mysql/binlog --checksum
# Verification restore, first day of each month at 5:00
0 5 1 * * mysql /opt/scripts/verify-restore.sh
With OtterStorage's no per-request cost (PUT/GET/LIST) and no charge for deletes, syncing binlogs every minute or running frequent incrementals doesn't inflate the bill: you only pay for stored TB. That changes the math compared to providers where every operation counts.
Upload to a bucket with rclone or restic
If you do logical backups and want a sync layer or encryption and deduplication, rclone and restic are excellent companions.
rclone to sync dumps
# ~/.config/rclone/rclone.conf
[otter]
type = s3
provider = Other
endpoint = https://es-mad-1.s3.otterstorage.io
region = eu-mad
access_key_id = AKIAOTTEREXAMPLE
secret_access_key = your_secret_here
# Upload local dumps and mirror deletions to the destination
rclone sync /var/backups/mysql otter:backups-mysql --transfers 8 --checksum --progress
You'll find the details in the rclone guide.
restic for encrypted, deduplicated snapshots
export RESTIC_REPOSITORY="s3:https://es-mad-1.s3.otterstorage.io/backups-mysql/restic"
export RESTIC_PASSWORD_FILE="/etc/mysql/restic.pass"
export AWS_ACCESS_KEY_ID="AKIAOTTEREXAMPLE"
export AWS_SECRET_ACCESS_KEY="your_secret_here"
# Initialize the repository once
restic init
# Streaming backup of the dump (no disk), with a tag
mysqldump --single-transaction --quick mydb \
| restic backup --stdin --stdin-filename mydb.sql --tag mysql
# Retention policy: 7 daily, 4 weekly, 6 monthly
restic forget --keep-daily 7 --keep-weekly 4 --keep-monthly 6 --prune
Check the restic guide to fine-tune retention. Restic encrypts everything client-side, so not even we could read your snapshots.
mysqldump vs. Mariabackup/XtraBackup: when to use each
| Criterion | mysqldump / mariadb-dump | Mariabackup / XtraBackup |
|---|---|---|
| Backup type | Logical (SQL) | Physical (files) + binary logs |
| PITR (point in time) | Only with binlogs, by hand | Yes, to an exact second |
| Incrementals | No, always full | Yes, full and incremental |
| Impact on the server | CPU/IO load while dumping | Hot, minimal impact |
| Performance on large databases | Slow (hours) to restore | Fast (file copy) |
| Portability across major versions | High | Same major version |
| Encryption and compression | Manual (GPG / restic / gzip) | Built in (--stream + zstd) |
| Best use case | Small databases, migrations, cloning | Production, low RPO, large volumes |
Immutability with Object Lock against ransomware
Modern ransomware goes for your backups first: if it encrypts or deletes them, you have no alternative to paying. The defense is to make your copies immutable. Enable Object Lock (WORM) on the backup bucket with OtterVault: during the retention period, not even an attacker with your credentials can overwrite or delete the objects.
OtterStorage offers two modes. Governance lets users with a special permission bypass retention (useful to fix mistakes); Compliance allows no exceptions, not even for the root account, which meets regulatory immutability requirements. For full bucket-level protection you also have Legal Hold.
# Create the bucket with versioning and Object Lock enabled (WORM requirement)
aws s3api create-bucket --bucket backups-mysql \
--object-lock-enabled-for-bucket \
--endpoint-url https://es-mad-1.s3.otterstorage.io
# Default retention: 30 days in Compliance mode
aws s3api put-object-lock-configuration --bucket backups-mysql \
--object-lock-configuration '{"ObjectLockEnabled":"Enabled","Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":30}}}' \
--endpoint-url https://es-mad-1.s3.otterstorage.io
Object Lock requires versioning enabled on the bucket. Dig deeper in the Object Lock guide and versioning; to expire old binlogs automatically, combine it with lifecycle rules.
Test the restore (what really matters)
Say it with us: an untested restore is technical debt. Schedule periodic restores in an isolated environment and check that the database comes up and the data adds up. For a logical dump:
# Download and restore to a verification database
aws s3 cp s3://backups-mysql/mydb/mydb-2026-06-26.sql.gz - \
--endpoint-url https://es-mad-1.s3.otterstorage.io \
| gunzip \
| mysql mydb_restore
# Quick integrity check
mysql -N -e "SELECT COUNT(*) FROM mydb_restore.customers;"
With a physical backup, restoring means preparing (applying the redo log) and then copying back. Recovery to an exact point adds the binlogs up to the chosen instant:
# 1) Recover and prepare the base backup (downloaded and decompressed to /restore)
mariabackup --prepare --target-dir=/restore
# 2) Restore the files (server stopped) and start it
mariabackup --copy-back --target-dir=/restore
chown -R mysql:mysql /var/lib/mysql && systemctl start mariadb
# 3) PITR: replay the binlogs up to just before the accidental delete
mysqlbinlog --stop-datetime="2026-06-26 14:24:59" \
/var/backups/mysql/binlog/mysql-bin.0000* | mysql
Document how long it takes (your real RTO) and automate the monthly verification with the script we added to cron earlier. Follow the 3-2-1 principle: three copies, on two media, one off your infrastructure. S3 with Object Lock covers that external immutable copy. If you're coming from another provider, OtterBridge helps with assisted migration and OtterSync replicates across regions to keep your off-site copy in EU-FRA or US-EAST.
Take a look at the backup solution and the per-TB pricing (HDD €8, SSD €16, NVMe €45, no per-request cost) to size your strategy.
Frequently asked questions
mysqldump or Mariabackup/XtraBackup for production? +
How much does it cost to upload all those binlogs and incrementals to S3? +
How do I protect MySQL backups against ransomware? +
Immutable backups for your databases
Object Lock, retention and no per-request cost.
Become a Founding Otter