Réplication maître-esclave MySQL sur la même machine

La réplication MySQL est un processus qui permet de copier automatiquement les données d'un serveur de base de données MySQL (le maître) vers un ou plusieurs serveurs de base de données MySQL (les esclaves). Il est généralement utilisé pour étendre l'accès en lecture sur plusieurs serveurs à des fins d'évolutivité, bien qu'il puisse également être utilisé à d'autres fins telles que le basculement ou l'analyse de données sur l'esclave afin de ne pas surcharger le maître.

La réplication maître-esclave étant une réplication unidirectionnelle (de maître à esclave), seule la base de données maître est utilisée pour les opérations d'écriture, tandis que les opérations de lecture peuvent être réparties sur plusieurs bases de données esclaves. Cela signifie que si la réplication maître-esclave est utilisée comme solution d'évolution horizontale, vous devez définir au moins deux sources de données, une pour les opérations d'écriture et la seconde pour les opérations de lecture.

Les développeurs MySQL travaillent généralement sur une seule machine et ont tendance à avoir tout leur environnement de développement sur cette machine, avec la logique qu'ils ne dépendent pas d'un réseau ou d'une connexion Internet. Si une réplication maître-esclave est nécessaire parce que, par exemple, ils doivent tester la réplication dans un environnement de développement avant de déployer des modifications ailleurs, ils doivent la créer sur la même machine. Bien que la configuration d'une seule instance MySQL soit assez simple, nous devons faire un effort supplémentaire pour en configurer une seconde, puis une réplication maître-esclave.

Pour ce tutoriel étape par étape, j'ai choisi Ubuntu Linux comme système d'exploitation hôte, et les commandes fournies sont pour ce système d'exploitation. Si vous souhaitez configurer votre réplication maître-esclave MySQL sur un autre système d'exploitation, vous devrez apporter des modifications à ses commandes spécifiques. Cependant, les principes généraux de configuration de la réplication maître-esclave MySQL sur la même machine sont les mêmes pour tous les systèmes d'exploitation.

Installation de la première instance MySQL

Si vous avez déjà une instance de base de données MySQL installée sur votre machine, vous pouvez ignorer cette étape.

Le moyen le plus simple d'installer MySQL sur Ubuntu consiste à exécuter la commande suivante à partir d'une invite de terminal :

 sudo apt-get install mysql-server

Au cours du processus d'installation, vous serez invité à définir un mot de passe pour l'utilisateur root MySQL.

Configurer mysqld_multi

Afin de gérer efficacement deux instances MySQL sur la même machine, nous devons utiliser mysqld_multi .

La première étape de la configuration de mysqld_multi est la création de deux groupes [mysqld] séparés dans le fichier my.cnf existant. L'emplacement par défaut du fichier my.cnf sur Ubuntu est /etc/mysql/ . Alors, ouvrez le fichier my.cnf avec votre éditeur de texte préféré et renommez le groupe [mysqld] existant en [mysqld1] . Ce groupe renommé sera utilisé pour la configuration de la première instance MySQL et sera également configuré en tant qu'instance maître. Comme dans la réplication maître-esclave MySQL, chaque instance doit avoir son propre server-id unique, ajoutez la ligne suivante dans le groupe [mysqld1] :

 server-id = 1

Comme nous avons besoin d'un groupe [mysqld] séparé pour la deuxième instance MySQL, copiez le groupe [mysqld1] avec toutes les configurations actuelles et collez-le ci-dessous dans le même fichier my.cnf . Maintenant, renommez le groupe copié en [mysqld2] et apportez les modifications suivantes à la configuration de l'esclave :

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

Pour configurer la deuxième instance MySQL en tant qu'esclave, définissez server-id sur 2, car il doit être différent de l'id de serveur du maître.

Étant donné que les deux instances s'exécuteront sur la même machine, définissez le port de la deuxième instance sur 3307 car il doit être différent du port utilisé pour la première instance, qui est 3306 par défaut.

