MySQL 5.0 et la réplication des identity

Introduction

Cette note technique présente la gestion de la réplication des identity dans un système de réplication MySQL 5.0. Pour aborder cette note technique, prendre connaissance du document « Réplication MySQL 5.0 - Mise en œuvre pratique.

Cas pratique

Le cas pratique se base sur l'architecture mise en œuvre dans la note technique Réplication MySQL 5.0 - Mise en œuvre pratique.

Toutes les tables t_adm% du serveur primaire DBA_T1_MYS sont répliquées vers le serveur secondaire DBA_T2_MYS.

Dans ce système de réplication, la table t_adm_connection possède une colonne avec l'option auto_increment et l'objectif est de regarder plus en détail les informations contenues dans les fichiers de logs binaires pour la gestion de cette colonne identity dans le système de réplication.


mysql-DBA_T1_MYS > use cgcam;

mysql-DBA_T1_MYS >

create table t_adm_connection
  (id int not null auto_increment primary key,
	 date_c date null);
	 
Query OK, 0 rows affected (0.24 sec)

mysql-DBA_T1_MYS >

insert into t_adm_connection (date_c) values ('2005-10-18');

Query OK, 1 row affected (0.27 sec)

La création de la table t_adm_connection est répliquée du serveur DBA_T2_MYS vers le serveur DBA_T2_MYS ainsi que l'insertion de la première ligne :


mysql-DBA_T2_MYS > use cgcam;
mysql-DBA_T2_MYS > select * from t_adm_connection;
+----+------------+
| id | date_c     |
+----+------------+
|  1 | 2005-10-18 |
+----+------------+
1 row in set (0.01 sec)

Création d'un conflit de réplication sur la colonne identity

Génération d'un conflit

A présent un conflit de réplication va être généré en créant une ligne sur la table répliquée t_adm_connection du serveur secondaire DBA_T2_MYS :


mysql-DBA_T2_MYS> insert into t_adm_connection(date_c) values ('2005-10-19');
Query OK, 1 row affected (0.00 sec)

mysql> select  * from t_adm_connection;
+----+------------+
| id | date_c     |
+----+------------+
|  1 | 2005-10-18 |
|  2 | 2005-10-19 |
+----+------------+

2 rows in set (0.01 sec)

Si l'on ajoute alors une ligne dans la table t_adm_connection sur le serveur primaire DBA_T1_MYS, alors le système de réplication vers le serveur secondaire DBA_T2_MYS tombe et l'objectif est de voir plus en détail pourquoi :


mysql-DBA_T1_MYS> insert into t_adm_connection(date_c) values ('2005-10-18');
Query OK, 1 row affected (0.00 sec)

Vérification du système de réplication vers DBA_T2_MYS en échec

Plusieurs outils sont disponibles pour vérifier que le système de réplication vers DBA_T2_MYS est en échec, le thread esclave SQL (thread SQL slave) est en effet arrêté :

  • le fichier de log du serveur secondaire DBA_T2_MYS
  • la commande show slave status sur le serveur secondaire DBA_T2_MYS

Le fichier de log du serveur DBA_T2_MYS indique les informations d'échec, dans l'environnement normalisé, le fichier de log DBA_T2_MYS se trouve dans le répertoire $LOG /Software/mysql/dba/DBA_T2_MYS/log :


051018 14:38:25 [Note] Slave SQL thread initialized, starting replication in log 'blog_DBA_T1_MYS.000020' at position 1631, relay log 'C:\dba\mysql\DBA_T2_MYS\rpl\relay_DBA_T2_MYS.000043' position: 1774
051018 14:38:26 [Note] Slave I/O thread: connected to master 'DBA_T2_MYS_maint@CGC:40102',  replication started in log 'blog_DBA_T1_MYS.000020' at position 1631
051018 14:42:20 [ERROR] Slave: Error 'Duplicate entry '2' for key 1' on query. Default database: 'cgcam'. Query: 'insert into t_adm_connection(date_c) values ('2005-10-18')', Error_code: 1062
051018 14:42:20 [ERROR] 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.000021' position 410

La commande SHOW SLAVE STATUS donne également les informations concernant l'arrêt du thread esclave SQL :


mysql-DBA_T2_MYS> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: CGC
                Master_User: DBA_T2_MYS_maint
                Master_Port: 40102
              Connect_Retry: 60
            Master_Log_File: blog_DBA_T1_MYS.000021
        Read_Master_Log_Pos: 560
             Relay_Log_File: relay_DBA_T2_MYS.000046
              Relay_Log_Pos: 553
      Relay_Master_Log_File: blog_DBA_T1_MYS.000021
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: cgcam.t_adm%
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '2' for key 1' on query. Def
ault database: 'cgcam'. Query: 'insert into t_adm_connection(date_c) values ('20
05-10-18')'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 410
            Relay_Log_Space: 703
            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)
ERROR:
No query specified

Le fichier de log du serveur secondaire DBA_T2_MYS et la commande SHOW SLAVE STATUS indiquent bien que le thread esclave SQL est arrêté à cause de l'erreur N°1062 - Attempt to insert duplicate key avec le requête insert into t_adm_connection(date_c) values ('2005-10-18').

Cependant pourquoi la requête insert into t_adm_connection(date_c) values ('2005-10-18') n'a pas été exécutée sur le serveur secondaire en appliquant id=3 ?

