Réplication MySQL 5.0 - Mise en œuvre pratique

Introduction

Cette note technique présente une mise en œuvre pratique de la réplication avec MySQL 5.0. Les commandes d’administration et de monitoring de la réplication seront balayées à travers cet exemple pratique.

Architecture demise en œuvre

L’architecture de réplication mise en œuvre dans cette note technique est représentée ci-dessous :

Dans l’architecture, DBA_T1_MYS est le serveur primaire et un système de réplication est mis en œuvre pour la base cgcam uniquement. La base cgcamidb (architecture innoDb) ne sera pas répliquée.

Les règles de réplications sont les suivantes :

toutes les tables t_adm% de la base primaire cgcam (DBA_T1_MYS) sont répliquées vers la base cgcam du serveur DBA_T2_MYS (port 40103)
toutes les tables t_si% et t_asi% de la base primaire cgcam (DBA_T1_MYS) sont répliquées vers la base cgcam du serveur DBA_T3_MYS (port 40104).

Dans cette architecture technique, toutes les bases de données sont en mode MyISAM pour le mode de stockage.

Les logs binaires du serveur master

Généralités et paramétrage

En vue de mettre en œuvre une réplication, le serveur master (DBA_T1_MYS) doit conserver des logs binaires des mises à jour. Un fichier d’index permet de gérer la rotation des fichiers binaires de log du serveur master.

Les fichiers binaires de log comportent uniquement les commandes qui mettent à jour effectivement des données. Ainsi, les commandes UPDATE ou DELETE qui ne mettent à jour aucune donnée ne sont pas transcrites dans les fichiers binaires de log. Même les commandes UPDATE qui écrasent la valeur d’une colonne avec la même valeur ne sont pas transcrites dans les fichiers binaires de log.

Dans la mise en œuvre technique de cet exemple, le serveur DBA_T1_MYS démarre en prenant en compte les paramètres de configuration dans le fichier DBA_T1_MYS.cfg. Dans le fichier de configuration DBA_T1_MYS.cfg sont spécifiés tous les paramètres pour la mise en route des fichiers binaires de log pour le serveur master :


`log-bin`	Chemin et préfixe des fichiers binaires de log, par défaut les fichiers binaires de log sont nomenclaturés par le nom du host suivi de `–bin`. Dans la normalisation, le répertoire des logs binaires d’un serveur MySQL est identifié par la variable `$BINLOG` et a pour valeur `/<appname>/mysql/<servername>/binlogs`.
`log-bin-index`	Chemin et nomenclature du fichier d’index des logs binaires. Dans la normalisation, le fichier d’index des logs binaires d’un serveur MySQL est nommé `blog_<servername>.index` et est installé dans le répertoire `$BINLOG` (`/<appname>/mysql/<servername>/binlogs`).
`max_binlog_size`	Taille maximale des fichiers binaires de log. Une transaction n’est jamais répartie sur plusieurs fichiers binaires de log, c’est pourquoi un fichier binaire de log peut dépasser le paramètre `max_binlog_size`.
`binlog_cache_size`	Pour les tables transactionnelles (InnoDB), toutes les mises à jour sont mises en cache jusqu’à la commande `COMMIT`. Chaque thread alloue un buffer de la taille de `binlog_cache_size` pour les requêtes. Si la requête est plus volumineuse que le paramètre `binlog_cache_size`, le thread ouvre un fichier temporaire pour stocker la transaction. Ce dernier fichier est détruit une fois la transaction validée.
`binlog-do-db`	Indique au serveur de tracer dans les fichiers binaires de log les mises à jour uniquement si la base de données courante est la base de données spécifiée par le paramètre `binlog-do-db` (exemple : `binlog-do-db=cgcam`). Si `binlog-do-db=sales`, dans ce cas si l’on écrit : `USE prices ; UPDATE sales.january set amount=amount + 1000` comme la base de données courante est `prices` et non `sales`, cette commande SQL n’est pas transcrite dans le log binaire !
`binlog-ignore-db`	Indique au serveur d’ignorer dansles fichiers binaires de log les mises à jour uniquement si la base de données courante est la base de données spécifiée par le paramètre `binlog-ignore-db` (exemple : `binlog-ignore-db=cgcam`). Si `binlog-ignore-db=sales`, dans ce cas si l’on écrit : `USE prices ; UPDATE sales.january set amount=amount + 1000` comme la base de données courante est `prices` et non `sales`, cette commande SQL est transcrite dans le log binaire !

Configuration du serveur DBA_T1_MYS : fichier $CFG/DBA_T1_MYS.cnf

/dba/mysql/DBA_T1_MYS/binlogs/blog_DBA_T1_MYS

...
log-bin = /dba/mysql/DBA_T1_MYS/binlogs/blog_DBA_T1_MYS
log-bin-index = /dba/mysql/DBA_T1_MYS/binlogs/blog_DBA_T1_MYS.index
binlog-do-db=cgcam
binlog-ignore-db=cgcamidb,mysql
max_binlog_size=10M
...

Commandes d’administration des fichiers binaires de log

Liste des fichiers binaires de log : SHOW MASTER LOGS

La commande SHOW MASTER LOGS liste les fichiers binaires de log :

mysql-DBA_T1_MYS > show master logs;

