Mysql使用GTID跳过一个或多个失败事务

IT那活儿 2022-08-21

5271

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

前言

在Mysql运维过程中，偶尔会遇到这样的情况，从库发现同步出现异常，通过分析发现从库表被认为删除导致，或者大批量更新，通过确认这些表都是临时表或者这部分数据可以丢弃，那么可以直接通过GTID跳过失败事务即可。

使用gtid跳过事务有两种方法：

set gtid_next,可以跳过单个事务;
set GTID_PURGED，可以跳过多个事务.

故障模拟场景环境：

LINUX 7.5
MYSQL 5.7.24主从配置

GTID介绍

GTID (Global Transaction ID)是全局事务ID,由主库上生成的与事务绑定的唯一标识，这个标识不仅在主库上是唯一的，在MySQL集群内也是唯一的。

GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

server_uuid 一般是发起事务的uuid, 标识了该事务执行的源节点，存储在数据目录中的auto.cnf文件中，transaction_id 是在该主库上生成的事务序列号，从1开始，1-2代表第二个事务；第1-n代表n个事务。

GTID = server_uuid:transaction_id

场景一处理过程

3.1 查看从库复制状态发现从库复制到主库binlog:mysql-bin.000009，pos：1377位置失败。

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.22
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1941
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 1550
Relay_Master_Log_File: mysql-bin.000009
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: 1051
Last_Error: Error 'Unknown table 'dbtest1.tab2'' on query. Default database: 'dbtest1'. Query: 'DROP TABLE `tab2` /* generated by server */'
Skip_Counter: 0
Exec_Master_Log_Pos: 1377
Relay_Log_Space: 2404
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: 1051
Last_SQL_Error: Error 'Unknown table 'dbtest1.tab2'' on query. Default database: 'dbtest1'. Query: 'DROP TABLE `tab2` /* generated by server */'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200122 10:29:41
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-34
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-31:34
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

3.2 查看主从库表信息发现从库少了tab2，tab3,tab4。

主库：

mysql> show tables from dbtest1;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| tab1 |
| tab2 |
| tab3 |
| tab4 |
+-------------------+
4 rows in set (0.00 sec)

从库：

mysql> show tables from dbtest1;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| tab1 |
+-------------------+
1 row in set (0.00 sec)

3.3 分析主库binlog:mysql-bin.000009，pos：1377开始往后的binlog日志。

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009 --start-position=1377 > mysql-bin.000009.log

示例：

[mysql@gaussdb12 log]$ mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009 --start-position=1377 > mysql-bin.000009.log
[mysql@gaussdb12 log]$
[mysql@gaussdb12 log]$ more mysql-bin.000009.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1377
#200122 10:10:43 server id 330602  end_log_pos 1442 CRC32 0x9bde31e6    GTID last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32'/*!*/;
# at 1442
#200122 10:10:43 server id 330602  end_log_pos 1565 CRC32 0x07275b09    Query thread_id=6     exec_time=0     error_code=0
use `dbtest1`/*!*/;
SET TIMESTAMP=1579659043/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `tab2` /* generated by server */
/*!*/;
# at 1565
#200122 10:10:47 server id 330602  end_log_pos 1630 CRC32 0x5b73e3dd    GTID last_committed=4        sequence_number=5       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33'/*!*/;
# at 1630
#200122 10:10:47 server id 330602  end_log_pos 1753 CRC32 0xadd86b23    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659047/*!*/;
DROP TABLE `tab3` /* generated by server */
/*!*/;
# at 1753
#200122 10:10:52 server id 330602  end_log_pos 1818 CRC32 0x639aca9b    GTID last_committed=5        sequence_number=6       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:34'/*!*/;
# at 1818
#200122 10:10:52 server id 330602  end_log_pos 1941 CRC32 0x41816cc3    Query thread_id=6     exec_time=0     error_code=0
--More--(0%)
[1]+ Stopped more mysql-bin.000009.log
[mysql@gaussdb12 log]$ more mysql-bin.000009.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1377
#200122 10:10:43 server id 330602  end_log_pos 1442 CRC32 0x9bde31e6    GTID last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32'/*!*/;
# at 1442
#200122 10:10:43 server id 330602  end_log_pos 1565 CRC32 0x07275b09    Query thread_id=6     exec_time=0     error_code=0
use `dbtest1`/*!*/;
SET TIMESTAMP=1579659043/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `tab2` /* generated by server */
/*!*/;
# at 1565
#200122 10:10:47 server id 330602  end_log_pos 1630 CRC32 0x5b73e3dd    GTID last_committed=4        sequence_number=5       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33'/*!*/;
# at 1630
#200122 10:10:47 server id 330602  end_log_pos 1753 CRC32 0xadd86b23    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659047/*!*/;
DROP TABLE `tab3` /* generated by server */
/*!*/;
# at 1753
#200122 10:10:52 server id 330602  end_log_pos 1818 CRC32 0x639aca9b    GTID last_committed=5        sequence_number=6       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:34'/*!*/;
# at 1818
#200122 10:10:52 server id 330602  end_log_pos 1941 CRC32 0x41816cc3    Query thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1579659052/*!*/;
DROP TABLE `tab4` /* generated by server */
/*!*/;
# at 1941
#200122 10:41:51 server id 330602  end_log_pos 2006 CRC32 0x4892fb28    GTID last_committed=6        sequence_number=7       rbr_only=no
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:35'/*!*/;
# at 2006
#200122 10:41:51 server id 330602  end_log_pos 3158 CRC32 0x9bde3958    Query thread_id=6     exec_time=1     error_code=0
use `dbtest2`/*!*/;
SET TIMESTAMP=1579660911/*!*/;
CREATE TABLE `tab3` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',

