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).

/!\ 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 d’une réplication MASTER/SLAVE avec la position des binlogs (ancien mode)

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;

/!\ Bien que non obligatoire, on recommande de toujours 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.

Enfin, exécuter SHOW SLAVE STATUS pour vérifier le bon fonctionnement.

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

Réinjecté un dump sans écrire dans les binlogs

Si l’on veux injecter un dump dans une base mysql, ou une instance complète, sans écrire dans les binlogs, on peut utiliser l’option --init-command qui initialise une requêtes msyql avant chaque requête du dump :

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

Si dump compréssé :

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

Activation d’une 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 utilisé 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émarré 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 d’une réplication MASTER/SLAVE avec Mariabackup

Voir la doc de Mariabackup ici : https://wiki.evolix.org/HowtoMySQL/mariabackup#mise-en-place-dune-r%C3%A9plication-esclave-avec-mariabackup

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

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

Lorsque l’on veux mettre en place une boucle de réplication MASTER/SLAVE des deux côtés, on commence a mettre en place une réplication MASTER/SLAVE classique, soit vaec un mysqldump –master-data, soit avec Mariabackup comme indiqué plus haut.

Une fois le MASTER/SLAVE synchronisé, sur le MASTER où l’on veux configuré un SLAVE, on vérifie que la variable gtid_slave_pos soit bien vide :

mysql> show variable like 'gtid_slave_pos';
Empty set

Alors on peut 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;

Switcher un slave avec une réplication “ancien mode” vers la réplication 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%.%

Réplication MASTER/MASTER

Pour une réplication MASTER/MASTER, il faut simplement activer deux réplications MASTER/SLAVE entre les deux serveurs concernés.

On conseille également de :

  • 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.)

Il est important de savoir qu’utiliser une réplication MySQL MASTER/MASTER implique :

  • de ne pas considérer un INSERT comme immédiatement écrit, il pourrait y avoir un délai de quelques secondes (il faut donc bannir un code qui ferait un INSERT puis un SELECT immédiat de la ligne insérée)
  • d’éviter la directive NOW() dans ses requêtes SQL (valable aussi dans le cas MASTER/SLAVE)

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';

Munin

Il est aussi intéressant d’ajouter certains graphes à munin pour suivre la application dans le temps.

ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_bin_relay_log'
ln -s '/usr/share/munin/plugins/mysql_' '/etc/munin/plugins/mysql_replication'

pt-heartbeat

Dans le paquet perconal-toolkit on trouvera un outil pour mesurer la latence dans la réplication d’une meilleure façon que nativement.
L’astuce consiste à insérer une valeur datée de l’heure actuelle (timestamp) sur le master et comparer le timestamp avec la date du slave.

# mysql -e "CREATE DATABASE percona;"
# mysql -e "GRANT ALL PRIVILEGES ON \`percona\`.* TO 'percona'@'%' IDENTIFIED BY 'password';"
# mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'percona'@'%';"
# adduser --disabled-password percona
# vim /home/percona/.my.cnf

[client]
user = percona
password = password

# chmod 600 /home/percona/.my.cnf
# chown percona: /home/percona/.my.cnf
# pt-heartbeat --defaults-file /home/percona/.my.cnf --create-table --database percona --table heartbeat --update

Le lancer en démon :

# pt-heartbeat --defaults-file /home/percona/.my.cnf --create-table --database percona --table heartbeat --update --daemonize

Le mettre dans une unité systemd /etc/systemd/system/pt-heartbeat.service :

[Unit]
Description=Check slave lag.
After=network.target mysql.service

[Service]
User=percona
ExecStart=/usr/bin/pt-heartbeat --defaults-file /home/percona/.my.cnf --create-table --database percona --table heartbeat --update
Type=simple
Restart=always

[Install]
WantedBy=default.target

Consulter la latence sur le slave :

# pt-heartbeat -defaults-file /home/percona/.my.cnf --create-table --database percona --table heartbeat --check

On pourra ensuite surveiller en temps réel la latence ou écrire un cron de surveillance, voir un check Nagios.

# pt-heartbeat -defaults-file /home/percona/.my.cnf --create-table --database percona --table heartbeat --monitor

Résolution des erreurs lors de la réplication

On vérifie les erreurs avec les commandes SHOW SLAVE STATUS et SHOW MASTER STATUS.

En cas d’erreur, il faut « simplement » résoudre l’erreur, puis relancer la réplication avec la commande START SLAVE. Voici quelques erreurs possibles :

Zapper l’erreur en cours