Lecture du fichier de relay log du thread esclave SQL

Le thread slave SQL comme le montre le schéma ci-dessus prend ses ordres dans le fichiers de logs de relai situés dans le répertoire $RPL (/dba/mysql/DBA_T2_MYS/rpl).

La commande SHOW SLAVE STATUS indique que l'instruction en échec est dans le fichier de relai $RPL/relay_DBA_T2_MYS.000046 à la position 553 :


 Relay_Log_File: relay_DBA_T2_MYS.000046
 Relay_Log_Pos: 553

Avec ces informations, lançons la traduction de ce fichier de relai relay_DBA_T2_MYS.000046 à partir de la position 553 grâce au binaire mysqlbinlog :

DBA_T2_MYS > mysqlbinlog $RPL/relay_DBA_T2_MYS.000046 --start-position=553
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 553
#051018 14:42:20 server id 1  end_log_pos 438   Intvar
SET INSERT_ID=2;
# at 581
#051018 14:42:20 server id 1  end_log_pos 560   Query   thread_id=2     exec_time=0
error_code=0
use cgcam;
SET TIMESTAMP=1129639340;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.
collation_server=8;
insert into t_adm_connection(date_c) values ('2005-10-18');
# at 703
#051018 15:42:22 server id 2  end_log_pos 753   Rotate to relay_DBA_T2_MYS.00004
7  pos: 4
ROLLBACK;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Le fichier de relai indique bien SET INSERT_ID=2; ce qui précise au thread esclave SQL de forcer la colonne identity à la valeur 2 lors de l'insertion de la ligne dans la table t_adm_connection, or la ligne existe déjà dans la table t_adm_connection du serveur secondaire DBA_T2_MYS, ce qui explique l'échec de la réplication.

Reprise du système de réplication

Deux options sont possibles pour la reprise du système de réplication vers DBA_T2_MYS en fonction des besoins fonctionnels :

Option 1 : suppression de la ligne dans la table t_adm_connection pour laquelle id=2 dans le serveur secondaire DBA_T2_MYS et reprise de la réplication avec la commande START SLAVE :


mysql-DBA_T2_MYS> delete from t_adm_connection where id=2;
mysql-DBA_T2_MYS> start slave;

Option 2 : suppression de la commande insert contenue dans le système de réplication avec la commande SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 et reprise de la réplication avec la commande START SLAVE :


mysql-DBA_T2_MYS> set global sql_slave_skip_counter=1;
mysql-DBA_T2_MYS> start slave;

La commande SHOW SLAVE STATUS ou SHOW PROCESSLIST permet de vérifier la bonne reprise du thread SQL esclave dans le système de réplication :

mysql-DBA_T2_MYS> show processlist\G;

*************************** 3. row ***************************
     Id: 4
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 5432
 State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

mysql-DBA_T2_MYS> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: CGC
                Master_User: DBA_T2_MYS_maint
                Master_Port: 40102
              Connect_Retry: 60
            Master_Log_File: blog_DBA_T1_MYS.000021
        Read_Master_Log_Pos: 560
             Relay_Log_File: relay_DBA_T2_MYS.000047
              Relay_Log_Pos: 241
      Relay_Master_Log_File: blog_DBA_T1_MYS.000021
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

Éviter les collisions d'identity dans un système de réplication multi master : variables systèmes auto_increment_increment et auto_increment_offset

A partir de MySQL 5.0.2, deux variables systèmes auto_increment_increment et auto_increment_offset permettent de gérer et d'éviter les collisions de clés identity dans un contexte où deux serveurs jouent le rôle de maitre dans un système de réplication MySQL.

Dans ce nouveau contexte, les tables t_adm_connection des serveurs primaires DBA_T1_MYS et DBA_T3_MYS sont répliquées vers le serveur secondaire DBA_T2_MYS.

Variables systèmes auto_increment_increment et auto_increment_offset

  • La variable système auto_increment_increment permet de contrôler l'intervalle d'incrémentation de la colonne identity.
  • La variable système auto_increment_offset détermine le point de démarrage de la colonne identity.

En jouant avec ces deux variables système, il est plus simple de gérer un système de réplication multi master pour une table.

Dans un contexte de plusieurs tables répliquées avec des colonnes identity sur deux serveurs primaires, il devient préférable de repenser le modèle.

Cas pratique

Dans le cas pratique des tables t_adm_connection répliquées vers DBA_T2_MYS, les variables systèmes auto_increment_increment et auto_increment_offset vont être appliquées sur les serveurs primaires DBA_T1_MYS et DBA_T3_MYS de façon à n'avoir respectivement que des valeurs impaires et paires pour la colonne id de la table t_adm_connection, ainsi les commandes SET INSERT_ID dans la réplication sont parfaitement maîtrisées :


DBA_T1_MYS
auto_increment_increment : 2
auto_increment_offset : 1

select id from t_adm_connection
-------------------------------
1
3
5
7
9                                         DBA_T2_MYS

DBA_T3_MYS                              select id from t_adm_connection
auto_increment_increment : 2              -------------------------------
auto_increment_offset : 2                 1
                                          2
select id from t_adm_connection           3
-------------------------------           4
2                                         5
4                                         6
6                                         7
8                                         8
10                                        9
                                          10