发现从pos 1377到pos 1941的操作为drop tab1,tab2,tab3。

3.4 这时候我们可以选择GTID_NEXT进行一个事务一个事务跳，也可以使用gtid_purged跳过多个事务。

第一种方式使用GTID_NEXT进行单个事务跳跃：

从库：

stop slave sql_thread;
SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32'; <<===GTID对应第三步查出来的失败事务的GTID
show global variables like '%gtid%'; <<===查看全局GTID信息
begin;commit; <<===执行空事务
SET @@SESSION.GTID_NEXT=automatic;                          <<===GTID自动执行
start slave sql_thread; <<===启动SQL线程
show slave status\G; <<===确认事务已跳过

示例：

mysql> SET @@SESSION.GTID_NEXT= '9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%gtid%';
+----------------------------------+-----------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-----------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-31:34 |
| gtid_executed_compression_period | 1000                                                                                    |
| gtid_mode | ON |
| gtid_owned | 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32#6 |
| gtid_purged |                                                                                         |
| session_track_gtids | OFF |
+----------------------------------+-----------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT=automatic;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

检查同步状态：

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.57.22
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 1941
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 1738
Relay_Master_Log_File: mysql-bin.000009
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: 1051
Last_Error: Error 'Unknown table 'dbtest1.tab3'' on query. Default database: 'dbtest1'. Query: 'DROP TABLE `tab3` /* generated by server */'
Skip_Counter: 0
Exec_Master_Log_Pos: 1565
Relay_Log_Space: 2404
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: 1051
Last_SQL_Error: Error 'Unknown table 'dbtest1.tab3'' on query. Default database: 'dbtest1'. Query: 'DROP TABLE `tab3` /* generated by server */'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200122 10:40:18
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-34
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-32:34
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

从同步信息来看已跳过GTID:9c0f0bd7-3b52-11ea-b2f5-000c291e2519:32事务了，现在报dbtest1.tab3不存在，如果继续进行单个事务跳跃重复上面步骤即可。

3.5 接下来使用gtid_purged跳过多个事务。

stop slave;
reset master;
set global gtid_purged='9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34';
start slave;

示例：

mysql> set global gtid_purged='9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)

检查同步状态：

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 76825
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 76998
Relay_Master_Log_File: mysql-bin.000009
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: 76825
Relay_Log_Space: 77288
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: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-38
Executed_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-38
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

确认已和主库同步。

场景二处理过程

4.1 从库复制卡在一个表上的批量大事务或者是从库数据跟主库不一致导致同步复制失败，通过GTID跳过大事务，重新初始化表。

