MySQL GTID est un produit évolué sur la base de la réplication maître-esclave MySQL traditionnelle, qui garantit l'unicité de chaque chose via l'UUID plus l'ID de transaction. Cette méthode de fonctionnement signifie que nous n'avons plus besoin de nous soucier de ce qu'on appelle log_file et log_Pos, mais simplement d'indiquer à la bibliothèque esclave sur quel serveur trouver la bibliothèque principale. Elle simplifie la configuration maître-esclave et le processus de basculement et est plus sûre et plus fiable que la réplication traditionnelle. Étant donné que les GTID sont continus et sans trous, lorsque des conflits de données se produisent dans la bibliothèque maître-esclave, ils peuvent être ignorés en injectant des éléments vides. Cet article décrit principalement la méthode de gestion des erreurs de l'architecture maître-esclave GTID.
Configuration de la réplication maître-esclave MySQL GTID
Création de gtid maître-esclave basé sur mysqldump
很多无法预料的情形导致mysql主从发生事务冲突，主从失败或停止的情形，即需要修复主从 对于GTID方式的主从架构而言，更多的是处理事务冲突来修复主从 GTID不支持通过传统设置sql_slave_skip_counter方法来跳过事务 方法：通过注入空事务来填补事务空洞，等同于传统复制的(set global sql_slave_skip_counter = 1) 步骤： stop slave； set gtid_next='xxxxxxx:N'; --指定下一个事务执行的版本，即想要跳过的GTID begin; commit; --注入一个空事物 set gtid_next='AUTOMATIC' --自动的寻找GTID事务。 start slave; --开始同步
1、主库新增记录，从库提示主键冲突 2、主库对象可更新，从库无对应的对象可更新 3、主库对象可删除，从库无对应的对象可删除 4、通过延迟从修复主库意外删除的对象 5、主库日志被purged的情形
当前演示的主从架构图 # mysqlrplshow --master=root:pass@192.168.1.233:3306 --discover-slaves-login=root:pass --verboseWARNING: Using a password on the command line interface can be insecure. # master on 192.168.1.233: ... connected. # Finding slaves for master: 192.168.1.233:3306 # Replication Topology Graph 192.168.1.233:3306 (MASTER) | +--- 192.168.1.245:3306 [IO: Yes, SQL: Yes] - (SLAVE) | +--- 192.168.1.247:3306 [IO: Yes, SQL: Yes] - (SLAVE)(root@192.168.1.233)[tempdb]>show slave hosts;+-----------+---------------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------+------+-----------+--------------------------------------+| 245 | 192.168.1.245 | 3306 | 233 | 78336cdc-8cfb-11e6-ba9f-000c29328504 || 247 | 192.168.1.247 | 3306 | 233 | 13a26fc1-555a-11e6-b5e0-000c292e1642 | +-----------+---------------+------+-----------+--------------------------------------+--演示的mysql版本 (root@192.168.1.233)[tempdb]>show variables like 'version';+---------------+------------+| Variable_name | Value | +---------------+------------+| version | 5.7.12-log | +---------------+------------+--查看gtid是否开启 (root@192.168.1.233)[tempdb]>show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value | +---------------+-------+| gtid_mode | ON | +---------------+-------+--主库上面可以看到基于gtid的dump线程，如下 (root@192.168.1.233)[tempdb]>show processlist;+----+------+-----------------------+--------+------------------+------+| Id | User | Host | db | Command | Time | +----+------+-----------------------+--------+------------------+------+| 17 | repl | node245.edq.com:52685 | NULL | Binlog Dump GTID | 2738 | | 18 | repl | node247.edq.com:33516 | NULL | Binlog Dump GTID | 2690 || 24 | root | localhost | tempdb | Query | 0 | +----+------+-----------------------+--------+------------------+------+
(root@Master)[tempdb]>create table t1 (
-> id tinyint not null primary key,ename varchar(20),blog varchar(50));
(root@Master)[tempdb]>insert into t1
-> values(1,'leshami','http://blog.csdn.net/leshami');
(root@Master)[tempdb]>insert into t1
-> values(2,'robin','http://blog.csdn.net/robinson_0612');
(root@Master)[tempdb]>set sql_log_bin=off;
(root@Master)[tempdb]>delete from t1 where ename='robin';
(root@Master)[tempdb]>set sql_log_bin=on;
(root@Master)[tempdb]>insert into t1
-> values(2,'robin','http://blog.csdn.net/robinson_0612');
-- 从库状态报错，提示重复的primary key
(root@Slave)[tempdb]>show slave status \G
*************************** 1. row ***************************Last_Errno: 1062Last_Error: Could not execute Write_rows event on table tempdb.t1; Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log node233-binlog.000004, end_log_pos 4426
Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-89
Auto_Position: 1
-- 如下解决方案，可以通过删除重库的这条记录
(root@Slave)[tempdb]>stop slave;
(root@Slave)[tempdb]>delete from t1 where ename='robin';
(root@Slave)[tempdb]>start slave;
(root@Slave)[tempdb]>show slave status \G
*************************** 1. row ***************************
Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90,
78336cdc-8cfb-11e6-ba9f-000c29328504:1 --这里多了一个GTID，注意这个是从库上执行的，这里的UUID跟IP 245的UUID一致
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
(root@Slave)[tempdb]>show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 78336cdc-8cfb-11e6-ba9f-000c29328504 |
+---------------+--------------------------------------+--首先在从库上删除leshami这条记录
(root@Slave)[tempdb]>delete from t1 where ename='leshami';
--接下来再主库尝试更新leshami这条记录
(root@Master)[tempdb]>update t1 set
-> blog='http://blog.csdn.net/robinson_0612' where ename='leshami';Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查看从库状态
(root@Slave)[tempdb]>show slave status \G*************************** 1. row ***************************Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table tempdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log node233-binlog.000004, end_log_pos 4769Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-2-- 通过mysqlbinlog在主服务器上寻找报错的binglog日志文件及位置，找到对应的SQL语句，如下所示
-- update中的where后面的部分为更新前的数据，set部分为更新后的数据，因此可以将更新前的数据插入到从库# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/node233-binlog.000004|grep -A '10' 4769#161009 13:46:34 server id 233 end_log_pos 4769 CRC32 0xb60df74e Update_rows: table id 147 flags: STMT_END_F### UPDATE `tempdb`.`t1`### WHERE### @1=1 /* TINYINT meta=0 nullable=0 is_null=0 */### @2='leshami' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */### @3='http://blog.csdn.net/leshami' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */### SET### @1=1 /* TINYINT meta=0 nullable=0 is_null=0 */### @2='leshami' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */### @3='http://blog.csdn.net/robinson_0612' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */# at 4769#161009 13:46:34 server id 233 end_log_pos 4800 CRC32 0xa9669811 Xid = 1749COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
(root@Slave)[tempdb]>select * from t1;
+----+-------+------------------------------------+
| id | ename | blog |
+----+-------+------------------------------------+
| 2 | robin | http://www.php.cn/ |
+----+-------+------------------------------------+
(root@Slave)[tempdb]>stop slave sql_thread;
(root@Slave)[tempdb]>insert into t1 values(1,'leshami','http://blog.csdn.net/leshami');
(root@Slave)[tempdb]>start slave sql_thread;
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-3 Auto_Position: 1-- 如果是在主库上删除记录，而从库上找不到对应的记录，则可以直接跳过该事务 -- 下面我们首选在从库上删除一条记录 (root@Slave)[tempdb]>delete from t1 where ename='robin'; -- 接下来在主库上删除该记录 (root@Master)[tempdb]>delete from t1 where ename='robin'; -- 从库端提示无法找到对应的记录 (root@Slave)[tempdb]>show slave status \G*************************** 1. row ***************************Last_SQL_Error: Could not execute Delete_rows event on table tempdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log node233-binlog.000004, end_log_pos 5070Last_SQL_Error_Timestamp: 161009 15:08:06 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1 -- 下面通过注入空事务来跳过 (root@Slave)[tempdb]>stop slave sql_thread; (root@Slave)[tempdb]>set gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:92'; (root@Slave)[tempdb]>begin;commit; (root@Slave)[tempdb]>set gtid_next='AUTOMATIC'; (root@Slave)[tempdb]>start slave sql_thread; (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-92, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
-- 主库上面新增表及记录
(root@Master)[tempdb]>create table t2 (id tinyint not null primary key,
-> ename varchar(20),blog varchar(50));(root@Master)[tempdb]>insert into t2
-> values(1,'leshami','http://blog.csdn.net/leshami');(root@Master)[tempdb]>insert into t2
-> values(2,'robin','http://blog.csdn.net/robinson_0612');(root@Master)[tempdb]>select * from t2;
+----+---------+------------------------------------+
| id | ename | blog |
+----+---------+------------------------------------+
| 1 | leshami | http://www.php.cn/ |
| 2 | robin | http://www.php.cn/ |
+----+---------+------------------------------------+
--先将从库配置为延迟从
(root@Slave)[tempdb]>stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>CHANGE MASTER TO MASTER_DELAY = 300;
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>start slave sql_thread;
Query OK, 0 rows affected (0.02 sec)
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes SQL_Delay: 300 root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-99 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1--查看主库上的binglog gtid
(root@Master)[tempdb]>show master status\G*************************** 1. row *************************** File: node233-binlog.000004 Position: 6970 Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99
1 row in set (0.00 sec)
--在主库上truncate t2
(root@Master)[tempdb]>truncate table t2;
Query OK, 0 rows affected (0.03 sec)
--再次查看主库上的binglog gtid，有99变成了100，这个100即是我们需要跳过的ID
(root@Master)[tempdb]>show master status\G*************************** 1. row *************************** File: node233-binlog.000004 Position: 7121 Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100
1 row in set (0.00 sec)
--从库上跳过被意外truncate的事务
(root@Slave)[tempdb]>stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>set gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100';
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>start slave sql_thread;
Query OK, 0 rows affected (0.02 sec)
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000004 Read_Master_Log_Pos: 7121 Relay_Log_File: node245-relay-bin.000003 Relay_Log_Pos: 2982 Relay_Master_Log_File: node233-binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........................... Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1-- 很多时候我们并不知道表何时被truncate，因此可以从binlog日志得到其gtid
-- 如下所示，可以得到这串 SET @@SESSION.GTID_NEXT= '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'
-- 100即为这个truncate对应的gtid的事务号# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/node233-binlog.000004|grep -i \> "truncate table t2" -A3 -B10 ### @3='http://blog.csdn.net/robinson_0612' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */# at 6939#161009 18:04:58 server id 233 end_log_pos 6970 CRC32 0x71c5121c Xid = 1775COMMIT/*!*/;# at 6970#161009 18:08:42 server id 233 end_log_pos 7035 CRC32 0x00ba9437 GTID last_committed=26 sequence_number=27SET @@SESSION.GTID_NEXT= '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'/*!*/;# at 7035#161009 18:08:42 server id 233 end_log_pos 7121 CRC32 0x5a8b9723 Query thread_id=26 exec_time=0 error_code=0SET TIMESTAMP=1476007722/*!*/;
truncate table t2
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;-- 首先停止从库，模拟从库被意外宕机 (root@Slave)[tempdb]>stop slave; Query OK, 0 rows affected (0.08 sec) --在主库上进行相应的操作 --此时主库上的gtid_purged为空 (root@Master)[tempdb]>show variables like '%gtid_purged%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_purged | | +---------------+-------+ --查看主库binlog (root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000001 | 1362104 | | node233-binlog.000002 | 1331 | | node233-binlog.000003 | 217 | | node233-binlog.000004 | 7121 | +-----------------------+-----------+ (root@Master)[tempdb]>select * from t1; +----+---------+------------------------------------+ | id | ename | blog | +----+---------+------------------------------------+ | 1 | leshami | http://www.php.cn/ | | 2 | robin | http://www.php.cn/ | +----+---------+------------------------------------+ --从主库删除记录 (root@Master)[tempdb]>delete from t1; --切换日志 (root@Master)[tempdb]>flush logs; --新增记录 (root@Master)[tempdb]>insert into t1 values(1, -> 'xuputi','http://blog.csdn.net/leshami');(root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000001 | 1362104 | | node233-binlog.000002 | 1331 | | node233-binlog.000003 | 217 | | node233-binlog.000004 | 7513 | | node233-binlog.000005 | 490 | +-----------------------+-----------+ --清理binlog (root@Master)[tempdb]>purge binary logs to 'node233-binlog.000005'; Query OK, 0 rows affected (0.01 sec) (root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000005 | 490 | +-----------------------+-----------+ --此时可以看到相应的gtid_purged值 (root@Master)[tempdb]>show variables like '%gtid_purged%'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | gtid_purged | 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101 | +---------------+--------------------------------------------+ --下面启动从库 (root@Slave)[tempdb]>start slave; Query OK, 0 rows affected (0.00 sec) --从库状态提示有日志被purged (root@Slave)[tempdb]>show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000004 Read_Master_Log_Pos: 7121 Relay_Log_File: node245-relay-bin.000003 Relay_Log_Pos: 3133 Relay_Master_Log_File: node233-binlog.000004 Slave_IO_Running: No Slave_SQL_Running: Yes ............... Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' .................. Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1-- 从库上gtid_purged参数，此时为75 (root@Slave)[tempdb]>show variables like '%gtid_purged%'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_purged | 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-75 | +---------------+-------------------------------------------+ --停止从库 (root@Slave)[tempdb]>stop slave; Query OK, 0 rows affected (0.01 sec) --下面尝试使用gtid_purged进行跳过事务，，如下，提示仅仅当GLOBAL.GTID_EXECUTED为空才能被设置 (root@Slave)[tempdb]>set global gtid_purged = '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. --如下查看，已经存在被执行的gtid，即gtid_executed肯定是不为空，且这些gtid记录在从库的binary log中 (root@Slave)[tempdb]>show global variables like '%gtid_executed%'\G*************************** 1. row ***************************Variable_name: gtid_executed Value: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4*************************** 2. row ***************************Variable_name: gtid_executed_compression_period Value: 1000--下面我们在从库上reset master，即清空从库binlog (root@Slave)[tempdb]>reset master; Query OK, 0 rows affected (0.05 sec) --再次查看gtid_executed已经为空值 (root@Slave)[tempdb]>show global variables like '%gtid_executed%'\G*************************** 1. row ***************************Variable_name: gtid_executed Value: *************************** 2. row ***************************Variable_name: gtid_executed_compression_period Value: 1000--此时再次设置gtid_purged的值 (root@Slave)[tempdb]>set global gtid_purged = '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101'; Query OK, 0 rows affected (0.01 sec) --启动从库 (root@Slave)[tempdb]>start slave; Query OK, 0 rows affected (0.03 sec) --提示有重复记录，如下所示 --是由于我们在从库停止期间delete这个事务没有被从库的relay log接受到 --其次主从的binlog又被purged，而且从库启动后，执行了gtid_purged，因此主库上新增的记录在从库上提示主键重复 (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000005 Read_Master_Log_Pos: 490 Relay_Log_File: node245-relay-bin.000004 Relay_Log_Pos: 417 Relay_Master_Log_File: node233-binlog.000005 Slave_IO_Running: Yes Slave_SQL_Running: No ................ Last_SQL_Error: Could not execute Write_rows event on table tempdb.t1; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log node233-binlog.000005, end_log_pos 459 Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100:102 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101 Auto_Position: 1--在从库上删除id为1的记录 (root@Slave)[tempdb]>delete from t1 where id=1; Query OK, 1 row affected (0.05 sec) --启动从库的sql_thread线程 (root@Slave)[tempdb]>start slave sql_thread; Query OK, 0 rows affected (0.02 sec) --再次查看正常 (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000005 Read_Master_Log_Pos: 490 Relay_Log_File: node245-relay-bin.000004 Relay_Log_Pos: 713 Relay_Master_Log_File: node233-binlog.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes--上面的这个示例，主要是演示我们使用gtid_purged方式来达到跳过事务的目的 --事实上，主从的数据已经不一致了，应根据实际的需要考虑是否进行相应的修复
2 En raison du caractère unique de l'ID de transaction, il est possible d'appliquer le GTID d'autres bibliothèques esclaves à d'autres bibliothèques esclaves, ce qui permet un basculement pratique
3. . GTID est continu et non creux, donc pour les conflits Dans ce cas, des transactions vides doivent être injectées pour obtenir
4. Les erreurs humaines causées par la suppression accidentelle d'objets sur la base de données principale peuvent être évitées en configurant des délais
.
MySQL GTID est dans le maître-esclave MySQL traditionnel. Le produit a évolué sur la base de la réplication, qui utilise l'UUID plus l'ID de transaction pour garantir l'unicité de chaque chose. Cette méthode de fonctionnement signifie que nous n'avons plus besoin de nous soucier de ce qu'on appelle log_file et log_Pos, mais simplement d'indiquer à la bibliothèque esclave sur quel serveur trouver la bibliothèque principale. Elle simplifie la configuration maître-esclave et le processus de basculement et est plus sûre et plus fiable que la réplication traditionnelle. Étant donné que les GTID sont continus et sans trous, lorsque des conflits de données surviennent dans la bibliothèque maître-esclave, ils peuvent être ignorés en injectant des éléments vides. Cet article décrit principalement la méthode de gestion des erreurs de l'architecture maître-esclave GTID.1. Fonctionnalités associées de GTIDConfiguration de la réplication maître-esclave MySQL GTID
Création de gtid maître-esclave basé sur mysqldump
很多无法预料的情形导致mysql主从发生事务冲突，主从失败或停止的情形，即需要修复主从
对于GTID方式的主从架构而言，更多的是处理事务冲突来修复主从
GTID不支持通过传统设置sql_slave_skip_counter方法来跳过事务
方法：通过注入空事务来填补事务空洞，等同于传统复制的(set global sql_slave_skip_counter = 1)
步骤：
stop slave；
set gtid_next='xxxxxxx:N'; --指定下一个事务执行的版本，即想要跳过的GTID
begin;
commit; --注入一个空事物
set gtid_next='AUTOMATIC' --自动的寻找GTID事务。
start slave; --开始同步 1、主库新增记录，从库提示主键冲突
2、主库对象可更新，从库无对应的对象可更新
3、主库对象可删除，从库无对应的对象可删除
4、通过延迟从修复主库意外删除的对象
5、主库日志被purged的情形当前演示的主从架构图 # mysqlrplshow --master=root:pass@192.168.1.233:3306 --discover-slaves-login=root:pass --verboseWARNING: Using a password on the command line interface can be insecure. # master on 192.168.1.233: ... connected. # Finding slaves for master: 192.168.1.233:3306 # Replication Topology Graph 192.168.1.233:3306 (MASTER) | +--- 192.168.1.245:3306 [IO: Yes, SQL: Yes] - (SLAVE) | +--- 192.168.1.247:3306 [IO: Yes, SQL: Yes] - (SLAVE)(root@192.168.1.233)[tempdb]>show slave hosts;+-----------+---------------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------+------+-----------+--------------------------------------+| 245 | 192.168.1.245 | 3306 | 233 | 78336cdc-8cfb-11e6-ba9f-000c29328504 || 247 | 192.168.1.247 | 3306 | 233 | 13a26fc1-555a-11e6-b5e0-000c292e1642 | +-----------+---------------+------+-----------+--------------------------------------+--演示的mysql版本 (root@192.168.1.233)[tempdb]>show variables like 'version';+---------------+------------+| Variable_name | Value | +---------------+------------+| version | 5.7.12-log | +---------------+------------+--查看gtid是否开启 (root@192.168.1.233)[tempdb]>show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value | +---------------+-------+| gtid_mode | ON | +---------------+-------+--主库上面可以看到基于gtid的dump线程，如下 (root@192.168.1.233)[tempdb]>show processlist;+----+------+-----------------------+--------+------------------+------+| Id | User | Host | db | Command | Time | +----+------+-----------------------+--------+------------------+------+| 17 | repl | node245.edq.com:52685 | NULL | Binlog Dump GTID | 2738 | | 18 | repl | node247.edq.com:33516 | NULL | Binlog Dump GTID | 2690 || 24 | root | localhost | tempdb | Query | 0 | +----+------+-----------------------+--------+------------------+------+
(root@Master)[tempdb]>create table t1 (
-> id tinyint not null primary key,ename varchar(20),blog varchar(50));
(root@Master)[tempdb]>insert into t1
-> values(1,'leshami','http://blog.csdn.net/leshami');
(root@Master)[tempdb]>insert into t1
-> values(2,'robin','http://blog.csdn.net/robinson_0612');
(root@Master)[tempdb]>set sql_log_bin=off;
(root@Master)[tempdb]>delete from t1 where ename='robin';
(root@Master)[tempdb]>set sql_log_bin=on;
(root@Master)[tempdb]>insert into t1
-> values(2,'robin','http://blog.csdn.net/robinson_0612');
-- 从库状态报错，提示重复的primary key
(root@Slave)[tempdb]>show slave status \G
*************************** 1. row ***************************Last_Errno: 1062Last_Error: Could not execute Write_rows event on table tempdb.t1; Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log node233-binlog.000004, end_log_pos 4426
Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-89
Auto_Position: 1
-- 如下解决方案，可以通过删除重库的这条记录
(root@Slave)[tempdb]>stop slave;
(root@Slave)[tempdb]>delete from t1 where ename='robin';
(root@Slave)[tempdb]>start slave;
(root@Slave)[tempdb]>show slave status \G
*************************** 1. row ***************************
Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-90
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90,
78336cdc-8cfb-11e6-ba9f-000c29328504:1 --这里多了一个GTID，注意这个是从库上执行的，这里的UUID跟IP 245的UUID一致
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
(root@Slave)[tempdb]>show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 78336cdc-8cfb-11e6-ba9f-000c29328504 |
+---------------+--------------------------------------+--首先在从库上删除leshami这条记录
(root@Slave)[tempdb]>delete from t1 where ename='leshami';
--接下来再主库尝试更新leshami这条记录
(root@Master)[tempdb]>update t1 set
-> blog='http://blog.csdn.net/robinson_0612' where ename='leshami';Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查看从库状态
(root@Slave)[tempdb]>show slave status \G*************************** 1. row ***************************Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table tempdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log node233-binlog.000004, end_log_pos 4769Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-90, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-2-- 通过mysqlbinlog在主服务器上寻找报错的binglog日志文件及位置，找到对应的SQL语句，如下所示
-- update中的where后面的部分为更新前的数据，set部分为更新后的数据，因此可以将更新前的数据插入到从库# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/node233-binlog.000004|grep -A '10' 4769#161009 13:46:34 server id 233 end_log_pos 4769 CRC32 0xb60df74e Update_rows: table id 147 flags: STMT_END_F### UPDATE `tempdb`.`t1`### WHERE### @1=1 /* TINYINT meta=0 nullable=0 is_null=0 */### @2='leshami' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */### @3='http://blog.csdn.net/leshami' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */### SET### @1=1 /* TINYINT meta=0 nullable=0 is_null=0 */### @2='leshami' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */### @3='http://blog.csdn.net/robinson_0612' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */# at 4769#161009 13:46:34 server id 233 end_log_pos 4800 CRC32 0xa9669811 Xid = 1749COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
(root@Slave)[tempdb]>select * from t1;
+----+-------+------------------------------------+
| id | ename | blog |
+----+-------+------------------------------------+
| 2 | robin | http://www.php.cn/ |
+----+-------+------------------------------------+
(root@Slave)[tempdb]>stop slave sql_thread;
(root@Slave)[tempdb]>insert into t1 values(1,'leshami','http://blog.csdn.net/leshami');
(root@Slave)[tempdb]>start slave sql_thread;
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-91 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-3 Auto_Position: 1-- 如果是在主库上删除记录，而从库上找不到对应的记录，则可以直接跳过该事务 -- 下面我们首选在从库上删除一条记录 (root@Slave)[tempdb]>delete from t1 where ename='robin'; -- 接下来在主库上删除该记录 (root@Master)[tempdb]>delete from t1 where ename='robin'; -- 从库端提示无法找到对应的记录 (root@Slave)[tempdb]>show slave status \G*************************** 1. row ***************************Last_SQL_Error: Could not execute Delete_rows event on table tempdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log node233-binlog.000004, end_log_pos 5070Last_SQL_Error_Timestamp: 161009 15:08:06 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-91, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1 -- 下面通过注入空事务来跳过 (root@Slave)[tempdb]>stop slave sql_thread; (root@Slave)[tempdb]>set gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:92'; (root@Slave)[tempdb]>begin;commit; (root@Slave)[tempdb]>set gtid_next='AUTOMATIC'; (root@Slave)[tempdb]>start slave sql_thread; (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-92 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-92, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
-- 主库上面新增表及记录
(root@Master)[tempdb]>create table t2 (id tinyint not null primary key,
-> ename varchar(20),blog varchar(50));(root@Master)[tempdb]>insert into t2
-> values(1,'leshami','http://blog.csdn.net/leshami');(root@Master)[tempdb]>insert into t2
-> values(2,'robin','http://blog.csdn.net/robinson_0612');(root@Master)[tempdb]>select * from t2;
+----+---------+------------------------------------+
| id | ename | blog |
+----+---------+------------------------------------+
| 1 | leshami | http://www.php.cn/ |
| 2 | robin | http://www.php.cn/ |
+----+---------+------------------------------------+
--先将从库配置为延迟从
(root@Slave)[tempdb]>stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>CHANGE MASTER TO MASTER_DELAY = 300;
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>start slave sql_thread;
Query OK, 0 rows affected (0.02 sec)
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes SQL_Delay: 300 root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-99 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1--查看主库上的binglog gtid
(root@Master)[tempdb]>show master status\G*************************** 1. row *************************** File: node233-binlog.000004 Position: 6970 Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-99
1 row in set (0.00 sec)
--在主库上truncate t2
(root@Master)[tempdb]>truncate table t2;
Query OK, 0 rows affected (0.03 sec)
--再次查看主库上的binglog gtid，有99变成了100，这个100即是我们需要跳过的ID
(root@Master)[tempdb]>show master status\G*************************** 1. row *************************** File: node233-binlog.000004 Position: 7121 Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100
1 row in set (0.00 sec)
--从库上跳过被意外truncate的事务
(root@Slave)[tempdb]>stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>set gtid_next='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100';
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
(root@Slave)[tempdb]>set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
(root@Slave)[tempdb]>start slave sql_thread;
Query OK, 0 rows affected (0.02 sec)
(root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000004 Read_Master_Log_Pos: 7121 Relay_Log_File: node245-relay-bin.000003 Relay_Log_Pos: 2982 Relay_Master_Log_File: node233-binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........................... Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1-- 很多时候我们并不知道表何时被truncate，因此可以从binlog日志得到其gtid
-- 如下所示，可以得到这串 SET @@SESSION.GTID_NEXT= '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'
-- 100即为这个truncate对应的gtid的事务号# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /data/node233-binlog.000004|grep -i \> "truncate table t2" -A3 -B10 ### @3='http://blog.csdn.net/robinson_0612' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */# at 6939#161009 18:04:58 server id 233 end_log_pos 6970 CRC32 0x71c5121c Xid = 1775COMMIT/*!*/;# at 6970#161009 18:08:42 server id 233 end_log_pos 7035 CRC32 0x00ba9437 GTID last_committed=26 sequence_number=27SET @@SESSION.GTID_NEXT= '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:100'/*!*/;# at 7035#161009 18:08:42 server id 233 end_log_pos 7121 CRC32 0x5a8b9723 Query thread_id=26 exec_time=0 error_code=0SET TIMESTAMP=1476007722/*!*/;
truncate table t2
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;-- 首先停止从库，模拟从库被意外宕机 (root@Slave)[tempdb]>stop slave; Query OK, 0 rows affected (0.08 sec) --在主库上进行相应的操作 --此时主库上的gtid_purged为空 (root@Master)[tempdb]>show variables like '%gtid_purged%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_purged | | +---------------+-------+ --查看主库binlog (root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000001 | 1362104 | | node233-binlog.000002 | 1331 | | node233-binlog.000003 | 217 | | node233-binlog.000004 | 7121 | +-----------------------+-----------+ (root@Master)[tempdb]>select * from t1; +----+---------+------------------------------------+ | id | ename | blog | +----+---------+------------------------------------+ | 1 | leshami | http://www.php.cn/ | | 2 | robin | http://www.php.cn/ | +----+---------+------------------------------------+ --从主库删除记录 (root@Master)[tempdb]>delete from t1; --切换日志 (root@Master)[tempdb]>flush logs; --新增记录 (root@Master)[tempdb]>insert into t1 values(1, -> 'xuputi','http://blog.csdn.net/leshami');(root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000001 | 1362104 | | node233-binlog.000002 | 1331 | | node233-binlog.000003 | 217 | | node233-binlog.000004 | 7513 | | node233-binlog.000005 | 490 | +-----------------------+-----------+ --清理binlog (root@Master)[tempdb]>purge binary logs to 'node233-binlog.000005'; Query OK, 0 rows affected (0.01 sec) (root@Master)[tempdb]>show binary logs; +-----------------------+-----------+ | Log_name | File_size | +-----------------------+-----------+ | node233-binlog.000005 | 490 | +-----------------------+-----------+ --此时可以看到相应的gtid_purged值 (root@Master)[tempdb]>show variables like '%gtid_purged%'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | gtid_purged | 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101 | +---------------+--------------------------------------------+ --下面启动从库 (root@Slave)[tempdb]>start slave; Query OK, 0 rows affected (0.00 sec) --从库状态提示有日志被purged (root@Slave)[tempdb]>show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000004 Read_Master_Log_Pos: 7121 Relay_Log_File: node245-relay-bin.000003 Relay_Log_Pos: 3133 Relay_Master_Log_File: node233-binlog.000004 Slave_IO_Running: No Slave_SQL_Running: Yes ............... Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' .................. Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4 Auto_Position: 1-- 从库上gtid_purged参数，此时为75 (root@Slave)[tempdb]>show variables like '%gtid_purged%'; +---------------+-------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------+ | gtid_purged | 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-75 | +---------------+-------------------------------------------+ --停止从库 (root@Slave)[tempdb]>stop slave; Query OK, 0 rows affected (0.01 sec) --下面尝试使用gtid_purged进行跳过事务，，如下，提示仅仅当GLOBAL.GTID_EXECUTED为空才能被设置 (root@Slave)[tempdb]>set global gtid_purged = '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. --如下查看，已经存在被执行的gtid，即gtid_executed肯定是不为空，且这些gtid记录在从库的binary log中 (root@Slave)[tempdb]>show global variables like '%gtid_executed%'\G*************************** 1. row ***************************Variable_name: gtid_executed Value: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-100, 78336cdc-8cfb-11e6-ba9f-000c29328504:1-4*************************** 2. row ***************************Variable_name: gtid_executed_compression_period Value: 1000--下面我们在从库上reset master，即清空从库binlog (root@Slave)[tempdb]>reset master; Query OK, 0 rows affected (0.05 sec) --再次查看gtid_executed已经为空值 (root@Slave)[tempdb]>show global variables like '%gtid_executed%'\G*************************** 1. row ***************************Variable_name: gtid_executed Value: *************************** 2. row ***************************Variable_name: gtid_executed_compression_period Value: 1000--此时再次设置gtid_purged的值 (root@Slave)[tempdb]>set global gtid_purged = '1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101'; Query OK, 0 rows affected (0.01 sec) --启动从库 (root@Slave)[tempdb]>start slave; Query OK, 0 rows affected (0.03 sec) --提示有重复记录，如下所示 --是由于我们在从库停止期间delete这个事务没有被从库的relay log接受到 --其次主从的binlog又被purged，而且从库启动后，执行了gtid_purged，因此主库上新增的记录在从库上提示主键重复 (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000005 Read_Master_Log_Pos: 490 Relay_Log_File: node245-relay-bin.000004 Relay_Log_Pos: 417 Relay_Master_Log_File: node233-binlog.000005 Slave_IO_Running: Yes Slave_SQL_Running: No ................ Last_SQL_Error: Could not execute Write_rows event on table tempdb.t1; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log node233-binlog.000005, end_log_pos 459 Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:76-100:102 Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-101 Auto_Position: 1--在从库上删除id为1的记录 (root@Slave)[tempdb]>delete from t1 where id=1; Query OK, 1 row affected (0.05 sec) --启动从库的sql_thread线程 (root@Slave)[tempdb]>start slave sql_thread; Query OK, 0 rows affected (0.02 sec) --再次查看正常 (root@Slave)[tempdb]>show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: Master Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node233-binlog.000005 Read_Master_Log_Pos: 490 Relay_Log_File: node245-relay-bin.000004 Relay_Log_Pos: 713 Relay_Master_Log_File: node233-binlog.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes--上面的这个示例，主要是演示我们使用gtid_purged方式来达到跳过事务的目的 --事实上，主从的数据已经不一致了，应根据实际的需要考虑是否进行相应的修复
2 En raison de l'ID de transaction, son caractère unique permet d'appliquer le GTID d'autres bibliothèques esclaves à d'autres bibliothèques esclaves, c'est-à-dire qu'il fournit un basculement pratique
3. Le GTID est continu. et non vide. Par conséquent, en cas de conflit, il est nécessaire d'injecter des transactions nulles pour obtenir
4. Les erreurs humaines causées par la suppression accidentelle d'objets sur la bibliothèque principale peuvent être évitées en configurant des délais
Ce qui précède est le contenu Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php .cn) !
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
