Sauvegarder une base : mysqldump

Sauvegarder une base MySQL, c'est en produire une copie, le plus souvent avec mysqldump, que tu sais restaurer. Une base sans sauvegarde testée est une base que tu perdras un jour : panne de disque, erreur humaine (DELETE sans WHERE), rançongiciel. Ce chapitre donne les gestes d'exploitation, de la sauvegarde de la base parc à la supervision du serveur ; le cours Sauvegarde et PRA/PCA traite la stratégie (règle 3-2-1, objectifs de perte et d'interruption).

mysqldump

mysqldump produit une sauvegarde logique : un fichier texte d'instructions SQL (CREATE TABLE, INSERT) qui recréent la base. Il se lance depuis le terminal, pas depuis le client SQL, avec un compte dédié :

CREATE USER 'sauvegarde'@'localhost' IDENTIFIED BY 'Phrase-de-passe-de-sauvegarde-2026';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, LOCK TABLES, RELOAD, REPLICATION CLIENT
  ON *.* TO 'sauvegarde'@'localhost';

SELECT, SHOW VIEW, TRIGGER et EVENT permettent de lire les tables, les vues, les triggers et les évènements. Sans --single-transaction, mysqldump verrouille les tables en lecture pendant la copie (son option --lock-tables est active par défaut) : il lui faut LOCK TABLES, sinon il s'arrête sur l'erreur 1044. RELOAD et REPLICATION CLIENT servent à noter la position du journal binaire (option --source-data, voir Automatiser et planifier) ; PROCESS, à copier la description des espaces de tables. Aucun de ces droits ne permet de modifier les données.

# Une base complète, avec ses procédures, fonctions, triggers et évènements
mysqldump -u sauvegarde -p --single-transaction --routines --events parc > parc_2026-10-28.sql

# Plusieurs bases (le fichier contient alors les CREATE DATABASE et USE)
mysqldump -u sauvegarde -p --single-transaction --databases parc glpi > bases.sql

# Toutes les bases du serveur
mysqldump -u sauvegarde -p --single-transaction --all-databases --routines --events > serveur.sql

# Certaines tables seulement
mysqldump -u sauvegarde -p parc ticket intervention > tickets.sql

# La structure seule, ou les données seules
mysqldump -u sauvegarde -p --no-data parc > parc_structure.sql
mysqldump -u sauvegarde -p --no-create-info parc > parc_donnees.sql

# Compressé à la volée
mysqldump -u sauvegarde -p --single-transaction --routines --events parc | gzip > parc_2026-10-28.sql.gz

MySQL 8.4 a supprimé l'ancien outil mysqlpump. Pour de gros volumes, MySQL Shell propose util.dumpInstance() et util.loadDump(), plus rapides car parallélisés. Sur MariaDB, l'outil s'appelle mariadb-dump (le nom mysqldump reste disponible). Une sauvegarde physique (copie des fichiers de données, avec MySQL Enterprise Backup, Percona XtraBackup ou Mariabackup) est plus rapide à restaurer sur de très grosses bases.

Restaurer, et tester la restauration

# Restaurer dans une base existante (vide de préférence)
mysql -u admin_parc -p parc < parc_2026-10-28.sql

# Depuis une sauvegarde compressée
gunzip < parc_2026-10-28.sql.gz | mysql -u admin_parc -p parc

Dans le client SQL, SOURCE /chemin/parc_2026-10-28.sql; fait la même chose.

-- Après restauration dans parc_test : comparer les nombres de lignes
SELECT 'ticket' AS table_verifiee, COUNT(*) AS lignes FROM parc_test.ticket
UNION ALL SELECT 'intervention', COUNT(*) FROM parc_test.intervention
UNION ALL SELECT 'employe', COUNT(*) FROM parc_test.employe;

Automatiser et planifier

Un script lancé chaque nuit par cron (Linux) ou le Planificateur de tâches (Windows) sauvegarde la base, garde 14 jours d'historique et supprime les plus anciennes :

#!/usr/bin/env bash
set -euo pipefail
DOSSIER=/var/backups/mysql
DATE=$(date +%F_%H%M)

mysqldump --defaults-extra-file=/etc/mysql/sauvegarde.cnf \
  --single-transaction --source-data=2 --routines --events parc | gzip > "$DOSSIER/parc_$DATE.sql.gz"

# Rotation : supprimer les sauvegardes de plus de 14 jours
find "$DOSSIER" -name 'parc_*.sql.gz' -mtime +14 -delete

# crontab de l'utilisateur système qui sauvegarde : tous les jours à 2 h 30
30 2 * * * /usr/local/bin/sauvegarde_parc.sh

--source-data=2 écrit en commentaire, en tête du fichier, la position du journal binaire au moment exact de la sauvegarde : c'est de là que repartira une restauration à un instant donné, décrite dans la section suivante. Avec --single-transaction, le verrou nécessaire pour lire cette position ne dure qu'un instant.

Le mot de passe n'apparaît ni dans le script ni dans la ligne de commande (où ps le montrerait à tous les utilisateurs de la machine) : il est dans un fichier d'options lisible par le seul compte de sauvegarde (chmod 600).

# /etc/mysql/sauvegarde.cnf
[client]
user=sauvegarde
password=Phrase-de-passe-de-sauvegarde-2026

Copie ensuite les sauvegardes hors du serveur (un autre site, un stockage objet) : une sauvegarde sur le même disque disparaît avec lui, et un rançongiciel qui chiffre le serveur chiffre aussi ses sauvegardes locales.

Journal binaire et restauration à un instant donné

Le journal binaire (binary log) enregistre toutes les modifications de données validées. Il est actif par défaut depuis MySQL 8.0. Combiné à une sauvegarde complète, il permet une restauration à un instant donné (point-in-time recovery) : on restaure la sauvegarde de la nuit, puis on rejoue les modifications enregistrées depuis, jusqu'à la minute qui précède l'erreur.

# 1. Restaurer la sauvegarde de 2 h 30
gunzip < parc_2026-10-28_0230.sql.gz | mysql -u admin_parc -p parc

# 2. Lire dans son en-tête la position du journal binaire au moment de la sauvegarde
gunzip < parc_2026-10-28_0230.sql.gz | grep -m 1 'SOURCE_LOG_POS'
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000042', SOURCE_LOG_POS=1834;

# 3. Repérer la position du DELETE malheureux de 14 h 12 (chaque évènement est précédé de "# at <position>")
mysqlbinlog --base64-output=decode-rows --verbose --start-datetime="2026-10-28 14:10:00" binlog.000043 | less

# 4. Rejouer de la position de la sauvegarde jusqu'à celle qui précède le DELETE, sur les deux fichiers
mysqlbinlog --start-position=1834 --stop-position=917216 binlog.000042 binlog.000043 | mysql -u root -p

Repartir de la position notée dans la sauvegarde, et non d'une heure approximative, garantit qu'aucune transaction validée pendant la copie n'est rejouée deux fois ni oubliée. Une journée peut s'étendre sur plusieurs fichiers de journal : donne-les tous, dans l'ordre ; --start-position s'applique au premier, --stop-position au dernier. Le rejeu se fait avec root ou un compte qui a le privilège BINLOG_ADMIN : le journal contient des instructions internes que admin_parc, limité à la base parc, n'a pas le droit d'exécuter. La variable binlog_expire_logs_seconds fixe la durée de conservation du journal (30 jours par défaut). Sur MariaDB, le journal binaire est désactivé par défaut (log_bin dans la configuration).

Faire évoluer un schéma en production

Sur ta machine, tu modifies la base comme tu veux. En production, chaque modification de structure passe par un script de migration :

• un fichier par évolution, numéroté et versionné avec le code dans Git : V001__creation.sql, V002__ajout_fournisseur.sql, V003__index_ticket_ouverture.sql ;
• une migration déjà appliquée en production ne se modifie jamais : on en écrit une nouvelle ;
• une table de suivi, dans la base, enregistre les migrations déjà passées ;
• chaque migration est testée sur une copie de la base de production, après une sauvegarde.

Des outils automatisent ce suivi : Flyway et Liquibase (indépendants du langage), Doctrine Migrations (PHP, Symfony), les migrations de Laravel, Prisma Migrate (Node.js), Entity Framework Core Migrations (C#). Pense aussi à l'effet d'un ALTER TABLE sur une grosse table : MySQL 8 sait faire beaucoup de modifications sans bloquer les écritures (ALGORITHM=INPLACE ou INSTANT), mais pas toutes. Teste la durée avant.

Réplication et haute disponibilité

La réplication recopie automatiquement les modifications d'un serveur source vers un ou plusieurs serveurs réplicas, en relisant son journal binaire. Elle sert à répartir les lectures (les rapports interrogent le réplica), à avoir un serveur de secours prêt à prendre le relais, ou à sauvegarder depuis le réplica sans charger la source.

• Par défaut, la réplication MySQL est asynchrone : une transaction validée sur la source peut ne pas encore être arrivée sur le réplica au moment d'une panne.
• MySQL 8.4 a supprimé l'ancienne terminologie : on écrit CHANGE REPLICATION SOURCE TO, START REPLICA et SHOW REPLICA STATUS (plus CHANGE MASTER, START SLAVE).
• Pour un basculement automatique, MySQL propose Group Replication et InnoDB Cluster ; MariaDB s'appuie souvent sur Galera Cluster.

Ce sont des sujets d'administration système : ils intéressent directement l'option SISR, en lien avec les cours Linux, services et Sauvegarde et PRA/PCA.

Superviser un serveur de bases de données

SHOW GLOBAL STATUS LIKE 'Threads_connected';   -- connexions ouvertes
SHOW GLOBAL STATUS LIKE 'Slow_queries';        -- requêtes lentes depuis le démarrage
SHOW GLOBAL STATUS LIKE 'Uptime';              -- secondes depuis le démarrage
SHOW PROCESSLIST;                              -- ce que fait chaque connexion en ce moment
SHOW VARIABLES LIKE 'max_connections';         -- limite de connexions simultanées

SHOW PROCESSLIST révèle une requête bloquée depuis 600 secondes ; KILL 1234; interrompt la connexion qui porte cet identifiant. La base système performance_schema et les vues du schéma sys (sys.statement_analysis, sys.schema_unused_indexes) donnent des statistiques détaillées. Un outil de supervision comme Zabbix ou Prometheus, avec son exporteur MySQL, suit ces indicateurs dans le temps et alerte : disque presque plein, réplication en retard, connexions saturées (cours Supervision et monitoring).

Exercices

mysqldump -u sauvegarde -p --no-data --routines parc > parc_structure.sql
mysqldump -u sauvegarde -p --single-transaction --routines --events parc > parc_complet.sql
mysqldump -u sauvegarde -p --single-transaction parc ticket intervention > parc_tickets.sql

mysqldump -u root -pRoot2026 --all-databases > /root/sauvegarde.sql