Sauvegarder et restaurer MySQL : mysqldump, MySQL Shell et binlog PITR

Un DROP TABLE accidentel en production, une corruption disque après une coupure de courant, un ransomware qui chiffre vos données — sans stratégie de sauvegarde testée, ces scénarios détruisent votre activité. MySQL propose plusieurs méthodes complémentaires — du simple dump SQL à la restauration à la seconde près — selon le RPO (combien de données pouvez-vous perdre) et le RTO (combien de temps pouvez-vous rester hors ligne) que votre organisation accepte.

Ce guide couvre les trois outils de sauvegarde de l’écosystème MySQL, l’archivage des binary logs et la restauration Point-In-Time (PITR) qui permet de revenir à un instant précis avant une erreur humaine.

Version actuelle

Ce guide utilise MySQL 8.4 LTS (avril 2024). Changement important : mysqlpump est déprécié (depuis MySQL 8.0.34) — MySQL Shell (mysqlsh) est désormais l’outil recommandé pour les dumps logiques parallèles. Le binary log est activé par défaut.

Ce que vous allez apprendre

• Exécuter des sauvegardes logiques avec mysqldump (instance, base, tables)
• Utiliser MySQL Shell pour des dumps et restaurations parallèles bien plus rapides
• Comprendre le rôle de Percona XtraBackup pour les sauvegardes physiques à chaud
• Configurer l’archivage des binary logs pour conserver l’historique des modifications
• Restaurer à un point précis dans le temps (PITR) après un DROP TABLE accidentel
• Choisir la bonne stratégie selon la taille de votre base et vos exigences RPO/RTO

Dans quel contexte ?

La sauvegarde MySQL ne se met pas en place le jour où on en a besoin — c’est déjà trop tard. Vous êtes concerné si :

• Vous administrez une base MySQL en production et n’avez pas encore de stratégie de sauvegarde formalisée
• Vous devez garantir un RPO inférieur à quelques minutes (secteur financier, e-commerce, santé)
• Vous migrez depuis MariaDB et devez adapter vos scripts de sauvegarde (les outils diffèrent)
• Vous préparez un plan de reprise d’activité (PRA) et devez documenter les procédures de restauration
• Un incident récent (corruption, erreur humaine) a révélé que vos sauvegardes n’étaient pas testées

Ce guide ne couvre pas…

Réplication MySQL Source-replica, Group Replication et InnoDB Cluster pour la haute disponibilité

Monitoring et maintenance Performance Schema, slow queries, OPTIMIZE TABLE et alertes

Prérequis

• MySQL 8.4 installé et opérationnel — voir le guide d’installation
• Un utilisateur dédié aux sauvegardes — pour mysqldump : SELECT, SHOW VIEW, TRIGGER (toujours requis), plus LOCK TABLES (sans --single-transaction), RELOAD (avec --source-data ou GTID), PROCESS (sans --no-tablespaces). Pour MySQL Shell : SELECT, RELOAD, EVENT, SHOW VIEW, TRIGGER (BACKUP_ADMIN recommandé pour LOCK INSTANCE FOR BACKUP, mais pas strictement requis)
• Accès root ou sudo sur le serveur pour les opérations de restauration et l’installation de XtraBackup
• Avoir lu le guide de configuration MySQL (notamment la section sur le binary log)

Les deux familles de sauvegarde

Comme pour PostgreSQL, MySQL distingue deux grandes familles de sauvegarde. Chacune a ses cas d’usage.

Sauvegarde logique : export SQL ou CSV

La sauvegarde logique produit un fichier texte contenant les instructions SQL (CREATE TABLE, INSERT INTO) pour reconstruire la base. C’est comme photographier un livre page par page pour pouvoir le réimprimer.

Outils : mysqldump (mono-thread, inclus avec MySQL), MySQL Shell util.dumpInstance() (multi-thread, compression).

Avantages : portable entre versions, sélectif (une table, un schéma), lisible par un humain.

Limites : lent sur les grosses bases (sérialisation SQL), ne permet pas le PITR seul.

Sauvegarde physique : copie des fichiers InnoDB

