Howto réplication MySQL

Le principe de la réplication MySQL repose sur les binlogs qui contiennent les requêtes en écriture (INSERT, UPDATE, DELETE, etc.) d’un serveur qu’on nommera PRIMARY qui sont récupérés par un serveur qu’on appelera REPLICA et stockés dans des fichiers relay-binlogs puis rejoués en local.

Sur le serveur PRIMARY, on peut avoir des informations sur les binlogs :

MariaDB [(none)]> SHOW MASTER STATUS; select @@global.gtid_current_pos;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000447 | 83       |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

+---------------------------+
| @@global.gtid_current_pos |
+---------------------------+
| 0-1-519                   |
+---------------------------+
1 row in set (0.001 sec)

Sur le serveur REPLICA, on peut avoir différentes informations avec :

MariaDB [(none)]> SHOW REPLICA STATUS\G
MariaDB [(none)]> SHOW VARIABLES LIKE 'gtid%';

notamment :

  • des informations sur la récupération des binlogs du PRIMARY :
Master_Log_File: mysql-bin.000447
Read_Master_Log_Pos: 83
  • des informations sur le traitement des relay-binlogs :
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 87906677
  • la position « virtuelle » dans le traitement des binlogs :
Relay_Master_Log_File: mysql-bin.000446
Exec_Master_Log_Pos: 42
Gtid_IO_Pos: 0-1-513
gtid_slave_pos | 0-1-513
  • des informations sur le statut de la réplication :
Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Note : les termes MASTER et SLAVE sont progessivement remplacés par PRIMARY et REPLICA pour ne plus utiliser de références à l’esclavage

Préparation d’une réplication PRIMARY/REPLICA

Il faut :

  • soit disposer de deux serveurs MySQL inactifs avec un datadir identique,
  • soit réaliser un mysqldump --master-data sur le serveur où se trouve les données à conserver :
# mysqldump --master-data --all-databases --events > mysql.dump

--master-data ajoute un CHANGE MASTER TO dans le dump contenant les informations nécessaires à la réplication (nom de fichier et position). Dans certains cas, il peut être nécessaire de faire un FLUSH PRIVILEGES après l’injection du dump.

/!\ Cette option implique --lock-all-tables qui bloque toutes les tables pendant tout la durée dump (alors que sinon c’est locké base par base) !

Il faut également :

  • autoriser la connexion du serveur MySQL REPLICA vers le serveur PRIMARY ;
  • activer les binlogs sur le serveur PRIMARY (on conseille le type mixed en général) ;
  • positionner un server-id différent sur chaque serveur (ne pas utiliser 0) ;
  • créer un utilisateur dédié pour la réplication sur le serveur PRIMARY : GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'PASSWORD';.

Astuce très utile : pour effectuer des requêtes non prises en compte par la réplication, une astuce est d’utiliser interactivement SET sql_log_bin ce qui n’écrira pas les requêtes SQL suivantes dans le binlog du serveur (et elles ne seront donc pas répliquées au serveur REPLICA) :

mysql> SET sql_log_bin = 0;

Note : cela nécessite le droit MySQL SUPER

Configuration type pour activation réplication

Il faut donc activer les binlogs, a priori avec le format “mixed”. Voir https://wiki.evolix.org/HowtoMySQL#binlogs.

log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 10
max_binlog_size   = 100M
binlog_format     = mixed

Il faut donc aussi positionner un server-id différent sur chaque serveur (ne pas utiliser 0) :

server-id         = 1

Enfin, de façon facultative, on peut forcer le gtid_domain_id pour des cas particuliers (multi-master ou toplogie complexe) et_ gtid_strict_mode_ qui force un ordre strict des évènements dans les binlogs.

Activation réplication PRIMARY/REPLICA avec binlogs (ancienne méthode)

Il faut récupérer les informations MASTER_LOG_FILE et MASTER_LOG_POS :

  • soit sur l’un des deux serveurs inactifs avec SHOW MASTER STATUS (dans le cas de deux serveurs avec datadir identique),
  • soit récupérer l’information dans le mysqldump --master-data (avec la commande head par exemple).

