說GTID - GTID-based復制中的限制

• 同一個事務（語句）中，不能同時涉及事務和非事務數據表的變更，這會導致一個事務對應多個GTID，違反了事務與GTID的一對一對應原則。
  

[root@mysql.sock][db1]> show create table t2 \G

*************************** 1. row ***************************

? ? ? ?Table: t2

Create Table: CREATE TABLE `t2` (

? `id` int(11) NOT NULL AUTO_INCREMENT,

? PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

[root@mysql.sock][db1]> start transaction;

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][db1]> insert into t1 select null;

Query OK, 1 row affected (0.00 sec)

Records: 1? Duplicates: 0? Warnings: 0

[root@mysql.sock][db1]> insert into t2 select null;

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.

[root@mysql.sock][db1]> commit;

Query OK, 0 rows affected (0.01 sec)

• 不能使用CREATE TABLE ... SELECT語句。

[root@mysql.sock][db1]> create table t2 select * from t2;

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

• 在事務，存儲過程，函數和觸發器中，不能使用CREATE TEMPORARY TABLE，和DROP TEMPORARY TABLE語句。

[root@mysql.sock][db1]> start transaction;

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][db1]> create temporary table t4 (id int auto_increment primary key);

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.

[root@mysql.sock][db1]> rollback;

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][db1]> create temporary table t4 (id int auto_increment primary key);

Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock][db1]> desc t4;

+-------+---------+------+-----+---------+----------------+

| Field | Type? ? | Null | Key | Default | Extra? ? ? ? ? |

+-------+---------+------+-----+---------+----------------+

| id? ? | int(11) | NO? ?| PRI | NULL? ? | auto_increment |

+-------+---------+------+-----+---------+----------------+

1 row in set (0.00 sec)

[root@mysql.sock][db1]> drop temporary table t4;

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][db1]> desc t4;

ERROR 1146 (42S02): Table 'db1.t4' doesn't exist

• 開啟--enforce-gtid-consistency參數，阻止執行違反GTID-based復制原則的語句。

• 跳過事務的執行，參數sql_slave_skip_counter不再起作用，需要使用如下注入空事務的方式跳過。

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';

BEGIN;

COMMIT;

SET GTID_NEXT='AUTOMATIC';

CHANGE MASTER TO語句中的IGNORE_SERVER_IDS不在起作用，已經回放過的事務會自動跳過。

• 在開啟GTID的實例上使用mysqldump導出數據時，在備份文件中會設置@@SESSION.SQL_LOG_BIN= 0，當使用該備份文件向目標實例導入數據時，不記錄二進制日志。

• 在開啟GTID的實例上使用mysql_upgrade原地升級MySQL版本時，要不寫二進制日志（這也是mysql_upgrade的默認行為，沒開啟--write-binlog）。