查看slave状态报错：

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 82041
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 79665
Relay_Master_Log_File: mysql-bin.000009
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: 1032
Last_Error: Could not execute Update_rows event on table dbtest1.tab4; Can't find record in 'tab4', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000009, end_log_pos 79825
Skip_Counter: 0
Exec_Master_Log_Pos: 79492
Relay_Log_Space: 82504
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table dbtest1.tab4; Can't find record in 'tab4', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000009, end_log_pos 79825
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200122 14:51:20
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-51
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:1,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-44
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

4.2 发现update行在从库中不存在，开始主库备份表。

主库：

mysqldump -uroot -proot -hlocalhost --
socket=/mysqlhome/mysql_data/db/mysql.sock --single-
transaction --set-gtid-purged=on  --triggers --routines --
events dbtest1 tab4 >/tmp/tab4.sql
cat /tmp/tab4.sql |egrep SET |egrep -v "^/"

示例：

[mysql@gaussdb12 log]$ mysqldump -uroot -proot -hlocalhost --socket=/mysqlhome/mysql_data/db/mysql.sock --single-transaction --set-gtid-purged=on --triggers --routines --events dbtest1 tab4 >/tmp/tab4.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[mysql@gaussdb12 log]$
[mysql@gaussdb12 log]$ cat tmp/tab4.sql |egrep SET |egrep -v "^/"
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

从库恢复：

reset master; //清空GTID_EXECUTED
cat /tmp/tab4.sql |mysql -uroot -proot --socket=/home/mysql_data/db/mysql.sock dbtest1
会执行备份文件中的SET @@GLOBAL.GTID_PURGED。@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
> show slave status \G
Retrieved_Gtid_Set: 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-22
Executed_Gtid_Set: 59fe7a3e-9dd6-11e7-9d6c-000c29e57c69:1-22
> start slave;

示例：

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000  |
| gtid_mode | ON |
| gtid_owned |       |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.01 sec)

[mysql@mysql11 db]$ cat /tmp/tab4.sql |mysql -uroot -proot --socket=/home/mysql_data/db/mysql.sock dbtest1
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show global variables like '%gtid%';
+----------------------------------+---------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61 |
| gtid_executed_compression_period | 1000                                                                                  |
| gtid_mode | ON |
| gtid_owned |                                                                                       |
| gtid_purged | 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61 |
| session_track_gtids | OFF |
+----------------------------------+---------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 86630
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 79665
Relay_Master_Log_File: mysql-bin.000009
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: 1032
Last_Error: Could not execute Update_rows event on table dbtest1.tab4; Can't find record in 'tab4', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000009, end_log_pos 79825
Skip_Counter: 0
Exec_Master_Log_Pos: 79492
Relay_Log_Space: 87093
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table dbtest1.tab4; Can't find record in 'tab4', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000009, end_log_pos 79825
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200122 14:51:20
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-61
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> start slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.XX
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 86630
Relay_Log_File: mysql-relay.000011
Relay_Log_Pos: 86803
Relay_Master_Log_File: mysql-bin.000009
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: 86630
Relay_Log_Space: 87093
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: 330602
Master_UUID: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Executed_Gtid_Set: 66e8e5c1-3b2a-11ea-966e-000c29965524:13-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-63
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

问题处理

set global gtid_purged报ERROR 1840

mysql> set global gtid_purged='9c0f0bd7-3b52-11ea-b2f5-000c291e2519:33-34';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> show global GTID_EXECUTED;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GTID_EXECUTED' at line 1
mysql>
mysql> show global variables like '%gtid%';
+----------------------------------+-----------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-----------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 66e8e5c1-3b2a-11ea-966e-000c29965524:1-37,
9c0f0bd7-3b52-11ea-b2f5-000c291e2519:1-32:34 |
| gtid_executed_compression_period | 1000                                                                                    |
| gtid_mode | ON |
| gtid_owned |                                                                                         |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

原因：

gtid_executed不为空导致。

解决方法：

方法一：reset mater
这个操作可以将当前库的GTID_EXECUTED值置空。
方法二：--set-gtid-purged=off
在dump导出时，添加--set-gtid-purged=off参数，避免将gtid信息导出。
mysqldump -uroot -p --set-gtid-purged=off -d sso > sso1.sql

本文作者：汤杰（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Mysql使用GTID跳过一个或多个失败事务

本文作者：汤 杰（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

评论

本文作者：汤杰（上海新炬王翦团队）