当使用create table ... as select建表时报错ERROR 1786

问题描述：当使用create table ... as select建表时报错ERROR 1786.
数据库：mysql 5.7.21
1、异常重现
mysql> create table booksbak as select * from books;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

2、异常原因
ERROR 1786是由于开启enforce_gtid_consistency=true功能导致，MySQL官方解释当启用enforce_gtid_consistency功能时，MySQL只允许能够保障事务安全且能被日志记录的SQL语句被执行，
像create table ... select和create temporary table等同时更新事务表和非事务表的SQL语句或事务不允许执行.
--查enforce_gtid_consistency参数
mysql> show variables like 'enforce_gtid_consistency';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+
1 row in set (0.01 sec)

3、解决方案
说明：以下提供两种解决方案.
3.1、拆分建表语句
--将建表语句拆分成两部分.
mysql> create table booksbak like books;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into booksbak select * from books;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
3.2、关闭GTID模式
3.2.1、修改配置文件
按如下修改my.cnf文件.
gtid_mode=off
enforce_gtid_consistency=off

3.2.2、重启mysql进程
mysql> shutdown
mysql> exit
$ mysqld_safe  --defaults-file=/home/mysql/etc/my.cnf &
3.2.3、检查参数生效情况
mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode   | OFF  |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF  |
+--------------------------+-------+
1 row in set (0.01 sec)

说明：修改参数后成功建表.

参考文档：https://www.jb51.net/article/51753.htm