MySQL中GTID的几个限制和解决方案

现在我看待一个技术,总是会换一种角度来看,在他能实现什么的基础上,我更喜欢看他不能做什么,为什么不能这么做。

比如MySQL GTID在5.6试水,5.7已经发展完善,但是还是有一些场景是受限的。比如下面的两个。

一个是create table xxx as select 的模式,另外一个是临时表相关的。

今天我们就来简单说说这两个场景。

GTID中create 语句限制的解法

create table xxx as select的语句,其实会被拆分为两部分,create语句和insert语句,但是如果想一次搞定,MySQL会抛出如下的错误。

mysql> create table test_new as select *from test; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

这种语句其实目标明确,复制表结构,复制数据,insert的部分好解决,难点就在于create table的部分,如果一个表的列有100个,那么拼出这么一个语句来就是一个工程了。

我们也巧学巧用,看看MySQL有什么特别的方法来处理。

除了规规矩矩的拼出建表语句之外,还有一个方法是MySQL特有的用法 like。

create table xxx as select 的方式会被拆分成两部分。

create table xxxx like data_mgr; insert into xxxx select *from data_mgr;

临时表的限制和考虑

另外一个看起来就有些蹊跷了,看着文档就是没有什么好说的,记住了就好,其实不然。

如果在事务中有临时表的变动,很可能会导致数据不一致,这在MySQL的5.5版本中有相应的bug,可以参见https://bugs.mysql.com/bug.php?id=76940

如果需要复现,可以在找一套5.5的环境来模拟一下,分分钟出效果。

我们创建两个表t1,t2,然后建立两个表之间的外键关联,作为 后续测试所用。

create table t1(c1 int primary key) engine=innodb; insert into t1 values(1),(2),(3),(4),(5); create table t2 (c1 int, c2 int, foreign key(c2) references t1(c1)) engine=innodb; insert into t2 values(1,1),(2,2),(5,5);

创建临时表

> create temporary table tmp as select * from t1; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0

模拟这个bug,开启事务。

> begin; > drop temporary table if exists tmp; Query OK, 0 rows affected (0.00 sec) > delete from t1 where c1 > 2; ERROR 1451 (23000): Cannot delete or update a parent row: a fore; Query OK, 0 rows affected (0.00 sec)

然后使用mysqlbinlog来查看一下里面的信息。可以看到除了上面的临时表操作,后面的delete也会写入binlog

use `test`/*!*/; SET TIMESTAMP=1499784283/*!*/; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ /*!*/; # at 300 # at 341 #170711 22:44:46 server id 13386 end_log_pos 341 Table_map: `test`.`t1` mapped to number 207 #170711 22:44:46 server id 13386 end_log_pos 380 Delete_rows: table id 207 flags: STMT_END_F BINLOG ' XuRkWRNKNAAAKQAAAFUBAAAAAM8AAAAAAAEABHRlc3QAAnQxAAEDAAA= XuRkWRlKNAAAJwAAAHwBAAAAAM8AAAAAAAEAAf/+AwAAAP4EAAAA '/*!*/; ### DELETE FROM test.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### DELETE FROM test.t1 ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ # at 380 #170711 22:44:49 server id 13386 end_log_pos 449 Query thread_id=176 exec_time=0 error_code=0 SET TIMESTAMP=1499784289/*!*/; COMMIT

通过这个可以清晰的看到尽管已经做了事务回滚,但是binlog还是会记录下回滚的变更,这在某些场景中会触发主从数据不一致。

而在GTID中,已经做了这个检查,归根结底,还是cache里面的机制,大体来说,binlog有两个cache来缓存事务的binlog:

binlog_cache_data stmt_cache; //存放非事务表和临时表binlog binlog_cache_data trx_cache; //存放事务表binlog

此处参考了https://www.kancloud.cn/taobaomysql/monthly/67044

所以说两个概念性的知识点如果稍一扩展就会有很多可行的方案来。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-07-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏云计算爱好者

你常用的10个MySQL命令

今天给大家介绍一些简单的MySQL常用的实用命令。如果你已经熟练使用MySQL就可以跳过啦!如果还不知道的都可以在自己机器上练习一下。

1797
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

7904
来自专栏李智的专栏

Mysql学习(基本指令、语句)

  1) 数值   int //int(3)与长度无关,不够3位前面补0,默认看不见     float   2) 字符串 ...

752
来自专栏GreenLeaves

Oracle Schema

1、这是Schema的definition: A schema is a collection of database objects (used by a u...

2056
来自专栏源哥的专栏

oracle中如何删除重复数据

        我们可能会出现这种情况,某个表原来设计不周全,导致表里面的数据数据重复,那么,如何对重复的数据进行删除呢?         重复的数据可能有这样...

1313
来自专栏Ryan Miao

mysql创建定时执行存储过程任务

sql语法很多,是一门完整语言。这里仅仅实现一个功能,不做深入研究。 目标:定时更新表或者清空表。 案例:曾经做过定时清空位置信息表的任务。(然而,当时并未考虑...

4167
来自专栏marsggbo

python混账的编码问题解决之道

下面的代码作用是修改文件的编码格式。代码很简单,但是也很牛逼(在我看来),这是在segment上找到的解决办法,废话不多说,直接上代码。 import cod...

1907
来自专栏农夫安全

注入学习之sqli-labs-3(第二关)

前言 本来是想一个个关卡讲下去,后来自己测试了一下,发现第二、三、四这三关跟第一关,起始原理是一样的,只不过是单引号,双引号,带不带括号的区别,只要我们带入的语...

3416
来自专栏IT技术精选文摘

MySQL 有几种Join,其底层实现原理是什么?

mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:

4823
来自专栏JavaWeb

MySQL-大批量数据如何快速的数据迁移

2912

扫码关注云+社区

领取腾讯云代金券