Réplication de bases de données MySQL

Il est facile de mettre en place facilement un système de réplication de bases de données sur MySQL entre deux serveurs Ubuntu.

Pré-requis : deux serveurs Ubuntu avec MySQL (paquet mysql-server). Un serveur sera le serveur maitre, sur lequel les données seront modifiées, et l'autre serveur sera le serveur esclave, dont la base de données sera modifiée automatiquement en fonction des requêtes sur le serveur maitre.

Réplication Uni-directionnelle

Etape 1 : autorisation des connexions réseaux et des utilisateurs

Sur les deux machines, les services MySQL doivent accepter les connexions réseaux (et non pas seulement les connexions locales). Cela se configure dans le fichier /etc/mysql/mysql.conf.d/mysqld.cnf avec l'option bind-address (par défaut à 127.0.0.1, restreignant ici les connexions à la machine locale).

Plusieurs possibiltés :

  • Commenter la ligne de l'option bind-address (les connexions venant de n'importe quelle machine seront acceptées) ;
  • Mettre 0.0.0.0 à la place de 127.0.0.1 (les connexions venant de n'importe quelle machine seront acceptées) ;
  • Mettre l'adresse de votre réseau à la place de 127.0.0.1 (les connexions venant de n'importe quelle machine de votre réseau seront acceptées).

N'oubliez pas de redémarrer le service mysql pour que la modification du fichier mysqld.cnf soit prise en compte

Vous devez ensuite créer un utilisateur MySQL ayant le droit REPLICATION SLAVE sur les bases de données devant être répliquées. N'oubliez pas de valider ces droits avec la commande MySQL FLUSH PRIVILEGES.

Etape 2 : configuration du serveur maitre

Trois instructions du fichier /etc/mysql/mysql.conf.d/mysqld.cnf permettent de configurer le serveur maitre :

  • server-id : identifiant du serveur, il s'agit d'un numéro unique dans l'ensemble des serveurs de réplication sur le réseau. Si le serveur est à la fois maitre et esclave, il ne doit avoir qu'un seul identifiant.
  • log_bin : fichier journal des requêtes SQL. Ce fichier est utilisé par l'esclave pour refaire les requpees SQL sur ses propres bases de données.
  • binlog_do_db : base de données à répliquer. Si plusieurs bases de données doivent être répliquées, vous devez utiliser plusieurs fois cette instruction.
Etape 3 : configuration du serveur esclave

Trois instructions du fichier /etc/mysql/mysql.conf.d/mysqld.cnf permettent également de configurer le serveur esclave :

  • server-id : identifiant du serveur, il s'agit d'un numéro unique dans l'ensemble des serveurs de réplication sur le réseau. Si le serveur est à la fois maitre et esclave, il ne doit avoir qu'un seul identifiant.
  • master_connect_retry : nombre de tentatives successives de connexion pour la réplication. Facultatif mais conseillé pour éviter les tentatives interrompues de connexion si le serveur n'est pas joignable.
  • replicate_do_db : base de données à répliquer. Si plusieurs bases de données doivent être répliquées, vous devez utiliser plusieurs fois cette instruction.
Etape 4 : extraction de la base de données du maitre et transfert sur le serveur esclave

Avant de lancer la réplication, les bases de données à répliquer doivent être identiques sur le client et le serveur :

  • Verrouiller les bases de données sur le serveur maitre (commande SQL FLUSH TABLES WITH READ LOCK;).
  • Extraire les bases de données sur le serveur maitre dans des fichiers SQL (avec la commande mysqldump).
  • Transférer les fichiers SQL sur le serveur esclave.
  • Importer les bases de données dans MySQL sur le serveur esclave (avec la commande mysql).
Etape 5 : lancement du processus de réplication
Sur le serveur maitre

Lancer la commande SQL SHOW MASTER STATUS; afin de récupérer le nom du fichier journal du service MySQL et la position à partir de laquelle le serveur esclave devra lire le fichier.

Sur le serveur esclave
  • Stopper le processus esclave (au cas où il soit lancé) avec la commande SQL STOP SLAVE;
  • Configurer le processus esclave avec la commande SQL suivante :
CHANGE MASTER TO  
MASTER_HOST='X.X.X.X', Adresse IP du serveur maitre
MASTER_USER='utilisateur', Utilisateur SQL ayant les droits de réplication sur le serveur maitre
MASTER_PASSWORD='motdepasse', Mot de passe de l'utilisateur SQL
MASTER_LOG_FILE='fichierjournal', Nom du fichier journal du serveur maitre
MASTER_LOG_POS='positionjournal';  Position à partir de laquelle lire le fichier journal
  • Lancer le processus esclave avec la commande SQL START SLAVE;
Sur le serveur maitre

Déverrouiler les bases de données avec la commande SQL UNLOCK TABLES;

Votre réplication de base de données est prête.

 

Réplication croisée

Configuration des serveurs

Afin de mettre en place une réplication croisée, les deux serveurs doivent être configurés à la fois en mode maitre et en mode esclave. Attention toutefois à ne donner qu'un seul identifiant (instruction server-id du fichier de configuration de MySQL) à chaque serveur.

Problématique des champs auto-incrémentés

Pour éviter des doublons dans les champs auto-incrémentés (en cas d'insertion simultanées de données sur les deux serveurs ou en cas de latence dans la communication entre les serveurs), il est possible d'utiliser deux instructions dans les fichiers de configuration des serveurs permettant de définir la valeur de départ et la valeur d'incrément de ces champs auto-incrémentés :

  • auto_increment_offset : valeur de départ des champs auto-incrémentés
  • auto_increment_increment : valeur d'incrément des champs auto-incrémentés

Par exemple, pour deux serveurs en réplication croisée :

  • Serveur 1 : départ à 10 et incrément de 2
  • Serveur 2 : départ à 11 et incrément de 2