mysql 表备份遇到的问题【全网最全】

末蓝、 2024-03-26 22:25 29阅读 0赞

**目录**

省流：

正文：

1、报错

2、原因

3、解决方法

方法一：关闭 ENFORCE\_GTID\_CONSISTENCY （不推荐）：

方法二（推荐）：

4、开启关闭GTID

--------------------

### 省流： ###

不推荐如下写法：

create table ...  as select * from ...;
    create table t1_old  as select * from t1;

推荐：

-- 分成两步来写
    create table t1_old like t1;
    insert into t1_old select * from t1;

### 正文： ###

### **1、报错** ###

在执行sql：create table 表A as select \* from 表B时，报错：

Statement violates GTID consistency: CREATE TABLE ... SELECT.

### **2、原因** ###

MySQL5.7.6版本之后， enforce\_gtid\_consistency=true 功能导致，MySQL只允许能够保障事务安全并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

### **3、解决方法**  ###

####    **方法一：关闭 ENFORCE\_GTID\_CONSISTENCY （不推荐）：** ####

这个方法是网上很多文章介绍的方法。但如果是用来备份表，不推荐此方法，因为会遇到很多和事务有关问题。 详情见下面的参考文章。

我自己遇到的问题是：master主库备份，但slave从库没有备份数据（只是一张空表）。导致服务查询数据的时候查不到。

SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'OFF';

直接执行上面这条命令可能会报错：

GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON.

意思是说要先关闭 GTID\_MODE，再关闭 ENFORCE\_GTID\_CONSISTENCY。

SET GLOBAL GTID_MODE = 'OFF';

但是直接关闭GTID\_MODE会报错：

The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: 
    OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. 
    Also note that this value must be stepped up or down simultaneously on all servers. 
    See the Manual for instructions.

这意思是说，不能直接设置OFF，要**如下执行**：

SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
    SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
    SET GLOBAL GTID_MODE = 'OFF';

这样就关闭了 GTID\_MODE，然后再关闭 ENFORCE\_GTID\_CONSISTENCY。

SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'OFF';

详情见 **4、开启关闭GTID**

#### **方法二（推荐）：** ####

将创建表和复制数据分成两步执行：

create table t1_old like t1;
    insert into t1_old select * from t1;

### 4、开启关闭GTID ###

（1）必须是5.7.6版本以上的mysql

（2）GTID状态为OFF

2、开启步骤：

（1）：SET GLOBAL ENFORCE\_GTID\_CONSISTENCY = 'WARN';

（2）：SET GLOBAL ENFORCE\_GTID\_CONSISTENCY = 'ON';

（3）：SET GLOBAL GTID\_MODE = 'OFF\_PERMISSIVE';

（4）：SET GLOBAL GTID\_MODE = 'ON\_PERMISSIVE';

（5）：SET GLOBAL GTID\_MODE = 'ON';

3、要永久启用，在my.cnf配置文件中添加参数：

gtid-mode=ON

enforce-gtid-consistency

在线关闭步骤：

1、要求：

（1）必须是5.7.6版本以上的mysql

（2）GTID状态为OFF

2、关闭步骤：

（1）：stop slave;

（2）：SET GLOBAL GTID\_MODE = 'ON\_PERMISSIVE';

（3）：SET GLOBAL GTID\_MODE = 'OFF\_PERMISSIVE';

（4）：SET GLOBAL GTID\_MODE = 'OFF';

注：

每次开启和关闭时，都是这样一个过程：

打开-->过度模式-->完全打开

停止-->过度模式-->完全关闭

参考：

[为什么要避免使用“CREATE TABLE AS SELECT”语句][CREATE TABLE AS SELECT]

[mysql在线开启或禁用GTID模式][mysql_GTID]

[技术分享 | MySQL 在线开启 GTID 的每个阶段是要做什么 - 知乎][_ MySQL _ GTID _ -]

=======================分割线=========================

文章到此已经结束，以下是紫薯布丁

SET GLOBAL GTID\_MODE = 'ON\_PERMISSIVE';  
SET GLOBAL GTID\_MODE = 'OFF\_PERMISSIVE';  
SET GLOBAL GTID\_MODE = 'OFF';

SET GLOBAL ENFORCE\_GTID\_CONSISTENCY = 'OFF';

The value of @@GLOBAL.GTID\_MODE can only be changed one step at a time:  
OFF <-> OFF\_PERMISSIVE <-> ON\_PERMISSIVE <-> ON.  
Also note that this value must be stepped up or down simultaneously on all servers.  
See the Manual for instructions.

GTID\_MODE = ON requires ENFORCE\_GTID\_CONSISTENCY = ON.

\-- 分成两步来写  
create table t1\_old like t1;  
insert into t1\_old select \* from t1;

create table ... as select \* from ...;  
create table t1\_old as select \* from t1;

[CREATE TABLE AS SELECT]: https://www.cnblogs.com/fander/p/10309759.html
[mysql_GTID]: https://www.cnblogs.com/magmell/p/9223556.html
[_ MySQL _ GTID _ -]: https://zhuanlan.zhihu.com/p/459927255