Sauvegarder et restaurer une base de donnees MySQL

Credit : Logo officiel

Sauvegarder et restaurer une base de donnees MySQL

Dylan D. — Agent Support Technique Serveur Depannage 1897 mots 10 min de lecture

La sauvegarde MySQL, ce truc qu'on regrette de ne pas avoir fait

La semaine derniere, un client e-commerce m'appelle un mardi a 14h : "J'ai fait un DELETE sans WHERE sur ma table commandes". Classique. Heureusement on avait un backup automatise a 3h du matin sur S3, et un dump a midi avant son intervention manuelle. Il a perdu 2 heures de commandes WooCommerce, soit 1400 euros de chiffre d'affaires. Sans backup, c'etait 6 mois d'historique evapore.

Je ne vais pas faire un laius sur l'importance des backups. Si vous avez deja perdu une base, vous savez. Si vous n'avez jamais perdu de base, vous ne savez pas encore. Ce guide regroupe toutes les methodes que j'utilise sur MariaDB 10.11 et MySQL 8.0 en production : mysqldump, xtrabackup, automatisation cron, restauration partielle, et surtout les pieges que j'ai vu exploser chez des clients.

mysqldump : l'outil universel

C'est l'outil standard, deja installe avec le serveur MySQL ou MariaDB. Il produit un fichier SQL texte, lisible et restaurable n'importe ou.

Les commandes de base

# Sauvegarder une seule base
mysqldump -u root -p monsite > monsite_backup.sql

# Avec routines, triggers et events (tres souvent oublies)
mysqldump -u root -p --routines --triggers --events monsite > monsite_full.sql

# Toutes les bases d'un coup
mysqldump -u root -p --all-databases > all_databases.sql

# Certaines tables seulement
mysqldump -u root -p monsite wp_posts wp_postmeta > tables.sql

# Structure seule, sans donnees (utile pour cloner un schema)
mysqldump -u root -p --no-data monsite > monsite_schema.sql

# Donnees seules, sans structure
mysqldump -u root -p --no-create-info monsite > monsite_data.sql

Compresser a la volee

Les dumps SQL c'est du texte, ca se compresse super bien (ratio 5:1 a 10:1) :

# Gzip classique
mysqldump -u root -p monsite | gzip > monsite_$(date +%Y%m%d_%H%M).sql.gz

# Zstd : plus rapide, meilleur ratio
mysqldump -u root -p monsite | zstd -T0 > monsite_$(date +%Y%m%d_%H%M).sql.zst

# Pigz : gzip multi-thread
mysqldump -u root -p monsite | pigz -p 4 > monsite.sql.gz

Je suis passe a zstd -T0 partout : sur une base de 12 Go, j'ai divise le temps de backup par 3 tout en obtenant un fichier 8% plus petit qu'avec gzip.

Les options qu'il faut TOUJOURS utiliser

mysqldump -u backup_user -p \
  --single-transaction \
  --quick \
  --lock-tables=false \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  --column-statistics=0 \
  monsite | zstd -T0 > monsite_$(date +%Y%m%d_%H%M).sql.zst

Creer un user dedie aux backups

Ne pas faire tourner les backups en root. Creer un compte avec les permissions minimales :

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'MotDePasseFort123!';
GRANT SELECT, SHOW VIEW, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, EVENT, TRIGGER
  ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Puis stocker le mot de passe dans ~/.my.cnf (chmod 600) pour ne plus le passer en CLI :

[client]
user=backup_user
password=MotDePasseFort123!

chmod 600 ~/.my.cnf
mysqldump --single-transaction monsite > monsite.sql

Xtrabackup pour les grosses bases

Si votre base fait plus de 10 Go, mysqldump devient trop lent. Percona XtraBackup fait des hot backups physiques (copie des fichiers InnoDB), sans interruption de service.

Installation et usage

# Debian 12
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup ps-80
sudo apt update
sudo apt install percona-xtrabackup-80

# Backup complet
sudo xtrabackup --backup \
  --target-dir=/backup/full/ \
  --user=backup_user --password=MotDePasse

# Preparer le backup pour restauration
sudo xtrabackup --prepare --target-dir=/backup/full/

# Backup incremental base sur le precedent
sudo xtrabackup --backup \
  --target-dir=/backup/inc1/ \
  --incremental-basedir=/backup/full/ \
  --user=backup_user --password=MotDePasse

Restauration xtrabackup

# Arreter MySQL
sudo systemctl stop mariadb