La sauvegarde physique copie les fichiers de données bruts (tablespaces InnoDB, redo logs). C’est comme cloner le disque dur d’un serveur.

Outil : Percona XtraBackup (open source, sauvegarde à chaud sans verrouillage InnoDB).

MySQL Enterprise Backup

MySQL propose aussi MySQL Enterprise Backup (MEB) dans l’édition commerciale. XtraBackup est l’alternative open source la plus utilisée et celle que nous détaillons ici.

Avantages : rapide (copie binaire), sauvegarde à chaud (pas de verrouillage), base pour le PITR physique.

Limites : restauration du cluster entier (pas sélectif), pas portable entre versions majeures, outil externe à installer.

Comparatif rapide

Critère	Logique (mysqldump / mysqlsh)	Physique (XtraBackup)
Granularité	Table, schéma ou instance	Instance complète
PITR	Non (seul) — complément binlogs nécessaire	Oui, avec les binlogs
Portabilité	Entre versions, entre OS	Même version majeure
Vitesse sauvegarde	Lent (SQL sérialisé) — mysqlsh améliore avec le parallélisme	Rapide (copie fichiers)
Vitesse restauration	Lent (réexécute les INSERT) — mysqlsh améliore avec loadDump	Rapide (copie fichiers)
Impact serveur	Faible avec --single-transaction	Faible (copie à chaud)

Glissez pour voir

Règle pratique : commencez par mysqldump ou MySQL Shell. Passez à XtraBackup quand la volumétrie ou le RTO l’exige (typiquement au-delà de 50-100 Go).

Sauvegarde logique avec mysqldump

mysqldump est l’outil historique de MySQL. Il est inclus dans l’installation standard et fonctionne en mode mono-thread.

Dump complet de l’instance

Pour sauvegarder toutes les bases y compris les bases système (mysql, sys) :

mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events \
  > /tmp/full_instance.sql

-- MySQL dump 10.13  Distrib 8.4.5, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version    8.4.5

-- ...
-- Dumping data for table `clients`
-- ...

Le fichier produit est du SQL pur lisible — vous pouvez l’inspecter avec head ou grep.

Dump d’une base spécifique

Pour sauvegarder uniquement la base lab_mysql :

mysqldump -u root -p --single-transaction \
  --routines --triggers --events \
  lab_mysql > /tmp/lab_mysql.sql

Vérification rapide :

head -30 /tmp/lab_mysql.sql

-- MySQL dump 10.13  Distrib 8.4.5, for Linux (x86_64)
--
-- Host: localhost    Database: lab_mysql
-- ------------------------------------------------------
-- Server version    8.4.5

Dump de tables sélectionnées

Pour ne sauvegarder que certaines tables :

# Une seule table
mysqldump -u root -p --single-transaction lab_mysql clients > /tmp/clients_only.sql

# Plusieurs tables
mysqldump -u root -p --single-transaction lab_mysql clients commandes > /tmp/clients_commandes.sql

# Tout sauf certaines tables
mysqldump -u root -p --single-transaction \
  --ignore-table=lab_mysql.logs_acces \
  lab_mysql > /tmp/lab_sans_logs.sql

Options essentielles : —single-transaction, —routines, —triggers

Option	Rôle	Indispensable ?
--single-transaction	Démarre une transaction REPEATABLE READ pour un dump cohérent sans verrouiller les tables (InnoDB uniquement)	Oui — sans cette option, mysqldump pose des LOCK TABLES qui bloquent les écritures
--routines	Inclut les procédures stockées et les fonctions	Oui, souvent oublié
--triggers	Inclut les triggers (activé par défaut depuis MySQL 5.x)	Oui (par défaut)
--events	Inclut le planificateur d’événements (cron MySQL)	Oui si vous utilisez EVENT SCHEDULER
--set-gtid-purged	Contrôle l’inclusion des instructions GTID dans le dump — AUTO (défaut) : ajoute SET @@GLOBAL.gtid_purged si GTID activé ; ON : force (erreur si GTID désactivé) ; OFF : omet les instructions GTID ; COMMENTED : ajoute en commentaire SQL	Laisser AUTO par défaut — utiliser OFF uniquement pour une restauration sur serveur isolé sans réplication
--result-file	Écrit dans un fichier au lieu de stdout (évite les problèmes d’encodage sur Windows)	Optionnel

