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 statussur 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
À 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_incrementpermet de contrôler l’intervalle d’incrémentation de la colonneidentity. - La variable système
auto_increment_offsetdétermine le point de démarrage de la colonneidentity.
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