Sur le serveur REPLICA, exécuter :

mysql> CHANGE MASTER TO
   MASTER_HOST='192.0.2.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   MASTER_LOG_FILE='mysql-bin.NNNNNN',
   MASTER_LOG_POS=NNN;

/!\ On recommande d’indiquer les directives MASTER_LOG_FILE et MASTER_LOG_POS pour éviter des problèmes

Puis démarrer la réplication sur le serveur B avec la commande : START SLAVE.

Désactivation

Pour supprimer toute trace de réplication (sauf si des infos sont en dur dans la configuration) :

MariaDB [(none)]> RESET SLAVE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> RESET SLAVE ALL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "";

Pour éviter que la réplication démarre automatiquement au démarrage, on ajoutera dans la configuration :

[mysqld]
skip-slave-start

(Re)injecter un dump sans écrire dans les binlogs

L’option --init-command permet de ne pas écrire dans les binlogs :

# mysql --init-command="SET SQL_LOG_BIN = 0;" -o mabase < mabase.sql

ou :

# zcat mabase.sql.gz | mysql --init-command="SET SQL_LOG_BIN = 0;"

Activation réplication PRIMARY/REPLICA avec GTID

Depuis plusieurs années, MySQL ajoute à chaque ligne de ses binlogs un identifiant unique appelé Global Transaction ID (GTID).

Note : attention, l’implémentation des GTID entre MySQL et MariaDB sont différentes et incompatibles entres-elles.

Exemple de GTID : 0-2-51987 qui a donc la forme suivante :

<domaine ID (souvent 0)>-<server-id>-<numéro d'écriture>

Le GTID est utile pour voir différentes informations :

  • gtid_current_pos : dernier GTID exécuté sur le serveur (pas toujours fiable)
  • gtid_binlog_pos : dernier GTID écrit dans les binlogs
  • gtid_slave_pos : dernier GTID appliqué via la réplication
MariaDB [(none)]> select @@gtid_current_pos, @@gtid_binlog_pos, @@gtid_slave_pos;
+--------------------+-------------------+------------------+
| @@gtid_current_pos | @@gtid_binlog_pos | @@gtid_slave_pos |
+--------------------+-------------------+------------------+
| 0-1-51989          | 0-2-51988         | 0-1-51989        |
+--------------------+-------------------+------------------+

Mise en place d’un réplica depuis un dump

Mysqldump avec l’option --master-data ou --dump-slave=1 donne la position du binlog, ou la position GTID en début de fichier de dump.

  • --master-data : On récupère la position actuelle d’écriture de binlogs (comme show master status).

  • --dump-slave=<valeur> : On récupère la position actuelle de réplication slave (comme show slave status) pour faire un autre réplica pour le même master, si la valeur de l’option est positionné sur 1, le mysqldump l’imprimera sous la forme d’une commande CHANGE MASTER, si la valeur est positionné sur 2, elle l’imprimera sous forme de commentaire.

  • Cette option activera --lock-all-tables, à moins que --single-transaction ne soit également spécifié,

Pour connaitre la valeur GTID avec le fichier binaire et sa position, si on fait un backup physique du master par exemple et qu’on fait un SHOW MASTER STATUS, il faut utiliser la fonction BINLOG_GTID_POS, comme ceci, si le fichier binaire est “master-bin.000001” et sa position “600” par exemple :

mysql> SELECT BINLOG_GTID_POS("master-bin.000001", 600);
0-1-42

Au final, on met donc la valeur GTID “0-1-42” sur la variable gtid_slave_pos, puis on démarre avec master_use_gtid=slave_pos :

MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "0-1-42";

MariaDB [(none)]> CHANGE MASTER TO
   MASTER_HOST='192.0.2.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   master_use_gtid=slave_pos;

Mise en place d’un réplica de zéro

Si l’on a deux bases créées récemment, avec un serveur REPLICA avec un gtid_slave_pos vide, on peut faire :

MariaDB [(none)]> select @@gtid_slave_pos;
+------------------+
| @@gtid_slave_pos |
+------------------+
|                  |
+------------------+

