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

Voici 5 travaux pratiques (TP) adaptés au cours d’Administration des Bases de Données MySQL, couvrant les concepts clés du programme :

TP 1 : Installation, configuration de base et sécurisation

Objectif : Maîtriser l’installation de MySQL, la configuration de base et les bonnes pratiques de sécurité.
Tâches :

Installer MySQL Server sur une machine virtuelle ou locale (Ubuntu/CentOS).
Exécuter mysql_secure_installation pour renforcer la sécurité.
Configurer le fichier my.cnf avec des paramètres de base (mémoire, logs, moteur InnoDB).
Créer un utilisateur dédié à l’administration (ex: admin_db) avec des privilèges restreints.
Test de connexion avec l’utilisateur nouvellement créé.
Livrable :

Script d’installation et de configuration commenté.
Copie du fichier my.cnf final.
Journal des actions avec les commandes exécutées.

TP 2 : Gestion des utilisateurs et des privilèges

Objectif : Comprendre la gestion fine des accès et des rôles dans MySQL.
Tâches :

Créer une base de données entreprise avec deux tables : employes et departements.
Créer trois rôles : lecture_seule, edition_limitee, administration_locale.
Attribuer les privilèges appropriés à chaque rôle (ex: SELECT pour lecture_seule, INSERT/UPDATE pour edition_limitee, etc.).
Créer des utilisateurs et leur assigner ces rôles.
Tester chaque compte avec des opérations permises et interdites.
Livrable :

Script SQL de création des rôles et utilisateurs.
Journal de tests avec les erreurs obtenues lors des tentatives d’accès non autorisé.

TP 3 : Sauvegarde et restauration de données

Objectif : Mettre en place une stratégie de sauvegarde logique et physique.
Tâches :

Effectuer une sauvegarde complète avec mysqldump de la base entreprise.
Créer une sauvegarde incrémentale en utilisant les logs binaires.
Simuler une perte de données (suppression accidentelle d’une table).
Restaurer la base à partir de la sauvegarde complète puis appliquer les logs binaires pour récupérer les données perdues.
Configurer une tâche cron pour automatiser les sauvegardes quotidiennes.
Livrable :

Scripts de sauvegarde et restauration.
Preuve de la restauration réussie (captures d’écran ou exports des tables restaurées).

TP 4 : Partitionnement et optimisation des performances

Objectif : Améliorer les performances grâce au partitionnement et à l’optimisation des requêtes.
Tâches :

Créer une table ventes avec un champ date_vente et la partitionner par range (année ou trimestre).
Insérer des données de test (au moins 10 000 enregistrements).
Comparer les temps d’exécution de requêtes sur la table partitionnée et non partitionnée.
Utiliser EXPLAIN pour analyser les plans d’exécution.
Créer des index stratégiques et mesurer leur impact.
Livrable :

Script de création de la table partitionnée et des index.
Comparatif des performances avant/après partitionnement et indexation.

TP 5 : Monitoring, maintenance et résolution d’incidents

Objectif : Surveiller l’état du serveur et diagnostiquer des problèmes courants.
Tâches :

Configurer le slow_query_log pour capturer les requêtes lentes (> 2s).
Utiliser mysqldumpslow ou pt-query-digest pour analyser les logs.
Simuler un blocage (deadlock) et utiliser SHOW ENGINE INNODB STATUS pour l’identifier.
Surveiller les ressources (CPU, mémoire, disque) via PERFORMANCE_SCHEMA ou des outils externes (ex: mysqladmin status).
Planifier une maintenance avec OPTIMIZE TABLE sur les tables fragmentées.
Livrable :

Rapport d’analyse des requêtes lentes avec des propositions d’optimisation.
Compte-rendu de résolution du deadlock et des mesures préventives.

Critères d’évaluation communs :

Exactitude technique des configurations et scripts.
Capacité à expliquer les choix effectués.
Qualité de la documentation (commentaires, procédures).
Robustesse des solutions face aux scenarios proposés.

Ces TP permettront aux étudiants de pratiquer les compétences essentielles d’un administrateur MySQL, de l’installation à la maintenance avancée, en passant par la sécurité et la récupération de données.