MySQL Create table as select无法执行

一、前言

作为一个专业的DBA，在进行表结构变更或数据变更前做好备份那是必须的，今天在新系统上(MySQL版本是5.7) 使用Create table as Select时直接报 Statement violates GTID consistency: CREATE TABLE ... SELECT,瞬间懵逼了，看提示信息说是违反了GTID一致性。

二、GTID介绍

GTID（Global Transaction ID）,即全局事务标识符，它由UUID+TID构成，其中UUID指的是MySQL实例唯一标识，在/var/lib/mysql/auto.cnf文件定义 （类似 server-uuid=ee9a3626-e24e-11eb-b13d-00163e0ab4aa )，TID代表该实例上已经提交事务的数量。

根据GTID可以知道事务最初是在哪个实例上提交的，可以更简单实现主从复制且更安全。

注：下次写一篇传统的主从复制和GTID主从复制的文章。

三、报错原因及解决方法

MySQL5.6及更低版本系统参数enforce_gtid_consistency 默认值为OFF，MySQL5.7及更高版本默认值为ON，当设置为ON时，MySQL只允许能够保障事务安全并且能够被日志记录的语句才会被执行，而像create table as select 或create temporarytable 这种一条语句中包括事务和非事务是不被允许执行的。

解决方法

1、直接enforce_gtid_consistency 为OFF,但不建议这么做。

2、Create table A as  select * from B 拆成两部分 create table like A;insert into A as select * from B .

以前做单表备份时一直是用create table A as select ,但是这种备份其实是有些问题的，创建的新表中并没有旧表的primary key,Extra,auto_increment这些属性，如果误操作要恢复会有些麻烦，而拆成两条语句做备份就不存在这个问题。