MariaDB [(none)]> CHANGE MASTER TO
   MASTER_HOST='192.0.2.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   master_use_gtid=slave_pos;

Cela récupèrera l’ensemble des binlogs du serveur PRIMARY depuis le début (sous réserve que les binlogs soient bien disponibles).

Attention, cela ne récupèrera PAS les données avant que les binlogs du serveur PRIMARY aient été activés.

Si il y a déjà eu une réplication sur le serveur REPLICA on pourra tout réinitialiser ainsi :

MariaDB [(none)]> STOP REPLICA;
MariaDB [(none)]> RESET REPLICA;
MariaDB [(none)]> RESET REPLICA ALL;
MariaDB [(none)]> SET GLOBAL gtid_slave_pos = "";

Activation réplication PRIMARY/REPLICA avec Mariabackup

Voir la doc de Mariabackup

On peut récupérer la position GTID lors d’un backup fait par Mariabackup dans le fichier xtrabackup_binlog_info

Switcher réplication “ancien mode” vers GTID

Si on a déjà une réplication existante et que l’on veut la basculer en mode GTID, on peut le faire de la façon suivante :

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO
   MASTER_HOST='192.0.2.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   master_use_gtid=current_pos;

mysql> START SLAVE;

Attention, il ne doit pas y avoir eu d’écriture en local depuis la coupure de la réplication, sinon current_pos aura la valeur de cette écriture et la réplication ne démarrera pas (il faudra forcer slave_pos) :

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-2-12, which is not in the master's binlog'

“slave_pos” vs “current_pos”

Quand doit-on utiliser master_use_gtid=slave_pos ou master_use_gtid=current_pos ?

En général on utilise master_use_gtid=slave_pos que ça soit en forçant slave_pos (cas d’un dump) ou lors d’une initialisation de zéro (avec slave_pos vide). master_use_gtid=current_pos peut-être utile seulement dans le cas où l’on veut reprendre une réplication interrompue récemment (et encore, il faut bien vérifier le contenu de gtid_current_pos).

Configuration avancée

https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html

Configuration de la réplication via fichier de configuration

La configuration d’une réplication via la commande CHANGE MASTER TO […] est persistente, elle est notamment conservée en cas de redémarrage de MySQL car conservée dans le fichier master.info situé par défaut dans le datadir (y compris le mot de passe en clair !). Nous conseillons cette méthode, mais on peut également configurer via la configuration de MySQL ainsi :

master-host = 192.0.2.33
master-user = repl
master-password = PASSWORD
#master-port = 3306
#master-connect-retry = 60
#master-info-file = master.info
#slave_compressed_protocol=1
#log-slave-updates

Note : En cas d’une bande passante réduite, l’option slave_compressed_protocol permet une compression des données côté MASTER et décompression des données côté SLAVE (cela consomme évidemment davantage de ressources CPU).

Configuration d’une réplication partielle

Une manière d’avoir une réplication peut être de ne pas écrire toutes les requêtes dans les binlogs sur le serveur MASTER via les options binlog_do_db/binlog_ignore_db mais ce n’est pas conseillé car les binlogs ont souvent d’autres utilités (vérifier les requêtes, ou servir pour d’autres serveurs SLAVE).

Une manière différente (ou complémentaire) est d’utiliser les directives replicate-do-db/replicate-ignore-db/replicate-do-table/replicate-ignore-table/replicate-wild-do-table/replicate-wild-ignore-table sur le serveur SLAVE.

/!\ Ces directives ne sont pas parfaites, notamment les requêtes « croisées » du type USE foo; UPDATE bar.baz SET […] ne seront pas comprises, ce qui peut poser des problèmes !

Pour ignorer les requêtes concernant la base mysql :

[mysqld]
replicate-ignore-db = mysql

Pour n’inclure que les requêtes concernant les bases foo et bar :

[mysqld]
replicate-do-db = foo
replicate-do-db = bar

Pour n’inclure que les requêtes concernant les tables foo.baz et foo.qux :

[mysqld]
replicate-do-db = foo
replicate-do-table = foo.baz
replicate-do-table = foo.qux

