---
categories: databases
title: Howto réplication MySQL
...

Pour le guide d'installation et d'usage courant, voir [HowtoMySQL](/HowtoMySQL).

Pour la réplication synchrone avec Galera, voir [HowtoMySQL/Galera](/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](/HowtoMySQL#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 :

~~~{.ini}
[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](mariabackup#mise-en-place-dune-réplication-esclave-avec-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 :

~~~{.ini}
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](/HowtoMySQL#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_ :

~~~{.ini}
[mysqld]
replicate-ignore-db = mysql
~~~

Pour n'inclure que les requêtes concernant les bases _foo_ et _bar_ :

~~~{.ini}
[mysqld]
replicate-do-db = foo
replicate-do-db = bar
~~~

Pour n'inclure que les requêtes concernant les tables _foo.baz_ et _foo.qux_ :

~~~{.ini}
[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 :

~~~{.ini}
[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 :

~~~{.ini}
[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](Percona#pt-heartbeat)

## Contrôle intégrité d'une réplication

Déplacé sur la page de Percona pour [pt-table-checksum](Percona#pt-table-checksum) et [pt-table-sync](Percona#pt-table-sync)

## 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 "cdb0-sql41";
Query OK, 0 rows affected (2.01 sec)

mysql> RESET SLAVE ALL FOR CHANNEL "cdb0-sql41";
Query OK, 0 rows affected (0.02 sec)
~~~

## Erreurs de réplication

Déplacé sur la page des [erreurs](Replication/Troubleshooting)
