新特性解读 | MySQL 8.0 对 GTID 的限制解除

最新推荐文章于 2024-06-10 21:53:15 发布
最新推荐文章于 2024-06-10 21:53:15 发布
阅读量281 收藏
点赞数
文章标签： GTID 限制取消 GTID MySQL 8.0 新特性
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ActionTech/article/details/125779129
版权

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

在 MySQL 5.6 以及MySQL 5.7 上使用 GTID ，一直以来都有几个硬性限制，特别是针对开发人员编写 SQL 的两条限制，官方文档对这两条限制详细描述如下：

CREATE TABLE … SELECT statements. CREATE TABLE ... SELECT statements are not allowed when using GTID-based replication. When binlog_format is set to STATEMENT, a CREATE TABLE ... SELECT statement is recorded in the binary log as one transaction with one GTID, but if ROW format is used, the statement is recorded as two transactions with two GTIDs. If a source used STATEMENT format and a replica used ROW format, the replica would be unable to handle the transaction correctly, therefore the CREATE TABLE ... SELECT statement is disallowed with GTIDs to prevent this scenario.

Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions, procedures, functions, and triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

以上大概意思讲的是对于这两条 SQL 语句，如果想在 GTID 模式下使用，为了不破坏事务一致性，是被严格限制而不允许使用的。 为了满足需求，一般我们会通过一些途径来绕过这些限制。这个硬性限制随着 MySQL 8.0 一些新特性的发布，连带着被间接取消掉。比如 MySQL 8.0 的 DDL 原子性！

我们先来看下在 MySQL 5.7 下这一行为对事务的影响以及如何通过变通的方法绕过这些限制。
  1. create table … select … : 这条语句本身是懒人写法，语义上分别属于两个隐式事务（一条DDL语句，一条DML语句）。但在GTID开启后，单个语句只能给它分配一个GTID事务号，如果强制使用，会直接报语句违反GTID一致性。比如下面例子：直接执行这条语句就会报错。
   mysql:ytt:5.7.34-log> create table trans1(id int primary key, log_date date);
   Query OK, 0 rows affected (0.03 sec)
   
   <mysql:ytt:5.7.34-log> insert trans1 values (1,'2022-01-02');
   Query OK, 1 row affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> create table trans2 as select * from trans1;
   ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

既然理解了需求，就想办法变通下。针对这条语句，拆分为两条语句即可。

需要注意的是拆分后第一条 DDL 语句的后续工作，是延迟建立索引还是根本不需要索引？如果是延迟建立索引，那很简单，使用 MySQL 的 create table … like … 语法就行。虽然 create table … like … 语法是直接克隆原表，索引也是立即创建，不过最终目标是一致的。示例如下：

   <mysql:ytt:5.7.34-log> reset master;
   Query OK, 0 rows affected (0.02 sec)
   
   mysql:ytt:5.7.34-log> create table trans2 like trans1;
   Query OK, 0 rows affected (0.02 sec)
   
   <mysql:ytt:5.7.34-log> insert trans2 select * from trans1;
   Query OK, 1 row affected (0.02 sec)
   Records: 1  Duplicates: 0  Warnings: 0

对应的 binlog 数据如下，拆分为两个 GTID 事务号：00020135-1111-1111-1111-111111111111:1-2

   <mysql:ytt:5.7.34-log> show binlog events in 'mysql-bin.000001'\G
   *************************** 1. row ***************************
     ...
   *************************** 3. row ***************************
      Log_name: mysql-bin.000001
           Pos: 154
    Event_type: Gtid
     Server_id: 100
   End_log_pos: 219
          Info: SET @@SESSION.GTID_NEXT= '00020135-1111-1111-1111-111111111111:1'
   *************************** 4. row ***************************
      Log_name: mysql-bin.000001
           Pos: 219
    Event_type: Query
     Server_id: 100
   End_log_pos: 316
          Info: use `ytt`; create table trans2 like trans1
   *************************** 5. row ***************************
      Log_name: mysql-bin.000001
           Pos: 316
    Event_type: Gtid
     Server_id: 100
   End_log_pos: 381
          Info: SET @@SESSION.GTID_NEXT= '00020135-1111-1111-1111-111111111111:2'
   *************************** 6. row ***************************
      Log_name: mysql-bin.000001
           Pos: 381
    Event_type: Query
     Server_id: 100
   End_log_pos: 452
          Info: BEGIN
   *************************** 7. row ***************************
      Log_name: mysql-bin.000001
           Pos: 452
    Event_type: Table_map
     Server_id: 100
   End_log_pos: 501
          Info: table_id: 112 (ytt.trans2)
   *************************** 8. row ***************************
      Log_name: mysql-bin.000001
           Pos: 501
    Event_type: Write_rows
     Server_id: 100
   End_log_pos: 552
          Info: table_id: 112 flags: STMT_END_F
   *************************** 9. row ***************************
      Log_name: mysql-bin.000001
           Pos: 552
    Event_type: Xid
     Server_id: 100
   End_log_pos: 583
          Info: COMMIT /* xid=54 */
   9 rows in set (0.00 sec)

