MySQL 基于GTID的复制（五）（没懂）

使用GTID复制的限制条件：（没懂）
由于GTID复制是依赖于事务的，所以MySQL的一些属性不支持
（1）当一个事务中既包含对InnoDB表的操作，也包含对非事务型存储引擎表(MyISAM)的操作时，就会导致一个事务中可能会产生多个GTID的情况；（2）当master和slave的表使用的存储引擎不一样时，都会导致GTID复制功能不正常
create table…select语句在基于语句复制的环境中是不安全的，在基于行复制的环境中，此语句会被拆分成两个事件，一是创建表，二是insert数据，在某些情况下这两个事件会被分配相同的GTID，而导致insert数据的操作被忽略，所以GTID复制不支持create table … select语句
create/drop temporary table语句在GTID复制环境中不能放在事务中执行，只能单独执行，并且autocommit要开启

[root@slave1 ~]# mysql
mysql> create table temp345 select * from temp234;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

[root@slave1 ~]# mysql
mysql> create table temp345(id int,name varchar(10));

mysql> drop table temp345;

mysql> create table temp345(id int,name varchar(10)) engine=myisam;

mysql> start transaction;

mysql> update temp234 set name='aa';

mysql> insert into temp345 select * from temp234;
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-slave1 ~]# mysql -u  root -p
mysql> rollback;

mysql> drop table temp345;

mysql> create table temp345 like temp234;

sql_slave_skip_counter语句是不支持的，如果想要跳过事务，可以使用gtid_executed变量

[root@slave1 ~]# mysql
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| binlog.000002 |     1023 |              |                  | 3405199f-e06c-11ea-a49f-000c29ce4198:1, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:1-2 |
+---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+

[root@slave2 ~]# mysql
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

           Retrieved_Gtid_Set: 3405199f-e06c-11ea-a49f-000c29ce4198:1, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:2
            Executed_Gtid_Set: 3405199f-e06c-11ea-a49f-000c29ce4198:1, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:1-2

[root@slave2 ~]# /etc/init.d/mysqld stop

[root@slave1 ~]# mysql
use course
insert into temp234 values(300,'a');
insert into temp234 values(400,'a');

[root@slave2 ~]# /etc/init.d/mysqld start 
[root@slave2 ~]# mysql
mysql> use course
mysql> select * from temp234;
+------+------+
| id   | name |
+------+------+
|  300 | a    |
|  400 | a    |
+------+------+

[root@slave2 ~]# mysql
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

           Retrieved_Gtid_Set: 3405199f-e06c-11ea-a49f-000c29ce4198:1-3, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:2
            Executed_Gtid_Set: 3405199f-e06c-11ea-a49f-000c29ce4198:1-3, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:1-2

mysql> show variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                              |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                 |
| enforce_gtid_consistency         | ON                                                                                 |
| gtid_executed                    | 3405199f-e06c-11ea-a49f-000c29ce4198:1-3, c74e4b27-d5f6-11ea-bbe0-000c29396a9d:1-2 |
| gtid_executed_compression_period | 1000                                                                               |
| gtid_mode                        | ON                                                                                 |
| gtid_next                        | AUTOMATIC                                                                          |
| gtid_owned                       |                                                                                    |
| gtid_purged                      |                                                                                    |
| session_track_gtids              | OFF                                                                                |
+----------------------------------+------------------------------------------------------------------------------------+