Cours MariaDB / Sauvegarder une base avec mariadb-dump et vérifier le fichier

mariadb-dump crée une sauvegarde logique : un fichier SQL capable de recréer des définitions et des lignes. Choisissez un nouveau chemin de sortie et les options de cohérence adaptées, contrôlez les erreurs puis vérifiez une restauration dans une base distincte ; l’existence d’un fichier ne prouve pas qu’une sauvegarde est utilisable.

Définir le périmètre et les objectifs de restauration

Cet exercice sauvegarde quatre tables InnoDB dans la base indépendante backup46_lab. InnoDB est un moteur de stockage qui prend en charge les transactions. Le fichier obtenu n’est pas une sauvegarde complète des comptes du serveur, de sa configuration ou de fichiers externes.

La quantité de données que vous pouvez perdre et le temps disponible pour rétablir le service déterminent la fréquence, l’emplacement et les compléments nécessaires à la sauvegarde. Un volume de conteneur conserve des fichiers selon sa configuration, mais ne remplace pas une sauvegarde séparée et vérifiée.

Domaine Couvert ici À examiner en complément
Données Quatre tables InnoDB Autres moteurs, volume et modifications de structure concurrentes
Objets Tables et clés Vues, comptes définisseurs, routines, déclencheurs et événements
Comptes et configuration Hors du périmètre de ce dump de base Procédures de récupération séparées
Vérification Restauration dans une autre base vide Lignes, contraintes, totaux métier et accès

Étapes verticales : base source, fichier SQL, restauration dans une autre base vide, puis comparaison du schéma, des lignes, des contraintes et des totaux métier

La référence officielle de mariadb-dump décrit les options, leurs conditions et les questions de compatibilité. Relevez les versions du serveur, du programme de sauvegarde et du client prévu pour la restauration ; ne déduisez pas leur compatibilité du seul nom d’un exécutable.

Créer une base source indépendante

Préparez un serveur MariaDB d’apprentissage et un compte course_user déjà créé pour les connexions TCP locales. Dans une connexion d’administrateur, utilisez SHOW DATABASES; pour vérifier que backup46_lab est disponible. Si la base existe, arrêtez-vous et choisissez un autre nom dans l’ensemble de l’exercice ; ne supprimez pas des données existantes pour faire fonctionner l’exemple.

Enregistrez le bloc complet suivant dans un fichier UTF-8, par exemple C:/mariadb-lab/c46-preparation.sql. Dans le client MariaDB de l’administrateur, lancez SOURCE C:/mariadb-lab/c46-preparation.sql; avec le chemin réel. Le bloc contient le schéma, les clés et toutes les lignes :

CREATE DATABASE backup46_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE backup46_lab;
CREATE TABLE c46_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c46_order_customer FOREIGN KEY (customer_id)
  REFERENCES c46_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c46_item_order FOREIGN KEY (order_id)
  REFERENCES c46_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c46_item_product FOREIGN KEY (product_id)
  REFERENCES c46_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c46_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c46_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c46_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c46_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Le jeu de données conserve les noms et les villes en coréen pour reproduire les mêmes valeurs UTF-8. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe. Les clients 1 et 3 ont la ville Séoul ; les clients 2 et 4 ont Busan et Daejeon. Utilisez les identifiants dans les comparaisons et préservez les littéraux fournis.

Vous devez obtenir quatre clients, quatre produits, quatre commandes et six détails de commande. La commande 104 est annulée ; elle ne doit pas entrer dans le total des marchandises payées utilisé plus loin.

Les droits existants sur shopcourse ne donnent pas automatiquement accès à cette nouvelle base. Avec l’administrateur, accordez au compte d’apprentissage le périmètre nécessaire :

GRANT ALL PRIVILEGES ON backup46_lab.* TO 'course_user'@'127.0.0.1';

Ici, ALL PRIVILEGES est limité à la seule base jetable de l’exercice. Ce n’est pas un modèle de compte opérationnel pour toutes les sauvegardes : les privilèges réels dépendent des objets, des options et des règles de votre serveur. Vérifiez aussi que le compte correspond bien à l’origine 127.0.0.1 utilisée par les commandes.

Choisir un nouveau fichier et contrôler le résultat de la commande

Les commandes suivantes s’exécutent dans le terminal du système d’exploitation, pas à l’invite SQL. Utilisez le dossier bin de l’installation ou ajoutez-le au PATH, c’est-à-dire aux dossiers dans lesquels le système cherche les programmes. Commencez par identifier les deux clients :

mariadb-dump --version
mariadb --version

Dans PowerShell, adaptez le port et le chemin si nécessaire. Le port 3306 suppose ici un serveur local à ce port. L’option --password demande le secret à l’invite ; n’ajoutez pas le mot de passe à la ligne de commande. --result-file demande au programme d’écrire le fichier et évite les différences d’encodage de la redirection de sortie entre versions de PowerShell.

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$backup46Path='C:/mariadb-lab/backup46-first.sql'
if (Test-Path -LiteralPath $backup46Path) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=C:/mariadb-lab/backup46-first.sql backup46_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed: inspect the error and verify the file.' }
Get-Item -LiteralPath $backup46Path | Select-Object Name,Length
Get-FileHash -LiteralPath $backup46Path -Algorithm SHA256