On peut faire manuellement :

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Fichier de clé incorrect

Incorrect key file for table './base/table.MYI'; try to repair it

Il faut réparer la table concernée.

Doublon sur clé unique

Duplicate entry 'NNNNNN' for key N

Une solution peut être de supprimer la ligne concernée (ou de zapper l’erreur).

Beaucoup d’erreurs à ignorer

Si pour une raison ou un autre, on a plein de DUPLICATE ENTRY mais que l’est sûr de vouloir les ignorer, on peut faire cela en redémarrant MySQL avec le paramètre : slave-skip-errors = 1062 ; on peut faire également cela avec d’autres types d’erreurs. Malheureusement, il faut forcément redémarrer MySQL car cette commande ne se fait pas à chaud : http://bugs.mysql.com/bug.php?id=35611

On peut également avoir d’autres erreurs, par exemple Could not execute Delete_rows event on table foo.bar; Can’t find record in ‘bar’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log […] et on mettre cette fois slave-skip-errors = 1032

Si plusieurs types d’erreur à ignorer : slave-skip-errors = 1032,1062

L’inconvénient est qu’il faut redémarrer MySQL. Pour éviter cela on peut automatiser le zap de l’erreur.

Exemple avec l’erreur 1062 :

# while true; do while mysql -e "show slave status" | grep '1062.Error.*REPORT'; \
 do mysql -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;"; done ; sleep 1; done

Exemple avec l’erreur 1032 (« Could not execute Delete_rows event ») :

# while true; do while mysql -e "show slave status" | grep 'Error_code: 1032'; \
 do mysql -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;"; done ; sleep 1; done

On peut également utilisé un script Shell plus évolué qui prendre les motifs à ignorer dans un fichier error.txt (expressions rationnelles étendues, compatibles grep -E) et qui proposera de zapper manuellement (ou pas) si l’erreur ne correspondant aux motifs :

#!/bin/sh

# File containing error messages to skip (one per line).
error_messages="errors.txt"

# Sleep interval between 2 check.
sleep_interval="1"

# Exit when Seconds_Behind_Master reached 0.
exit_when_uptodate="false"

# Options to pass to mysql.
#mysql_opt="-P 3307"

# File to log skipped queries to (leave empty for no logs).
log_file=""

mysql_skip_error() {
    error="$1"

    printf "Skiping: $error\n"
    mysql $mysql_opt -e 'SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;'

    [ -n "$log_file" ] && echo "$error" >>"$log_file"
}

while true; do
    slave_status="$(mysql $mysql_opt -e 'SHOW SLAVE STATUS\G')"
    seconds_behind_master=$(echo "$slave_status" |grep 'Seconds_Behind_Master: ' |awk -F ' ' '{print $2}')
    last_SQL_error="$(echo "$slave_status" |grep 'Last_SQL_Error: ' |sed 's/^.\+Last_SQL_Error: //')"

    if $exit_when_uptodate && [ "$seconds_behind_master" = "0" ]; then
        printf 'Replication is up to date!\n'
        exit 0

    elif [ -z "$last_SQL_error" ]; then
        sleep $sleep_interval

    elif echo "$last_SQL_error" |grep -q -f $error_messages; then
        mysql_skip_error "$last_SQL_error"

    else
        printf "Current SQL error doesn't match the pattern:\n"
        printf "$last_SQL_error\n"
        printf "Skip it? [y/N]: "
        read reply
        if [ "$reply" = "y" ] || [ "$reply" = "Y" ]; then
            mysql_skip_error $last_SQL_error
        fi
    fi
done

Récupération de position impossible

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)

Cela signifie que la position indiquée sur le binlog du master est impossible à récupérer. On peut le vérifier avec une commande du type mysqlbinlog mysqld-bin.00123 --start-position=251 sur le master.

Si l’on constate que le binlog est corrompu avec des erreurs du type ERROR: Error in Log_event::read_log_event(): ‘read error’ # Warning: this binlog is either in use or was not closed properly. ou ERROR: Error in Log_event::read_log_event(): ‘Event too small’, data_len: 0, event_type: 0 l’idée est d’identifier les requêtes non jouées sur le slave dans le binlog corrompu (voir le Relay_Master_Log_File via SHOW SLAVE STATUS) et de les rejouer (cf HowtoMySQL#Replay) puis de passer au binlog suivant via une commande du type CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000124' , MASTER_LOG_POS=106; START SLAVE; (la position à indiquer est souvent 106, cf mysqlbinlog mysql-bin.000124).

