Gestion des procédures stockées

Les procédures stockées permettent d'encapsuler des traitements complexes au sein du serveur de base de données. Voici un exemple illustrant la suppression d'un enregistrement spécifique et la mise à jour simultanée d'une autre table.

DELIMITER //
CREATE PROCEDURE sp_nettoyage_et_maj()
BEGIN
    -- Suppression de l'étudiant avec l'identifiant 100
    DELETE FROM table_etudiants WHERE id_etudiant = 100;
    
    -- Mise à jour du genre pour un enseignant spécifique
    UPDATE table_professeurs 
    SET genre = 'Féminin' 
    WHERE matricule = 'T003';
END //
DELIMITER ;

Pour supprimer une procédure devenue inutile, on utilise la commande suivante :

DROP PROCEDURE IF EXISTS sp_nettoyage_et_maj;

Automatisation avec les Triggers

Un déclencheur (trigger) permet d'exécuter automatiquement un bloc d'instructions lors d'un événement DML (INSERT, UPDATE ou DELETE). L'exemple suivant crée une table d'audit pour enregistrer les insertions de nouveaux enseignants.

CREATE TABLE journal_activites (
    id_log INT PRIMARY KEY AUTO_INCREMENT,
    action_type VARCHAR(20),
    description TEXT,
    date_action DATETIME DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER tg_audit_insertion_prof AFTER INSERT ON table_professeurs
FOR EACH ROW
BEGIN
    INSERT INTO journal_activites (action_type, description) 
    VALUES ('INSERT', 'Ajout d\'un nouveau membre au corps enseignant');
END$$
DELIMITER ;

Test de l'automatisme par une insertion :

INSERT INTO table_professeurs (id, matricule, nom, genre, tel) 
VALUES (7, 'T007', 'M. Durand', 'Masculin', '0102030405');

Sécurité et privilèges utilisateurs

La gestion des accès est cruciale pour l'intégrité des données. On peut créer des utilisateurs avec des droits restreints.

-- Création d'un nouvel utilisateur
CREATE USER 'admin_lecture'@'localhost' IDENTIFIED BY 'MotDePasseSecurise123';

-- Attribution des droits de lecture uniquement
GRANT SELECT ON table_professeurs TO 'admin_lecture'@'localhost';

Stratégies de sauvegarde et de restauration

MySQL propose plusieurs approches pour la manipulation des données à grande échelle, que ce soit par clonage de table ou export vers des fichiers externes.

-- Duplication rapide d'une table
CREATE TABLE archive_etudiants AS SELECT * FROM table_etudiants;

-- Exportation via ligne de commande (mysqldump)
-- mysqldump -u utilisateur -p nom_base > sauvegarde_complete.sql

-- Exportation de données vers un fichier CSV/texte
SELECT * INTO OUTFILE '/var/lib/mysql-files/export_cours.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_cours;

-- Importation depuis un fichier
LOAD DATA INFILE '/var/lib/mysql-files/export_cours.csv'
INTO TABLE table_cours
FIELDS TERMINATED BY ',';

Fondamentaux des transactions : ACID

Pour garantir la fiabilité des opérations, MySQL respecte les quatre propriétés fondamentales :

• Atomicité : La transaction est effectuée entièrement ou pas du tout.
• Cohérence : Le passage d'un état valide à un autre état valide est garanti.
• Isolation : Les transactions concurrentes n'interfèrent pas entre elles.
• Durabilité : Une fois validées, les modifications sont permanentes.

Gestion des verrous (Locks)

Les verrous sont utilisés pour gérer la concurrence d'accès lors de sessions multiples.

-- Verrouiller une table en lecture
LOCK TABLES table_cours READ;

-- Verrouiller une table en écriture
LOCK TABLES table_cours WRITE;

-- Libérer les verrous
UNLOCK TABLES;

Configuration de la réplication Master-Slave

La réplication permet de copier les données d'un serveur maître vers un ou plusieurs serveurs esclaves.

1. Configuration du Maître (my.cnf) :

[mysqld]
log-bin=mysql-bin
server-id=101
binlog_checksum=none

2. Préparation du compte de réplication sur le Maître :

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'pass_repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
-- Noter les informations via : SHOW MASTER STATUS;

3. Configuration de l'Esclave :

STOP SLAVE;
CHANGE MASTER TO 
    MASTER_HOST='192.168.1.100', 
    MASTER_USER='repl_user', 
    MASTER_PASSWORD='pass_repl', 
    MASTER_LOG_FILE='mysql-bin.000001', 
    MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G

Note : En cas de conflit d'UUID lors d'un clonage de machine virtuelle, supprimez le fichier auto.cnf dans le répertoire de données MySQL et redémarrez le service pour en générer un nouveau.