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.

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…

Deux sujets voisins reviennent souvent dans la même conversation et méritent leur propre page. La réplication protège de la panne matérielle mais recopie fidèlement un DROP TABLE, elle ne remplace donc jamais une sauvegarde. Le monitoring sert à repérer la dérive avant l'incident, par exemple un disque qui se remplit de binary logs non purgés.

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) qui reconstruisent la base quand on les rejoue. Rien n'est copié au niveau disque : le serveur relit les tables et regénère du SQL, ce qui explique à la fois la portabilité du résultat et sa lenteur.

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) tels qu'ils existent dans le data directory. Aucune requête SQL n'est produite, ce qui rend l'opération rapide mais impose de restaurer sur un serveur de même version majeure de MySQL.

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

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)

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

Le dump partiel sert surtout à extraire une table de référence pour la copier vers un environnement de test, ou à écarter une table de logs qui pèse plus lourd que le reste de la base. Le piège est silencieux : mysqldump désactive FOREIGN_KEY_CHECKS dans l'en-tête du fichier, donc restaurer une table seule réussit sans erreur même si la table référencée par sa clé étrangère est absente. La contrainte reste déclarée et orpheline, et c'est le premier INSERT qui échoue avec ERROR 1452. Exportez toujours les tables liées ensemble, ou retirez la contrainte sur la copie.

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

La colonne à lire en priorité est la troisième : elle distingue les options actives par défaut de celles que vous devez ajouter vous-même. Sur MySQL 8.4, mysqldump --help affiche routines FALSE et events FALSE dans le récapitulatif des valeurs effectives, alors que triggers vaut TRUE. Autrement dit, un dump lancé sans options recopie bien les triggers mais oublie silencieusement les procédures stockées, les fonctions et les événements planifiés. La perte ne se voit qu'au moment de la restauration, quand une application appelle une procédure qui n'existe plus.

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

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

Les quatre options ci-dessous se règlent ensemble : threads fixe le nombre de flux d'export simultanés, et chunking détermine s'il y a assez de travail à distribuer entre eux. Sans découpage en chunks, une table de 200 Go monopolise un seul thread pendant que les autres attendent, et le parallélisme n'apporte rien. Surveillez la charge disque du serveur pendant les premiers essais : monter threads au-delà du nombre de cœurs sature les entrées-sorties sans gain de durée.

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

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 profite du même parallélisme que le dump : chaque chunk est chargé par un thread distinct, là où mysql < dump.sql rejoue un flux SQL unique. Deux paramètres décident du comportement en cas de reprise. resetProgress: true efface le journal d'avancement et recharge tout depuis le début, à réserver à un chargement initial dans une base vide. ignoreExistingObjects: false fait échouer le chargement si une table cible existe déjà, ce qui évite d'écraser par accident une base encore en service.

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.

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})`

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 :

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

Installation de Percona XtraBackup

XtraBackup ne vit pas dans les dépôts de la distribution : il faut d'abord installer le paquet percona-release, qui déclare les dépôts Percona signés, puis activer explicitement le canal pxb-84. C'est cette dernière commande qui décide de la version majeure installée, et l'oublier fait remonter une XtraBackup 8.0 incompatible avec MySQL 8.4. Les deux onglets ci-dessous couvrent les familles Debian et RHEL.

Debian / Ubuntu
Rocky / RHEL

# 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

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

Le --target-dir doit pointer vers un répertoire vide ou inexistant, XtraBackup refuse d'écrire dans un répertoire déjà peuplé. Le mot de passe passé en clair sur la ligne de commande apparaît dans l'historique du shell et dans ps, en production préférez un fichier d'options avec des permissions 0600 ou la commande mysql_config_editor. La sauvegarde consomme des entrées-sorties disque proportionnelles à la taille du data directory, planifiez-la hors des pics de trafic.

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 :

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!

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

Arrêter MySQL :
Fenêtre de terminal
```
sudo systemctl stop mysql
```

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

Redémarrer MySQL :
Fenêtre de terminal
```
sudo systemctl start mysql
```

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

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

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/

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/

Grâce à --stop-never, le processus reste connecté au serveur et continue d'écrire les nouveaux événements dès qu'ils arrivent, au lieu de s'arrêter à la fin du fichier courant. Lancez-le sous un service systemd avec redémarrage automatique : s'il meurt sans que personne le remarque, le RPO repart à la durée écoulée depuis la dernière sauvegarde complète.

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 :

Un dump de référence (mysqldump ou MySQL Shell), le point de départ
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.

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.

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 |
+----------+

Noter l'instant de référence (le "safe point") :

SELECT NOW() AS safe_timestamp;

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

Simuler la catastrophe (quelques secondes après) :
```
DROP TABLE commandes;
DROP TABLE clients;
```
Les tables ont disparu.

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.

Restaurer le dump de référence :
Fenêtre de terminal
```
mysql -u root -p lab_mysql < /tmp/lab_mysql_backup.sql
```
La base est revenue à l'état du dump (5 clients, sans "PITR Test").

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.

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

Un PITR peut réussir techniquement et laisser la base dans un état fonctionnellement incohérent : commandes sans client, compteurs applicatifs désynchronisés, séquences d'AUTO_INCREMENT repositionnées. Les trois requêtes ci-dessous couvrent le niveau structurel, à compléter par des contrôles métier propres à votre application. CHECK TABLE lit réellement les pages InnoDB, ce n'est pas une simple lecture de métadonnées, comptez du temps sur les grosses tables.

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

À cette volumétrie, le dump complet tient en quelques minutes et l'outil historique suffit. L'effort porte ailleurs : sur l'archivage des binlogs, qui fait passer le RPO de 24 heures à quelques minutes pour un coût de stockage négligeable, et sur le test de restauration hebdomadaire, qui est le seul moyen de savoir que le fichier produit chaque nuit est exploitable.

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

C'est la tranche où mysqldump commence à déborder de la fenêtre de nuit et où le parallélisme de MySQL Shell change vraiment la donne. Le point de bascule ne se décide pas à la taille affichée mais à la durée mesurée : chronométrez un dump réel, et si la restauration dépasse le RTO annoncé au métier, il faut changer d'outil, pas ajouter des threads.

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

Au-delà de quelques centaines de gigaoctets, une restauration logique se compte en heures parce qu'elle réexécute chaque INSERT et reconstruit les index ligne à ligne. La sauvegarde physique remplace ce travail par une copie de fichiers, d'où le gain sur le RTO. En contrepartie, vous héritez d'une contrainte d'exploitation : la chaîne full plus incrémentales doit être complète et préparée dans l'ordre, une incrémentale manquante rend inutilisables toutes celles qui la suivent.

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

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

La plupart des incidents de sauvegarde MySQL se rangent dans trois familles : un privilège manquant sur le compte de sauvegarde, une incompatibilité de version entre l'outil et le serveur, et une limite de taille atteinte (paquet réseau, rétention de binlogs). Lisez d'abord la colonne du milieu : le message d'erreur brut est souvent trompeur, alors que la cause réelle se corrige en une commande.

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`

À 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