Glissez pour voir

--single-transaction et tables MyISAM

--single-transaction ne fonctionne correctement qu’avec InnoDB. Si votre base contient encore des tables MyISAM (legacy), mysqldump posera quand même un verrou global sur ces tables. En MySQL 8.4, le moteur par défaut est InnoDB — vérifiez avec SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'votre_base' AND ENGINE != 'InnoDB';.

Limites de mysqldump sur les grosses bases

mysqldump est mono-thread : il exporte les tables une par une, séquentiellement. Sur une base de 100 Go :

• La sauvegarde peut prendre plusieurs heures
• La restauration (mysql < dump.sql) est encore plus lente car elle réexécute chaque INSERT un par un
• Le fichier SQL produit est volumineux (pas de compression native)

Quand passer à MySQL Shell : dès que vos dumps prennent plus de 30 minutes ou que la base dépasse quelques dizaines de Go.

Sauvegarde avec MySQL Shell (mysqlsh)

MySQL Shell (mysqlsh) est le client nouvelle génération de MySQL. Ses utilitaires de dump et de chargement sont multi-threads et produisent des fichiers compressés — c’est le successeur recommandé de mysqlpump (déprécié depuis MySQL 8.0.34).

util.dumpInstance() et util.dumpSchemas()

Connectez-vous à MySQL Shell puis lancez le dump :

mysqlsh root@localhost -- util dump-instance /tmp/full_dump

Ou en mode interactif :

// En mode JavaScript dans mysqlsh
util.dumpInstance("/tmp/full_dump", {
  threads: 4,
  compression: "zstd"
})

Acquiring global read lock
Global read lock acquired
Initializing - done
4 out of 6 schemas will be dumped and within them 12 tables, 0 views.
2 out of 4 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values...
120% (856 rows / ~712 rows), 856 rows/s, 52.34 KB/s
Duration: 00:00:01s
Schemas dumped: 4
Tables dumped: 12
Data size: 52.34 KB
Rows written: 856
Bytes written: 18.72 KB
Average throughput: 52.34 KB/s

Pour une seule base :

util.dumpSchemas(["lab_mysql"], "/tmp/lab_dump", {
  threads: 4,
  compression: "zstd"
})

Le résultat est un répertoire contenant un fichier par table (DDL + données séparés) :

ls /tmp/full_dump/

lab_mysql@clients@@0.tsv.zst
lab_mysql@clients@@0.tsv.zst.idx
lab_mysql@clients.json
lab_mysql@clients.sql
lab_mysql@commandes@@0.tsv.zst
lab_mysql@commandes.json
lab_mysql@commandes.sql
@.done.json
@.json
@.post.sql
@.sql
@.users.sql

Chaque table a son fichier DDL (.sql) et ses données (.tsv.zst compressé zstd). Cette séparation permet la restauration parallèle et sélective.

Parallélisme et compression

Option	Valeur par défaut	Recommandation
threads	4	Nombre de cœurs CPU disponibles (ne pas dépasser)
compression	zstd	Laisser zstd — meilleur ratio compression/vitesse
chunking	true	Découpe les grosses tables en chunks pour paralléliser leur export
bytesPerChunk	256M	Taille de chaque chunk — diminuer si la RAM est limitée

Glissez pour voir

La combinaison parallélisme + compression rend MySQL Shell 5 à 10 fois plus rapide que mysqldump sur les bases volumineuses.

util.loadDump() : restauration parallèle

La restauration est aussi parallèle :

util.loadDump("/tmp/full_dump", {
  threads: 4,
  resetProgress: true,
  ignoreExistingObjects: false
})

Loading DDL and Data from '/tmp/full_dump' using 4 threads.
Opening dump...
Target is MySQL 8.4.5. Dump was produced from MySQL 8.4.5
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Loading data using 4 threads...
120% (856 rows / ~712 rows), 0.00 rows/s, 0.00 B/s
Recreating indexes - done
12 chunks (856 rows, 52.34 KB) for 12 tables in 4 schemas were loaded in 2 sec
0 warnings were reported during the load.

