场景描述

• 简介

  生产上进行数据变更时，经常担心会对已有数据误操作，一般会先进行备份，这里记录一次MySQL备份失败解决过程。

报错信息

• 描述

  
      -- 备份语句
      CREATE TABLE 新表 SELECT * FROM 旧表
  
      -- 这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。
      -- 不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用"alter"添加，而且容易搞错。
  
      Error Code: 1786 Statement violates GTID consistency: CREATE TABLE ... SELECT.
  
  

原因分析

• MySQL 5.6及以上版本导致

  这是因为在5.6及以上的版本内，开启了 enforce_gtid_consistency=true 功能导致的，MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候，

  MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

解决方案

• 方案一

  修改数据配置

      
      -- 执行sql脚本
      SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;
  
      -- 或修改配置文件 
      ENFORCE_GTID_CONSISTENCY = off;
  
  

• 方案二

  
  
      -- 将 create table xxx as select 的方式拆分成两部分。
  
      create table xxxx like test;
  
      insert into xxxx select * from test;
  
  
  

参考链接

• sql创建备份表和复制数据到备份表

  https://www.cnblogs.com/bingyublog/p/7825069.html

• 解决 mysql 报错: Error Code: 1786 Statement violates GTID consistency: CREATE TABLE … SELECT.

  https://blog.csdn.net/weixin_41287692/article/details/83028724