+------------------------+
| Log_name               |
+------------------------+
| blog_DBA_T1_MYS.000001 |
| blog_DBA_T1_MYS.000002 |
| blog_DBA_T1_MYS.000003 |
| blog_DBA_T1_MYS.000004 |
| blog_DBA_T1_MYS.000005 |
| blog_DBA_T1_MYS.000006 |
| blog_DBA_T1_MYS.000007 |
| blog_DBA_T1_MYS.000008 |
| blog_DBA_T1_MYS.000009 |
| blog_DBA_T1_MYS.000010 |
| blog_DBA_T1_MYS.000011 |
| blog_DBA_T1_MYS.000012 |
| blog_DBA_T1_MYS.000013 |
| blog_DBA_T1_MYS.000014 |
| blog_DBA_T1_MYS.000015 |
| blog_DBA_T1_MYS.000016 |
| blog_DBA_T1_MYS.000017 |
| blog_DBA_T1_MYS.000018 |
| blog_DBA_T1_MYS.000019 |
+------------------------+

Position courante dans les fichiers de logs binaires : SHOW MASTER STATUS

La commande SHOW MASTER STATUS permet de connaître le positionnement courant au niveau des fichiers de log binaires, ainsi que les bases de données courantes analysées (binlog-do-db) ou ignorées (binlog-ignore-db) :

mysql-DBA_T1_MYS > show master status;

+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| blog_DBA_T1_MYS.000019 | 95       | cgcam        | cgcamidb,mysql   |
+------------------------+----------+--------------+------------------+

Forcer la rotation des fichiers binaires de logs : FLUSH LOGS

La commande FLUSH LOGS force la rotation des fichiers binaires de logs. Ainsi pour forcer le passage du fichier binaire de log blog_DBA_T1_MYS.000019 vers le nouveau fichier binaire de log blog_DBA_T1_MYS.000020 :

mysql-DBA_T1_MYS > flush logs ;
mysql-DBA_T1_MYS > show master status;

+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| blog_DBA_T1_MYS.000020 | 95       | cgcam        | cgcamidb,mysql   |
+------------------------+----------+--------------+------------------+

Un nouveau fichier binaire de log est également créé lorsque :

le serveur redémarre
le paramètre max_binlog_size est atteint
mysqladmin refresh est exécuté
mysqladmin flush-logs est exécuté

Traduction des fichiers binaires de log : mysqlbinlog

Le binaire mysqlbinlog dans le répertoire $MYSQL/bin ou %MYSQL%/bin permet de traduire les fichiers binaires de log :

$MYSQL/bin/mysqlbinlog [options] logfiles

Exemple :

$MYSQL/bin/mysqlbinlog --result-file=blog_DBA_T1_MYS.011.txt  blog_DBA_T1_MYS.000011

blog_DBA_T1_MYS.011.txt

...
#040514 17:34:38 server id 1 end_log_pos 1262117 Query thread_id=2
exec_time=0 error_code=0
SET TIMESTAMP=1084548878;
delete from t_si_ts_detail
where login='SPA';
...

Purge des fichiers binaires de log : PURGE MASTER LOGS

La commande PURGE MASTER LOGS purge les fichiers de log binaires soit en utilisant les noms de fichiers, soit en se basant sur une date et une heure :

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

En utilisant le nom de fichier, tous les fichiers de log binaires précédant le fichier de log binaire spécifié sont supprimés.

Mécanisme de la réplication entre serveur maître et serveur esclave

3 threads sont impliqués dans le mécanisme de réplication d’un serveur maître vers un serveur esclave.

Lorsque la réplication est déclenchée sur le serveur esclave, le thread Slave I/O est créé sur le serveur esclave : celui-ci se connecte au serveur maître et lui demande de lui adresser les requêtes enregistrées dans ses logs binaires. Pour y répondre, le serveur primaire créé un thread identifiable dans la liste des process (SHOW PROCESSLIST) par Binlog Dump dans la colonne commande. Le thread Slave I/O du serveur esclave lit ce que lui adresse le thread Binlog Dump sur le serveur maître pour les copier simplement dans des fichiers locaux appelés fichiers logs de relai (relay logs).

Le dernier thread impliqué dans la réplication, le thread Slave SQL, est créé sur le serveur esclave, se charge de lire les logs de relai (relay logs) et d’exécuter les requêtes qu’il contient sur la base de réplication. Les logs de relai (relay logs) peuvent être traduits avec le binaire mysqlbinlog comme des fichiers binaires de log.

Création des comptes pour les process esclaves sur le serveur master

Des comptes doivent être créés sur le serveur master (DBA_T1_MYS) pour permettre aux serveurs esclaves de se connecter (DBA_T2_MYS et DBA_T3_MYS) et ceci afin de déclencher le thread Binlog Dump sur le serveur maître. Le privilège REPLICATION SLAVE doit être attribué à ces comptes.

Par ailleurs, comme l’on désire réaliser des matérialisations de tables sur les serveurs répliqués avec les commandes LOAD TABLE FROM MASTER et LOAD DATA FROM MASTER, les privilèges SUPER et RELOAD doivent être également attribués à ces comptes de réplication créés sur le serveur master, ainsi que le privilège SELECT sur les tables à matérialiser.

Pour DBA_T2_MYS :

mysql-DBA_T1_MYS > grant replication slave on *.* to admdb_maint@'%' identified by 'admdb_maint_ps';
mysql-DBA_T1_MYS > grant super,reload on *.* to admdb_maint;
mysql-DBA_T1_MYS > grant select on cgcam.* to admdb_maint;
mysql-DBA_T1_MYS > flush privileges;

Pour DBA_T3_MYS :

mysql-DBA_T1_MYS > grant replication slave on *.* to sidb_maint@'%' identified by 'sidb_maint_ps';
mysql-DBA_T1_MYS > grant super,reload on *.* to sidb_maint;
mysql-DBA_T1_MYS > grant select on cgcam.* to sidb_maint;
mysql-DBA_T1_MYS > flush privileges;

