Error_code: handler error HA_ERR_FOUND_DUPP_KEY with Multi DC Galera Cluster. DC1 DC2 DC3 A-B-C----GTID Replication---->D-E-F----GTID Replication---->G Node A {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 100 | 11 | 1 | 1 | +------------------------+------------------+-------------+-------------------+ {code} Node B {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 100 | 12 | 1 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} Node C {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 100 | 13 | 1 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} Node D {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 200 | 21 | 2 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} Node E {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 200 | 22 | 2 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} Node F {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 200 | 23 | 2 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} Node G {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 300 | 33 | 3 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) {code} On Node A {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | | | | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> create database test1; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 100-1-1 | 100-1-1 | 100-1-1 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node B {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 100-1-1 | 100-1-1 | 100-1-1 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node C {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 100-1-1 | 100-1-1 | 100-1-1 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.01 sec) {code} Lets Take backup from Node C to enable replication from Node C to Node D using mysqldump --master-data=2 {code} [root@vmc_c tmp]# mysqldump -u root -proot --master-data=2 --all-databases >/tmp/fullbackup.sql [root@vmc_c tmp]# ls -ltrh fullbackup.sql -rw-r--r-- 1 root root 469K Mar 20 23:47 fullbackup.sql [root@vmc_c tmp]# head -30 fullbackup.sql -- MySQL dump 10.16 Distrib 10.2.32-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.32-MariaDB-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_c.000001', MASTER_LOG_POS=466; -- -- GTID to start replication from -- -- SET GLOBAL gtid_slave_pos='100-1-1'; -- [root@vmc_c tmp]# scp fullbackup.sql root@192.168.47.104:/tmp/fullbackup.sql root@192.168.47.104's password: fullbackup.sql 100% 468KB 9.5MB/s 00:00 {code} DC 2 (D,E,F) Node D {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | | | | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) [root@vmc_d ~]# mysql -u root -proot select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 21-2-8,200-2-116 | 21-2-8,200-2-116 | 21-2-8,200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node E {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 200-2-116 | 200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node F {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 200-2-116 | 200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node D {code} MariaDB [(none)]> SET GLOBAL gtid_slave_pos='100-1-1'; Query OK, 0 rows affected, 1 warning (0.01 sec) MariaDB [(none)]> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1948 | Specified value for @@gtid_slave_pos contains no value for replication domain 21. This conflicts with the binary log which contains GTID 21-2-8. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------------+------------------+ | 21-2-8,200-2-116 | 21-2-8,200-2-116 | 21-2-8,100-1-1,200-2-116 | 100-1-1 | +-------------------+---------------------+--------------------------+------------------+ 1 row in set (0.00 sec) {code} Node E {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 200-2-116 | 200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node F {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 200-2-116 | 200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node D {code} MariaDB [(none)]> change master to master_host='192.168.47.103', master_user='replica', master_password='replica', master_use_gtid=slave_pos; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.47.103 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin_c.000001 Read_Master_Log_Pos: 466 Relay_Log_File: vmc_d-relay-bin.000002 Relay_Log_Pos: 674 Relay_Master_Log_File: mysql_bin_c.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 466 Relay_Log_Space: 983 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 100-1-1 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec) {code} Node A {code} MariaDB [(none)]> create database test2; Query OK, 1 row affected (0.01 sec) {code} Node D {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +--------------------------+--------------------------+--------------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +--------------------------+--------------------------+--------------------------+------------------+ | 21-2-8,100-1-2,200-2-116 | 21-2-8,100-1-2,200-2-116 | 21-2-8,100-1-2,200-2-116 | 100-1-2 | +--------------------------+--------------------------+--------------------------+------------------+ 1 row in set (0.00 sec) {code} Node E {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 100-1-2,200-2-116 | 100-1-2,200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Node F {code} MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 100-1-2,200-2-116 | 100-1-2,200-2-116 | 200-2-116 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) {code} Taking backup on Node F enabling replication on Node G Node F {code} [root@vmc_f tmp]# mysqldump -u root -proot --master-data=2 --all-databases >/tmp/fullbackup.sql [root@vmc_f tmp]# head -30 fullbackup.sql -- MySQL dump 10.16 Distrib 10.2.32-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.32-MariaDB-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_f.000001', MASTER_LOG_POS=39709; -- -- GTID to start replication from -- -- SET GLOBAL gtid_slave_pos='100-1-2,200-2-116'; -- [root@vmc_f tmp]# scp fullbackup.sql root@192.168.47.107:/tmp root@192.168.47.107's password: fullbackup.sql 100% 36KB 5.6MB/s 00:00 {code} Node G {code} MariaDB [(none)]> select @@wsrep_gtid_domain_id, @@gtid_domain_id,@@server_id, @@wsrep_gtid_mode; +------------------------+------------------+-------------+-------------------+ | @@wsrep_gtid_domain_id | @@gtid_domain_id | @@server_id | @@wsrep_gtid_mode | +------------------------+------------------+-------------+-------------------+ | 300 | 33 | 3 | 1 | +------------------------+------------------+-------------+-------------------+ 1 row in set (0.00 sec) [root@vmc_g ~]# mysql -u root -proot select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+--------------------+------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+--------------------+------------------+ | 300-3-117 | 300-3-117 | 300-3-117 | | +-------------------+---------------------+--------------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SET GLOBAL gtid_slave_pos='100-1-2,200-2-116'; Query OK, 0 rows affected, 1 warning (0.03 sec) MariaDB [(none)]> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1948 | Specified value for @@gtid_slave_pos contains no value for replication domain 300. This conflicts with the binary log which contains GTID 300-3-117. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +-------------------+---------------------+-----------------------------+-------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +-------------------+---------------------+-----------------------------+-------------------+ | 300-3-117 | 300-3-117 | 100-1-2,200-2-116,300-3-117 | 100-1-2,200-2-116 | +-------------------+---------------------+-----------------------------+-------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> change master to master_host='192.168.47.106', master_user='replica', master_password='replica', master_use_gtid=slave_pos; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.47.106 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin_f.000001 Read_Master_Log_Pos: 39709 Relay_Log_File: vmc_g-relay-bin.000002 Relay_Log_Pos: 733 Relay_Master_Log_File: mysql_bin_f.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 39709 Relay_Log_Space: 1042 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 100-1-2,200-2-116 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec) {code} Small load using sysbench on Node A {code} [root@vmc_a ~]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --threads=1 --table-size=1 --tables=1 /usr/share/sysbench/oltp_write_only.lua prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 1 records into 'sbtest1' Creating a secondary index on 'sbtest1'... MariaDB [(none)]> select * from sbtest.sbtest1; +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 1 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 | +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) {code} Output at Node G (After enabling replication) {code} MariaDB [(none)]> select * from sbtest.sbtest1; +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 1 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 | +----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) Meanwhile I created one Database on Node D and it got replicated to Node G as well. Started Load on Node A {code} [root@vmc_a ~]# sysbench /usr/share/sysbench/oltp_write_only.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --db-driver=mysql --tables=1 --threads=1 --rate=1 --events=0 --thread-stack-size=128K --report-interval=10 --time=1200 --forced-shutdown='0' run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 1 Target transaction rate: 1/sec Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 1200 seconds Initializing worker threads... Threads started! .. ..... FATAL: The --max-time limit has expired, forcing shutdown... SQL statistics: queries performed: read: 0 write: 3814 other: 3482 total: 7296 transactions: 1216 (1.01 per sec.) queries: 7296 (6.08 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Number of unfinished transactions on forced shutdown: 1 General statistics: total time: 1200.0036s total number of events: 1216 Latency (ms): min: 1.59 avg: 7.23 max: 64.15 95th percentile: 12.08 sum: 8788.35 Threads fairness: events (avg/stddev): 1217.0000/0.00 execution time (avg/stddev): 8.7884/0.45 MariaDB [(none)]> select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 357 | +----------+ 1 row in set (0.01 sec) {code} Node G MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.47.106 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin_f.000001 Read_Master_Log_Pos: 1587649 Relay_Log_File: vmc_g-relay-bin.000002 Relay_Log_Pos: 1548673 Relay_Master_Log_File: mysql_bin_f.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1587649 Relay_Log_Space: 1548982 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 100-1-1223,200-2-117 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec) MariaDB [(none)]> select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 357 | +----------+ 1 row in set (0.00 sec) ----------------------------------------Till Here replication is working fine with load as well from Node A till Node G------------------------------- Issue starts from here I took backup on Node F while Node A under load and restoring the same on Node G , lead to duplicate entry. Node F [root@vmc_f tmp]# mysqldump -u root -proot --master-data=2 --all-databases>/tmp/fullbackup.sql [root@vmc_f tmp]# head -30 fullbackup.sql -- MySQL dump 10.16 Distrib 10.2.32-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.2.32-MariaDB-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_f.000001', MASTER_LOG_POS=330876; -- -- GTID to start replication from -- -- SET GLOBAL gtid_slave_pos='100-1-286,200-2-117'; -- [root@vmc_f tmp]# scp fullbackup.sql root@192.168.47.107:/tmp root@192.168.47.107's password: fullbackup.sql 100% 65KB 7.3MB/s 00:00 Node G MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.00 sec) [root@vmc_g ~]# ls -ltrh /tmp/fullbackup.sql -rw-r--r-- 1 root root 65K Mar 21 00:37 /tmp/fullbackup.sql [root@vmc_g ~]# mysql -u root -proot select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 140 | +----------+ 1 row in set (0.00 sec) MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +--------------------------------+--------------------------------+--------------------------------+----------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +--------------------------------+--------------------------------+--------------------------------+----------------------+ | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117 | +--------------------------------+--------------------------------+--------------------------------+----------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SET GLOBAL gtid_slave_pos='100-1-286,200-2-117'; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1948 | Specified value for @@gtid_slave_pos contains no value for replication domain 300. This conflicts with the binary log which contains GTID 300-3-242. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos; +--------------------------------+--------------------------------+-------------------------------+---------------------+ | @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos | +--------------------------------+--------------------------------+-------------------------------+---------------------+ | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-286,200-2-117,300-3-242 | 100-1-286,200-2-117 | +--------------------------------+--------------------------------+-------------------------------+---------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> change master to master_host='192.168.47.106', master_user='replica', master_password='replica', master_use_gtid=slave_pos; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.47.106 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin_f.000001 Read_Master_Log_Pos: 1587649 Relay_Log_File: vmc_g-relay-bin.000002 Relay_Log_Pos: 817 Relay_Master_Log_File: mysql_bin_f.000001 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: 1062 Last_Error: Could not execute Write_rows_v1 event on table sbtest.sbtest1; Duplicate entry '5085' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql_bin_f.000001, end_log_pos 43693 Skip_Counter: 0 Exec_Master_Log_Pos: 42853 Relay_Log_Space: 1406302 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: 1062 Last_SQL_Error: Could not execute Write_rows_v1 event on table sbtest.sbtest1; Duplicate entry '5085' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql_bin_f.000001, end_log_pos 43693 Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 100-1-1223,200-2-117 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) MariaDB [(none)]> select * from sbtest.sbtest1 where id=5085; +------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 5085 | 5037 | 39476658110-32559687743-47203182949-51304521021-75485891409-50717849795-47190185045-16047806543-87533311936-70471326632 | 52490813876-70767371353-94467376165-15821586959-80410098309 | +------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 140 | +----------+ 1 row in set (0.00 sec) So what I found here if the backup from Node F is taken with (write) load on Node A and same restored on Node G lead to duplicate entry. Here backup taken on Node F migbe leading to caputre false GTID position causing duplicate entry on Node G.