La garde sur le nom de destination est nécessaire : ne considérez pas --result-file seul comme une protection contre l’écrasement d’un fichier. Choisissez un nom distinct pour chaque tentative et ne lancez pas deux tâches vers le même chemin. Après l’exécution, le code de sortie doit indiquer une réussite et le fichier doit être non vide.

SHA-256 calcule une empreinte du contenu, utile pour comparer une copie avec son original ou repérer une modification. Une taille non nulle et une empreinte calculable ne prouvent ni l’exhaustivité ni la possibilité de restaurer les données. Une erreur peut laisser un fichier partiel : conservez le diagnostic, ne l’utilisez pas pour une restauration et recommencez avec un nouveau nom après correction.

Variante pour un shell Ubuntu : utilisez cette variante ou celle de PowerShell selon votre client. Le sous-shell limite à cette opération les réglages umask 077, qui restreint les permissions des nouveaux fichiers, et set -C, qui protège la redirection contre l’écrasement d’une destination existante.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/backup46-first.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables backup46_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

Adaptez l’adresse, le port et les options de connexion au serveur d’apprentissage. La destination est ici dans le dossier personnel du compte qui lance le client. La sauvegarde logique est écrite par le programme client ; ce n’est pas le chemin côté serveur de SELECT INTO OUTFILE.

En cas d’erreur TLS, vérifiez les versions, le nom du certificat, l’autorité de confiance et l’éventuel certificat client selon les conditions officielles des connexions chiffrées MariaDB. TLS protège la connexion et sa vérification permet de contrôler l’identité du serveur. Appliquez les fichiers d’options ou certificats appropriés aux clients de sauvegarde et de restauration ; ne désactivez pas habituellement la vérification pour contourner une erreur.

Comprendre les conditions de cohérence de la sauvegarde

--single-transaction permet une lecture cohérente pour les tables transactionnelles telles qu’InnoDB. Cette option ne fournit pas la même garantie aux moteurs non transactionnels. Coordonnez les opérations ALTER, CREATE, DROP, RENAME et TRUNCATE pendant la sauvegarde : des changements de structure concurrents peuvent compromettre les hypothèses de cette lecture.

--quick lit les lignes progressivement. --skip-lock-tables rend explicite le choix relatif aux verrous de tables dans cet exemple ; cela ne signifie pas que la sauvegarde n’a aucun effet sur les verrous, les entrées-sorties ou le réseau. Adaptez la fenêtre et la méthode au volume et à l’activité du serveur.

La commande omet volontairement --databases pour permettre une restauration sous un autre nom. Inspectez toujours le fichier avant de l’exécuter : un dump contenant CREATE DATABASE ou USE peut sélectionner une autre cible que celle que vous pensiez utiliser. La restauration d’un fichier SQL exécute des instructions ; elle doit être réservée à un fichier de confiance et à une cible contrôlée.

Inventorier les autres objets et protéger les fichiers

Cette base de démonstration ne contient ni routine, ni événement, ni déclencheur, ni vue. Une base réelle doit faire l’objet d’un inventaire distinct. Vérifiez les options prises en charge par la version du programme, les privilèges requis, les attributs DEFINER — les comptes qui déterminent le contexte d’exécution de certains objets — et les dépendances au moment de la restauration.

Examinez notamment --routines et --events si ces objets sont nécessaires. Pour les déclencheurs et les autres définitions, contrôlez le contenu réellement produit au lieu de conclure, à partir d’un nom d’option, que toutes les dépendances ont été capturées.

Les versions récentes de mariadb-dump peuvent placer une commande d’activation du mode sandbox du client au début du fichier. Un ancien client MariaDB ou un client MySQL qui ne reconnaît pas cette commande peut échouer. Contrôlez les versions et choisissez un client de restauration compatible plutôt que d’ignorer une erreur en tête de fichier.

Le dump contient des définitions et des données métier. Appliquez les contrôles d’accès, le chiffrement au repos, la durée de conservation et les copies prévues dans un autre domaine de panne. Un fichier sur le même disque ne protège pas de la perte de ce disque. Ne placez pas les dumps dans un répertoire web public ou un dépôt accessible à tous.

Conserver la source jusqu’à la vérification de la restauration

Gardez la base source et le fichier de sauvegarde jusqu’à la validation d’une restauration dans une autre base vide. Il faut retrouver les définitions, les clés, les lignes et les résultats métier attendus, pas seulement obtenir une commande qui se termine sans erreur. Pour cette base, les repères sont quatre clients, quatre produits, quatre commandes, six détails et un total de marchandises payées de 39000.

SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM backup46_lab.c46_orders o
JOIN backup46_lab.c46_items i ON i.order_id=o.order_id
WHERE o.status='paid';

Enregistrez ce résultat de référence avant de restaurer. Comparez-le ensuite au même calcul dans la base de contrôle, en adaptant le nom de base sans modifier les tables source. Vérifiez également les caractères UTF-8, les clés étrangères et les contraintes. Ne supprimez jamais automatiquement la base d’origine après avoir créé le dump.

Consignez les versions, l’heure de la sauvegarde, les options, le chemin, le code de sortie, l’empreinte et le résultat du test de restauration. Si un contrôle échoue, conservez la source, le fichier et les erreurs, corrigez la cause puis créez une nouvelle sauvegarde distincte. La reprise d’activité dépend d’une restauration effectivement vérifiée, pas de la seule génération d’un fichier SQL.