Afin de vérifier que les privilèges REPLICATION SLAVE, RELOAD et SUPER sont correctement attribués sur les serveur master (DBA_T1_MYS) :

mysql-DBA_T1_MYS >

select User,
  Repl_slave_priv, Reload_priv,Super_priv
  from user where User != 'root' and User !='';

+-------------+-----------------+-------------+------------+
| User        | Repl_slave_priv | Reload_priv | Super_priv |
+-------------+-----------------+-------------+------------+
| admdb_maint | Y               | Y           | Y          |
| sidb_maint  | Y               | Y           | Y          |
+-------------+-----------------+-------------+------------+

Afin de vérifier que le privilège SELECT est correctement attribué sur les serveur master (DBA_T1_MYS) :

mysql-DBA_T1_MYS > select Db,User,Select_priv from db where User in ('admdb_maint','sidb_maint');

+-------+-------------+-------------+
| Db    | User        | Select_priv |
+-------+-------------+-------------+
| cgcam | admdb_maint | Y           |
| cgcam | sidb_maint  | Y           |
+-------+-------------+-------------+

Préparation des serveurs esclaves pour la réplication

Options de démarrage de serveurs esclaves pour la réplication

Identifiant du serveur esclave

Dans le fichier cnf de démarrage d’un serveur esclave doit figurer un identifiant unique de serveur , cet identifiant est donné avec le paramètre server-id. Pour le serveur maître, server-id=1, pour chaque serveur esclave server-id vaut de 2 à 2^32 et doit être unique.

[mysqld]
...
server-id=2
...

Paramètres de connexion au serveur maître

Les paramètres de connexion au serveur maître peuvent être spécifiés dans le fichier de démarrage du serveur esclave :

[mysqld]
server-id=2
master-host=CGC
master-port=40102
master-user=admdb_maint
master-password=admdb_maint_ps
master-connect-retry=60

Dans ce contexte, au démarrage du serveur esclave, les paramètres de connexion au serveur maître sont lus et enregistrés dans un fichier appelé master_<servername>.info et localisé dans le répertoire $CFG (/<appname>/mysql/<servername>/cfg). Le paramètre de configuration master-info-file contrôle la localisation et la nomenclature de ce fichier.

[mysqld]
master-info-file=/dba/mysql/DBA_T2_MYS/cfg/master_DBA_T2_MYS.info

Paramètres concernant les fichiers de logs de relai (relay logs)

Par défaut, les threads Slave du serveur esclave utilisent un fichier appelé relay-log.info pour enregistrer les informations relatives aux fichiers de log de relai, il est toutefois possible de contrôler la localisation et la nomenclature de ce fichier avec le paramètre de configuration relay-log-info-file (dans la normalisation, ce fichier relay-log.info nommé relay_<servername>.info est localisé dans le répertoire $CFG du serveur, soit /<appname>/mysql/<servername>/cfg) :

[mysqld]
relay-log-info-file=/dba/mysql/DBA_T2_MYS/cfg/relay_DBA_T2_MYS.info

La localisation, la taille et la nomenclature des fichiers de logs de relai peuvent être également spécifiés avec les paramètres max-relay-log-size et relay-log.

Dans la normalisation, les fichiers de logs de relai ont pour nomenclature relay_<servername> et sont localisés dans le répertoire $RPL (/<appname>/mysql/<servername>/rpl ), le fichier d’index des logs de relai a pour nomenclature relay_<servername>.index et est également localisé dans le répertoire $RPL.

[mysqld]
max-relay-log-size=10M
relay-log=/dba/mysql/DBA_T2_MYS/rpl/relay_DBA_T2_MYS
relay-log-index=/dba/mysql/DBA_T2_MYS/rpl/relay_DBA_T2_MYS.index

Il est également possible de spécifier la taille maximale autorisée des fichiers de logs de relai avec le paramètre relay-log-space-limit : en spécifiant ce paramètre, le thread I/O ne peut continuer d’écrire dans les fichiers de logs de relai si la taille maximale autorisée est atteinte et doit attendre pour écrire que le thread SQL ait libéré assez d’espace avec son processus de purge au cours du traitement des évènements SQL :

[mysqld]
relay-log-space-limit=100M

Démarrage automatique des threads sur le serveur esclave

Le paramètre skip-start-slave permet de spécifier au serveur esclave de ne pas démarrer automatiquement les threads I/O et SQL, cette configuration doit être appliquée lors de la première mise en route de la réplication :

[mysqld]
skip-slave-start

Applications de règles de réplication

Les paramètres replicate-* contrôlent les flux de réplication au niveau des threads du serveur esclave, ces paramètres sont récapitulés dans le tableau qui suit :


`replicate-do-table=db_name.table_name`	Indique au thread slave de restreindre la réplication à une table spécifique. Pour spécifierplusieurs tables, inscrire cette option plusieurs fois pour chaque table
`replicate-ignore-table=db_name.table_name`	Indique au thread slave d’ignorer certaines tables dans le système de réplication. Pour spécifier plusieurs tables, inscrire cette option plusieurs fois pour chaque table
`replicate-wild-do-table=db_name.table_name`	Indique au thread slave de restreindre la réplication aux tables respectant les critères données. Exemple : `replicate-wild-do-table=db_%.tb_%`. La réplication ne s’appliquera que pour les tables dont le nom commence par `tb_` et pour les bases de données dont le nom commence par `db_`.
`replicate-wild-ignore-table=db_name.table_name`	Indique au thread slave d’ignorer dans la réplication les tables respectant les critères données. Exemple : `replicate-wild-ignore-table=db_%.tb_%`. La réplication ne s’appliquera pas pour les tables dont le nom commence par `tb_` et pour les bases de données dont le nom commence par `db_`.
`replicate-ignore-db=database_name`	Indique au thread slave d’ignorer une base de données pour la réplication. Pour spécifier plus d’une base de données, inscrire cette option pour chaque base de données à ignorer
`replicate-do-db=database_name`	Indique au thread slave de restreindre la réplication à une base de données. Pour spécifier plus d’une base de données, inscrire cette option pour chaque base de données.
`replicate-rewrite-db=from_name->to_name`	Cette option effectue une mise à jour d’une base de données du serveur master vers une autre base de données du serveur esclave portant un nom différent

