Howto réplication MySQL

Préparation d’une réplication MASTER/SLAVE
Activation réplication MASTER/SLAVE avec binlogs (ancien)
- Désactivation
(Re)injecter un dump sans écrire dans les binlogs
Activation réplication MASTER/SLAVE avec GTID
Configuration avancée
Monitoring
- Icinga/nagios
- pt-heartbeat
Contrôle intégrité d’une réplication
Actions sur les slaves en mode réplication par Channel
Erreurs de réplication

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

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 donne la position GTID en début de fichier de dump, en commentaires.

--master-data : On récupère la position actuelle d’écriture de binlogs (comme show master status).
--dump-slave : 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

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é par une erreur, on peut mettre en place un check nrpe mysql_slave

Il faut pour cela donner les droits “REPLICATION CLIENT” a l’utilisateur MySQL nrpe :

GRANT REPLICATION CLIENT 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)

Erreurs de réplication

Déplacé sur la page des erreurs