Configurer l'esclave MySQL

Contexte et objectifs

Ce document explique, étape par étape, comment :

• préparer /etc/mysql/my.cnf sur l’esclave ;
• créer et remplir la base de données cible ;
• configurer la réplication sécurisée (SSL) ;
• vérifier que Slave_IO_Running et Slave_SQL_Running sont à Yes.

Définition rapide : réplication MySQL — mécanisme de copie en continu des modifications d’une base (maître) vers une autre (esclave).

4 Configurer l’esclave

Maintenant, configurons l’esclave. Ouvrez /etc/mysql/my.cnf et assurez-vous d’avoir les réglages suivants dans la section [mysqld] :

serveur2 :

vi /etc/mysql/my.cnf

| [...] server-id=2 master-connect-retry=60 replicate-do-db=exampledb [...] |

La valeur de server-id doit être unique et donc différente de celle du maître.

Redémarrez ensuite MySQL :

/etc/init.d/mysql restart

Important : adaptez server-id, les chemins et mots de passe à votre environnement. Conservez une copie de my.cnf avant modification.

Créer la base vide sur l’esclave

Avant de configurer la réplication, créez une base de données vide exampledb sur server2 :

mysql -u root -p

CREATE DATABASE exampledb;  
quit;

Importer le dump SQL (snapshot)

Sur server2, importez le snapshot SQL généré depuis le maître :

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave  
cd /tmp  
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Connectez-vous de nouveau à MySQL :

mysql -u root -p

Puis exécutez la commande CHANGE MASTER TO pour faire de server2 un esclave de server1. Remplacez les valeurs par celles obtenues avec SHOW MASTER STATUS; sur le maître :

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/newcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/newcerts/client-key.pem';

• MASTER_HOST : adresse IP ou nom d’hôte du maître (ici 192.168.0.100).
• MASTER_USER : utilisateur ayant le privilège REPLICATION SUR le maître.
• MASTER_PASSWORD : mot de passe de MASTER_USER sur le maître.
• MASTER_LOG_FILE : nom du fichier binaire renvoyé par SHOW MASTER STATUS; sur le maître.
• MASTER_LOG_POS : position renvoyée par SHOW MASTER STATUS; sur le maître.
• MASTER_SSL : active l’utilisation d’une connexion SSL.
• MASTER_SSL_CA, MASTER_SSL_CERT, MASTER_SSL_KEY : chemins des certificats/clé sur l’esclave.

Démarrez ensuite l’esclave :

START SLAVE;

Puis vérifiez le statut de l’esclave :

SHOW SLAVE STATUS \G

Il est essentiel que Slave_IO_Running et Slave_SQL_Running aient la valeur Yes (sinon, corrigez la configuration et consultez /var/log/syslog pour les erreurs). Comme la connexion utilise SSL, les champs Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert et Master_SSL_Key doivent aussi être renseignés :

mysql> SHOW SLAVE STATUS \G  
************************* 1. row *************************  
               Slave_IO_State: Waiting for master to send event  
                 Master_Host: 192.168.0.100  
                 Master_User: slave_user  
                 Master_Port: 3306  
               Connect_Retry: 60  
             Master_Log_File: mysql-bin.000001  
         Read_Master_Log_Pos: 106  
               Relay_Log_File: mysqld-relay-bin.000002  
                Relay_Log_Pos: 251  
        Relay_Master_Log_File: mysql-bin.000001  
             Slave_IO_Running: Yes  
           Slave_SQL_Running: Yes  
               Replicate_Do_DB: exampledb  
           Replicate_Ignore_DB:  
            Replicate_Do_Table:  
       Replicate_Ignore_Table:  
      Replicate_Wild_Do_Table:  
  Replicate_Wild_Ignore_Table:  
                   Last_Errno: 0  
                   Last_Error:  
                  Skip_Counter: 0  
          Exec_Master_Log_Pos: 106  
              Relay_Log_Space: 407  
              Until_Condition: None  
               Until_Log_File:  
                Until_Log_Pos: 0  
           Master_SSL_Allowed: Yes  
           Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem  
           Master_SSL_CA_Path:  
              Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem  
            Master_SSL_Cipher:  
               Master_SSL_Key: /etc/mysql/newcerts/client-key.pem  
        Seconds_Behind_Master: 0  
Master_SSL_Verify_Server_Cert: No  
                Last_IO_Errno: 0  
                Last_IO_Error:  
               Last_SQL_Errno: 0  
               Last_SQL_Error:  