loadDump : reprise sur erreur

util.loadDump() enregistre sa progression dans un fichier load-progress*.json. Si la restauration est interrompue, relancez la même commande — elle reprend là où elle s’est arrêtée (sans resetProgress: true).

mysqlpump est déprécié — utiliser mysqlsh

mysqlpump a été introduit en MySQL 5.7 comme alternative parallèle à mysqldump, mais il souffrait de limitations (pas de cohérence transactionnelle fiable, options incohérentes). MySQL 8.0.34 l’a déprécié (il sera retiré dans une version future). Si vos scripts utilisent encore mysqlpump, migrez vers MySQL Shell :

Ancien (mysqlpump)	Nouveau (mysqlsh)
mysqlpump --all-databases	util.dumpInstance("/path/dump")
mysqlpump --databases db1 db2	util.dumpSchemas(["db1","db2"], "/path/dump")
mysqlpump --default-parallelism=4	util.dumpInstance("/path/dump", {threads: 4})

Glissez pour voir

Sauvegarde physique avec Percona XtraBackup

Principe : copie à chaud des fichiers InnoDB

Percona XtraBackup copie les fichiers de données InnoDB (tablespaces) pendant que le serveur tourne — sans verrouillage des tables (sauf un bref FTWRL pour les métadonnées non-InnoDB). C’est l’équivalent MySQL de pg_basebackup pour PostgreSQL.

Le processus en trois étapes :

1. Backup : copie les fichiers InnoDB + capture le redo log en continu
2. Prepare : applique le redo log pour rendre le backup cohérent (comme un crash recovery)
3. Restore : copie le backup préparé à la place du data directory

Percona XtraBackup 8.4

Utilisez Percona XtraBackup 8.4 pour MySQL 8.4 — les versions de XtraBackup doivent correspondre à la version majeure de MySQL. XtraBackup 8.0 ne fonctionne pas avec MySQL 8.4.

Installation de Percona XtraBackup

Debian / Ubuntu

# Ajouter le dépôt Percona
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release setup pxb-84
sudo apt update
sudo apt install percona-xtrabackup-84

Rocky / RHEL

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup pxb-84
sudo yum install percona-xtrabackup-84

Vérification :

xtrabackup --version

xtrabackup version 8.4.0-2 based on MySQL server 8.4.5

Sauvegarde complète

sudo xtrabackup --backup \
  --user=root --password='VotreMotDePasse' \
  --target-dir=/var/backups/mysql/full_$(date +%Y%m%d)

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: Starting backup threads (using 1 thread)...
xtrabackup: Redo log capacity 104857600 bytes
...
xtrabackup: Transaction log of lsn (38948302) to (38948312) was copied.
xtrabackup: completed OK!

Le message completed OK! confirme la réussite. Le répertoire cible contient une copie des fichiers InnoDB + les redo logs capturés pendant le backup.

Sauvegarde incrémentale

Après une sauvegarde complète, vous pouvez ne sauvegarder que les pages modifiées depuis :

# Sauvegarde incrémentale basée sur la full
sudo xtrabackup --backup \
  --user=root --password='VotreMotDePasse' \
  --target-dir=/var/backups/mysql/incr_$(date +%Y%m%d) \
  --incremental-basedir=/var/backups/mysql/full_20260413

La sauvegarde incrémentale ne contient que les pages InnoDB modifiées depuis le full_20260413 — le volume est bien plus faible qu’une sauvegarde complète.

Préparation et restauration

La restauration suit trois étapes :

1. Préparer la sauvegarde complète (appliquer le redo log) :

   sudo xtrabackup --prepare --target-dir=/var/backups/mysql/full_20260413

   InnoDB: Starting crash recovery.
   ...
   xtrabackup: completed OK!

2. Si incrémentale — appliquer chaque incrémental sur la full :

   sudo xtrabackup --prepare \
     --target-dir=/var/backups/mysql/full_20260413 \
     --incremental-dir=/var/backups/mysql/incr_20260414

