Mysql GTID的限制以及解决方案

矫情吗；* 2023-06-27 13:59 6阅读 0赞

事务中混合多个存储引擎,会产生多个GTID。

1.  当使用GTID,在同一个事务中,更新包括了非事务引擎(MyISAM)和事务引擎(InnoDB)表的操作,就会导致多个GTID分配给同一个事务。

mysql> CREATE TABLE `t_test_myisam` (
        ->   `id` int(11) NOT NULL,
        ->   `name` varchar(10) DEFAULT NULL,
        ->   PRIMARY KEY (`id`)
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update t_test set name ='aa1' where id =1;
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> insert into t_test_myisam values(1,'aa2');
    ERROR 1785 (HY000): Statement violates GTID consistency: 
    Updates to non-transactional tables can only be done in either autocommitted statements 
    or single-statement transactions, and never in the same statement as updates to transactional tables.

1.  主从表的存储引擎不一致,会导致数据不一致。
2.  基于GTID复制,不支持CREATE TABLE…SELECT 语句  
    该语句实际上被记录为两个单独的事件,一个是创建表,另一个插入数据。当事务执行该语句时,在一些情况下,这两个事件可能接收到相同的事务ID,导致插入的事件被从库跳过。

mysql> create table t_test_new as  select * from t_test;
    ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

解决方案:

mysql> create table t_test_new like t_test;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> insert into t_test_new select * from t_test;
    Query OK, 5 rows affected (0.03 sec)
    Records: 5  Duplicates: 0  Warnings: 0

1.  不支持事务里包含 CREAT TEMPORARY TABLE和DROP TEMPORARY TABLE  
    在 autocommit=1的情况下,可以创建临时表,MASTER创建临时表不产生GTID信息,所以不会同步到SLAVE。

#事务里
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE TEMPORARY TABLE TMP_TEST(ID int);
    ERROR 1787 (HY000): Statement violates GTID consistency: 
    CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  
    These statements are also not allowed in a function or trigger because functions and triggers are
    also considered to be multi-statement transactions.
    
    #事务外
    mysql> CREATE TEMPORARY TABLE TMP_TEST(ID int);
    Query OK, 0 rows affected (0.00 sec)

MySQL5.7中cache里面的机制，大体来说，binlog有两个cache来缓存事务的binlog

#存放非事务表和临时表binlog
    binlog_cache_data stmt_cache;  
    #存放事务表binlog
    binlog_cache_data trx_cache;

GTID中,会检查这两个cache,如有冲突,则抛出错误

1.  不推荐在GTID模式的实例上进行 mysql\_upgrade  
    归根结底,还是因为mysql\_upgrade的过程中要创建或修改系统表(非事务引擎)