Si l’on juge cela non nécessaire (données non critiques), on pourra bien sûr passer directement au binlog suivant en ignorant les requêtes du binlog corrompu. Bien sûr, suite à ces manipulations risquées, on vérifiera ensuite la cohérence de la base de données répliquée (COUNT(*) ou outils plus avancés).

Could not parse relay log event entry

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log),
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code.
If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Souvent un binlog corrompu, voir le Relay_Master_Log_File SHOW SLAVE STATUS.

Note: Jusqu’à MySQL <= 5.1 au moins, changer la position dans un Relay_log avec un CHANGE MASTER TO ne marche pas. Voir Changement de la position dans un Relay_log.

Error 1594 - Relay log read failure - Could not parse relay log event entry

On peut changer la position du Relay_log, sur le serveur slave, il faut bien identifier sur quel Relay_Master_Log_File et a quelle position du Exec_Master_Log_Pos se trouve le slave.

Il faut, avant toute chose, faire un SHOW SLAVE STATUS\G (copier la sortie complète dans un fichier à part, pour mémoire) pour voir les valeurs de Relay_Master_Log_File et Exec_Master_Log_Pos :

MariaDB [(none)]> SHOW SLAVE STATUS\G

************************* 1. row ***************************

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.194.74
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001274
          Read_Master_Log_Pos: 1045327404
               Relay_Log_File: 3_dbbackup.003821
                Relay_Log_Pos: 617884398
        Relay_Master_Log_File: mysql-bin.001273
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 617884110
              Relay_Log_Space: 3192816253
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 13
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative

Il faut donc stopper le slave :

MariaDB [(none)]> STOP SLAVE;

Ensuite faire un CHANGE MASTER TO en indiquant le Relay_Master_Log_File et la position du Exec_Master_Log_Pos comme ceci dans l’exemple ci-dessus :

MariaDB [(none)]> CHANGE MASTER TO master_log_file='mysql-bin.001273', master_log_pos=617884110;

MariaDB [(none)]> START SLAVE;

Normalement a ce stade là, la réplication continue à la position indiquée. Il se peut qu’il y ait des Duplicate Entry, qu’il faut alors étudier de près pour envisager de les sauter.

Erreur fatale à la lecture du binlog

Erreur : Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'

On obtient apparemment cela dans différents cas.

Réinitialiser la réplication

Dans certains cas exceptionnels, une solution radicale est de réinitialiser la réplication avec un STOP SLAVE; RESET SLAVE; START SLAVE; Attention, cela doit être fait dans de très rares cas maîtrisés (attention notamment aux conflits DUPLICATE ENTRY que cela risque de provoquer).

Status OK, mais pas de réplication

Si un SHOW SLAVE STATUS ne retourne pas d’erreur mais que la réplication ne se fait pas, les logs du slave peuvent contenir une erreur du type :

[Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.003357' at position 389449
[Note] Slave: received end packet from server, apparent master shutdown:

Il se peut que le master se réplique sur 2 slaves ayant un server-id identique !

Erreur de checksum fatale

Si vous rencontrez une erreur du type :

Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.015405' at 4, the last event read from 'mysql-bin.015405' at 4, the last byte read from 'mysql-bin.015405' at 256.'

Il est possible que l’erreur soit dûe au fait le master et le slave n’ont pas la même version.

ERROR 1201 lors de l’injection du dump

Si lors de l’injection du dump sur le slave cette erreur apparaît :

ERROR 1201 (HY000) at line 22: Could not initialize master info structure

Il faut supprimer toute traces des anciennes réplications avec :

RESET SLAVE;

Et ensuite ré-injecter le dump.

Changement de la position dans un Relay_log

À faire uniquement si en tentant de changer la position d’un Relay_log sur un slave, vous obtenez cette erreur :

Error initializing relay log position: Could not find target log during
relay log initialization

Il faut alors stopper le processus slave de réplication :

mysql> STOP SLAVE;

Puis éditer (en gardant une sauvegarde) le fichier ${datadir}/relay-log.info. La première ligne correspond au Relay_Log_File, la seconde au Relay_Log_Pos. Redémarrer MySQL.

Ré-intégré dans la réplication une base qui aurait été exclu.

Dans cet exemple, la base avait été exclu de la réplication, avec replicate-ignore-db, les lectures des requêtes de la base est ignoré sur le slave, mais le master continue d’écrire les requêtes de la base dans les binlogs.

1 - On enlève l’exclusion de la base dans le fichier de configuration :

#replicate-ignore-db = foo

2 - On fait un dump de cette base sur le master :

# mysqldump foo > foo.sql 

3- On ré-injecte ce dump sur le slave :

# mysql -o foo < foo.sql

4- On redémarre MySQL (ou l’instance) sur le slave, pour qu’il relise le fichier de configuration et ignore l’exclusion de la base sur le slave :

# systemctl restart mysql.service

S’il s’agit d’une instance, exemple si l’instance se trouve sur le port 3307 :

# mysqladmin -P 3307 shutdown
# mysqld_multi start 1

En cas de réplication master - master, avec un slave de chaque côté, il faut le faire des deux côtés.

Il existe d’autres méthodes, pour faire cela, celle-ci est la plus simple et elle a l’avantage de gérés les locks des tables, on ne doit pas le faire à la main.

Contrôle de l’intégrité de la réplication

pt-table-checksum

https://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html

C’est un outil de Percona intégré dans son toolkit. (Package Debian percona-toolkit disponible à partir de Wheezy).

L’outil vérifie l’intégrité de la réplication en effectuant des requêtes de checksum (crc32 par défaut) sur le master, puis les requêtes sont joués sur les slaves permettant de trouver des différences.

La méthode la plus simple pour l’utiliser est d’autoriser le master à se connecter au slave (authentification MySQL). Ainsi, il s’occupe lui-même de faire le nécessaire pour identifier les erreurs. Il suffira donc de lancer la commande sans argument pour qu’il identifie les incohérences. On pourra réaliser un cron avec l’argument -q qui ne fait remonter que les erreurs.

MAILTO=jdoe@example.com
42 9 * * 7 pt-table-checksum -q

Si on veut afficher seulement les différences on peut utiliser l’option --replicate-check-only exemple avec un pt-table-checksum sur une base en particulier:

# pt-table-checksum --replicate-check-only --databases foo

pt-table-sync

Si pt-table-checksum vous a remonté des incohérences, vous pouvez avec cet outil les corriger. Cela va identifier les différences et les corriger avec un REPLACE sur le master (qui sera donc répliqué sur le slave), garantissant la cohérence des données.

Exemple :

# pt-table-sync --print --replicate percona.checksums localhost
# pt-table-sync --execute --replicate percona.checksums localhost

En cas de Can't make changes on the master because no unique index exists. On peut synchroniser directement les différences sur le slave depuis le master.

# pt-table-sync --execute --no-check-slave localhost slave

Erreur “CRC32 never needs BIT_XOR optimization at /usr/bin/pt-table-*"

Avec MariaDB 10.3 il y a un souci avec la version du paquet debian de percona-toolkit, si vous avez une erreur de “CRC32 never needs BIT_XOR optimization” il faut passer en version MD5 comme ceci :

# pt-table-checksum -q --function MD5

Lire les erreurs de réplication, pour une réplication par Channel, ou Group réplication

Dans le cas d’une réplication par channel, ou Group Réplication, la sortie de `SHOW SLAVE STATUS’ indique sur quels Channel il y a une erreur, mais sans forcément indiquer de manière explicite l’erreur en question.

Exemple :

mysql> SHOW SLAVE STATUS\G

.....

 Exec_Master_Log_Pos: 18089090
              Relay_Log_Space: 1104382466
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1064
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000807, end_log_pos 18089265. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 62
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 200703 10:56:11
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: su6-sql2
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 

On vois que la réplication sur le Channel nommée su6-sql2 est en erreur, mais on ne sait pas sur quelle requête.

Le message d’erreur suggère de regarder les logs d’erreurs dans la table performance_schema.replication_applier_status_by_worker.

On peut le faire comme ceci, en indiquant le Channel en erreur, et le numéro du Worker, comme indiqué dans le message d’erreur précédent:

Exemple :

mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker WHERE WORKER_ID=1 AND CHANNEL_NAME='su6-sql2'\G;

*************************** 1. row ***************************
                                           CHANNEL_NAME: su6-sql2
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1064
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000807, end_log_pos 18089265; Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1' on query. Default database: 'DB_content_cfbe'. Query: 'delete from toppops_cfbe_null_20200702_104153_V220 where rank=1'
                                   LAST_ERROR_TIMESTAMP: 2020-07-03 10:56:11.801803
                               LAST_APPLIED_TRANSACTION: 
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: 
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.01 sec)

ERROR: 
No query specified

Comme ça on peut voir l’erreur explicite :

Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1' at line 1' on query. Default database: 'DB_content_cfbe'. Query: 'delete from toppops_cfbe_null_20200702_104153_V220 where rank=1'

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)