3. Arrêter MySQL :

   sudo systemctl stop mysql

4. Remplacer le data directory :

   sudo mv /var/lib/mysql /var/lib/mysql_damaged
   sudo xtrabackup --copy-back --target-dir=/var/backups/mysql/full_20260413
   sudo chown -R mysql:mysql /var/lib/mysql

5. Redémarrer MySQL :

   sudo systemctl start mysql

Permissions après restauration

Après --copy-back, les fichiers appartiennent à root. N’oubliez pas le chown -R mysql:mysql /var/lib/mysql — sinon MySQL ne démarrera pas avec une erreur de permission sur les tablespaces.

Archivage des binary logs

Activer le binary log (activé par défaut en 8.4)

En MySQL 8.4, le binary log est activé par défaut (log_bin = ON). Vérifiez :

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'log_bin_basename';
SHOW BINARY LOGS;

+-----------+-------+
| log_bin   | ON    |
+-----------+-------+

+------------------+---------------------------+
| log_bin_basename | /var/lib/mysql/binlog      |
+------------------+---------------------------+

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       180 | No        |
| binlog.000002 |      4521 | No        |
| binlog.000003 |       180 | No        |
+---------------+-----------+-----------+

Les fichiers binlog sont dans /var/lib/mysql/ par défaut. Chaque fichier contient les événements de modifications (INSERT, UPDATE, DELETE, DDL).

Configuration importante dans /etc/mysql/mysql.conf.d/mysqld.cnf :

[mysqld]
# Rétention des binlogs (30 jours par défaut en 8.4)
binlog_expire_logs_seconds = 2592000

# Format ROW pour la cohérence (défaut en 8.4)
binlog_format = ROW

# Durabilité : flush à chaque commit
sync_binlog = 1

Différence avec PostgreSQL

Les binary logs de MySQL combinent le rôle des WAL (Write-Ahead Log) de PostgreSQL pour la réplication et le PITR, mais sont un mécanisme distinct du redo log InnoDB (qui sert au crash recovery interne). PostgreSQL n’a qu’un seul mécanisme (les WAL) pour les deux.

mysqlbinlog : lire et filtrer les événements

mysqlbinlog est l’outil en ligne de commande pour lire les binary logs :

# Lire un binlog complet
mysqlbinlog /var/lib/mysql/binlog.000002

# at 4
#260413 10:15:00 server id 1  end_log_pos 126 ...
# at 126
#260413 10:15:00 server id 1  end_log_pos 197 ...
### INSERT INTO `lab_mysql`.`clients`
### SET
###   @1=1
###   @2='Alice Martin'
###   @3='alice@example.com'
###   @4='Paris'

Pour filtrer par base et par période :

# Événements d'une base entre deux instants
mysqlbinlog /var/lib/mysql/binlog.000002 \
  --database=lab_mysql \
  --start-datetime="2026-04-13 10:00:00" \
  --stop-datetime="2026-04-13 10:20:00" \
  --verbose

Options utiles :

Option	Rôle
--database	Filtrer par base (attention : ne marche pas pour toutes les requêtes cross-db)
--start-datetime / --stop-datetime	Filtrer par date
--start-position / --stop-position	Filtrer par position dans le binlog
--verbose (-v)	Décoder les événements ROW en pseudo-SQL lisible
--base64-output=DECODE-ROWS	Afficher les données sans le base64 brut

Glissez pour voir

Copier les binlogs vers un stockage externe

Les binlogs résident sur le même disque que les données. En production, externalisez-les :

# Copie manuelle vers un stockage externe
cp /var/lib/mysql/binlog.* /mnt/backup/mysql-binlogs/

# Ou via rsync vers un serveur distant
rsync -avz /var/lib/mysql/binlog.* backup-server:/backups/mysql-binlogs/

Ne copiez pas les binlogs pendant une écriture active

Utilisez FLUSH BINARY LOGS; avant de copier pour forcer la rotation — le binlog en cours d’écriture n’est pas garanti cohérent tant que MySQL écrit dedans. Copiez ensuite les fichiers précédents (pas le dernier actif).