1 row in set (0.00 sec)  
  
mysql>

Après vérification, quittez le shell MySQL sur server2 :

quit;

C’est tout. Dorénavant, chaque mise à jour de exampledb sur le maître sera répliquée vers exampledb sur l’esclave. Testez en appliquant quelques opérations INSERT/UPDATE/DELETE sur le maître.

Vérifications et critères d’acceptation

• Slave_IO_Running = Yes et Slave_SQL_Running = Yes.
• Read_Master_Log_Pos doit avancer après de nouvelles écritures sur le maître.
• Master_SSL_Allowed doit être Yes et les chemins des certificats renseignés.
• Aucune erreur dans /var/log/syslog liée à la réplication ou à SSL.

Liste de contrôle (rôles)

DBA

• Vérifier que server-id est unique.
• Créer la base vide et importer le snapshot.
• Exécuter CHANGE MASTER TO avec les bons paramètres.

Opérations (Ops)

• Confirmer l’existence et les permissions des fichiers de certificat sur l’esclave.
• Vérifier le pare-feu (port 3306 ouvert entre maître et esclave si nécessaire).
• Surveiller /var/log/syslog après démarrage du slave.

Développeur

• Tester la réplication via jeux de données non critiques.
• Valider l’application des triggers ou fonctions côté esclave si utilisées.

Procédure de dépannage rapide

1. Si Slave_IO_Running = No :
   • Vérifiez MASTER_HOST, port et connectivité réseau.
   • Contrôlez /var/log/syslog pour le détail de l’erreur.
2. Si Slave_SQL_Running = No :
   • Exécutez SHOW SLAVE STATUS \G et inspectez Last_SQL_Error.
   • Réparez la requête problématique ou utilisez SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; puis START SLAVE; pour sauter un événement (utiliser avec prudence).
3. Si problème SSL :
   • Vérifiez que MASTER_SSL_CA_File, MASTER_SSL_Cert et MASTER_SSL_Key existent et sont lisibles par mysql.
   • Testez une connexion TLS simple entre hôtes pour valider la chaîne de certificats.

Annulation / rollback basique

Si la réplication introduit un problème et que vous devez arrêter la propagation :

• Sur l’esclave :

STOP SLAVE;
RESET SLAVE ALL; -- efface la configuration de master

• Restaurez la base à partir d’un dump antérieur si nécessaire. Documentez la raison et l’heure du rollback.

Sécurité et bonnes pratiques

• Utilisez TLS/SSL pour chiffrer la réplication (comme dans l’exemple).
• Restreignez l’utilisateur de réplication à l’accès nécessaire (REPLICATION SLAVE).
• Ne stockez pas les mots de passe en clair dans des scripts non protégés.
• Protégez les certificats (permissions 600) et conservez des sauvegardes hors-ligne.

Tests et critères d’acceptation (cas de test)

• TC1 — Initialisation : importer snapshot et démarrer le slave. Résultat attendu : Slave_IO_Running et Slave_SQL_Running = Yes.
• TC2 — Durabilité : insérer 1000 lignes sur le maître; vérifier qu’elles apparaissent sur l’esclave en moins de X secondes (dépend du réseau).
• TC3 — SSL : vérifier que Master_SSL_Allowed = Yes et que les chemins de certificats sont renseignés.

Exemples d’échec et alternatives

Quand la réplication peut échouer :

• Incompatibilité de versions MySQL entre maître et esclave.
• Problèmes de permission ou fichiers de certificat manquants.
• Conflits de données si des écritures locales existent sur l’esclave.

Alternatives :

• Utiliser une solution de réplication basée sur binlog centralisé (proxy) ou outils de synchronisation comme Percona XtraBackup pour restaurations initiales.

Fiche technique (faits clés)

• Port MySQL par défaut : 3306.
• Paramètre critique : server-id (doit être unique).
• Chemins typiques de certificats : /etc/mysql/newcerts/ca-cert.pem, client-cert.pem, client-key.pem.

Résumé

Vous avez configuré un esclave MySQL en :

1. modifiant my.cnf et redémarrant MySQL ;
2. créant et important la base exampledb ;
3. exécutant CHANGE MASTER TO … avec paramètres SSL ;
4. démarrant l’esclave et vérifiant SHOW SLAVE STATUS.

Liens

• MySQL: http://www.mysql.com/
• Debian: http://www.debian.org/