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

Absolument. Voici les 10 sujets de travaux pratiques, présentés sous forme d’énoncés sans les corrections, pour vous permettre de réaliser les exercices de manière autonome.

---

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

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

Tâches à réaliser :

1. Installer le serveur MySQL sur votre machine en utilisant le gestionnaire de paquets du système.
2. Lancer le script de sécurité post-installation fourni par MySQL et appliquer toutes les recommandations pour sécuriser l’installation.
3. Se connecter au serveur en tant qu’administrateur (root) en utilisant le mot de passe défini à l’étape précédente.
4. Une fois connecté, afficher la liste de toutes les bases de données, la version du serveur et l’utilisateur actuel.
5. Se déconnecter proprement du client MySQL.

---

TP 2 : Configuration du Serveur et Surveillance

Objectifs : Modifier la configuration du serveur et surveiller son état en temps réel.

Tâches à réaliser :

1. Localiser sur le système de fichiers le répertoire contenant les fichiers de configuration de MySQL.
2. Créer un nouveau fichier de configuration personnalisé pour augmenter le nombre maximal de connexions (max_connections) à 200 et pour activer le journal des requêtes lentes (slow_query_log).
3. Redémarrer le service MySQL pour que les nouvelles configurations soient prises en compte.
4. Se connecter à MySQL et vérifier que la variable max_connections a bien la nouvelle valeur.
5. Ouvrir un second terminal, surveiller les processus actifs du serveur, puis depuis le premier terminal, exécuter une requête intentionnellement lente pour vérifier qu’elle est bien enregistrée dans le journal des requêtes lentes.

---

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.

Tâches à réaliser :

1. Créer une base de données nommée boutique.
2. À l’intérieur de cette base, créer une table produits en utilisant le moteur de stockage InnoDB. Cette table doit contenir une variété de types de données : un identifiant auto-incrémenté, des chaînes de caractères, un texte long, un nombre décimal, un booléen et un champ de type JSON.
3. Créer une seconde table categories en utilisant cette fois le moteur de stockage MyISAM.
4. Insérer quelques enregistrements dans les deux tables, en s’assurant d’inclure des données structurées dans le champ JSON de la table produits.
5. Rédiger et exécuter une requête SQL pour extraire une valeur spécifique du champ JSON.

---

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.

Tâches à réaliser :

1. En tant qu’administrateur, créer trois utilisateurs distincts : app_admin, dev_user et readonly_user, chacun avec un mot de passe robuste.
2. Attribuer à l’utilisateur app_admin tous les droits (tous les privilèges) sur la base de données boutique.
3. Attribuer à l’utilisateur dev_user les droits de lecture, d’insertion, de mise à jour et de suppression sur la base boutique.
4. Attribuer à l’utilisateur readonly_user uniquement le droit de lecture sur la base boutique.
5. Se connecter successivement avec chacun de ces trois comptes et tester que les actions autorisées fonctionnent et que les actions interdites retournent bien une erreur.

---

TP 5 : Indexation et Analyse de Performance

Objectifs : Comprendre l’impact des index sur les performances des requêtes.

Tâches à réaliser :

1. En utilisant la base boutique, peupler la table produits avec un grand volume de données (par exemple, des dizaines de milliers d’enregistrements).
2. Rédiger une requête qui recherche des informations dans une colonne non indexée (par exemple, une description). Analyser son plan d’exécution avec la commande EXPLAIN.
3. Créer un index sur une colonne de la table produits qui est fréquemment utilisée pour des recherches (par exemple, la colonne nom).
4. Rédiger une requête de recherche sur cette colonne nouvellement indexée et analyser de nouveau son plan d’exécution.
5. Comparer les résultats des deux analyses pour constater l’amélioration de performance apportée par l’index.

---

TP 6 : Sauvegarde et Restauration Logique

Objectifs : Mettre en place une stratégie de sauvegarde et tester une restauration.

Tâches à réaliser :

1. Utiliser l’utilitaire en ligne de commande mysqldump pour créer un fichier de sauvegarde complet de la base de données boutique.
2. Compresser le fichier de sauvegarde obtenu pour économiser de l’espace disque.
3. Simuler une erreur de manipulation en supprimant accidentellement une des tables de la base boutique.
4. Utiliser le fichier de sauvegarde compressé pour restaurer intégralement la base de données boutique.
5. Vérifier que la table supprimée a bien été restaurée avec toutes ses données.

---

TP 7 : Gestion des Transactions

Objectifs : Comprendre le comportement des transactions ACID et l’importance des commandes COMMIT et ROLLBACK.

Tâches à réaliser :

1. Créer une table comptes_bancaires adaptée aux transactions (avec le moteur InnoDB) contenant un titulaire et un solde.
2. Insérer deux comptes avec des soldes initiaux différents.
3. Simuler un virement d’argent d’un compte à l’autre en encapsulant les opérations de mise à jour des soldes dans une transaction. Valider cette transaction avec COMMIT et vérifier que les soldes ont été correctement mis à jour.
4. Simuler un nouveau virement, mais après les opérations de mise à jour, annuler la transaction avec ROLLBACK. Vérifier que cette fois, les soldes des comptes sont restés inchangés.

---

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é.

Tâches à réaliser :

1. S’assurer que les tables produits et categories de la base boutique sont correctement liées (par exemple, via une clé étrangère categorie_id).
2. Créer une vue nommée details_produits qui affiche pour chaque produit son nom, son prix et le nom de sa catégorie (en faisant une jointure).
3. Rédiger et exécuter une requête qui filtre les résultats de cette vue (par exemple, afficher les produits dont le prix est supérieur à une certaine valeur).
4. En utilisant l’utilisateur readonly_user (créé dans un TP précédent), vérifier qu’il peut interroger la vue pour obtenir des informations, sans avoir besoin d’un accès direct aux tables sous-jacentes.

---

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

Objectifs : Utiliser les commandes SQL pour exporter et importer des données depuis/vers un fichier CSV.

Tâches à réaliser :

1. Rédiger une requête SQL utilisant SELECT INTO OUTFILE pour exporter tout le contenu de la table categories dans un fichier CSV sur le serveur.
2. Se connecter au serveur via le terminal et vérifier que le fichier CSV a bien été créé et que son format est correct.
3. Créer une table temporaire dans MySQL dont la structure est compatible avec les données du fichier CSV.
4. Rédiger une requête SQL utilisant LOAD DATA INFILE pour importer les données du fichier CSV dans cette nouvelle table.
5. Vérifier en interrogeant la table temporaire que toutes les données ont été correctement importées.

---

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.

Tâches à réaliser :

1. Créer une table logs_visites destinée à stocker des millions d’enregistrements. Partitionner cette table par RANGE sur l’année de la colonne date_visite.
2. Insérer des données de test qui se répartissent sur au moins deux années différentes pour remplir plusieurs partitions.
3. Utiliser la commande EXPLAIN PARTITIONS pour vérifier qu’une requête filtrant sur une année spécifique utilise bien la partition correspondante.
4. Supprimer toutes les données d’une année en une seule commande, en supprimant la partition associée.
5. Après de nombreuses insertions et suppressions sur une table InnoDB, effectuer une opération de maintenance (OPTIMIZE TABLE) pour reconstruire la table et libérer l’espace inutilisé.