ogp-mysql57-gtid-replication-b.png
MySQL使用Create table select的时候报错如下:

root@monitor-db 19:35:25 [test]>create table a3 select * from a2 ;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

问题分析:
MySQL开启gtid以后就不能使用了,MySQL只允许能够保障事务安全,并且能够被日志记录的SQL语句被执行,像create table … select 和 create temporarytable语句,以及同时更新事务表和非事务表的SQL语句或事务都不允许执行

root@monitor-db 19:35:12 [test]>show global variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如业务需要,可以在线关闭掉gtid,执行如下SQL关闭gtid:

set global gtid_mode='ON_PERMISSIVE';

set global gtid_mode='OFF_PERMISSIVE';

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = OFF;

set global gtid_mode='OFF';

show global variables like '%gtid_mode%';

查询执行结果:

root@monitor-db 19:21:31 [test]>show global variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

再次创建:

root@monitor-db 19:32:46 [test]>desc a2;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| id              | int(10) unsigned | NO   |     | 0       |       |
| title           | varchar(100)     | NO   |     | NULL    |       |
| author          | varchar(40)      | NO   |     | NULL    |       |
| submission_date | date             | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

root@monitor-db 19:32:51 [test]>create table a3 select * from a2 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@monitor-db 19:32:57 [test]>desc a3;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| id              | int(10) unsigned | NO   |     | 0       |       |
| title           | varchar(100)     | NO   |     | NULL    |       |
| author          | varchar(40)      | NO   |     | NULL    |       |
| submission_date | date             | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

已经OK;
在线打开GTID:

root@monitor-db 19:41:31 [test]>set global gtid_mode='OFF_PERMISSIVE';
Query OK, 0 rows affected (0.00 sec)

root@monitor-db 19:44:14 [test]>
root@monitor-db 19:44:14 [test]>set global gtid_mode='ON_PERMISSIVE';
Query OK, 0 rows affected (0.00 sec)

root@monitor-db 19:44:14 [test]>
root@monitor-db 19:44:14 [test]>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
Query OK, 0 rows affected (0.00 sec)

root@monitor-db 19:44:14 [test]>
root@monitor-db 19:44:14 [test]>set global gtid_mode='ON';
Query OK, 0 rows affected (0.01 sec)

root@monitor-db 19:44:14 [test]>
root@monitor-db 19:44:14 [test]>show global variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

打开ok,MySQL5.7开启/关闭GTID,不用重启服务很方便;

带符号 * 的表示必填项