MySQL : sauvegarde et restauration

Une sauvegarde n’a de valeur que testée : restaurer régulièrement sur une machine d’essai. Les exemples utilisent la base boutique.

Sauvegarde logique avec `mysqldump`

mysqldump produit un fichier SQL qui recrée la structure et les données.

mysqldump -u app -p --single-transaction --routines --triggers --events boutique > boutique.sql

Option	Rôle
`--single-transaction`	Copie cohérente des tables InnoDB sans les verrouiller
`--routines`, `--triggers`, `--events`	Inclut procédures, déclencheurs et événements planifiés
`--databases base1 base2`	Plusieurs bases (ajoute `CREATE DATABASE`)
`--all-databases`	Toutes les bases (compte avec les droits suffisants)
`--no-data`	Structure uniquement
`--no-create-info`	Données uniquement
`--set-gtid-purged=OFF`	Évite les directives GTID lors d’une restauration sur un autre serveur

mysqldump -u app -p --single-transaction boutique clients commandes > extrait.sql   # certaines tables
mysqldump -u admin -p --single-transaction --all-databases > tout.sql

Compresser

mysqldump -u app -p --single-transaction boutique | gzip > boutique-$(date +%F).sql.gz

Restaurer

mysql -u app -p boutique < boutique.sql                  # la base doit exister
gunzip -c boutique-2026-10-05.sql.gz | mysql -u app -p boutique

Pour restaurer sous un autre nom ou sur un autre serveur :

mysql -u admin -p -e "CREATE DATABASE boutique_test CHARACTER SET utf8mb4;"
mysql -u admin -p boutique_test < boutique.sql

Vérifier la sauvegarde

ls -lh boutique.sql.gz
gunzip -c boutique.sql.gz | head -20                     # lire le début
gunzip -c boutique.sql.gz | grep -c "INSERT INTO"        # nombre d'instructions d'insertion

Le test définitif est la restauration complète dans une base d’essai.

Planifier avec cron

Identifiants dans un fichier d’options, pour ne pas écrire le mot de passe dans la commande :

cat > ~/.mysql-backup.cnf <<'CONF'
[client]
user=sauvegarde
password=<mot-de-passe>
CONF
chmod 600 ~/.mysql-backup.cnf

Script /usr/local/bin/sauvegarde-mysql.sh :

#!/usr/bin/env bash
set -euo pipefail

DEST="/var/backups/mysql"
mkdir -p "$DEST"

mysqldump --defaults-extra-file="$HOME/.mysql-backup.cnf" \
  --single-transaction --routines --triggers --events boutique \
  | gzip > "$DEST/boutique-$(date +%F_%H%M).sql.gz"

# Conserve 14 jours
find "$DEST" -name "boutique-*.sql.gz" -mtime +14 -delete

sudo chmod 750 /usr/local/bin/sauvegarde-mysql.sh
crontab -e

30 2 * * * /usr/local/bin/sauvegarde-mysql.sh >> /var/log/sauvegarde-mysql.log 2>&1

Copier ensuite les sauvegardes hors du serveur (autre machine, stockage distant).

Autres méthodes

Méthode	Usage
Journaux binaires (`binlog`)	Restauration à un instant précis (PITR) en rejouant les modifications après une sauvegarde complète (`mysqlbinlog`)
Copie physique (Percona XtraBackup, MySQL Enterprise Backup)	Grandes bases : plus rapide que `mysqldump`
Instantané disque (LVM, ZFS)	Rapide, avec `FLUSH TABLES WITH READ LOCK` ou serveur arrêté pour la cohérence

SHOW BINARY LOG STATUS;       -- position actuelle du journal binaire (MySQL 8.4)
SHOW BINARY LOGS;

Dépannage

Symptôme	Piste
`Access denied ... PROCESS privilege`	Ajouter `--no-tablespaces` ou accorder le droit `PROCESS` au compte de sauvegarde
Fichier de sauvegarde vide ou très petit	Une erreur est survenue : vérifier le code de sortie et les messages (`set -o pipefail` dans un script)
Restauration très lente	Désactiver temporairement les index secondaires ou utiliser `--extended-insert` (par défaut) ; envisager une copie physique
`Unknown collation: utf8mb4_0900_ai_ci`	Restauration d’une sauvegarde 8.x vers un serveur plus ancien (MariaDB ou MySQL 5.7) : adapter la collation

Pour aller plus loin

Manuel : mysqldump
Planification : Bonnes pratiques Bash