Pour automatiser l’archivage en continu, utilisez mysqlbinlog en mode streaming :

# Streaming continu des binlogs vers un fichier distant
mysqlbinlog --read-from-remote-server --host=localhost \
  --user=repl_user --password='xxx' \
  --raw --stop-never \
  binlog.000001 \
  --result-file=/mnt/backup/mysql-binlogs/

Cette commande suit les binlogs en temps réel — comme un tail -f pour les binary logs.

Restauration Point-In-Time (PITR)

Le PITR est la capacité de restaurer votre base à un instant précis dans le passé. C’est le filet de sécurité ultime contre les erreurs humaines — un DROP TABLE à 14h32 peut être annulé en restaurant l’état à 14h31.

Le principe : restaurer un dump + rejouer les binlogs

Le PITR MySQL combine deux éléments :

1. Un dump de référence (mysqldump ou MySQL Shell) — le point de départ
2. Les binary logs entre le dump et l’instant cible — les modifications à rejouer

Le processus :

[Dump à 02h00] ──── binlogs ──── [DROP TABLE à 14h32] ──── binlogs ── [maintenant]
                         │                    │
                   On rejoue           On s'arrête
                   ces binlogs         AVANT cette position

Démonstration : simuler un DROP TABLE et restaurer

La base lab_mysql contient des clients et des commandes. Nous allons simuler un accident puis restaurer.

1. Créer un dump de référence :

   mysqldump -u root -p --single-transaction \
     --routines --triggers --events \
     --flush-logs --source-data=2 \
     lab_mysql > /tmp/lab_mysql_backup.sql

   L’option --flush-logs force une rotation du binlog. L’option --source-data=2 inscrit la position binlog dans un commentaire du dump (--master-data est un alias déprécié) :

   head -30 /tmp/lab_mysql_backup.sql | grep "CHANGE"

   -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=157;

   Le dump est cohérent à la position binlog.000004:157.

2. Insérer une ligne (qui doit survivre à la restauration) :

   INSERT INTO clients (nom, email, ville)
   VALUES ('PITR Test', 'pitr@example.com', 'Nantes');
   
   SELECT COUNT(*) FROM clients;

   +----------+
   | COUNT(*) |
   +----------+
   |        6 |
   +----------+

3. Noter l’instant de référence (le “safe point”) :

   SELECT NOW() AS safe_timestamp;

   +---------------------+
   | safe_timestamp      |
   +---------------------+
   | 2026-04-13 10:19:49 |
   +---------------------+

4. Simuler la catastrophe (quelques secondes après) :

   DROP TABLE commandes;
   DROP TABLE clients;

   Les tables ont disparu.

5. Forcer la rotation du binlog :

   FLUSH BINARY LOGS;
   SHOW BINARY LOGS;

   +---------------+-----------+
   | Log_name      | File_size |
   +---------------+-----------+
   | binlog.000004 |     12847 |
   | binlog.000005 |       180 |
   +---------------+-----------+

   Le DROP TABLE est dans binlog.000004. Les événements après la rotation sont dans binlog.000005.

6. Restaurer le dump de référence :

   mysql -u root -p lab_mysql < /tmp/lab_mysql_backup.sql

   La base est revenue à l’état du dump (5 clients, sans “PITR Test”).

7. Rejouer les binlogs jusqu’au safe point :

   mysqlbinlog /var/lib/mysql/binlog.000004 \
     --start-position=157 \
     --stop-datetime="2026-04-13 10:19:49" \
     --database=lab_mysql \
     | mysql -u root -p lab_mysql

   Cette commande lit le binlog depuis la position du dump (157) et s’arrête avant le DROP TABLE.

8. Vérifier la restauration :

   SELECT nom, email FROM clients ORDER BY id;

   +---------------+--------------------+
   | nom           | email              |
   +---------------+--------------------+
   | Alice Martin  | alice@example.com  |
   | Bob Dupont    | bob@example.com    |
   | Claire Durand | claire@example.com |
   | David Moreau  | david@example.com  |
   | Eva Bernard   | eva@example.com    |
   | PITR Test     | pitr@example.com   |
   +---------------+--------------------+

   6 clients : les 5 d’origine + la ligne insérée avant le DROP. Le PITR a fonctionné.