Options de démarrage de serveurs esclaves (Exemple pratique)

Dans le contexte de notre cas pratique, voici les paramètres de démarrage DBA_T2_MYS et DBA_T3_MYS

Serveur DBA_T2_MYS : $CFG/DBA_T2_MYS.cnf

server-id=2
master-host=CGC
master-port=40102
master-user=admdb_maint
master-password=admdb_maint_ps
master-connect-retry=60
master-info-file=/dba/mysql/DBA_T2_MYS/cfg/master_DBA_T2_MYS.info

skip-slave-start

max-relay-log-size=10M
relay-log=/dba/mysql/DBA_T2_MYS/rpl/relay_DBA_T2_MYS
relay-log-index=/dba/mysql/DBA_T2_MYS/rpl/relay_DBA_T2_MYS.index
relay-log-info-file=/dba/mysql/DBA_T2_MYS/cfg/relay_DBA_T2_MYS.info
relay-log-space-limit=100M

replicate-wild-do-table=cgcam.t_adm%

Serveur DBA_T3_MYS : fichier $CFG/DBA_T3_MYS.cnf

server-id=3
master-host=CGC
master-port=40102
master-user=sidb_maint
master-password=sidb_maint_ps
master-connect-retry=60
master-info-file=/dba/mysql/DBA_T3_MYS/cfg/master_DBA_T3_MYS.info

skip-slave-start

max-relay-log-size=10M
relay-log=/dba/mysql/DBA_T3_MYS/rpl/relay_DBA_T3_MYS
relay-log-index=/dba/mysql/DBA_T3_MYS/rpl/relay_DBA_T3_MYS.index
relay-log-info-file=/dba/mysql/DBA_T3_MYS/cfg/relay_DBA_T3_MYS.info
relay-log-space-limit=100M

replicate-wild-do-table=cgcam.t_si%
replicate-do-table=cgcam.t_asi_users

Synchronisation et mise en route de la réplication

Pour la synchronisation, il est impératif de démarrer le serveur esclave avec skip-slave-start !

Dans l’exemple pratique, une synchronisation entre DBA_T1_MYS et DBA_T3_MYS est réalisée. DBA_T3_MYS est démarré avec l’option skip-slave-start.

Gel du serveur maître : FLUSH TABLES WITH READ LOCK

Le serveur primaire doit être gelé en écriture avec la commande FLUSH TABLES WITH READ LOCK

mysql-DBA_T1_MYS > flush tables with read lock;

À l’issue du gel du serveur primaire en écriture, exécuter la commande SHOW MASTER STATUS pour repérer le fichier de log binaire courant et la position courante, noter ces valeurs car elles sont indispensables pour le démarrage des threads esclaves :

mysql-DBA_T1_MYS > show master status;

+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| blog_DBA_T1_MYS.000118 |   385030 | cgcam        | cgcamidb,mysql   |
+------------------------+----------+--------------+------------------+

Matérialisation de la base cgcam sur le serveur secondaire

Deux options sont possibles :

soit prendre une photo du contenu du répertoire cgcam sur le serveur primaire DBA_T1_MYS et installer cette photo dans le répertoire cgcam du serveur secondaire DBA_T3_MYS
soit matérialiser les tables avec les commandes LOAD TABLE <table_name> from master

La seconde méthode ne peut être appliquée que lorsque la base de données n’est pas trop volumineuse.

Exemple avec la deuxième méthode :

mysql-DBA_T3_MYS > source materialize.txt

materialize.txt :

load table t_asi_users from master;
load table t_si_client_details from master;
load table t_si_doc_details from master;
load table t_si_doc_folders from master;
load table t_si_doc_sections from master;...

Dégel de l’écriture sur le serveur maître

À l’issue de la photographie ou de la matérialisation, déverrouiller les tables au sein du serveur maître avec la commande UNLOCK TABLES :

mysql-DBA_T1_MYS > unlock tables ;

Démarrage des threads esclave

Application du point de démarrage : CHANGE MASTER

À ce stade, il est nécessaire d’indiquer aux threads esclave I/O et SQL qui vont être démarrés à partir de quel point il faut traiter les instructions provenant des logs binaires du serveur maître. Pour cela la position courante et le fichier de log binaire courant notés au moment du gel du serveur maître vont être donnés avec la commande CHANGE MASTER TO…

mysql-DBA_T3_MYS > change master to
master_log_file='blog_DBA_T1_MYS.000118',master_log_pos=385030 ;

Pour vérifier que ces paramètres ont été correctement appliqués, consulter le fichier relay-log.info ou bien exécuter show slave status\G.

mysql-DBA_T3_MYS > show slave status\G;

*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: CGC
                Master_User: sidb_maint
                Master_Port: 40102
              Connect_Retry: 60
            Master_Log_File: blog_DBA_T1_MYS.000118
        Read_Master_Log_Pos: 385030
             Relay_Log_File: relay_DBA_T3_MYS.000001
              Relay_Log_Pos: 4
      Relay_Master_Log_File: blog_DBA_T1_MYS.000118
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table: cgcam.t_asi_users
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: cgcam.t_si%
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 385030
            Relay_Log_Space: 0
            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