Afin de permettre à cette deuxième instance d'utiliser les mêmes binaires MySQL, nous devons définir des valeurs différentes pour socket , pid-file , datadir et log_error .

Nous devons également activer relay-log afin d'utiliser la deuxième instance en tant qu'esclave (paramètres relay-log , relay-log-index et relay-log-info-file ), ainsi que pour définir master-info-file .

Enfin, afin de rendre l'instance esclave en lecture seule, le paramètre read_only est défini sur 1 . Vous devez être prudent avec cette option car elle n'empêche pas complètement les modifications sur l'esclave. Même lorsque read_only est défini sur 1 , les mises à jour ne seront autorisées que par les utilisateurs disposant du privilège SUPER . MySQL a récemment introduit le nouveau paramètre super_read_only pour empêcher les utilisateurs SUPER d'apporter des modifications. Cette option est disponible avec la version 5.7.8.

Outre les [mysqld1] et [mysqld2] , nous devons également ajouter un nouveau groupe [mysqld_multi] au fichier my.cnf :

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

Une fois que nous aurons installé la deuxième instance MySQL et que nous aurons démarré les deux, nous donnerons les privilèges appropriés à l'utilisateur multi_admin afin de pouvoir arrêter les instances MySQL.

Créer de nouveaux dossiers pour la deuxième instance MySQL

Dans l'étape précédente, nous avons préparé le fichier de configuration pour la deuxième instance MySQL. Dans ce fichier de configuration, deux nouveaux dossiers sont utilisés. Les commandes Linux suivantes doivent être utilisées pour créer ces dossiers avec les privilèges appropriés :

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

Paramètres de sécurité supplémentaires dans AppArmor

Dans certains environnements Linux, les paramètres de sécurité AppArmor sont nécessaires pour exécuter la deuxième instance MySQL. Au moins, ils sont requis sur Ubuntu.

Pour configurer correctement AppArmor, modifiez le fichier /etc/apparmor.d/usr.sbin.mysqld avec votre éditeur de texte préféré, ajoutez les lignes suivantes :

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

Après avoir enregistré le fichier, redémarrez la machine pour que ces modifications prennent effet.

Installation de la deuxième instance MySQL

Plusieurs approches différentes peuvent être suivies pour l'installation de la deuxième instance MySQL. L'approche présentée dans ce tutoriel utilise les mêmes binaires MySQL que la première, avec des fichiers de données séparés nécessaires pour la seconde installation.

Puisque nous avons déjà préparé le fichier de configuration et les dossiers nécessaires et les changements de sécurité dans les étapes précédentes, la dernière étape d'installation de la deuxième instance MySQL est l'initialisation du répertoire de données MySQL.

Exécutez la commande suivante afin d'initialiser le nouveau répertoire de données MySQL :

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

Une fois le répertoire de données MySQL initialisé, vous pouvez démarrer les deux instances MySQL à l'aide du service mysqld_multi :

 mysqld_multi start

Définissez le mot de passe root pour la deuxième instance MySQL en utilisant mysqladmin avec l'hôte et le port appropriés. Gardez à l'esprit que si l'hôte et le port ne sont pas spécifiés, mysqladmin se connectera à la première instance MySQL par défaut :

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

Dans l'exemple ci-dessus, j'ai défini le mot de passe sur "rootpwd", mais il est recommandé d'utiliser un mot de passe plus sécurisé.

Configuration supplémentaire de mysqld_multi

À la fin de la section "Configuration de mysqld_multi", j'ai écrit que nous donnerons les privilèges appropriés à l'utilisateur multi_admin plus tard, c'est donc le moment pour cela. Nous devons donner à cet utilisateur les privilèges appropriés dans les deux instances, alors commençons par nous connecter à la première instance :

 mysql --host=127.0.0.1 --port=3306 -uroot -p

Une fois connecté, exécutez les deux commandes suivantes :

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Quittez le client MySQL et connectez-vous à la deuxième instance :

 mysql --host=127.0.0.1 --port=3307 -uroot -p

