Cours de Génie Logiciel
Thème :
Junior
1 vues ·9/13 leçons

TP-02 & CORRIGEES : Administration des Bases de Données MySQL

TP 1 : Installation, Sécurisation et Première Connexion

Objectifs : Maîtriser l’installation de base, sécuriser le serveur et se connecter. (Chapitres 2, 4)

  1. Installation : Si ce n’est pas déjà fait, installez le serveur MySQL.

    sudo apt update
sudo apt install mysql-server

  2. Sécurisation : Lancez le script de sécurité post-installation. Répondez “Y” (oui) à toutes les questions pour :

    • Définir un mot de passe pour l’utilisateur root.
    • Supprimer les utilisateurs anonymes.
    • Interdire la connexion root à distance.
    • Supprimer la base de données de test.
    • Recharger les tables de privilèges.
    sudo mysql_secure_installation

  3. Connexion et Vérification :

    • Connectez-vous au serveur en tant que root avec le mot de passe que vous venez de définir.
      sudo mysql -u root -p
    • Une fois connecté, affichez la liste des bases de données, la version du serveur et l’utilisateur actuel.
      SHOW DATABASES;
SELECT VERSION();
SELECT USER();
    • Quittez le client avec exit; ou \q.

TP 2 : Configuration du Serveur et Surveillance

Objectifs : Modifier la configuration du serveur et surveiller son état. (Chapitres 4, 5)

  1. Configuration :

    • Trouvez le fichier de configuration principal (my.cnf) et les fichiers de configuration inclus.
    • Créez un nouveau fichier de configuration personnalisé pour ne pas modifier les fichiers par défaut.
      sudo nano /etc/mysql/mysql.conf.d/z_custom.cnf
    • Ajoutez-y les lignes suivantes pour augmenter le nombre maximal de connexions et activer le journal des requêtes lentes :
      [mysqld]
max_connections = 200
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

  2. Redémarrage et Vérification :

    • Redémarrez le service MySQL pour appliquer les changements.
      sudo systemctl restart mysql
    • Reconnectez-vous et vérifiez que la variable max_connections a bien été modifiée.
      SHOW VARIABLES LIKE 'max_connections';

  3. Surveillance :

    • Ouvrez deux terminaux. Dans le premier, connectez-vous à MySQL.
    • Dans le second, affichez la liste des processus en cours pour observer la connexion du premier terminal.
      mysqladmin -u root -p processlist
    • Depuis le client MySQL, exécutez une requête qui prend plus d’une seconde (par exemple, SELECT SLEEP(2);).
    • Vérifiez que le fichier /var/log/mysql/slow.log a bien été créé et contient un enregistrement pour votre requête lente.

TP 3 : Création d’une Base de Données Complexes

Objectifs : Mettre en pratique les types de données et les moteurs de stockage. (Chapitres 7, 9)

  1. Création de la base et des tables :

    • Créez une base de données pour une petite boutique en ligne.
      CREATE DATABASE boutique;
USE boutique;
    • Créez une table produits avec le moteur InnoDB (par défaut) en utilisant divers types de données :
      CREATE TABLE produits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    prix DECIMAL(10, 2) NOT NULL,
    stock INT UNSIGNED DEFAULT 0,
    en_vente BOOLEAN DEFAULT TRUE,
    carateristiques JSON,
    date_ajout DATETIME DEFAULT CURRENT_TIMESTAMP
);
    • Créez une table categories avec le moteur MyISAM, adapté pour les lectures intensives.
      CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    description VARCHAR(255)
) ENGINE=MyISAM;

  2. Insertion et Interrogation :

    • Insérez quelques données dans les deux tables, y compris des données JSON dans la table produits.
      INSERT INTO categories (nom, description) VALUES ('Électronique', 'Appareils électroniques');
INSERT INTO produits (nom, prix, carateristiques)
VALUES ('Smartphone', 599.99, '{"couleur": "noir", "memoire": "128GB", "marque": "TechCorp"}');
    • Extrayez une valeur spécifique du champ JSON.
      SELECT nom, JSON_EXTRACT(carateristiques, '$.marque') AS marque FROM produits;

TP 4 : Gestion des Utilisateurs et des Privilèges

