Credit : Illustration backtotheweb.fr

Sauvegarder et restaurer une base de donnees MySQL

Sauvegarder et restaurer une base de donnees MySQL

On a tous galéré avec ça au moins une fois.

La sauvegarde de vos bases de donnees est critique. Une mauvaise manipulation, un crash disque ou un piratage peut tout detruire. Voici les methodes de sauvegarde et restauration.

mysqldump : la methode classique

mysqldump est l'outil standard, installe avec MySQL/MariaDB :

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

# Sauvegarder avec les routines et triggers
mysqldump -u root -p --routines --triggers --events monsite > monsite_full.sql

# Sauvegarder toutes les bases
mysqldump -u root -p --all-databases > all_databases.sql

# Sauvegarder uniquement certaines tables
mysqldump -u root -p monsite wp_posts wp_postmeta > tables_specifiques.sql

# Sauvegarder la structure uniquement (sans donnees)
mysqldump -u root -p --no-data monsite > monsite_schema.sql

# Sauvegarder les donnees uniquement (sans structure)
mysqldump -u root -p --no-create-info monsite > monsite_data.sql

Compression a la volee

Les dumps SQL sont tres compressibles (ratio 5:1 a 10:1) :

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

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

# Compression avec parallelisme (pigz)
mysqldump -u root -p monsite | pigz -p 4 > monsite_$(date +%Y%m%d_%H%M).sql.gz

Options recommandees pour mysqldump

mysqldump -u root -p \
  --single-transaction \
  --quick \
  --lock-tables=false \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF \
  monsite | gzip > monsite_$(date +%Y%m%d_%H%M).sql.gz

• --single-transaction : sauvegarde consistante sans verrouiller les tables (InnoDB)
• --quick : n'utilise pas de buffer memoire pour les grandes tables
• --lock-tables=false : pas de verrouillage global

mysqlpump : la version parallele

mysqlpump permet des sauvegardes paralleles, plus rapides sur de grosses bases :

mysqlpump -u root -p \
  --default-parallelism=4 \
  --databases monsite boutique > multi_backup.sql

Avantages par rapport a mysqldump :

• Parallelisme natif
• Compression integree (--compress-output=zlib)
• Progress bar

Xtrabackup : sauvegarde physique (production)

Pour les bases volumineuses (> 10 Go), Percona XtraBackup est la reference :

# Installation
sudo apt install percona-xtrabackup-80

# Sauvegarde complete
sudo xtrabackup --backup --target-dir=/backup/full/ \
  --user=root --password=MotDePasse

# Preparer la sauvegarde
sudo xtrabackup --prepare --target-dir=/backup/full/

# Sauvegarde incrementale
sudo xtrabackup --backup --target-dir=/backup/inc1/ \
  --incremental-basedir=/backup/full/ \
  --user=root --password=MotDePasse

Avantages de xtrabackup :

• Hot backup : pas d'interruption de service
• Sauvegardes incrementales
• Beaucoup plus rapide que mysqldump sur les grosses bases

Restauration

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

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



> **Astuce** : j'ai appris ça à la dure après un incident en prod un vendredi soir.


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

# Restaurer dans une nouvelle base
mysql -u root -p -e "CREATE DATABASE monsite_restore;"
mysql -u root -p monsite_restore < monsite_backup.sql

Restauration avec xtrabackup :

# Arreter MySQL
sudo systemctl stop mysql

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

# Copier la sauvegarde
sudo xtrabackup --copy-back --target-dir=/backup/full/

# Corriger les permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Redemarrer
sudo systemctl start mysql

Automatiser avec cron

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

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION=7

mkdir -p $BACKUP_DIR

# Sauvegarder
mysqldump -u backup_user -pMotDePasse \
  --single-transaction --quick \
  --all-databases | gzip > "$BACKUP_DIR/all_dbs_$DATE.sql.gz"

# Supprimer les sauvegardes anciennes
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION -delete

# Verifier la taille
ls -lh "$BACKUP_DIR/all_dbs_$DATE.sql.gz"

Ajoutez le cron :

chmod +x /opt/scripts/backup-mysql.sh
crontab -e
# Sauvegarde tous les jours a 3h du matin
0 3 * * * /opt/scripts/backup-mysql.sh >> /var/log/mysql-backup.log 2>&1

Verifier vos sauvegardes

Une sauvegarde non testee n'est pas une sauvegarde :

# Verifier l'integrite du fichier gz
gunzip -t monsite_backup.sql.gz

# Restaurer sur un serveur de test
mysql -u root -p test_restore < monsite_backup.sql
mysql -u root -p -e "SELECT COUNT(*) FROM test_restore.wp_posts;"

Testez vos restaurations regulierement. C'est la seule facon d'etre certain que vos sauvegardes fonctionnent.