Cours MariaDB / Projet final : commandes, droits, sauvegarde et restauration

Ce projet final MariaDB construit une base de commandes, enregistre une commande et son mouvement de stock avec un compte restreint, puis sauvegarde l’état validé et le restaure dans une autre base. La réussite se vérifie séparément sur le schéma, les valeurs métier, les droits et la restauration : un message SQL sans erreur ne suffit pas.

Définir les rôles et les résultats attendus

Utilisez une nouvelle base d’apprentissage, final48_lab, sans lien avec un service réel de commandes ou de paiement. L’exercice crée quatre tables InnoDB — clients, produits, commandes et lignes de commande — puis valide la commande 105 avant la sauvegarde. Il distingue l’administrateur, le compte d’apprentissage course_user, autorisé à gérer ce schéma, et le compte applicatif limité final48_app.

Une transaction regroupe les modifications qui doivent être validées ou annulées ensemble. Revoyez au besoin les transactions InnoDB avec COMMIT et ROLLBACK. Ce scénario à données connues reste un exercice supervisé : une application réelle doit aussi valider les entrées, lier les paramètres, authentifier les utilisateurs et empêcher qu’une même demande soit traitée deux fois.

Étape Résultat observable
Schéma Quatre tables InnoDB, clés étrangères et contraintes sur les valeurs
Nouvelle commande Stock du produit 1 : 20 → 18 ; montant des articles de la commande 105 : 7000
Droits Lectures, insertions de commandes et de lignes, modification du stock ; opérations non accordées refusées
Restauration Autre base initialement vide : 4 clients, 4 produits, 5 commandes, 7 lignes
Contrôle métier Total des articles payés : 46000 ; lignes et contraintes identiques à la source

Les syntaxes et leurs conditions sont détaillées dans les documentations officielles de GRANT et de mariadb-dump.

Créer une base dédiée et les données initiales

Préparez un serveur MariaDB et les clients mariadb et mariadb-dump. Les commandes supposent une connexion TCP locale sur le port 3306 ; adaptez le port et le chemin des exécutables à votre installation. L’administrateur doit pouvoir créer les bases et le compte de cet exercice. Le compte course_user@127.0.0.1 doit déjà exister avec son propre mot de passe.

Vérifiez que les noms final48_lab, final48_restore et final48_app sont libres. S’ils sont déjà utilisés, choisissez d’autres noms et adaptez toutes les références ; ne supprimez pas des objets existants pour faire fonctionner l’exemple. Dans la session administrateur, exécutez le schéma complet et ses données :

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_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 c48_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 c48_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_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_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_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_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 c48_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);

Les chaînes coréennes sont conservées afin de reproduire les mêmes données UTF-8 que dans les autres exercices. Les produits 1 à 4 désignent un cahier, un stylo, une tasse et une lampe. Les clients 1 et 3 ont la valeur de ville Séoul ; les clients 2 et 4 ont les valeurs Busan et Daejeon. Les explications s’appuient sur les identifiants stables plutôt que sur une traduction des valeurs enregistrées.

Accordez au compte d’apprentissage l’accès à cette seule base si nécessaire :

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

Vous devez obtenir initialement 4 clients, 4 produits, 4 commandes et 6 lignes de commande. Contrôlez les tables avec SHOW CREATE TABLE et les effectifs avec SELECT COUNT(*). Le prix actuel du catalogue, price, et le prix historique d’une ligne, unit_price, ont des rôles différents : modifier le catalogue ne doit pas réécrire les ventes passées.

Se connecter avec des droits limités au scénario

Dans la session administrateur, remplacez le mot de passe de démonstration par une valeur unique avant de créer le compte. Les autorisations ci-dessous permettent de lire les données de la base, d’insérer des commandes et des lignes, et de modifier uniquement la colonne stock des produits.

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

Ouvrez ensuite une nouvelle session depuis PowerShell ou un terminal. Le client demandera le mot de passe : ne le placez pas directement dans la commande.

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

Exécutez SELECT CURRENT_USER(); et une lecture de produit pour confirmer que cette nouvelle connexion utilise bien final48_app@127.0.0.1. Une commande ALTER TABLE doit être refusée, tout comme la suppression d’une commande ou la modification d’un prix. Ne poursuivez pas en administrateur pour contourner ces refus : ils font partie du résultat attendu.

L’autorisation SELECT ON final48_lab.* concerne toutes les tables de cette base d’exercice ; elle ne constitue pas un minimum universel pour toute application. Une application réelle peut limiter davantage les tables, colonnes ou rôles accessibles. Ce compte ne doit pas gérer les utilisateurs ni sauvegarder le serveur entier.

Valider ensemble le stock, la commande et sa ligne

Restez dans la même session applicative pour toute la transaction. Le verrouillage de la ligne du produit avec FOR UPDATE s’effectue après le début explicite de la transaction InnoDB. La condition stock>=2 évite de réserver deux unités lorsque le stock disponible est insuffisant.

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

N’exécutez le bloc suivant que si reserved_rows vaut 1. Si le résultat vaut 0 ou si une instruction échoue, exécutez ROLLBACK; dans cette connexion et arrêtez la tentative sans insérer la commande ni sa ligne. Ne fermez pas la session entre la réservation et la décision finale.

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

