mysqldump / mariadb-dump: back up and restore a MariaDB or MySQL database

Compressing and shipping elsewhere

SQL dumps compress very well. Compress on the fly rather than afterwards: the uncompressed file never lands on the server's disk.

mariadb-dump --single-transaction --routines --events mydb \
  | zstd -T0 > mydb-$(date +%F).sql.zst

mariadb-dump --single-transaction --routines --events mydb \
  | zstd -T0 | ssh backup@srv-backup "cat > /backups/mydb-$(date +%F).sql.zst"

mariadb-dump --single-transaction --routines --events --databases mydb \
  | ssh admin@new-server "mariadb"

The dump header already disables unique and foreign key checks: no need to do it by hand. What really slows an import down is single-threaded replay and index rebuilding.

• Temporarily relax durability with innodb_flush_log_at_trx_commit = 2, and set it back to 1 right after. Only on a server where a crash during the import doesn't matter: worst case, you restart the import.
• Size innodb_buffer_pool_size and the redo log for the import's write load, not the usual workload.
• If the target is a primary with replicas and the import must not be replicated, run SET sql_log_bin = 0 in the import session. Otherwise, check the dump header: a MySQL dump with GTIDs itself contains SET @@SESSION.sql_log_bin=0, which --set-gtid-purged=OFF avoids when the import must be replicated.
• When the restore takes longer than your recovery objective allows, the real lever is a different tool: parallel restore with mydumper / myloader, or a physical backup with mariabackup.

-- For the duration of the import only, then back to 1
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- ... import ...
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

Automating: a backup script with retention

One database per file, on-the-fly compression, dumps 14 days old or more purged, only when every backup succeeded. Credentials live in an option file readable by root only, never on the command line (visible in the process list).

# /root/.my.cnf
[client]
user=backup
password=********

#!/usr/bin/env bash
# /usr/local/bin/backup-mariadb.sh — credentials read from /root/.my.cnf (chmod 600)
set -euo pipefail

DEST=/backups/mariadb
DATE=$(date +%F-%H%M)
mkdir -p "$DEST"

# List the databases first: stop if the query fails or returns nothing
DBS=$(mariadb -N -B -e "SHOW DATABASES" \
  | grep -Ev '^(information_schema|performance_schema|sys)$')
[ -n "$DBS" ] || { echo "no database to dump" >&2; exit 1; }

while IFS= read -r DB; do
  OUT="$DEST/$DB-$DATE.sql.zst"
  if ! mariadb-dump --single-transaction --routines --events \
      --default-character-set=utf8mb4 --hex-blob "$DB" \
      | zstd -T0 -q > "$OUT"; then
    rm -f "$OUT"; echo "dump failed: $DB" >&2; exit 1
  fi
done <<< "$DBS"

# Only reached when every dump succeeded.
# -mtime +13 = files 14 full days old or more
find "$DEST" -name '*.sql.zst' -mtime +13 -delete