Objectifs : Créer des utilisateurs avec des droits spécifiques selon le principe du moindre privilège. (Chapitre 12)

  1. Création des utilisateurs :

    • Connectez-vous en tant que root.
    • Créez trois utilisateurs : un administrateur d’application, un développeur et un lecteur (pour les rapports).
      CREATE USER 'app_admin'@'localhost' IDENTIFIED BY 'AdminPass123!';
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass123!';
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'ReadPass123!';

  2. Attribution des privilèges :

    • Donnez tous les droits sur la base boutique à l’administrateur.
      GRANT ALL PRIVILEGES ON boutique.* TO 'app_admin'@'localhost';
    • Donnez les droits de lecture, insertion, modification et suppression au développeur.
      GRANT SELECT, INSERT, UPDATE, DELETE ON boutique.* TO 'dev_user'@'localhost';
    • Donnez uniquement le droit de lecture au readonly_user.
      GRANT SELECT ON boutique.* TO 'readonly_user'@'localhost';
    • Appliquez les changements.
      FLUSH PRIVILEGES;

  3. Test des droits :

    • Déconnectez-vous de root.
    • Connectez-vous successivement avec chaque utilisateur (mysql -u readonly_user -p, etc.) et vérifiez que vous pouvez exécuter les actions autorisées et que vous recevez une erreur pour celles qui sont interdites (par exemple, readonly_user qui essaie de faire un INSERT).

TP 5 : Indexation et Analyse de Performance

Objectifs : Comprendre l’impact des index sur les performances des requêtes. (Chapitre 11, concepts de performance)

  1. Préparation :

    • Utilisez la base boutique et la table produits.
    • Insérez un grand nombre de produits (vous pouvez utiliser une procédure stockée ou un script pour générer des données). Pour ce TP, imaginons que vous avez 100 000 produits.

  2. Analyse sans index :

    • Exécutez une requête de recherche sur une colonne non indexée, par exemple la description.
      EXPLAIN SELECT * FROM produits WHERE description LIKE '%ordinateur%';
    • Notez le type de scan (probablement ALL pour un scan complet de la table).

  3. Création d’un index et nouvelle analyse :

    • Créez un index sur la colonne nom.
      CREATE INDEX idx_produits_nom ON produits(nom);
    • Maintenant, exécutez une recherche sur cette colonne.
      EXPLAIN SELECT * FROM produits WHERE nom = 'Smartphone';
    • Comparez le résultat. Vous devriez voir que l’index est utilisé (type: ref), ce qui est beaucoup plus efficace.

TP 6 : Sauvegarde et Restauration Logique

Objectifs : Mettre en place une stratégie de sauvegarde et tester une restauration. (Chapitre 15)

  1. Sauvegarde :

    • Utilisez mysqldump pour créer une sauvegarde complète de la base boutique.
      mysqldump -u root -p boutique > boutique_backup.sql
    • Compressez le fichier de sauvegarde.
      gzip boutique_backup.sql

  2. Simulation de perte de données :

    • Connectez-vous à MySQL et supprimez la table produits par erreur.
      USE boutique;
DROP TABLE produits;
SHOW TABLES; -- Vérifiez que la table a disparu

  3. Restauration :

    • Restaurez la base de données à partir du fichier de sauvegarde.
      gunzip < boutique_backup.sql.gz | mysql -u root -p boutique
    • Vérifiez que la table produits et toutes ses données ont bien été restaurées.
      USE boutique;
SHOW TABLES;
SELECT COUNT(*) FROM produits;

TP 7 : Gestion des Transactions

Objectifs : Comprendre le comportement des transactions ACID. (Chapitre 11)

  1. Préparation :

    • Créez une table comptes_bancaires.
      CREATE TABLE comptes_bancaires (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulaire VARCHAR(100),
    solde DECIMAL(15, 2) NOT NULL
) ENGINE=InnoDB;
    • Insérez deux comptes.
      INSERT INTO comptes_bancaires (titulaire, solde) VALUES ('Alice', 1000.00), ('Bob', 500.00);

  2. Simulation de transfert (avec COMMIT) :

    • Ouvrez deux terminaux connectés à MySQL.
    • Terminal 1 : Démarrez une transaction et effectuez un virement de 100€ d’Alice à Bob.
      START TRANSACTION;
UPDATE comptes_bancaires SET solde = solde - 100 WHERE titulaire = 'Alice';
UPDATE comptes_bancaires SET solde = solde + 100 WHERE titulaire = 'Bob';
    • Terminal 2 : Avant de valider, vérifiez les soldes. Vous ne devriez pas voir les changements (selon le niveau d’isolation par défaut, REPEATABLE-READ).
      SELECT * FROM comptes_bancaires;
    • Terminal 1 : Validez la transaction.
      COMMIT;
    • Terminal 2 : Vérifiez à nouveau les soldes. Les changements sont maintenant visibles.

  3. Simulation d’erreur (avec ROLLBACK) :

    • Répétez l’opération, mais après les UPDATE, dans le Terminal 1, annulez la transaction.
      START TRANSACTION;