1 row in set (0.00 sec)

Démarrage des threads esclaves : START SLAVE

Pour le démarrage des threads esclaves, utiliser la commande start slave

mysql-DBA_T3_MYS > start slave;

Pour vérifier le bon démarrage des threads esclave I/O et SQL : exécuter la commande SHOW SLAVE STATUS\G sur le serveur répliqué et s’assurer que les statuts Slave_IO_Running et Slave_SQL_Running sont à YES.

mysql-DBA_T3_MYS > show slave status\G;
...
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
...

Au sein du serveur primaire, le thread Binlog Dump associé à DBA_T3_MYS (login sidb_maint) doit apparaître

mysql-DBA_T1_MYS > show processlist\G;

      Id: 70
    User: sidb_maint
    Host: localhost:3441
      db: NULL
 Command: Binlog Dump
    Time: 361
   State: Has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL

Les différents états des threads dans une réplication MySQL

Les commandes SHOW PROCESSLIST sur les serveurs maitres et esclaves permettent de déterminer l’état des threads engagés dans un système de réplication grâce à la colonne State renvoyée par cette commande.

Le thread BinLog Dump sur le serveur maître

mysql-DBA_T1_MYS> show processlist\G;

*************************** 1. row ***************************
      Id: 1
    User: sidb_maint
    Host: localhost:3147
      db: NULL
 Command: Binlog Dump
    Time: 180
   State: Has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL

Les états possibles pour le thread BinLog Dump sur un serveur maître sont :


`Sending binlog event to slave`	Les fichiers de log binaires se composent d’évènements, évènements comportant un ordre SQL plus des informations additionnelles. Cet état indique que le thread a lu un évènement à partir d’un fichier de log binaire et est actuellement en train de l’envoyer au thread I/O de l’esclave.
`Finished reading one binlog; switching to next binlog`	Fin de lecture d’un fichier binaire de log et bascule vers un nouveau fichier binaire de log en informant le thread esclave I/O de cette bascule.
`Has sent all binlog to slave; waiting for binlog to be updated`	Le thread a tout envoyé au serveur esclave et est en mode sommeil, dans l’attente d’évènements nouveaux dans les fichiers de log binaires du serveur maître.
`Waiting to finalize termination`	Etat très bref indiquant que le thread est en cours d’arrêt.

Le thread I/O Slave sur le serveur esclave

mysql-DBA_T3_MYS > show processlist\G;

*************************** 1. row ***************************
       Id: 1
     User: system user
     Host:
       db: NULL
  Command: Connect
     Time: 779
    State: Waiting for master to send event
     Info: NULL

Les états possibles pour le thread I/O sur un serveur esclave sont :


`Connecting to master`	Le thread tente une connexion au serveur maître
`Checking master version`	État très bref juste après la connexion au serveur maître
`Registering slave on master`	État très bref juste après la connexion au serveur maître
`Requesting binlog dump`	État très bref juste après la connexion au serveur maître
`Waiting to reconnect after a failed binlog dump request`	Si la connexion au serveur maître est interrompue, le thread I/O esclave se met en mode sleep avant de tenter une reconnexion au bout d’un intervalle en secondes spécifié par le paramètre `master-connect-retry`
`Reconnecting after a failed binlog dump request`	Le thread I/O esclave tente une reconnexion au serveur maître.
`Waiting for master to send event`	Le thread I/O esclave est connecté et attend des évènements des fichiers binaires de log du serveur maître. Si l’attente dépasse le paramètre `slave_net_timeout` en secondes, un timeout se produit : à ce stade, le thread considère la connexion comme cassée et tente une reconnexion.
`Queueing master event to the relay log`	Le thread I/O a lu un évènement et le copie dans les fichiers de log de relai.
`Waiting to reconnect after a failed master event read`	Une erreur est apparue lors de la lecture de l’évènement en provenance du serveur maître. Le thread I/O se met en mode sommeil avant de tenter une reconnexion à l’issue d’un intervalle en secondes spécifié par le paramètre `master-connect-retry`.
`Reconnecting after a failed master event read`	Le thread I/O tente une reconnexion au serveur maître à l’issue de la lecture d’un évènement qui a échoué.
`Waiting for the slave SQL thread to free enough relay log space`	Dans ce cas de figure le paramètre `relay-log-space-limit` n’est pas nul et les fichiers de log de relai ont atteint une taille totale qui dépasse ce paramètre. Le thread I/O attend que le thread SQL libère assez d’espace en traitant le contenu de ces fichiers de logs de relai et en supprimant certains de ces fichiers.
`Waiting for slave mutex on exit`	État très bref indiquant que le thread est en cours d’arrêt.

Le thread SQL Slave sur le serveur esclave

mysql-DBA_T3_MYS> show processlist\G;

*************************** 2. row ***************************
            Id: 2
          User: system user
          Host:
            db: NULL
       Command: Connect
          Time: 778
         State: Has read all relay log; waiting for the slave I/O thread to update it
         Info: NULL

Les états possibles pour le thread SQL sur un serveur esclave sont :


`Reading event from the relay log`	Le thread est en train de lire un évènement dans un fichier de log de relai pour le traiter.
`Has read all relay log; waiting for the slave I/O thread to update it`	Le thread a traité tous les évènements dans les fichiers de log de relai et attend de nouveaux évènements écrits par le thread I/O.
`Waiting for slave mutex on exit`	État très bref indiquant que le thread est en cours d’arrêt.

Désactivation de la réplication d’ordres SQL

