mysqldump / mariadb-dump : sauvegarder et restaurer une base MariaDB ou MySQL

Compresser et envoyer ailleurs

Un dump SQL se compresse très bien. Compressez à la volée plutôt qu'après coup : le fichier non compressé n'occupe jamais le disque du serveur.

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"

Le dump désactive déjà les contrôles d'unicité et de clés étrangères dans son en-tête : inutile de le refaire à la main. Ce qui ralentit vraiment un import, c'est le rejeu mono-thread et la reconstruction des index.

• Relâcher temporairement la durabilité avec innodb_flush_log_at_trx_commit = 2, et la remettre à 1 juste après. À ne faire que sur un serveur où une coupure pendant l'import n'a pas de conséquence : au pire, on relance l'import.
• Dimensionner innodb_buffer_pool_size et la taille du redo log pour la charge d'écriture de l'import, pas pour la charge habituelle.
• Si le serveur cible est un primaire avec des réplicas et que l'import ne doit pas être répliqué, SET sql_log_bin = 0 dans la session d'import. Dans le cas contraire, vérifiez l'en-tête du dump : un dump MySQL avec GTID contient lui-même SET @@SESSION.sql_log_bin=0, à éviter avec --set-gtid-purged=OFF si l'import doit être répliqué.
• Quand la restauration devient trop longue pour votre objectif de reprise, le vrai levier est de changer d'outil : restauration parallèle avec mydumper / myloader, ou sauvegarde physique avec 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;

Automatiser : un script de sauvegarde avec rétention

Une base par fichier, compression à la volée, purge des dumps de 14 jours et plus, uniquement si toutes les sauvegardes ont réussi. Les identifiants sont dans un fichier d'options lisible par root seul, jamais sur la ligne de commande (visible dans la liste des processus).

# /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