UPDATE comptes_bancaires SET solde = solde - 100 WHERE titulaire = 'Alice';
-- Oups, une erreur se produit !
ROLLBACK;
    • Vérifiez les soldes dans les deux terminaux. Aucun changement n’a été appliqué.

TP 8 : Utilisation des Vues

Objectifs : Créer et utiliser des vues pour simplifier l’accès aux données et renforcer la sécurité. (Chapitre 14)

  1. Création de la vue :

    • Dans la base boutique, créez une vue qui joint les produits et categories (vous devrez d’abord ajouter une categorie_id à la table produits et la peupler).

    • La vue details_produits montrera uniquement le nom du produit, son prix et le nom de sa catégorie.

      -- Assurez-vous d'avoir une colonne categorie_id dans produits
ALTER TABLE produits ADD COLUMN categorie_id INT;
UPDATE produits SET categorie_id = 1 WHERE id = 1; -- Exemple

CREATE VIEW details_produits AS
SELECT p.id, p.nom, p.prix, c.nom AS nom_categorie
FROM produits p
JOIN categories c ON p.categorie_id = c.id;

  2. Utilisation de la vue :

    • Interrogez la vue comme si c’était une table normale.
      SELECT * FROM details_produits WHERE prix > 500;
    • Connectez-vous avec l’utilisateur readonly_user et vérifiez qu’il peut interroger la vue sans avoir accès aux tables sous-jacentes.

TP 9 : Exportation et Importation de données (CSV)

Objectifs : Utiliser les commandes SELECT INTO OUTFILE et LOAD DATA INFILE. (Chapitre 14)

  1. Exportation :

    • Exportez le contenu de la table categories dans un fichier CSV.
      SELECT * FROM categories INTO OUTFILE '/tmp/categories.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
    • Vérifiez la création et le contenu du fichier /tmp/categories.csv.

  2. Importation :

    • Créez une table temporaire pour recevoir les données.
      CREATE TABLE categories_import (
    id INT,
    nom VARCHAR(50),
    description VARCHAR(255)
);
    • Importez les données depuis le fichier CSV dans cette nouvelle table.
      LOAD DATA INFILE '/tmp/categories.csv'
INTO TABLE categories_import
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
    • Vérifiez que les données ont bien été importées.
      SELECT * FROM categories_import;

TP 10 : Maintenance des Tables et Partitionnement

Objectifs : Mettre en œuvre le partitionnement sur une table de logs et effectuer des tâches de maintenance. (Chapitres 10, 13)

  1. Création d’une table partitionnée :

    • Créez une table logs_visites pour stocker les logs de visites de votre site. Partitionnez-la par RANGE sur l’année de la date de visite.
      CREATE TABLE logs_visites (
    id BIGINT AUTO_INCREMENT,
    ip VARCHAR(45),
    url_visitee VARCHAR(255),
    date_visite DATETIME,
    PRIMARY KEY (id, date_visite)
) PARTITION BY RANGE (YEAR(date_visite)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

  2. Insertion et Analyse :

    • Insérez des données dans différentes partitions (dates en 2023 et 2024).
    • Utilisez EXPLAIN PARTITIONS pour voir quelle partition est utilisée lorsque vous interrogez des données d’une année spécifique.
      EXPLAIN PARTITIONS SELECT * FROM logs_visites WHERE YEAR(date_visite) = 2023;

  3. Maintenance :

    • Supprimez toutes les visites de 2023 en supprimant simplement la partition correspondante. C’est beaucoup plus rapide qu’un DELETE.
      ALTER TABLE logs_visites DROP PARTITION p2023;
    • Après de nombreuses suppressions/mises à jour sur une table InnoDB, optimisez-la pour reconstruire la table et libérer l’espace.
      -- Sur une table non partitionnée ou une partition spécifique
OPTIMIZE TABLE produits;

Leçon précédente

TP-01 : Administration des Bases de Données MySQL

Prochaine leçon

TP-02 : Administration des Bases de Données MySQL

Prêt à passer à la suite ?

Vous avez terminé cette leçon sur TP-02 & CORRIGEES : Administration des Bases de Données MySQL. Continuez votre apprentissage avec la prochaine leçon ou explorez d'autres formations.

Continuer avec TP-02 : Administration des Bases de Données MySQL Explorer toutes les formations