/!\ On conseille de toujours utiliser replicate-do-db en complément de replicate-do-table/replicate-wild-do-table sinon les requêtes non spécifiques aux tables ne sont pas filtrées (…par exemple les DROP DATABASE venant du serveur MASTER !!)

Les directives replicate-wild-do-table/replicate-wild-ignore-table permettent d’utiliser des expressions régulières avec % et _ (comme pour l’opérateur SQL LIKE), exemple :

[mysqld]
replicate-wild-do-table = mysql.%
replicate-wild-ignore-table = foo%.%

Activation d’une boucle de réplication PRIMARY/PRIMARY

C’est en fait un double réplication PRIMARY/REPLICA.

Utiliser ce type de réplication implique :

  • Les INSERT ne sont pas immédiatement écrit car il peut y avoir un délai de dixièmes de secondes voire davantage : donc il faut bannir un code qui ferait un INSERT puis un SELECT immédiat de la ligne insérée.
  • Ne pas utiliser la directive NOW() en SQL.

Étapes :

  • Positionner la directive auto-increment-increment = 10 sur chaque serveur (par exemple)
  • Positionner la directive auto-increment-offset avec une valeur numérique différente sur chaque serveur (par exemple 0, 1, 2 etc.)
  • Mettre en place une réplication PRIMARY/REPLICA classique, soit avec un mysqldump –master-data, soit avec Mariabackup comme indiqué plus haut.
  • Une fois le PRIMARY/REPLICA synchronisé, sur le PRIMARY où l’on veut configurer un REPLICA, vérifier que la variable gtid_slave_pos est bien vide :
mysql> show variables like 'gtid_slave_pos';
Empty set
  • Puis, mettre en place la partie REPLICA avec un CHANGE MASTER TO depuis le PRIMARY vers le REPLICA :
mysql> CHANGE MASTER TO
   MASTER_HOST='192.0.2.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   master_use_gtid=slave_pos;

Réplications en chaîne

La règle de base de la réplication MySQL est : un serveur SLAVE ne peut avoir qu’un seul MASTER.

Cela n’empêche pas d’avoir plusieurs serveurs SLAVE pour un serveur MASTER. Et les serveurs SLAVE peuvent également être MASTER de plusieurs serveurs SLAVES… ce qui permet de faire des chaînes complexes de réplications.

Exemple avec 3 serveurs MASTER/MASTER/MASTER :

Serveur A -> Serveur B -> Serveur C [-> Serveur A]

Exemple avec de nombreux serveurs :

Serveur A <-> Serveur B
 \                 \
  \--> Serveur C    \--> Serveur F
   \--> Serveur D    \--> Serveur G
    \--> Serveur E    \---> Serveur H
          \
           \--> Serveur I
            \--> Serveur J

Dans ces cas, il est important d’activer l’option log-slave-updates permettant de générer des binlogs à partir des données reçues via la réplication et permettre ainsi d’être MASTER et transmettre ces données à un autre serveur SLAVE :

[mysqld]
log-slave-updates

Note : On pourrait penser que log-slave-updates provoque une boucle dans une situation master-master. Mais MySQL est « intelligent », il va ignorer les requêtes de réplications qui contiennent son server-id. A → B (avec server-id de A) → A (ignoré).

Monitoring

Icinga/nagios

Pour surveiller que la réplication se fait bien et n’est pas en retard ou cassée par une erreur, on peut mettre en place un check NRPE mysql_slave.

Jusqu’à MariaDB 10.3 inclus (Debian 10), il faut donner le droit REPLICATION CLIENT a l’utilisateur MySQL nrpe :

GRANT REPLICATION CLIENT on *.* TO 'nrpe'@'localhost' IDENTIFIED BY 'PASSWORD';

À partir de MariaDB 10.5 (Debian 11), il faut changer pour le droit SLAVE MONITOR :

GRANT SLAVE MONITOR on *.* TO 'nrpe'@'localhost' IDENTIFIED BY 'PASSWORD';

pt-heartbeat

Déplacé sur la page de Percona Toolkit.

