资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/Mysql使用GTID跳过一个或多个失败事务

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

IT那活儿 / 879人阅读
Mysql使用GTID跳过一个或多个失败事务
点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！
1

前 言

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

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

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

故障模拟场景环境：

  • LINUX 7.5

  • MYSQL 5.7.24主从配置

2

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

场景一处理过程

3.1 查看从库复制状态发现从库复制到主库binlog:mysql-bin.000009，pos：1377位置失败。
mysql> show slave statusG;
*************************** 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 statusG; <<===确认事务已跳过
示例：
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 statusG;
*************************** 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 statusG;
*************************** 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

场景二处理过程

4.1 从库复制卡在一个表上的批量大事务或者是从库数据跟主库不一致导致同步复制失败，通过GTID跳过大事务，重新初始化表。
查看slave状态报错：
mysql> show slave statusG;
*************************** 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; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events 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; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events 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 dont 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 statusG;
*************************** 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; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events 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; Cant find record in tab4, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the events 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 statusG;
*************************** 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那活儿”公众号

云服务器 GPU云服务器 一个数据库多个公司使用 Gtid MySQL 使用事务 mysql事务

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129327.html

相关文章

  • 深度分析 | MGR相同GTID产生不同transaction故障分析

    摘要：对于该故障的分析，我们要从主从实例相同，但是事务不同的原因入手，该问题猜测与相关，我们针对同步事务的时序做如下分析。接受者被动接收提议者的提议，并记录和反馈，或学习达成共识的提议。节点将的提案信息发送至组内，仍收到了大多数成员返回。 本文是由爱可生运维团队出品的「MySQL专栏」系列文章，内容来自于运维团队一线实战经验，涵盖MySQL各种特性的实践，优化案例，数据库架构，HA，监控等...

    wuaiqiu 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<