Identifier le point de restauration (position ou GTID)

Deux méthodes pour cibler le point de restauration :

Par date/heure (le plus courant) :

mysqlbinlog binlog.000004 --stop-datetime="2026-04-13 10:19:49"

Par position (plus précis — utile quand plusieurs transactions ont la même seconde) :

# Chercher la position du DROP TABLE
mysqlbinlog binlog.000004 --verbose | grep -B5 "DROP TABLE"

# at 12534
#260413 10:20:03 server id 1  end_log_pos 12639
# at 12639
### DROP TABLE `lab_mysql`.`commandes`

# Rejouer jusqu'à la position AVANT le DROP
mysqlbinlog binlog.000004 --start-position=157 --stop-position=12534 \
  | mysql -u root -p lab_mysql

Par GTID (si activé — recommandé pour les environnements répliqués) :

mysqlbinlog binlog.000004 \
  --exclude-gtids="7c1b9e1a-...:15" \
  | mysql -u root -p lab_mysql

Vérifier la cohérence après restauration

Après un PITR, vérifiez l’intégrité des données :

-- Vérifier les contraintes de clés étrangères
SET FOREIGN_KEY_CHECKS = 1;

-- Compter les lignes dans les tables critiques
SELECT
  (SELECT COUNT(*) FROM clients) AS nb_clients,
  (SELECT COUNT(*) FROM commandes) AS nb_commandes;

-- Vérifier les tables pour des erreurs internes
CHECK TABLE clients, commandes;

+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| lab_mysql.clients      | check | status   | OK       |
| lab_mysql.commandes    | check | status   | OK       |
+------------------------+-------+----------+----------+

Stratégie de sauvegarde recommandée

Petite base (< 10 Go) : mysqldump + binlogs

• mysqldump quotidien via cron (--single-transaction --routines --triggers --events --flush-logs --source-data=2)
• Binlogs archivés en continu vers un stockage externe
• Test de restauration hebdomadaire automatisé
• Rétention : 7 dumps glissants + 1 mensuel
• RPO : quelques minutes (binlogs archivés)
• RTO : quelques minutes (dump petit + replay binlogs rapide)

Base moyenne (10-500 Go) : mysqlsh dump + binlogs

• MySQL Shell util.dumpInstance() quotidien (parallèle + compression zstd)
• Binlogs archivés en continu
• Un dump complet hebdomadaire + dumps incrémentaux quotidiens si le volume change beaucoup
• Test de PITR mensuel
• RPO : quelques minutes
• RTO : 15-60 minutes selon le volume

Grosse base (> 500 Go) : XtraBackup + binlogs

• Percona XtraBackup complète hebdomadaire + incrémentale quotidienne
• Binlogs archivés en continu (streaming avec mysqlbinlog --read-from-remote-server)
• MySQL Shell pour les dumps sélectifs de tables critiques
• Test de restauration automatisé dans la CI
• RPO : quelques secondes (binlogs streamés)
• RTO : 30 minutes à quelques heures selon le volume

Tester ses sauvegardes

Une sauvegarde non testée n’est pas une sauvegarde. La vérification doit être automatisée et régulière.

Trois niveaux de vérification :

Niveau	Méthode	Ce que ça prouve
1. Intégrité fichier	Vérifier que le dump n’est pas vide ou tronqué	Le fichier a été produit sans erreur
2. Restauration	Restaurer dans une base jetable	Le dump est lisible et complet
3. Cohérence	Requêtes de contrôle sur la base restaurée	Les données sont exploitables

Glissez pour voir

Exemple de script de test automatisé :

#!/bin/bash
set -euo pipefail

DUMP="/var/backups/mysql/lab_mysql_$(date +%Y%m%d).sql"
TEST_DB="verify_$(date +%Y%m%d)"

# Dump
mysqldump -u root -p"${MYSQL_ROOT_PASSWORD}" \
  --single-transaction --routines --triggers --events \
  lab_mysql > "$DUMP"

