Login Logout

Howto réplication MySQL

Pour le guide d’installation et d’usage courant, voir HowtoMySQL.

Pour la réplication synchrone avec Galera, voir HowtoMySQL/Galera.

Préparation d’une réplication MASTER/SLAVE

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 le dump !

Il faut également :

  • autoriser la connexion du serveur MySQL SLAVE vers le serveur MASTER ;
  • activer les binlogs sur le serveur MASTER (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 MASTER : 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 SLAVE) :

mysql> SET sql_log_bin = 0;

Note : cela nécessite le droit MySQL SUPER

Configuration type pour activation réplication

  • La variable log_bin doit contenir le chemin ou l’on veux stocker les binlogs, si on veux les désactivés il faut utilisé la variable skip_log_bin ;
  • Positionner un server-id différent sur chaque serveur (ne pas utiliser 0) ;
  • binlog_format : voir la documentation sur les formats de binlogs pour plus de détails
  • gtid_domain_id : Si on fait une réplication simple en master -> slave, ou master <-> master entre 2 serveurs, gtid_domain_id peut avoir la même valeurs, si on fait du multi-master, ou une topologie de réplication plus complexes avec plussieurs réplications en chaine, il vaut mieux positionner une valeurs différentes, soit par serveurs, soit par grappes de serveurs.
  • gtid_strict_mode : applique un ordre sctict dans les événements dans les binlogs, par exemple on ne peux pas répliqué une séquence GTID inférieure à celui qui se trouve dans le binlog.
log_bin           = /var/log/mysql/mysql-bin.log
expire_logs_days  = 10
max_binlog_size   = 100M
binlog_format     = mixed
server-id         = 1
gtid_domain_id = 1
gtid_strict_mode = ON

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

tableau compatibilité
tableau compatibilité

Activation réplication MASTER/SLAVE avec binlogs (ancien)

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 SLAVE, exécuter :

mysql> CHANGE MASTER TO
   MASTER_HOST='192.168.0.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) :

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.00 sec)

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 MASTER/SLAVE avec GTID

ATTENTION : L’implémentation des GTID entre MySQL et MariaDB sont différentes et incompatibles entres-elles.

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-2

On peut donc mettre la valeur GTID “0-1-2” sur la variable gtid_slave_pos, puis démarrer la réplication avec un CHANGE MASTER TO, en positionnant la variable master_use_gtid sur slave_pos :

mysql> SET GLOBAL gtid_slave_pos = "0-1-2";
mysql> CHANGE MASTER TO
   MASTER_HOST='192.168.0.33',
   MASTER_USER='repl',
   MASTER_PASSWORD='PASSWORD',
   master_use_gtid=slave_pos;

Activation réplication MASTER/SLAVE 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 veux la basculer en mode GTID, on peut le faire de la façon suivante :

mysql> STOP SLAVE;

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

mysql> START SLAVE;

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 MASTER/MASTER

C’est une réplication MASTER/SLAVE des deux côtés.

Utiliser ce type de réplication implique :

  • Les INSERT ne sont pas immédiatement écrit car il y a un délai de quelques secondes. En cas, 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
  • 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 MASTER/SLAVE classique, soit avec un mysqldump –master-data, soit avec Mariabackup comme indiqué plus haut.
  • Une fois le MASTER/SLAVE synchronisé, sur le MASTER où l’on veut configurer un SLAVE, 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 SLAVE avec un CHANGE MASTER TO depuis le MASTER vers le SLAVE :
mysql> CHANGE MASTER TO
   MASTER_HOST='192.168.0.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