Une fois connecté, exécutez les deux mêmes commandes que ci-dessus :

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

Quittez le client MySQL.

Démarrer les deux instances MySQL automatiquement au démarrage

La dernière étape de la configuration de mysqld_multi est l'installation du script de démarrage automatique dans le init.d .

Pour ce faire, créez un nouveau fichier nommé mysqld_multi dans /etc/init.d et donnez-lui les privilèges appropriés :

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

Ouvrez ce nouveau fichier avec votre éditeur de texte préféré et copiez le script suivant :

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

Ajoutez le service mysqld_multi aux niveaux d' runlevels par défaut avec la commande suivante :

 update-rc.d mysqld_multi defaults

Redémarrez votre machine et vérifiez que les deux instances MySQL sont en cours d'exécution à l'aide de la commande suivante :

 mysqld_multi report

Configuration de la réplication maître-esclave

Maintenant, lorsque nous avons deux instances MySQL en cours d'exécution sur la même machine, nous allons configurer la première instance en tant que maître et la seconde en tant qu'esclave.

Une partie de la configuration a déjà été effectuée dans le chapitre « Configuration de mysqld_multi ». La seule modification restante dans le fichier my.cnf consiste à définir la journalisation binaire sur le maître. Pour ce faire, modifiez le fichier my.cnf avec les modifications et ajouts suivants dans le groupe [mysqld1] :

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

Redémarrez l'instance MySQL maître pour que ces modifications prennent effet :

 mysqld_multi stop 1 mysqld_multi start 1

Pour que l'esclave puisse se connecter au maître avec les privilèges de réplication corrects, un nouvel utilisateur doit être créé sur le maître. Connectez-vous à l'instance maître à l'aide du client MySQL avec l'hôte et le port appropriés :

 mysql -uroot -p --host=127.0.0.1 --port=3306

Créez un nouvel utilisateur pour la réplication :

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Quittez le client MySQL.

Exécutez la commande suivante afin de créer un vidage des données de base :

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

Ici, nous utilisons l'option --master-data=2 afin d'avoir un commentaire contenant une instruction CHANGE MASTER dans le fichier de sauvegarde. Ce commentaire indique les coordonnées de réplication au moment de la sauvegarde, et nous aurons besoin de ces coordonnées plus tard pour la mise à jour des informations du maître dans l'instance esclave. Voici l'exemple de ce commentaire :

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

Importez le vidage que vous avez créé à l'étape précédente dans l'instance esclave :

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

Enfin, pour que l'instance esclave se connecte à l'instance maître, les informations maître sur l'esclave doivent être mises à jour avec les paramètres de connexion appropriés.

Connectez-vous à l'instance esclave à l'aide du client MySQL avec l'hôte et le port appropriés :

 mysql -uroot -p --host=127.0.0.1 --port=3307

Exécutez la commande suivante afin de mettre à jour les informations du maître (prenez les coordonnées de réplication du fichier de vidage replicationdump.sql , comme expliqué ci-dessus) :

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

Exécutez la commande suivante pour démarrer l'esclave :

 mysql> START SLAVE;

Exécutez la commande suivante afin de vérifier que la réplication est opérationnelle :

 mysql> SHOW SLAVE STATUS \G

Toutes nos félicitations. Votre réplication maître-esclave MySQL sur la même machine est maintenant configurée avec succès.

Emballer

Avoir une réplication maître-esclave configurée dans votre environnement de développement est utile si vous en avez besoin pour une solution évolutive dans l'environnement de production. De cette façon, vous aurez également des sources de données distinctes configurées pour les opérations d'écriture et de lecture afin que vous puissiez tester localement que tout fonctionne comme prévu avant un déploiement ultérieur.

De plus, vous pouvez avoir plusieurs instances esclaves configurées sur la même machine pour tester l'équilibreur de charge qui distribue les opérations de lecture à plusieurs esclaves. Dans ce cas, vous pouvez utiliser ce même manuel pour configurer d'autres instances esclaves en répétant toutes les mêmes étapes.