Réplication MySQL

NdM : 2025/01/17 - Modification des adresses GUA → ULA (En plus, je me disais : Est-ce que le mot de passe transite en clair entre les 2 serveurs.. (en passant par le VPN c'est plus sûr et plus stable).
NdM : 2025/01/30 - Script pour mettre à jour le serveur MySQL Slave sur Debian-FR > Replication server Data-Bases MySQL #18.

Je vais vous expliquer comment-faire une réplication MySQL de maître à esclave ; pour l'instant j'ai essayé que cette solution. Le maître est sur mon serveur au Canada dans une des VM d'un des frontaux load balancer. L'esclave est sur ma machine en locale chez moi ; un "nouveau" serveur que je viens d'installer qui me sert de routeur en mode DMZ connecté à ma "livebox" du ISP Orange.fr

Dans un futur proche ; sur le serveur de chez moi en France ; je vais configurer un Maître qui sera en mode réplication Maître (FR) à Maître (CA) vers le Canada et faire 1 esclave pour chacun des Maîtres du même "réseau".

En clair pour le futur :

• CA.LAB3W.SRV : LB1.DB1 (maître) → CA.LAB3W.SRV : LB2.DB2 (esclave)

                                            ↕

• FR.LAB3W.SRV : LB1.DB0 (maître) → FR.LAB3W.SRV : LB2.BDD (esclave)

---

Pour le moment, je réplique les données de "CA.LAB3W.SRV : LB1.DB1 (maître)" vers "FR.LAB3W.SRV : LB1.DB0 (esclave)".

• CA.LAB3W.SRV : LB1.DB1 (maître) fc00:5300:60:9389:15:1:a:db1 → FR.LAB3W.SRV : LB1.DB0 (esclave) fc01::10:126:42:db0

---

1ère étape :

Dans un premier temps, il faut configurer les ID des serveurs et les logs en binaire.

2ème étape :

Dans un deuxième temps, il faut ajouter un utilisateur pour la réplication.

3ème étape :

Dans un troisième temps, il faut récupérer les bases de données et les ajouter au serveur esclave.

4ème étape :

Dans un quatrième temps, il faut "configurer" l'esclave pour qu'il commence la réplication.

C'est partit….

1ère étape :

Configurer les ID des serveurs et les logs en binaire.

Sur le MySQL Maître : server-id = 1

Script avec 37 lignes

001root@MASTER:/etc/mysql # vim /etc/mysql/mariadb.conf.d/50-server.cnf
002
003# this is read by the standalone daemon and embedded servers
004[server]
005
006# this is only for the mysqld standalone daemon
007[mysqld]
008#
009# * Basic Settings
010#
011user                    = mysql
012pid-file                = /run/mysqld/mysqld.pid
013socket                  = /run/mysqld/mysqld.sock
014port                   = 3306
015basedir                 = /usr
016datadir                 = /var/lib/mysql
017tmpdir                  = /tmp
018lc-messages-dir         = /usr/share/mysql
019#skip-external-locking
020
021# Instead of skip-networking the default is now to listen only on
022# localhost which is more compatible and is not less secure.
023#bind-address            = 127.0.0.1
024#bind-address           = 10.101.150.100
025# not work
026#bind-address           = 10.101.150.100, fc00:5300:60:9389:15:1:a:db1 
027bind-address            = *
028
029# The following can be used as easy to replay backup logs or for replication.
030# note: if you are setting up a replication slave, see README.Debian about
031#       other settings you may need to change.
032
033server-id               = 1
034log_bin                 = /var/log/mysql/mysql-bin.log
035expire_logs_days        = 10
036#max_binlog_size        = 100M
037sync-binlog             = 1

Sur le MySQL Esclave : server-id = 2

Script avec 39 lignes

001root@SLAVE:/etc/mysql # vim /etc/mysql/mariadb.conf.d/50-server.cnf
002
003# this is read by the standalone daemon and embedded servers
004[server]
005
006# this is only for the mysqld standalone daemon
007[mysqld]
008#
009# * Basic Settings
010#
011user                    = mysql
012pid-file                = /run/mysqld/mysqld.pid
013socket                  = /run/mysqld/mysqld.sock
014port                   = 3306
015basedir                 = /usr
016datadir                 = /var/lib/mysql
017tmpdir                  = /tmp
018lc-messages-dir         = /usr/share/mysql
019#skip-external-locking
020
021# Instead of skip-networking the default is now to listen only on
022# localhost which is more compatible and is not less secure.
023#bind-address            = 127.0.0.1
024#bind-address           = 10.126.42.100
025# not work
026#bind-address           = 10.126.42.100, fc01::10:126:42:db0 
027bind-address            = *
028
029... 
030
031# The following can be used as easy to replay backup logs or for replication.
032# note: if you are setting up a replication slave, see README.Debian about
033#       other settings you may need to change.
034
035server-id              = 2
036log_bin                = /var/log/mysql/mysql-bin.log
037expire_logs_days        = 10
038#max_binlog_size        = 100M
039sync-binlog             = 1

2ème étape :

Ajouter un utilisateur pour la réplication.

Script avec 37 lignes

001root@MASTER:/etc/mysql # mysql -u root -h localhost -p
002Enter password:
003Welcome to the MariaDB monitor.  Commands end with ; or \g.
004Your MariaDB connection id is 3931802
005Server version: 10.3.39-MariaDB-0+deb10u2-log Debian 10
006
007Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
008
009Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
010
011MariaDB [(none)]> CREATE USER 'replicator'@'fc01::10:126:42:db0' IDENTIFIED BY 'SUPER_MOT_PASSE_SUPER_LONG_QUE_PERSONNE_NE_PEUT_RETENIR_DE_MEMOIRE';
012MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'fc01::10:126:42:db0';
013MariaDB [(none)]> USE mysql;
014MariaDB [mysql]> SELECT user,host FROM user;
015+------------------+---------------------+
016| user             | host                |
017+------------------+---------------------+
018| mysqlstats       | %                   |
019| root             | 127.0.0.1           |
020| replicator       | fc01::10:126:42:db0 |
021| root             | ::1                 |
022| debian-sys-maint | localhost           |
023+------------------+---------------------+
0245 rows in set (0,000 sec)
025
026MariaDB [mysql]> FLUSH PRIVILEGES;
027MariaDB [mysql]> FLUSH TABLES WITH READ LOCK;
028MariaDB [mysql]>
029MariaDB [mysql]> SHOW MASTER STATUS\G
030*************************** 1. row ***************************
031            File: mysql-bin.000027
032        Position: 490607
033    Binlog_Do_DB:
034Binlog_Ignore_DB:
0351 row in set (0,000 sec)
036
037MariaDB [mysql]>

Ci-dessus, je vois que le serveur maître utilise le fichier mysql-bin.000027 en position 490607.

3ème étape :

Récupérer les bases de données et les ajouter au serveur esclave.

Je vais créer un backup des bases de données, sans les tables "performance_schema".

Script avec 3 lignes

001root@MASTER:/etc/mysql # mdkir /var/backups/mysql/20250113
002root@MASTER:/etc/mysql # cd /var/backups/mysql/20250113
003root@MASTER:/var/backups/mysql/20250113 # for database in $(mysql -Bs -e "show databases" | grep -v "^performance_schema$" | grep -v "^information_schema$"); do mysqldump -u root -p'MY_ROOT_SUPER_MOT_PASSE_SUPER_LONG' --default-character-set=utf8mb4 --skip-opt --force --routines --add-drop-table --create-options --disable-keys --extended-insert --quick --set-charset $database --events --skip-lock-tables | nice pbzip2 > $database.sql.bz2; done;

Une fois les bases données récupérer vous pouvez de-locker les tables.

Script avec 1 ligne

001MariaDB [(none)]> UNLOCK TABLES;

À partir de là nous avons toutes les bases de données du serveur Maître.

Envoyez les (le répertoire de sauvegarde) sur le serveur esclave.

Script avec 1 ligne

001root@MASTER:/var/backups/mysql/20250113 # rsync -av -e "ssh -6" /var/backups/mysql/20250113/ root@[fc01::10:126:42:db0]:/var/backups/mysql/20250113/

Ajouter les bases de données au serveur esclave.

1. Avoir installé et configuré le serveur_id du MySQL esclave et les logs binaire.
2. Se positionner dans le répertoire des sauvegardes que nous venons d'envoyer sur le serveur esclave.
3. Démarrer le serveur esclave sans les permissions d'utilisateurs et créer les bases de données et importer les données.

Script avec 7 lignes

001root@SLAVE # cd /var/backups/mysql/20250113/
002root@SLAVE # mysqld --skip-grant &
003root@SLAVE # for i in * ; do mysql -e "CREATE DATABASE $(echo $i | cut -f1 -d".")" ; done
004root@SLAVE # for i in * ; do bzcat $i|mysql -f $(echo $i | cut -f1 -d".") ; done
005root@SLAVE # ps aux | grep mysql
006root@SLAVE # kill PID
007root@SLAVE # /etc/init.d/mariadb start

Tuer le processus "mysqld --skip-grant" et relancer MySQL normalement.

À partir de là, nous avons 2 MySQL qui fonctionnent.

4ème étape :

Configurer l'esclave pour qu'il commence la réplication.

1. Vérifier sur le serveur MySQL Maître son STATUS pour vérifier si le logfile et la position ont changés.
2. Demander au serveur MySQL esclave la synchronisation en configurant le logfile et la position.

Sur le MySQL Maître :

Script avec 20 lignes

001root@MASTER:/etc/mysql # mysql -u root -h localhost -p
002Enter password:
003Welcome to the MariaDB monitor.  Commands end with ; or \g.
004Your MariaDB connection id is 3931802
005Server version: 10.3.39-MariaDB-0+deb10u2-log Debian 10
006
007Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
008
009Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
010
011MariaDB [(none)]>
012MariaDB [(none)]> SHOW MASTER STATUS\G
013*************************** 1. row ***************************
014            File: mysql-bin.000027
015        Position: 490607
016    Binlog_Do_DB:
017Binlog_Ignore_DB:
0181 row in set (0,000 sec)
019
020MariaDB [(none)]>

Sur le MySQL Esclave :

Script avec 13 lignes

001root@SLAVE:/etc/mysql #  mysql -u root -h localhost -p
002Enter password:
003Welcome to the MariaDB monitor.  Commands end with ; or \g.
004Your MariaDB connection id is 1681
005Server version: 10.11.6-MariaDB-0+deb12u1-log Debian 12
006
007Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
008
009Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
010
011MariaDB [(none)]> STOP SLAVE;
012MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='fc00:5300:60:9389:15:1:a:db1', MASTER_USER='replicator', MASTER_PASSWORD='SUPER_MOT_PASSE_SUPER_LONG_QUE_PERSONNE_NE_PEUT_RETENIR_DE_MEMOIRE', MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=490607;
013MariaDB [(none)]> START SLAVE;

À partir de là, la réplication commence et doit fonctionner.

Vous pouvez vérifier en visualisant le status du serveur esclave.

Script avec 59 lignes

001MariaDB [(none)]> SHOW SLAVE STATUS\G
002*************************** 1. row ***************************
003                Slave_IO_State: Waiting for master to send event
004                   Master_Host: fc00:5300:60:9389:15:1:a:db1
005                   Master_User: replicator
006                   Master_Port: 3306
007                 Connect_Retry: 60
008               Master_Log_File: mysql-bin.000027
009           Read_Master_Log_Pos: 490607
010                Relay_Log_File: db0-relay-bin.000003
011                 Relay_Log_Pos: 319333
012         Relay_Master_Log_File: mysql-bin.000027
013              Slave_IO_Running: Yes
014             Slave_SQL_Running: Yes
015          Replicate_Rewrite_DB:
016               Replicate_Do_DB:
017           Replicate_Ignore_DB:
018            Replicate_Do_Table:
019        Replicate_Ignore_Table:
020       Replicate_Wild_Do_Table:
021   Replicate_Wild_Ignore_Table:
022                    Last_Errno: 0
023                    Last_Error:
024                  Skip_Counter: 0
025           Exec_Master_Log_Pos: 490607
026               Relay_Log_Space: 320507
027               Until_Condition: None
028                Until_Log_File:
029                 Until_Log_Pos: 0
030            Master_SSL_Allowed: No
031            Master_SSL_CA_File:
032            Master_SSL_CA_Path:
033               Master_SSL_Cert:
034             Master_SSL_Cipher:
035                Master_SSL_Key:
036         Seconds_Behind_Master: 0
037 Master_SSL_Verify_Server_Cert: No
038                 Last_IO_Errno: 0
039                 Last_IO_Error:
040                Last_SQL_Errno: 0
041                Last_SQL_Error:
042   Replicate_Ignore_Server_Ids:
043              Master_Server_Id: 1
044                Master_SSL_Crl:
045            Master_SSL_Crlpath:
046                    Using_Gtid: No
047                   Gtid_IO_Pos:
048       Replicate_Do_Domain_Ids:
049   Replicate_Ignore_Domain_Ids:
050                 Parallel_Mode: optimistic
051                     SQL_Delay: 0
052           SQL_Remaining_Delay: NULL
053       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
054              Slave_DDL_Groups: 0
055Slave_Non_Transactional_Groups: 1121
056    Slave_Transactional_Groups: 0
0571 row in set (0,000 sec)
058
059MariaDB [(none)]>

Vérifiez qu'il n'y ai pas d'erreur sur la ligne "Last_IO_Error" → exemple :

Script avec 1 ligne

001Last_IO_Error: error connecting to master 'replicator@2607:5300:60:9389:15:1:a:db1:3306' - retry-time: 60  maximum-retries: 100000  message: Access denied for user 'replicator'@'2a01:cb1d:12:1c00:beef::cafe' (using password: YES)

Voir les processus :

Script avec 11 lignes

001MariaDB [(none)]> SHOW PROCESSLIST;
002+---------+-------------+--------------------+-------------+-----------+--------+--------------------------------------------------------+------------------+----------+
003| Id      | User        | Host               | db          | Command   | Time   | State                                                  | Info             | Progress |
004+---------+-------------+--------------------+-------------+-----------+--------+--------------------------------------------------------+------------------+----------+
005|    1380 | system user |                    | NULL        | Slave_IO  | 100145 | Waiting for master to send event                       | NULL             |    0.000 |
006|    1381 | system user |                    | NULL        | Slave_SQL |    169 | Slave has read all relay log; waiting for more updates | NULL             |    0.000 |
007| 2083656 | root        | localhost          | NULL        | Query     |      0 | starting                                               | SHOW PROCESSLIST |    0.000 |
008+---------+-------------+--------------------+-------------+-----------+--------+--------------------------------------------------------+------------------+----------+
0093 rows in set (0,000 sec)
010
011MariaDB [(none)]>

Et voilà c'est finit. Si vous modifiez une donnée, un champ sur le maître elle sera automatiquement envoyé sur l'esclace.

Je suis en période de prise en main de la réplication MySQL. Comme je vous le disais en début d'article je souhaiterais faire 2 Maître to Maître pour pouvoir utiliser l'administration (le backoffice de mes sites web) sur un serveur en local et avoir un maître en ligne pour les insert en base de données des j'aime, des vus, des commentaires etc. venant des internautes et des "journalistes" accédant à l'admin des sites de l'extérieur.

J'essaierais cela bientôt.

---

Quelques commandes (pour changer de mot de passe, revoquer l'autorisation d'un utilisateur de "replication" etc) :

Script avec 15 lignes

001MariaDB [(none)]> SHOW BINARY LOGS;
002MariaDB [(none)]> SHOW DATABASES;
003MariaDB [(none)]> USE a_table;
004MariaDB [(none)]> SHOW TABLES;
005MariaDB [(none)]> SELECT user, host FROM user;
006MariaDB [(none)]> UPDATE `user` SET `Host` = 'fc01::10:126:42:db0', `Password` = PASSWORD('MOT_DE_PASSE') WHERE `user`.`User` = 'replicator_test';
007MariaDB [(none)]> FLUSH PRIVILEGES;
008MariaDB [(none)]> DROP user 'replicator_test'@'2a01:cb1d:5:af00:1ab3:126:42:db0';
009MariaDB [(none)]> DELETE FROM user WHERE user = 'replicator_test';
010MariaDB [(none)]> CREATE USER 'replicator_test'@'fc01::10:126:42:db0' IDENTIFIED BY 'SUPER_MOT_PASSE_SUPER_LONG_QUE_PERSONNE_NE_PEUT_RETENIR_DE_MEMOIRE';
011MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replicator_test'@'fc01::10:126:42:db0';
012MariaDB [(none)]> REVOKE REPLICATION SLAVE ON *.* FROM 'replicator_test'@'2a01:cb1d:5:af00:1ab3:126:42:db0';
013MariaDB [(none)]> STOP SLAVE;;
014MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='fc00:5300:60:9389:15:1:a:db1', MASTER_USER='replicator', MASTER_PASSWORD='SUPER_MOT_PASSE_SUPER_LONG_QUE_PERSONNE_NE_PEUT_RETENIR_DE_MEMOIRE', MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=550629;
015MariaDB [(none)]> START SLAVE;

Petite info ; si vous avez des problèmes de droit, par exemple : error connecting to master 'replicator_test@2607:5300:60:9389:15:1:a:db1:3306' - retry-time: 60 maximum-retries: 100000 message: Access denied for user 'replicator_test'@'db1.lab3w.com' (using password: YES)

Sans tout ré-installer, ou reprendre depuis le début ; pour exemple j'ai réussis à changer sur les 2 serveurs MASTER et SLAVE, l'hôte du slave (pour une adresse locale) et le mot de passe, j'ai rincé (FLUSH) les privilèges MySQL, j'ai revoqué (REVOKE) l'accès depuis l'IPv6 GUA (internet) et j'ai accordé (GRANT) celle de l'adresse IPv6 ULA (Locale). Et la replication a repris.

Autre info : si vous avez une erreur de réplication sur une donnée ou un champ ; vous pouvez essayer de STOP SLAVE; et de reprendre à la position du MASTER (CHANGE MASTER [..] MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=580831) et de START SLAVE;. Ca fonctionne logiquement.

---

Pour se faciliter la, les connexions à MySQL (de l'utilisateur dont vous avez confiance) ; vous pouvez créer ce fichier et protéger le fichier en lecture seulement pour l'utilisateur.

Script avec 17 lignes

001root@fr.lab3w.lb1.db0:~ # cat /root/.my.cnf
002
003[mysql]
004#user = replicator
005#password = SUPER_MOT_PASSE_SUPER_LONG_QUE_PERSONNE_NE_PEUT_RETENIR_DE_MEMOIRE
006user = root
007password = MY_ROOT_SUPER_MOT_PASSE_SUPER_LONG
008
009ssl-ca = /etc/mysql/ssl/lab3w-cacert.pem
010ssl-cert = /etc/mysql/ssl/zw3b.fr.cert.pem
011ssl-verify-server-cert = false
012
013skip-ssl = true
014
015[mysqldump]
016user = root
017password = MY_ROOT_SUPER_MOT_PASSE_SUPER_LONG

Script avec 1 ligne

001root@fr.lab3w.lb1.db0:~ # chmod 600 /root/.my.cnf

Bon courage,
Romain.

Note de Moi-même 2025/01/30 : Je me suis fais un petit script pour mes bases de données ; au cas où le serveur MySQL Slave ne serait pas à jour !

• Sur Debian-FR.org → Replication server Data-Bases MySQL #18 : https://www.debian-fr.org/t/replication-server-data-bases-mysql/90835/18
  • « Dump Databases MySQL » /root/db_dump_and_restore_of_slave.sh
  • « Updating Databases on MySQL Slave » /root/db_check_master_and_update_slave.sh

Sources des informations pour la réplication MySQL :

• Créer un serveur MySQL de réplication (slave) des données existantes
• Replication Master/Slave temps réel MySQL
• Avantages et inconvénients des types de réplication MySQL et comment les exécuter dans la cloud
• Réparer un MySQL / MariaDB en erreur InnoDB
• Xtrabackup : Percona XtraBackup is an high performance, low-profile and non-blocking open-source backup utility for InnoDB and XtraDB databases.

---

---

Keywords :

mysql mysqld maria-db bases de données databases تكرار قواعد البيانات бази даних тиражування bases de datos replicación 데이터베이스 복제 数据库 复制 Datenbanken Replikation

---