Réplication de bases de données MySQL/MariaDB

La réplication de base de données est une technique permettant d'avoir plusieurs copies d'une même base de données sur différents serveurs. Il est facile de mettre en place ce service 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.

Remarque : les manipulations sont identiques avec des serveurs MariaDB, à l'exception du fichier de configuration à modifier (/etc/mysql/mariadb.conf.d/50-server.cnf au lieu de /etc/mysql/mysql.conf.d/mysqld.cnf)

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

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

• log-bin : activation des journaux binaires. Possibilité de préciser un fichier pour stocker les journaux (facultatif).
• 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 maître et esclave, il ne doit avoir qu'un seul identifiant.
• log-basename : Nom des fichiers des journaux.
• binlog-format : format des journaux (statement, row ou mixed).
• 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

Plusieurs 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.
• 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 :

• 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