Réplication Maître-Slave

1. Objectifs de la réplication maître-esclave

• Effectuer des sauvegardes en temps réel de la base de données pour assurer son intégrité.
• Permettre une séparation lecture-écriture où le serveur maître gère les écritures et le serveur esclave les lectures, améliorant ainsi les performances globales.

Le principe repose sur la synchronisation via les fichiers journaux binaires (binlog).

2. Modification des fichiers de configuration

Modifier les fichiers de configuration sur les deux machines. Assurez-vous que les server-id sont différents, généralement l’ID du maître étant inférieur à celui de l’esclave.

# Pour le serveur 1 (maître)
log-bin = /chemin/vers/mysql-bin
server-id = 1

# Pour le serveur 2 (esclave)
log-bin = /chemin/vers/mysql-bin
server-id = 2

3. Création d’un utilisateur pour la réplication

Créez un utilisateur dédié à la réplication et accordez-lui les droits nécessaires.

# Dans le serveur maître
GRANT REPLICATION SLAVE ON *.* TO 'utilisateur_replication'@'adresse_ip_esclave' IDENTIFIED BY 'mot_de_passe';
FLUSH PRIVILEGES;

4. Sauvegarde et restauration du serveur maître vers l’esclave

Effectuez une sauvegarde du serveur maître et restaurez-la sur l’esclave pour synchroniser leurs données.

# Verrouiller les tables pour une sauvegarde cohérente
FLUSH TABLES WITH READ LOCK;

# Obtenir la position actuelle du fichier binlog
SHOW MASTER STATUS;

# Sauvegarder les données
mysqldump -uutilisateur -pmot_de_passe --all-databases --master-data=2 --events | gzip > sauvegarde.sql.gz

# Déverrouiller les tables après la sauvegarde
UNLOCK TABLES;

# Restaurer la sauvegarde sur l’esclave
gunzip < sauvegarde.sql.gz | mysql -uutilisateur -pmot_de_passe

5. Configuration de l’esclave et activation

Configurez les paramètres de connexion entre l’esclave et le maître, puis activez la réplication.

# Dans le serveur esclave
CHANGE MASTER TO
    MASTER_HOST='adresse_ip_maître',
    MASTER_PORT=3306,
    MASTER_USER='utilisateur_replication',
    MASTER_PASSWORD='mot_de_passe',
    MASTER_LOG_FILE='nom_fichier_binlog',
    MASTER_LOG_POS=position;

START SLAVE;

# Vérifier l’état de la réplication
SHOW SLAVE STATUS\G

6. Séparation Lecture-Écriture

La séparation lecture-écriture est essentielle dans un environnemetn de production.

# Dans le fichier de configuration du serveur esclave
read-only

# Créer un utilisateur avec des privilèges limités
GRANT SELECT ON *.* TO 'utilisateur_seul_lecture'@'%' IDENTIFIED BY 'mot_de_passe';
FLUSH PRIVILEGES;

7. Gestion du basculement en cas de panne du maître

En cas de panne du serveur maître, sélectionnez un serveur esclave pour prendre son rôle.

# Sur le nouveau maître
RESET MASTER;

# Sur les autres esclaves
CHANGE MASTER TO MASTER_HOST='nouvelle_adresse_ip_maître';
START SLAVE;

8. Répilcation Maître-Maître

Pour une réplication maître-maître, assurez-vous que les tables ont des clés primaires auto-incrémentées.

# Dans les fichiers de configuration des deux serveurs
auto_increment_increment = 2
auto_increment_offset = 1 ou 2 selon le serveur

Sauvegarde et restauration de MySQL

1. Sauvegarde d’une seule base de données

# Commande de sauvegarde
mysqldump -uutilisateur -pmot_de_passe nom_base_de_donnees > sauvegarde.sql

# Avec compression
mysqldump -uutilisateur -pmot_de_passe nom_base_de_donnees | gzip > sauvegarde.sql.gz

# Restauration
mysql -uutilisateur -pmot_de_passe nom_base_de_donnees < sauvegarde.sql

2. Sauvegarde de plusieurs bases de données

mysqldump -uutilisateur -pmot_de_passe --databases base1 base2 | gzip > sauvegarde_multiples.sql.gz

3. Sauvegarde de tables individuelles

# Une seule table
mysqldump -uutilisateur -pmot_de_passe nom_base_de_donnees nom_table > sauvegarde_table.sql

# Plusieurs tables
mysqldump -uutilisateur -pmot_de_passe nom_base_de_donnees table1 table2 > sauvegarde_tables.sql

4. Paramètres importants de mysqldump

• -B : Ajoute les instructions CREATE DATABASE et USE.
• --compact : Supprime les commentaires.
• -A : Suavegarde toutes les bases de données.
• -F : Rafraîchit les journaux binaires.
• --master-data : Ajoute le nom et la position du fichier binlog.
• -x, --lock-all-tables : Verrouille toutes les tables.
• -l, --lock-tables : Verrouille chaque table avant sa sauvegarde.
• -d : Sauvegarde uniquement la structure des tables.
• -t : Sauvegarde uniquement les données.
• --single-transaction : Utilise une transaction pour la sauvegarde des bases de données InnoDB.

5. Restauration incrémentale

Pour une restauration incrémentale, vous avez besoin d’un point de sauvegarde complet et des journaux binaires.

# Sauvegarde complète
mysqldump -uutilisateur -pmot_de_passe --single-transaction --all-databases | gzip > sauvegarde_complette.sql.gz

# Après une modification, rafraîchir les journaux binaires
mysqladmin -uutilisateur -pmot_de_passe flush-logs

# Sauvegarder le dernier journal binaire
cp chemin/vers/mysql-bin.XXXX sauvegarde/

# Restauration
gunzip < sauvegarde_complette.sql.gz | mysql -uutilisateur -pmot_de_passe
mysqlbinlog chemin/vers/mysql-bin.XXXX | mysql -uutilisateur -pmot_de_passe