La commande SET SQL_LOG_BIN= [ 0 | 1 ] active ou désactive pour une session la réplication d’ordres SQL vers les esclaves.

En spécifiant SET SQL_LOG_BIN=0, les ordres SQL ne sont tout simplement pas écrits dans les fichiers binaires de log du serveur maître et donc non répliqués.

Tous les utilisateurs ne sont pas autorisés à exécuter la commande SET SQL_LOG_BIN, seuls les utilisateurs disponsant du privilège SUPER peuvent mettre en œuvre cette variable de session.

Cette commande de session est équivalente aux commandes "set replication on|off" de Sybase.

Gestion des erreurs avec la réplication MySQL

Les paramètres de connexion au serveur maître sont modifiés

Supposons que la connexion au serveur au maître pour le user sidb_maint soit modifié au niveau de son mot de passe :

mysql-DBA_T1_MYS> set password for sidb_maint@'%'=password('sidb_maint_pwd');
mysql-DBA_T1_MYS> flush privileges;

Dans ce cas de figure, une fois le paramètre slave_net_timeout atteint, la reconnexion du thread I/O de l’esclave n’aboutit pas sur serveur maître, ce qui peut se voir avec la commande SHOW SLAVE STATUS\G ou bien dans le fichier erreur du serveur esclave :

040519 13:08:02  Slave I/O thread: error reconnecting to master 'sidb_maint@CGC:'40102':
Error: 'Access denied for user: 'sidb_maint'@'localhost' (Using password: YES)'  errno: 1045  retry-time: 60  retries: 86400

mysql-DBA_T3_MYS> show slave status\G;
*************************** 1. row ***************************
          Slave_IO_State: Reconnecting after a failed master event read
             Master_Host: CGC
             Master_User: sidb_maint
             Master_Port: 40102
           Connect_Retry: 60
         Master_Log_File: blog_DBA_T1_MYS.000119
     Read_Master_Log_Pos: 95
          Relay_Log_File: relay_DBA_T3_MYS.000022
           Relay_Log_Pos: 235
   Relay_Master_Log_File: blog_DBA_T1_MYS.000119
        Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
...

Les informations de connexion au serveur maître sont stockées dans le fichier spécifié par le paramètre master-info-file, lequel peut être modifié avec la commande CHANGE MASTER TO mais avant de pouvoir appliquer cette commande, les threads esclaves doivent être arrêtés avec la commande STOP SLAVE. A l’issue de l’exécution de la commande CHANGE MASTER TO, la commande START SLAVE doit être exécutée.

mysql> CHANGE MASTER TO
              MASTER_HOST='master2.mycompany.com',
              MASTER_USER='replication',
              MASTER_PASSWORD='bigs3cret',
              MASTER_PORT=3306,
              MASTER_LOG_FILE='master2-bin.001',
              MASTER_LOG_POS=4,
              MASTER_CONNECT_RETRY=10;

Ainsi pour rétablir correctement la connexion :

mysql-DBA_T3_MYS> stop slave;
mysql-DBA_T3_MYS> CHANGE MASTER TO MASTER_PASSWORD='sidb_maint_pwd';
mysql-DBA_T3_MYS> start slave;

Échec d’un ordre SQL répliqué et relance après correction

Ce paragraphe présente un ordre SQL qui échoue sur l’environnement répliqué et dans ce contexte, la requête est exécutée à nouveau après correction.

La table t_si_test (id integer(2) not null) est créée sur les environnements maître et esclave, sauf que sur l’environnement esclave un index unique est créé sur la colonne id de la table t_si_test :

mysql-DBA_T1_MYS> create table t_si_test (id integer(2) not null);
mysql-DBA_T1_MYS> insert into t_si_test values (1);

À l’issue des commandes plus haut lancées sur l’environnement maître, la table t_si_test est également créée sur l’environnement esclave et la première ligne est répliquée.

Sur l’environnement esclave, l’index unique idx_id est créé sur la table t_si_test (id) :

mysql-DBA_T3_MYS> create unique idx_id on t_si_test(id);

Un doublon est alors créé sur l’environnement maître :

mysql-DBA_T1_MYS> insert into t_si_test values (1);

Compte tenu de l’index unique créé sur l’environnement esclave, la réplication échoue et le thread SQL Slave se met dans un état down : état indiqué dans le fichier mysql.err du serveur ou bien avec la commande SHOW SLAVE STATUS\G ;

ERROR: 1062  Duplicate entry '1' for key 1
040520 13:42:08  Slave: Error 'Duplicate entry '1' for key 1' on query 'insert into t_si_test values (1)'.
Default database: 'cgcam', Error_code: 1062
040520 13:42:08  Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'blog_DBA_T1_MYS.000120' position 290

mysql-DBA_T3_MYS> show slave status\G;
*************************** 1. row ***************************
                  Master_Host: CGC
                  Master_User: sidb_maint
                  Master_Port: 40102
                Connect_Retry: 60
              Master_Log_File: blog_DBA_T1_MYS.000120
          Read_Master_Log_Pos: 380
               Relay_Log_File: relay_DBA_T3_MYS.000007
                Relay_Log_Pos: 235
        Relay_Master_Log_File: blog_DBA_T1_MYS.000120
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table: cgcam.t_asi_users
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: cgcam.t_si%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '1' for key 1' on query 'ins
  ert into t_si_test values (1)'. Default database: 'cgcam'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 290

Dans ce contexte, le problème sera corrigé en supprimant l’index unique sur le serveur esclave pour la table t_si_test :

mysql-DBA_T3_MYS> drop index idx_id on t_si_test;

Pour relancer l’exécution de la requête SQL en échec, exécuter la commande START SLAVE sur le serveur esclave :

