MySQL GTID下事务处理注意事项

Love The Way You Lie 2022-05-14 10:56 289阅读 0赞

### 什么是GTID ###

什么是GTID呢， 简而言之，就是全局事务ID(global transaction identifier )，最初由google实现，官方MySQL在5.6才加入该功能。  
GTID是事务提交时创建分配的唯一标识符，所有事务均与GTID一一映射。

GTID的格式类似于：  
5882bfb0-c936-11e4-a843-000c292dc103:1  
这个字符串，用“：”分开，前面表示这个服务器的server\_uuid，这是一个128位的随机字符串，在第一次启动时生成（函数generate\_server\_uuid），对应的variables是只读变量server\_uuid。 它能以极高的概率保证全局唯一性，并存到文件  
DATADIR/auto.cnf中。因此要注意保护这个文件不要被删除或修改。

第二部分是一个自增的事务ID号，事务id号+server\_uuid来唯一标示一个事务。

mysql> show global variables like '%gtid%';
    +--------------------------+------------------------------------------+
    | Variable_name            | Value                                    |
    +--------------------------+------------------------------------------+
    | enforce_gtid_consistency | ON                                       |
    | gtid_executed            | 5882bfb0-c936-11e4-a843-000c292dc103:1-6 |
    | gtid_mode                | ON                                       |
    | gtid_owned               |                                          |
    | gtid_purged              |                                          |
    +--------------------------+------------------------------------------+
    5 rows in set (0.00 sec)
    
    mysql> show global variables like '%uuid%';
    +---------------+--------------------------------------+
    | Variable_name | Value                                |
    +---------------+--------------------------------------+
    | server_uuid   | 5882bfb0-c936-11e4-a843-000c292dc103 |
    +---------------+--------------------------------------+
    1 row in set (0.00 sec)
    
    shell> cat auto.cnf 
    [auto]
    server-uuid=5882bfb0-c936-11e4-a843-000c292dc103

### 设置GTID复制 ###

1.  同步主从数据
    
        mysql> SET @@global.read_only = ON;
        Query OK, 0 rows affected (0.01 sec)
2.  停止所有数据库
    
        shell> mysqladmin -u root -p shutdown
3.  设置开发GTID模式并启动所有数据库
    
        shell> vi my.cnf  添加如下内容
        ================================================================
        [mysqld]
        gtid_mode=ON
        log-slave-updates=ON
        enforce-gtid-consistency=ON          #强制GTID的一致性
        ================================================================
4.  从库指定主库
    
        mysql> CHANGE MASTER TO  
             -> MASTER_HOST = host, 
             -> MASTER_PORT = port, 
             -> MASTER_USER = user, 
             -> MASTER_PASSWORD = password, 
             -> MASTER_AUTO_POSITION = 1;
        
        mysql> START SLAVE;
        Query OK, 0 rows affected (0.04 sec)
5.  禁止read-only模式
    
        mysql> SET @@global.read_only = OFF;
        Query OK, 0 rows affected (0.00 sec)

### GTID复制的限制 ###

GTID 模式实例和非GTID模式实例是不能进行复制的，要求非常严格，要么都是GTID，要么都不是  
gtid\_mode 是只读的，要改变状态必须1)关闭实例、2)修改配置文件、3) 重启实例

*  更新非事务引擎表  
    在同一事务中更新事务表与非事务表将导致多个GTIDs分配给同一事务

mysql> cretea table tt (id int) engine=myisam;
    mysql> insert into tt values(1),(2);
    mysql> cretea table t (id int) engine=innodb;
    mysql> insert into t values(1),(2);
    mysql> set autocommit = 0；
    mysql> begin；
    
    mysql> update t set id = 3 where id =2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update tt set id = 3 where id =2;
    ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, 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.

*  CREATE TABLE … SELECT statements  
    不安全的基于语句复制，实际是两个独立的事件，一个用于建表，一个用于向新表插入源表数据。

mysql> create table t engine=innodb as select * from tt;
    ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.

*  临时表  
    事务内部不能执行创建删除临时表语句，但可以在事务外执行，但必须设置set autocommit = 1

mysql> create temporary table tttt(id int);
    ERROR 1787 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and 
    DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
    mysql> set autocommit = 1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create temporary table tttt(id int);
    Query OK, 0 rows affected (0.04 sec)

*  不执行不支持的语句  
    启用--enforce-gtid-consistency选项启动GTID模式，上述不支持的语句将会返回错误。

### 运维操作 ###

a. 忽略复制错误  
当备库复制出错时，传统的跳过错误的方法是设置sql\_slave\_skip\_counter,然后再START SLAVE。  
但如果打开了GTID，就会设置失败：

mysql> stop slave;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> set global sql_slave_skip_counter = 1;
    ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON.
    Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

提示的错误信息告诉我们，可以通过生成一个空事务来跳过错误的事务。  
我们手动产生一个备库复制错误：

[slave]
    mysql> alter table t add primary key pk_id(id);
    Query OK, 2 rows affected (0.12 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> insert into t values(1);
    mysql> insert into t values(4);
    mysql> insert into t values(5);
    
    mysql> show master status ;
    +-------------------+----------+--------------+------------------+-------------------------------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
    +-------------------+----------+--------------+------------------+-------------------------------------------+
    | mysql-info.000004 |      914 |              |                  | 5882bfb0-c936-11e4-a843-000c292dc103:1-17 |
    +-------------------+----------+--------------+------------------+-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show slave status \G
    *************************** 1. row ***************************
         ...
           Slave_IO_Running: Yes
           lave_SQL_Running: No
                 Last_Errno: 1062
                 Last_Error: Could not execute Write_rows event on table db_test.t; Duplicate entry '1' for key 'PRIMARY', 
           Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-info.000004, end_log_pos 401
         Retrieved_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-15
          Executed_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-14,
                             f1e6584a-c935-11e4-a840-000c29348dbe:1
              Auto_Position: 1
    1 row in set (0.00 sec)
    
    
    mysql> SET @@SESSION.GTID_NEXT= '5882bfb0-c936-11e4-a843-000c292dc103:15';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET SESSION GTID_NEXT = AUTOMATIC;
    
    mysql> start slave;
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                       Last_Errno: 0
               Retrieved_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-17
                Executed_Gtid_Set: 5882bfb0-c936-11e4-a843-000c292dc103:1-17,
                                   f1e6584a-c935-11e4-a840-000c29348dbe:1
                    Auto_Position: 1
    1 row in set (0.00 sec)

再查看show slave status，就会发现错误事务已经被跳过了。这种方法的原理很简单，空事务产生的GTID加入到GTID\_EXECUTED中，  
这相当于告诉备库，这个GTID对应的事务已经执行了，此时主从数据不一致。