Contrôle intégrité d’une réplication

Déplacé sur la page de Percona Toolkit.

Actions sur les slaves en mode réplication par Channel

Faire un SHOW SLAVE STATUS\G pour connaitre le Channel_Name, puis faire les actions classiques, START, STOP, RESET.

Exemple :

mysql> STOP SLAVE FOR CHANNEL "Channel_Name";
Query OK, 0 rows affected (2.01 sec)

mysql> RESET SLAVE ALL FOR CHANNEL "Channel_Name";
Query OK, 0 rows affected (0.02 sec)

Réplication quand le primaire et le réplica ont des définitions ou taille de tables / colones différentes.

En règle générale, une réplication consiste a avoir des définitions de tables identiques entre le primaire et le réplica. Les tables sur le réplica n’ont pas besoin d’avoir exactement la même définition pour que la réplication ait lieu. Il peut y avoir un nombre de colones différentes ou de définitions de données différentes et, dans certains cas, la réplication peut toujours avoir lieu.

Il est possible dans certains cas de répliquer vers une réplique qui possède une colonne d’un type différent sur la réplique et la principale. Ce processus est appelé “attribute promotion” (vers un type plus grand) ou “attribute demotion” (vers un type plus petit).

Les conditions diffèrent selon que la réplication est basée sur les instructions (Statement) ou sur les lignes (Row). Pour rappel le mode “MIXED” utilise à la fois Statement et Row, voir la page Binlogs Format pour plus de détails.

Réplication basée sur les instructions (Statement)

En général, lorsque vous utilisez la réplication basée sur des instructions, si une instruction peut s’exécuter correctement sur la réplique, elle sera répliquée.

Si une définition de colonne est du même type ou d’un type plus grand sur la réplique que sur la principale, elle peut être répliquée avec succès. Par exemple, une colonne définie comme VARCHAR(10) sera répliquée avec succès sur une réplique avec une définition de VARCHAR(12).

La réplication vers une réplique où la colonne est définie comme plus petite que sur la principale peut également fonctionner.

Réplication basée sur les lignes (Row)

Lors de l’utilisation de la réplication basée sur les lignes, la valeur de la variable slave_type_conversions est importante.

La valeur par défaut de cette variable est vide, auquel cas MariaDB n’effectuera pas de promotion ou de rétrogradation d’attribut.

Si les définitions de colonne ne correspondent pas, la réplication s’arrêtera

Si la valeur est définie sur ALL_NON_LOSSY, la réplication sécurisée est autorisée. Si elle est également définie sur ALL_LOSSY, la réplication sera autorisée même en cas de perte de données.

On peut modifié la variable slave_type_conversions de manière perssistante dans la configuration de MariaDB comme ceci :

[mariadb]
slave_type_conversions='ALL_NON_LOSSY'

On peut également modifié cette valeurs dynamiquement comme ceci :

SET GLOBAL slave_type_conversions = 'ALL_NON_LOSSY';

Cette action nécessite le privilege SUPER.

Conversions prises en charge

Entre TINYINT, SMALLINT, MEDIUMINT, INT et BIGINT.

Si la conversion avec perte est prise en charge, la valeur de la valeur primaire sera convertie au maximum ou au minimum autorisé sur la réplique, les conversions sans perte nécessitant que la colonne de réplique soit suffisamment grande. Par exemple, SMALLINT UNSIGNED peut être converti en MEDIUMINT, mais pas SMALLINT SIGNED.

Pour plus de détails sur la réplication entres des définitions de tables différentes voir la documentation MariaDB

Erreurs de réplication

Déplacé sur la page des erreurs

FAQ

Compatibilité de réplication MariaDB entre des versions différentes :

tableau compatibilité
tableau compatibilité
  • On peut faire une réplication depuis un MASTER en MySQL 5.5 et un SLAVE en MariaDB 10.11, par contre il faut désactivé la vérification des checksums des binlog sur le SLAVE en MariaDB 10.11 en désactivant l’option slave_sql_verify_checksum :
[mysqld]
...
slave_sql_verify_checksum = 0