TP 2 : Administration Avancée de MySQL - Corrigé détaillé
Schéma des tables utilisées
Avant de commencer, voici les tables principales utilisées dans ce TP :
CREATE TABLE departement (
id_dept INT AUTO_INCREMENT PRIMARY KEY,
nom_dept VARCHAR(50) NOT NULL
);
CREATE TABLE employe (
id_emp INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
id_dept INT,
chef INT NULL,
FOREIGN KEY (id_dept) REFERENCES departement(id_dept),
FOREIGN KEY (chef) REFERENCES employe(id_emp)
);
CREATE TABLE logs (
id_log INT AUTO_INCREMENT PRIMARY KEY,
date_action DATE NOT NULL,
utilisateur VARCHAR(50) NOT NULL,
action TEXT NOT NULL
);
Pour exécuter le script complet de création et d’insertion des données :
mysql -u root -p < chemin/vers/script.sql
Objectif du TP
Acquérir des compétences avancées en administration MySQL sur une machine locale : sécurité,
performances, transactions, sauvegarde/restauration, réplication et partitionnement.
1) Gestion du service MySQL
• Démarrage/arrêt du service : Utilisez Windows Services pour démarrer ou arrêter MySQL.
• Vérification du port : par défaut, MySQL écoute sur le port 3306, vérifier dans my.ini .
• Localisation du fichier de configuration : my.ini contient les paramètres du serveur.
• Vérification des logs : chemins des journaux pour suivi et débogage.
1
2) Transactions et verrous
• Création d'une table test :
CREATE TABLE test (id INT PRIMARY KEY, valeur VARCHAR(50));
INSERT INTO test VALUES (1,'A'), (2,'B');
• Test de verrouillage :
• Session 1 : START TRANSACTION; SELECT * FROM test WHERE id=1 FOR UPDATE;
• Session 2 : UPDATE test SET valeur='X' WHERE id=1; -> bloqué jusqu'à la validation/
rollback de Session 1.
• Explication : Les verrous empêchent les conflits d’écriture et assurent la cohérence.
3) Performance et indexation
• Requête sans index : SELECT * FROM employe WHERE id_dept=3;
• Ajout d’un index : CREATE INDEX idx_dept ON employe(id_dept);
• Comparaison plan d’exécution : EXPLAIN SELECT * FROM employe WHERE id_dept=3;
• Slow Query Log :
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
• Identifier les requêtes lentes avec : SELECT * FROM mysql.slow_log;
4) Sauvegarde et restauration
• Sauvegarde : mysqldump -u root -p nom_base > sauvegarde.sql
• Suppression d’une table : DROP TABLE logs;
• Restauration : mysql -u root -p nom_base < sauvegarde.sql
• Vérification : SELECT COUNT(*) FROM logs; pour vérifier les données restaurées.
5) Réplication MySQL sur la même machine
1. Dupliquer MySQL pour créer une deuxième instance (SLAVE).
2. Configurer le MASTER (port 3306) :
3. Activer log binaire : log-bin=mysql-bin dans my.ini
4. Définir server-id=1
5. Base à répliquer : replication_db
6. Configurer le SLAVE (port 3307) :
7. Nouveau dossier de données
8. server-id=2
9. Activer relay_log
10. Créer utilisateur de réplication :
CREATE USER 'repl'@'localhost' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';
11. Sauvegarder MASTER et importer sur SLAVE :
2
mysqldump -u root -p replication_db > dump.sql
mysql -u root -p -P 3307 replication_db < dump.sql
12. Configurer SLAVE :
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS= 154;
START SLAVE;
13. Vérifier réplication : SHOW SLAVE STATUS\G -> Slave_IO_Running et
Slave_SQL_Running doivent être YES.
6) Partitionnement d’une table MySQL
• Création et partitionnement :
CREATE TABLE logs (
id_log INT AUTO_INCREMENT PRIMARY KEY,
date_action DATE NOT NULL,
utilisateur VARCHAR(50) NOT NULL,
action TEXT NOT NULL
)
PARTITION BY RANGE (YEAR(date_action)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
• Insertion de données :
INSERT INTO logs (date_action, utilisateur, action) VALUES
('2022-01-05','user1','Connexion'),
('2023-05-10','user2','Modification'),
('2024-03-12','user3','Suppression');
-- Générer 100 lignes supplémentaires aléatoires avec une procédure si
nécessaire.
• Afficher partitions : SHOW TABLE STATUS LIKE 'logs';
• Requête filtrée pour 2023 :
SELECT * FROM logs WHERE YEAR(date_action)=2023;
3
• Observation : Seule la partition p2023 est scannée, améliorant les performances.
✅ Ce corrigé détaillé fournit toutes les commandes SQL, procédures et explications nécessaires pour
chaque partie du TP.