mysql-DBA_T3_MYS> START SLAVE;

L’ordre d’insertion du doublon est alors effectuée sur l’environnement esclave et la commande SHOW SLAVE STATUS indique bien que le thread SQL est en mode running.

Echec d’un ordre SQL répliqué et skip de requêtes : SET GLOBAL SQL_SLAVE_SKIP_COUNTER

Ce paragraphe présente des ordres SQL qui échouent sur l’environnement répliqué et dans ce contexte, les requêtes sont écartées.

La table t_si_skip (id integer(2) not null) est créée sur l’environnement maître mais pas sur l’environnement esclave en positionnant SET SQL_LOG_BIN=0 sur le serveur maître :

mysql-DBA_T1_MYS> set sql_log_bin = 0;

mysql-DBA_T1_MYS> create table t_si_skip (id integer(2) not null);

Des lignes sont alors insérées dans la table t_si_skip sur l’environnement maître mais la réplication est active (SET SQL_LOG_BIN=1) :

mysql-DBA_T1_MYS> set sql_log_bin = 1;
mysql-DBA_T1_MYS> insert into t_si_skip values (1);
mysql-DBA_T1_MYS> insert into t_si_skip values (2);
mysql-DBA_T1_MYS> insert into t_si_skip values (3);
mysql-DBA_T1_MYS> insert into t_si_test values (10);

Compte tenu de la non existence de la table t_si_skip sur l’environnement esclave, la réplication échoue et le thread SQL Slave se met dans un état down : état indiqué dans le fichier mysql.err du serveur ou bien avec la commande SHOW SLAVE STATUS\G ;

ERROR: 1146  Table 'cgcam.t_si_skip' doesn't exist

040519 14:17:28  Slave: Error 'Table 'cgcam.t_si_skip' doesn't exist' on query 'insert into t_si_skip values(1)'.
Default database: 'cgcam', Error_code: 1146

040519 14:17:28  Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'blog_DBA_T1_MYS.000120' position 470

mysql> show slave status\G;
                Slave_IO_State: Waiting for master to send event
                ...
               Master_Log_File: blog_DBA_T1_MYS.000120
           Read_Master_Log_Pos: 827
                Relay_Log_File: relay_DBA_T3_MYS.000023
                 Relay_Log_Pos: 235
         Relay_Master_Log_File: blog_DBA_T1_MYS.000120
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
                ...
                    Last_Errno: 1146
                    Last_Error: Error 'Table 'cgcam.t_si_skip' doesn't exist' on qu
ery 'insert into t_si_skip values(1)'. Default database: 'cgcam'

La commande SHOW SLAVE STATUS indique clairement que le thread SQL slave s’est arrêté à la position 235 dans le fichier de log de relai : relay_DBA_T3_MYS.000023.

Pour savoir combien de requêtes SQL doivent être écartées, mysqlbinlog est utilisée pour traduire le fichier relay_DBA_T3_MYS.000023 :

mysqlbinlog relay_DBA_T3_MYS.000023

# at 4
#040519 14:15:49 server id 3  end_log_pos 95    Start: binlog v 4, server v 5.0.
0-alpha-max-debug-log created 040519 14:15:49
# at 95
#700101  1:00:00 server id 1  end_log_pos 0     Rotate to blog_DBA_T1_MYS.000120 pos: 470
# at 144
#040519 13:31:01 server id 1  end_log_pos 0     Start: binlog v 4, server v 5.0.
0-alpha-max-debug-log created 040519 13:31:01 at startup
# at 235
#040519 14:17:28 server id 1  end_log_pos 559 Query thread_id=6 exec_time=0 error_code=0
use cgcam;
SET TIMESTAMP=1085141848;
SET @@session.foreign_key_checks=67108864, @@session.sql_auto_is_null=16384, @@session.unique_checks=134217728;
SET @@session.sql_mode=0;
insert into t_si_skip values(1);
# at 324
#040519 14:17:29 server id 1  end_log_pos 648 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1085141849;
insert into t_si_skip values(2);
# at 413
#040519 14:17:32 server id 1  end_log_pos 737 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1085141852;
insert into t_si_skip values(3);
# at 502
#040519 14:19:32 server id 3  end_log_pos 552   Rotate to relay_DBA_T3_MYS.000024  pos: 4

Il est également nécessaire de consulter le fichier de log de relai relay_DBA_T3_MYS.000024 car la traduction du fichier précédent indique une rotation vers le fichier relay_DBA_T3_MYS.000024.

mysqlbinlog relay_DBA_T3_MYS.000024

# at 4
#040521 14:19:32 server id 3  end_log_pos 95    Start: binlog v 4, server v 5.0.
0-alpha-max-debug-log created 040521 14:19:32
# at 95
#700101  1:00:00 server id 1  end_log_pos 0     Rotate to blog_DBA_T1_MYS.000120
pos: 737
# at 144
#040521 13:31:01 server id 1  end_log_pos 0     Start: binlog v 4, server v 5.0.
0-alpha-max-debug-log created 040521 13:31:01 at startup
# at 235
#040521 14:19:52 server id 1  end_log_pos 827 Query thread_id=6 exec_time=0 error_code=0
use cgcam;
SET TIMESTAMP=1085141992;
SET @@session.foreign_key_checks=67108864, @@session.sql_auto_is_null=16384, @@session.unique_checks=134217728;
SET @@session.sql_mode=0;
insert into t_si_test values(10);
# at 325
#040521 14:21:53 server id 3  end_log_pos 375   Rotate to relay_DBA_T3_MYS.000025  pos: 4