# Vérifier que le fichier n'est pas vide
if [ ! -s "$DUMP" ]; then
  echo "ERREUR: dump vide" >&2; exit 1
fi

# Restaurer dans une base temporaire
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -e "CREATE DATABASE $TEST_DB;"
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" "$TEST_DB" < "$DUMP"

# Vérifier le contenu
COUNT=$(mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -N -e \
  "SELECT COUNT(*) FROM $TEST_DB.clients;")

if [ "$COUNT" -gt 0 ]; then
  echo "OK: $COUNT clients restaurés"
else
  echo "ERREUR: base vide après restauration" >&2
  exit 1
fi

# Nettoyage
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -e "DROP DATABASE $TEST_DB;"

Dépannage

Symptôme	Cause probable	Solution
mysqldump: Got error: 1044: Access denied	Utilisateur sans privilège LOCK TABLES ou SELECT	Accorder les privilèges : GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
ERROR 1045 (28000): LOCK TABLES en plein dump	Tables MyISAM qui bloquent le verrou global	Utiliser --single-transaction (InnoDB uniquement) ou convertir les tables en InnoDB
Dump très lent (> 1h pour < 50 Go)	mysqldump est mono-thread	Passer à MySQL Shell util.dumpInstance() avec threads: 4
ERROR: Unknown table 'COLUMN_STATISTICS' lors de la restauration sur un ancien MySQL	Option --column-statistics activée par défaut dans mysqldump 8.x	Ajouter --column-statistics=0 au dump
mysqlbinlog: ERROR: Could not find GTID state	Binlogs purgés avant l’archivage	Vérifier binlog_expire_logs_seconds — augmenter la rétention
Got a packet bigger than 'max_allowed_packet'	Ligne ou BLOB trop volumineux pour la taille du paquet	Ajouter --max-allowed-packet=512M au dump et à la restauration
XtraBackup : xtrabackup: error: log block numbers mismatch	Version de XtraBackup incompatible avec MySQL	XtraBackup 8.4 est obligatoire pour MySQL 8.4
Restauration PITR : données manquantes	--stop-datetime trop tôt ou binlog manquant	Vérifier le timestamp exact dans le binlog avec mysqlbinlog --verbose

Glissez pour voir

À retenir

• Deux familles : mysqldump/mysqlsh (logique, portable, sélectif) et XtraBackup (physique, rapide, PITR physique).
• --single-transaction est indispensable avec mysqldump sur InnoDB — sans cette option, les écritures sont bloquées.
• MySQL Shell remplace mysqlpump (déprécié depuis 8.0.34) — ses dumps parallèles sont 5 à 10 fois plus rapides que mysqldump.
• XtraBackup est l’outil de référence pour les sauvegardes physiques à chaud — la version doit correspondre à la version majeure de MySQL.
• Le binary log est activé par défaut en MySQL 8.4 — surveillez la rétention (binlog_expire_logs_seconds) pour éviter que le disque se remplisse.
• Le PITR combine un dump de référence + le rejeu des binlogs jusqu’à un instant précis — c’est le filet de sécurité contre les DROP TABLE accidentels.
• --flush-logs --source-data=2 dans mysqldump inscrit la position binlog exacte dans le dump — indispensable pour le PITR (--master-data est un alias déprécié).
• Testez vos restaurations régulièrement — un backup non testé est un faux sentiment de sécurité.
• Pour les bases > 500 Go, combinez XtraBackup (sauvegardes incrémentales) + archivage binlog continu pour un RPO quasi nul.

Prochaines étapes

Réplication MySQL Source-replica, Group Replication et InnoDB Cluster — la haute disponibilité en complément du backup

Monitoring et maintenance Performance Schema, slow queries, OPTIMIZE TABLE et alertes — surveiller ce que vous sauvegardez

Sécuriser MySQL Authentification, rôles, TLS et audit — protéger les accès avant et après restauration

Configurer MySQL Binary log, InnoDB buffer pool et les paramètres qui impactent les sauvegardes

×

📖 Voir la documentation →