如果是后一种，只需要复制表结构和数据，不要索引，那也可以用 create table … like … 语法创建好表结构，完了手工删除表索引。如果表比较多，可以写个简单脚本对索引批量删除。

  1. 对于显式临时表的创建与删除： 这样的 DDL 语句在 GTID 模式下也是禁止放在事务块里执行的（显式的 begin; commit; 或者存储过程、存储函数、触发器等大事务块）。 直接在事务块里执行会报错：
   <mysql:ytt:5.7.34-log> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> create temporary table tmp(id int);
   ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

这种如何解决呢？官方也给出建议：把此类 DDL 语句放在事务块外面或者直接使用基于磁盘表的 DDL 语句来替代它。比如下面示例：在事务块外创建临时表，事务块内部引用临时表数据就行。

   <mysql:ytt:5.7.34-log> create temporary table tmp(id int,log_date date);
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> begin;
   Query OK, 0 rows affected (0.01 sec)
   
   <mysql:ytt:5.7.34-log> insert tmp values (100,'2022-10-21');
   Query OK, 1 row affected (0.01 sec)
   
   <mysql:ytt:5.7.34-log> insert trans1 select * from tmp;
   Query OK, 1 row affected (0.00 sec)
   Records: 1  Duplicates: 0  Warnings: 0
   
   <mysql:ytt:5.7.34-log> commit;
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> select * from trans1;
   +-----+------------+
   | id  | log_date   |
   +-----+------------+
   |   1 | 2022-01-02 |
   | 100 | 2022-10-21 |
   +-----+------------+
   2 rows in set (0.00 sec)
MySQL 8.0 原生 DDL 原子性，所以连带就解除了这两个 GTID 的限制。
  1. 对于 create table … like … 语句，在 MySQL 8.0 版本里只会生成一个 GTID 事务号，见下面 binlog 内容：0228ca56-db2f-11ec-83d3-080027951c4a:1。
mysql:ytt:8.0.29>create table trans2 as select * from trans1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

<mysql:ytt:8.0.29>show binlog events in 'binlog.000001'\G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 236
       Info: SET @@SESSION.GTID_NEXT= '0228ca56-db2f-11ec-83d3-080027951c4a:1'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 236
 Event_type: Query
  Server_id: 1
End_log_pos: 310
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000001
        Pos: 310
 Event_type: Query
  Server_id: 1
End_log_pos: 476
       Info: use `ytt`; CREATE TABLE `trans2` (
  `id` int NOT NULL,
  `log_date` date DEFAULT NULL
) START TRANSACTION
*************************** 6. row ***************************
   Log_name: binlog.000001
        Pos: 476
 Event_type: Table_map
  Server_id: 1
End_log_pos: 528
       Info: table_id: 349 (ytt.trans2)
*************************** 7. row ***************************
   Log_name: binlog.000001
        Pos: 528
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 571
       Info: table_id: 349 flags: STMT_END_F
*************************** 8. row ***************************
   Log_name: binlog.000001
        Pos: 571
 Event_type: Xid
  Server_id: 1
End_log_pos: 602
       Info: COMMIT /* xid=8833 */
8 rows in set (0.00 sec)
  1. 对于事务块里有显式临时表的 DDL 语句，可以正常执行：
<mysql:ytt:8.0.29>reset master;
Query OK, 0 rows affected (0.02 sec)

<mysql:ytt:8.0.29>begin;
Query OK, 0 rows affected (0.01 sec)

<mysql:ytt:8.0.29>create temporary table tmp(a int,b date);
Query OK, 0 rows affected (0.00 sec)

<mysql:ytt:8.0.29>insert into tmp values (10,'2022-12-31');
Query OK, 1 row affected (0.00 sec)

<mysql:ytt:8.0.29>insert trans1 select * from tmp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

<mysql:ytt:8.0.29>commit;
Query OK, 0 rows affected (0.01 sec)

<mysql:ytt:8.0.29>table trans1;
+----+------------+
| id | log_date   |
+----+------------+
|  1 | 2022-07-07 |
| 10 | 2022-12-31 |
+----+------------+
2 rows in set (0.00 sec)

这样生成的 GTID 事务号里（0228ca56-db2f-11ec-83d3-080027951c4a:1）只包含对磁盘表 trans1 的写入记录：

mysql:ytt:8.0.29>show binlog events in 'binlog.000001'\G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 236
       Info: SET @@SESSION.GTID_NEXT= '0228ca56-db2f-11ec-83d3-080027951c4a:1'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 236
 Event_type: Query
  Server_id: 1