# Vider le datadir
sudo rm -rf /var/lib/mysql/*

# Copier le backup prepare
sudo xtrabackup --copy-back --target-dir=/backup/full/

# Permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Redemarrer
sudo systemctl start mariadb

Sur une base de 60 Go, j'ai mesure : mysqldump met 42 minutes, xtrabackup 8 minutes. Et la restauration est encore plus rapide.

Restauration : maitriser les options

Restauration complete

# Depuis un SQL
mysql -u root -p monsite < monsite_backup.sql

# Depuis un gzip
gunzip < monsite_backup.sql.gz | mysql -u root -p monsite

# Depuis un zstd
zstd -d < monsite_backup.sql.zst | mysql -u root -p monsite

# Dans une nouvelle base pour test
mysql -u root -p -e "CREATE DATABASE monsite_restore CHARACTER SET utf8mb4;"
mysql -u root -p monsite_restore < monsite_backup.sql

Restauration plus rapide

Desactiver les checks pendant l'import (5x plus rapide sur les gros dumps) :

mysql -u root -p monsite <<EOF
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE /backup/monsite.sql;
COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;
EOF

Restaurer une seule table depuis un dump complet

Classique : on ne veut pas tout restaurer, juste wp_posts :

# Extraire les CREATE et INSERT pour cette table
sed -n '/-- Table structure for table `wp_posts`/,/-- Table structure for table/p' \
  monsite_backup.sql > wp_posts_only.sql

mysql -u root -p monsite < wp_posts_only.sql

Ou plus propre, generer un dump par table :

for table in $(mysql -u root -p monsite -Bse "SHOW TABLES"); do
    mysqldump -u root -p --single-transaction monsite $table \
      > "backup/${table}.sql"
done

Automatiser avec cron

Creer un script /opt/scripts/backup-mysql.sh :

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=14
LOG="/var/log/mysql-backup.log"

mkdir -p "$BACKUP_DIR"

echo "[$(date)] Debut backup" >> "$LOG"

# Liste des bases sauf systeme
DATABASES=$(mysql -Bse "SHOW DATABASES" \
  | grep -Ev "^(information_schema|performance_schema|mysql|sys){{CONTENT}}quot;)

for DB in $DATABASES; do
    FILE="$BACKUP_DIR/${DB}_${DATE}.sql.zst"
    mysqldump --single-transaction --quick --routines --triggers \
      --events --column-statistics=0 \
      --default-character-set=utf8mb4 "$DB" \
      | zstd -T0 > "$FILE"
    SIZE=$(du -h "$FILE" | cut -f1)
    echo "  $DB : $SIZE" >> "$LOG"
done

# Nettoyage
find "$BACKUP_DIR" -name "*.sql.zst" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Fin backup" >> "$LOG"

chmod +x /opt/scripts/backup-mysql.sh
sudo crontab -e
# Tous les jours a 3h
0 3 * * * /opt/scripts/backup-mysql.sh
# Toutes les heures pour les bases critiques (commandes)
5 * * * * mysqldump --single-transaction prestashop ps_orders ps_order_detail | zstd > /backup/hourly/orders_$(date +\%H).sql.zst

Externaliser sur S3 ou Backblaze

Un backup qui reste sur la meme machine que la base ne sert a rien si le serveur brule. Synchroniser sur stockage objet :

# Avec rclone (configure prealablement)
rclone sync /backup/mysql/ b2:mon-bucket-backup/mysql/ \
  --transfers 4 --checksum

# Avec aws-cli
aws s3 sync /backup/mysql/ s3://mon-bucket/mysql/ \
  --storage-class STANDARD_IA

Je lance le sync 30 minutes apres le dump pour eviter de saturer la bande passante pendant la fenetre de backup.

Tester ses backups : le reflexe vital

Un backup que vous n'avez jamais teste, ce n'est pas un backup, c'est de l'espoir. Au moins une fois par mois, je restaure un backup sur un serveur de test :

# Verifier l'integrite du fichier
zstd -t monsite_backup.sql.zst

# Restaurer dans une base temporaire
mysql -e "CREATE DATABASE test_restore;"
zstd -d < monsite_backup.sql.zst | mysql test_restore

# Verifier les compteurs
mysql -e "SELECT COUNT(*) AS posts FROM test_restore.wp_posts;"
mysql -e "SELECT COUNT(*) AS orders FROM test_restore.ps_orders;"

# Comparer avec la prod
mysql -e "SELECT COUNT(*) FROM monsite.wp_posts;"

# Drop la base de test
mysql -e "DROP DATABASE test_restore;"

Automatisez ce check une fois par semaine et envoyez un mail si les compteurs divergent de plus de 10%.

Erreurs courantes et leur fix

1. "Got error: 1153: Got a packet bigger than 'max_allowed_packet'"

Le dump contient des BLOBs ou des INSERTs trop gros. Fix :

mysql --max_allowed_packet=512M -u root -p monsite < dump.sql

Ou dans /etc/mysql/mariadb.conf.d/50-server.cnf :

max_allowed_packet = 512M

2. Caracteres bizarres apres restauration (?? au lieu d'accents)

Probleme classique de charset. Forcer utf8mb4 a l'export ET a l'import :

mysqldump --default-character-set=utf8mb4 monsite > dump.sql
mysql --default-character-set=utf8mb4 monsite < dump.sql

Guide complet : convertir un charset MySQL en utf8mb4.

3. "ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege"

Le dump contient SET @@SESSION.SQL_LOG_BIN ou DEFINER. Fix :

mysqldump --set-gtid-purged=OFF --no-create-info monsite > dump.sql
# Ou nettoyer les DEFINER
sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' dump.sql

4. Backup qui prend toute la nuit

Vous etes encore sur mysqldump pour 50 Go ? Migrez sur xtrabackup. Sinon, parallelisez par base :

ls /var/lib/mysql/ | grep -v ^mysql$ | xargs -P 4 -I {} \
  bash -c 'mysqldump --single-transaction {} | zstd > /backup/{}.sql.zst'

5. Le disque /backup se remplit

Verifier la retention dans le script et la rotation. Surveillance simple :

df -h /backup
du -sh /backup/* | sort -rh | head

Si /backup est plein, le prochain dump ne tient pas et vous n'aurez plus de backup recent. Mettre une alerte avec Netdata ou un script cron simple.

Replication binaire pour le PITR

Pour les bases critiques (e-commerce, SaaS), un dump quotidien ne suffit pas : si vous perdez la base a 17h, vous perdez 14h de transactions. La solution : binlog + dump pour faire du Point-In-Time Recovery.

Activer les binlogs dans /etc/mysql/mariadb.conf.d/50-server.cnf :

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14
server_id = 1

Sauvegarder les binlogs en continu :

mysqlbinlog --read-from-remote-server --raw \
  --host=localhost --user=backup_user --password \
  --result-file=/backup/binlogs/ \
  --stop-never mysql-bin.000001

En cas de DELETE foireux a 17h alors que le dump est de 3h, on restaure le dump, puis on rejoue les binlogs jusqu'a 16h59 :

mysqlbinlog --stop-datetime="2026-05-07 16:59:00" \
  /backup/binlogs/mysql-bin.* | mysql -u root -p monsite

La precision peut descendre a la seconde avec --stop-position au lieu de --stop-datetime.

Surveiller que les backups tournent

Un script qui plante sans qu'on s'en apercoive, c'est le pire scenario. Petite verification cron a 4h apres le dump de 3h :

#!/bin/bash
LATEST=$(find /backup/mysql -name "*.sql.zst" -mtime -1 | head -1)
if [ -z "$LATEST" ]; then
    echo "ALERT: pas de backup MySQL des dernieres 24h" \
      | mail -s "BACKUP MANQUANT $(hostname)" admin@monsite.fr
fi

SIZE=$(stat -c%s "$LATEST")
if [ $SIZE -lt 1000000 ]; then
    echo "ALERT: backup suspicieusement petit: $SIZE octets" \
      | mail -s "BACKUP CORROMPU $(hostname)" admin@monsite.fr
fi

Integrer aussi dans Grafana une alerte sur "file age > 26 hours" pour ne pas decouvrir l'absence de backup au moment ou on en a besoin.

Pour aller plus loin

Le backup, c'est l'assurance qu'on espere ne jamais utiliser

La regle du 3-2-1 : 3 copies, 2 supports differents, 1 hors-site. Tenez-vous a ca, automatisez, testez vos restaurations une fois par mois, surveillez l'espace disque. Le jour ou un client appelle a 22h un vendredi parce qu'il a fait un DELETE malheureux, vous serez tranquille. Sans backup teste, c'est plusieurs heures de stress et parfois plusieurs jours de donnees perdues. Avec un backup teste, c'est une commande et 10 minutes de remontage. La difference entre les deux, c'est generalement ce qui distingue un dev qui se forme et un agent support qui dort la nuit.

# Articles similaires

Sur les memes sujets et plus loin