Avec les données initiales intactes, reserved_rows vaut 1 et goods_total vaut 7000.00, soit deux unités à 3500. Les frais de port de 1000 ne sont pas inclus dans ce total des articles. En cas d’erreur d’insertion, annulez toute la tentative avec ROLLBACK; : une erreur sur une instruction ne signifie pas que toutes les modifications précédentes ont été annulées.

Ce n’est qu’après ces contrôles réussis que vous validez :

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

Le stock attendu est maintenant 18, avec 5 commandes et 7 lignes. Réutiliser l’identifiant 105 provoque une erreur de clé dupliquée ; si une nouvelle tentative a déjà diminué le stock dans une transaction, il faut également annuler cette tentative. Ne masquez pas le problème par IGNORE ou REPLACE. Une application doit gérer explicitement ces branches d’erreur et la déduplication des demandes. Un ROLLBACK ultérieur ne peut pas annuler la commande déjà validée par COMMIT.

Sauvegarder l’état validé et restaurer ailleurs

Utilisez maintenant le compte course_user, capable d’effectuer cette sauvegarde, et non le compte applicatif restreint. Choisissez la variante Windows ou Ubuntu selon votre environnement. Arrêtez les écritures de l’exercice pendant la comparaison ; une source modifiée après la sauvegarde ne peut plus servir telle quelle de référence.

Sous Windows PowerShell, le script crée le dossier si nécessaire, refuse d’écraser un fichier existant et vérifie le code de sortie de mariadb-dump. Conservez le fichier en cas d’échec pour diagnostiquer le problème, puis recommencez sous un nouveau nom.

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { 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=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

L’option --single-transaction fournit un instantané cohérent pour les tables transactionnelles comme InnoDB ; évitez les modifications concurrentes du schéma pendant le dump. Ce fichier de base ne reconstitue pas tous les utilisateurs, droits et paramètres du serveur. Les détails de périmètre, de contrôle du fichier et de copie sont présentés dans le guide de sauvegarde avec mariadb-dump.

Examinez le fichier SQL de confiance avant de l’exécuter : une instruction USE ou une référence qualifiée peut changer la destination effective. Utilisez un client compatible avec le dump, notamment avec les directives de mode sandbox des versions récentes. En administrateur, créez une autre base vide et accordez au compte d’apprentissage l’accès à cette destination :

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

Dans un nouveau terminal, ouvrez le client sur cette base cible avec l’arrêt sur erreur de fichier source :

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

Dans ce client MariaDB, vérifiez que la base sélectionnée est final48_restore et que la liste des tables est vide, puis lancez le fichier :

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

Sous Ubuntu, voici l’alternative pour créer le dump. Le sous-shell limite la portée de umask 077, qui restreint les droits des nouveaux fichiers, et de set -C, qui empêche la redirection d’écraser un fichier existant.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.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 final48_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
)

Un fichier non vide et une empreinte SHA-256 sont des éléments de contrôle, pas une preuve que la restauration fonctionnera. N’utilisez pas le fichier partiel d’une commande échouée. Pour restaurer sous Ubuntu, connectez-vous à la même base cible vide :

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

Dans le client, remplacez /home/learner par le véritable chemin absolu du fichier sauvegardé :

SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

Exécutez une seule des deux variantes de restauration pour une destination donnée. L’option --abort-source-on-error arrête les instructions suivantes du fichier ; elle n’annule pas les objets ni les modifications déjà appliqués. En cas d’échec, corrigez la cause et préparez une nouvelle destination vide pour recommencer. Le guide de restauration dans une base distincte détaille ces contrôles et les états partiels.

Si le client signale une erreur TLS, vérifiez les versions, le nom du serveur, l’autorité de certification de confiance et les éventuels certificats clients selon les conditions officielles des connexions chiffrées. Appliquez également les paramètres appropriés aux clients de sauvegarde et de restauration ; désactiver systématiquement la vérification ne résout pas la configuration de confiance. Ne redirigez pas automatiquement l’application vers la base de contrôle.

Comparer les valeurs et les opérations refusées

Dans une session autorisée à lire la base restaurée, vérifiez les effectifs, le total métier, le stock et la définition de la table des lignes :

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

Les effectifs doivent être 4, 4, 5 et 7, le total des articles des commandes payées 46000.00 et le stock du produit 1 égal à 18. Le total initial de 39000 augmente de 7000 ; les frais de port et la commande annulée ne sont pas additionnés à cet indicateur.

Comparez aussi toutes les colonnes des lignes source et restaurées dans un ordre de clés stable, les clés étrangères, les contraintes CHECK et les résultats des jointures importantes. Des effectifs identiques peuvent masquer un montant ou un client erroné. Vérifiez séparément le refus des clés étrangères invalides, des clés dupliquées et des opérations que le compte applicatif ne possède pas.

Conservez les versions utilisées, l’empreinte du dump, les requêtes et leurs résultats, ainsi que le périmètre des droits. Fermez les sessions d’exercice, puis examinez la propriété des données et les besoins de conservation avant de supprimer un compte ou une base. La source, la sauvegarde et la destination de contrôle restent distinctes : leur suppression ne fait pas partie des commandes proposées.