End_log_pos: 310
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000001
        Pos: 310
 Event_type: Table_map
  Server_id: 1
End_log_pos: 362
       Info: table_id: 405 (ytt.trans1)
*************************** 6. row ***************************
   Log_name: binlog.000001
        Pos: 362
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 405
       Info: table_id: 405 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: binlog.000001
        Pos: 405
 Event_type: Xid
  Server_id: 1
End_log_pos: 436
       Info: COMMIT /* xid=9374 */
7 rows in set (0.00 sec)

MySQL 8.0 已经发布好几年了，如果有需要这部分的功能改善，建议升级新版本。

爱可生开源社区
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql8 GTID 复制
zll4859291的博客
03-19 410
1.1 GTID全称Global transaction identifiers，也称之为全局事务ID。MySQL-5.6.2开始支持，MySQL-5.6.10后完善，GTID 分成两部分，一部分是服务的UUid,UUID保存在mysql数据目录的auto.cnf文件中，这是一个非常重要的文件，不能删除，这一部分是不会变的。下面是一个uuid的值举例：[auto]另外一部分就是事务ID了，随着事务的增加，值依次递增。也就是说，GTID实际上是由UUID+TID组成的。
mysql8基于GTID（全局事务）主从复制架构搭建
10-21 554
首先在vmware上创建3个cenos7虚拟机，ip分别是：192.168.0.112,192.168.0.113,192.168.0.114, 搭建架构图如下： 1.在3个虚拟机上安装mysql； 2.master机器(192.168.0.112)创建复制用户设置其权限： mysql> create user 'repl'@'%' identified by 'Repl_123456'; mysql> grant replication slave on *.* to 'repl
MySQL8基于GTID以及VIP实现高可用主从架构
最新发布
janyxe的博客
06-10 961
MySQL8基于GTID以及VIP实现高可用主从架构
MySQL 8 复制（四）——GTID与复制
wzy0623的专栏
06-06 9096
目录 一、GTID简介 1. 什么是GTID 2. GTID的格式与存储 二、GTID生命周期 1. 典型事务的GTID生命周期 2. GTID分配 3. gtid_next系统变量 4. gtid_purged系统变量 三、GTID自动定位 MySQL复制中使用的事务类型有以下两种： GTID事务：在二进制日志中每个GTID事务始终都以Gtid_log_ev...
mysql8.0互为主从gtid
qq_59520311的博客
11-28 239
gtid方式mysql8.0互为主从方式
mysql8 进阶(四) 主从复制之 GTID 复制实践
llk15884975173的博客
04-06 1222
基于 Docker 的 Mysql8.0.12 的主从复制之 GTID 复制。 Master mysql-gtid-master /usr/share/mysql/gtid/master 3306 Slave mysql-gtid-slave /usr/share/mysq
MySQL 5.6 GTID新特性实践
09-09
MySQL 5.6 引入的 GTID（Global Transaction ID）特性是一项重大的进步，它极大地简化了数据库复制和故障转移的过程。GTID 是一个全局唯一标识，用于标记已提交的事务，由两部分组成：UUID 和 TID。UUID 是每个 ...
MySQL8.0新特性
07-16
MySQL 8.0是MySQL数据库管理系统的一个重大更新版本，它带来了许多新的特性和改进，旨在提升...《MySQL+innodb+8.0+新特性.pdf》这个文档应该会提供更详细的解释和实例，建议详细阅读以深入了解MySQL 8.0的所有新功能。
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
├─新版MySQL DBA 课件ppt │ 第一课数据库介绍篇.pdf │ 第七课MySQL数据库设计.pdf │ 第三十一课percona-toolkits 的实战及自动化.pdf │ 第三课MySQL授权认证.pdf │ 第九课MySQL字符集.pdf │ 第二十一课MySQL...
mysql8.0最新版API.pdf
09-02
MySQL 8.0是MySQL数据库管理系统的一个重大更新版本，它带来了许多新特性和改进，旨在提升性能、可扩展性以及用户友好性。本参考手册详细介绍了MySQL 8.0从8.0.28到NDB Cluster 8.0.27-ndb-8.0.27的所有功能和变化。...
Mysql8.0.rar
07-25
1. **增强的数据类型**：MySQL 8.0引入了新的JSON数据类型，支持非结构化数据存储，适应现代Web应用的需求。此外，改进了日期和时间类型，如增加了更多的时间精度选项。 2. **更好的性能**：通过引入InnoDB存储引擎...
mysql8模拟千万条数据gtid主从分布环境
veritable的博客
07-26 251
1. 设置yum源2. 安装指定版本mysql3. 重置密码4. 设置远程登陆。
MySQL8.0+数据库GTID主从模式搭建
zhanhjxxx的博客
12-25 1705
部署环境[例]：主库：192.168.179.153:3306从库：192.168.179.154:3306。
阿里云RDS mysql报错：Statement violates GTID consistency: CREATE TABLE … SELECT
SuperManCUI的博客
07-19 2939
实际是拆分为两句，先根据复制表结构或指定字段来新建表，然后使用insertintoselect的方式来插入数据。问题分析从错误本身理解是GTID相关的问题，查了查资料，有解决办法是配置enforce_gtid_consistency=off来关闭,但RDS控制台参数配置里没有这个参数项，然后了解了一下，RDS主从应该会基于GTID，应该是这个原因，所以是不允许调整这个参数的。前者是表a不存在，会根据select字段来新建表，后者需要a存在且select字段的数量和类型要跟表结构一致。...
mysql gtid 限制_MySQL GTID详解
weixin_32395459的博客
01-27 394
GTID介绍从MySQL 5.6.5开始支持GTID，每一个在主库上提交的事务在复制集群中可以生成一个唯一的ID。一个GTID在一个服务器上只执行一次，避免重复执行导致主从不一致或者数据混乱。在传统复制的slave，binlog日志可以不开启，但是在GTID中，slave的binlog必须开启，目的是记录执行过的GTID。GTID的组成GTID = server_uuid:transaction_...
MySQL8.0轻松完成GTID组复制
KunLunDB_Linda的博客
02-22 360
前言 之前文章介绍了MySQL8.0主从复制，以及衍生出来的主主复制（可点如下链接查看）。 MySQL8.0轻松搞定GTID主从复制 MySQL8.0轻松搞定GTID主主复制 今天来聊一聊MySQL8.0的组复制。 主从复制技术仅解决了数据同步的问题，如果主服务器宕机，意味着数据库管理员需要介入，应用系统可能需要修改数据库连接地址或者重启才能实现。 组复制只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先
MySQL表字段数量限制以及行大小限制
weixin_41866717的博客
07-21 7906
mysql表字段限制和row size limits
MySQL8.0轻松完成GTID主主复制
KunLunDB_Linda的博客
02-22 915
前言 之前文章介绍了GTID主从复制，实现了一主多从的MySQL架构，今天我们来介绍一下主主复制的架构。 原理：主主复制就是根据主从复制的原理，将两个主库设置互为主从即可。 变化：在这两个主库的基础上，还可以搭建双主多从架构。 下面介绍在安装好MySQL8的centos8虚拟机上，实现GTID主主复制，以及双主多从复制的过程： 虚拟机列表： Master1: 192.168.25.149 Master2: 192.168.25.150 Slave1:
GTID mysql双主模式docker8.0
09-18
GTID（Global Transaction Identifier）是MySQL 5.6及更高版本引入的一种复制标识符。它用于标识在主从复制中所有操作的全局唯一ID，可以确保在复制过程中不会产生重复的事务。GTID是在双主模式下非常有用的，因为它可以简化双主复制的配置和管理。 在Docker 8.0中配置GTID双主模式，可以按照以下步骤进行操作： 1. 创建一个可连接的overlay网络： ```shell docker network create --driver overlay common-network --attachable ``` 2. 在两个MySQL容器中分别编辑配置文件`master.cnf`和`slave.cnf`，并添加以下内容： ``` !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] log-bin=... gtid_mode=ON enforce_gtid_consistency=true binlog_format=ROW ``` 3. 分别启动两个MySQL容器，并将配置文件和数据目录挂载到容器内部： ```shell docker run --restart=always -d -v /data/software/mysql8/master/config/my.cnf:/etc/mysql/my.cnf -v /data/software/mysql8/master/logs:/logs -v /data/software/mysql8/master/data/mysql:/var/lib/mysql -p 3306:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=root mysql:8.0.23 ``` ```shell docker run --restart=always -d -v /data/software/mysql8/slave/config/my.cnf:/etc/mysql/my.cnf -v /data/software/mysql8/slave/logs:/logs -v /data/software/mysql8/slave/data/mysql:/var/lib/mysql -p 3307:3306 --name mysql_slave -e MYSQL_ROOT_PASSWORD=root mysql:8.0.23 ``` 4. 在主节点上创建一个用于复制的用户，并授予适当的权限： ```shell docker exec -it mysql_master bash mysql -uroot -p GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; ``` 5. 在从节点上配置主节点的连接信息，并启动复制： ```shell docker exec -it mysql_slave bash mysql -uroot -p CHANGE MASTER TO MASTER_HOST='mysql_master', MASTER_PORT=3306, MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1; START SLAVE; ``` 完成上述步骤后，GTID双主模式的MySQL容器就配置好了。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值