La traduction des fichiers de logs de relai montre que 3 instructions doivent être écartées, ce qui est réalisé avec la commande SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n ; Cette commande ne peut être appliquée que si les threads esclaves sont stoppés avec la commande STOP SLAVE :

mysql-DBA_T3_MYS> STOP SLAVE;
mysql-DBA_T3_MYS> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3;
mysql-DBA_T3_MYS> START SLAVE;

Skip automatique des erreurs : slave-skip-errors

A chaque erreur rencontrée au niveau du serveur esclave, la réplication est stoppée. Il est possible d’indiquer au threads SQL de poursuivre la réplication même si une erreur est rencontrée avec le paramètre de configuration slave-skip-errors :

slave-skip-errors=1062,1053
slave-skip-errors=all

Ces paramètres doivent être utilisés avec précaution et sont fortement déconseillés, ces "skips" d’erreurs engendrant une potentielle inconsistence des bases répliquées.

Forcer et vérifier une synchronisation de la réplication : SELECT MASTER_POS_WAIT

Pour vérifier la bonne synchronisation de la réplication, bloquer les tables en écriture sur le serveur maître et récupérer le statut sur le serveur maître avec la commande SHOW MASTER STATUS\G :

mysql-DBA_T1_MYS> FLUSH TABLES WITH READ LOCK;
mysql-DBA_T1_MYS> SHOW MASTER STATUS\G;
         File: blog_DBA_T1_MYS.000120
     Position: 917

Au sein du serveur secondaire, exécuter ensuite la commande SELECT MASTER_POS_WAIT ('log_bin_master',offset)

mysql-DBA_T3_MYS> SELECT MASTER_POS_WAIT('blog_DBA_T1_MYS.000120',917);

La commande SELECT MASTER_POS_WAIT ne rend alors la main que lorsque les esclaves ont attrapé tous les changements sur le serveur maître jusqu’à la position indiquée (offset) :

mysql-DBA_T3_MYS> SHOW PROCESSLIST ;

+------------------------------------------------------------------------------------------------------+
| Id | User | State                             | Info                                                 | 
+------------------------------------------------------------------------------------------------------+
|  3 | root | Waiting for the slave SQL thread  | select master_pos_wait('blog_DBA_T1_MYS.000120',917) |
|    |      | to advance position               |                                                      |
+------------------------------------------------------------------------------------------------------+

Lorsque la commande SELECT MASTER_POS_WAIT rend la main, la réplication est complètement synchrone et l’écriture au niveau du serveur maître pour être déverrouillée :

mysql-DBA_T1_MYS> UNLOCK TABLES;

Resynchronisations

Resynchronisation complète

1) Pour effectuer une resynchronisation complète, bloquer les tables en écriture sur le serveur maître et récupérer le statut sur le serveur maître avec la commande SHOW MASTER STATUS\G :

mysql-DBA_T1_MYS> FLUSH TABLES WITH READ LOCK;
mysql-DBA_T1_MYS> SHOW MASTER STATUS\G;
        File: blog_DBA_T1_MYS.000120
    Position: 1008

2) Au sein du serveur secondaire, exécuter ensuite la commande SELECT MASTER_POS_WAIT ('log_bin_master',offset)

mysql-DBA_T3_MYS> SELECT MASTER_POS_WAIT('blog_DBA_T1_MYS.000120',1008);

La commande SELECT MASTER_POS_WAIT ne rend alors la main que lorsque les esclaves ont attrapé tous les changements sur le serveur maître jusqu’à la position indiquée (offset).

3) Arrêter ensuite les threads esclaves :

mysql-DBA_T3_MYS> STOP SLAVE;

4) Arrêter ensuite le serveur esclave DBA_T3_MYS

5) Réaliser une photographie de la base maître (répertoire cgcam) et copier cette photographie au niveau de la base esclave (répertoire cgcam).

6) Redémarrer DBA_T3_MYS et exécuter éventuellement START SLAVE si l’option skip-start-slave est appliqué au démarrage du serveur esclave.

7) Déverrouiller les tables en écriture sur le serveur maître :

mysql-DBA_T1_MYS> UNLOCK TABLES;

Resynchronisation d’une ou plusieurs tables

1) Pour effectuer une resynchronisation d’une ou plusieurs tables, bloquer les tables en écriture sur le serveur maître et récupérer le statut sur le serveur maître avec la commande SHOW MASTER STATUS\G :

mysql-DBA_T1_MYS> FLUSH TABLES WITH READ LOCK;
mysql-DBA_T1_MYS> SHOW MASTER STATUS\G;

      File: blog_DBA_T1_MYS.000120
  Position: 1099

2) Au sein du serveur secondaire, exécuter ensuite la commande SELECT MASTER_POS_WAIT ('log_bin_master',offset)

mysql-DBA_T3_MYS> SELECT MASTER_POS_WAIT('blog_DBA_T1_MYS.000120',1099);

La commande SELECT MASTER_POS_WAIT ne rend alors la main que lorsque les esclaves ont attrapé tous les changements sur le serveur maître jusqu’à la position indiquée (offset).

3) Arrêter ensuite les threads esclaves :

mysql-DBA_T3_MYS> STOP SLAVE;

4) Supprimer les tables à resynchroniser

mysql-DBA_T3_MYS> drop table t_si_ts_details;

5) Rematérialiser les tables à resynchroniser avec la commande LOAD TABLE.

mysql-DBA_T3_MYS> LOAD TABLE t_si_ts_details from master;

6) Redémarrer les threads esclaves sur le serveur esclave avec la commande START SLAVE.

mysql-DBA_T3_MYS> START SLAVE;

7) Déverrouiller les tables en écriture sur le serveur maître :

mysql-DBA_T1_MYS> UNLOCK TABLES;