专栏首页杨建荣的学习笔记MySQL自增列主从不一致的测试(r12笔记第37天)

MySQL自增列主从不一致的测试(r12笔记第37天)

MySQL里面有一个问题尤其值得注意,那就是自增列的重复值问题,之前也简单分析过一篇MySQL自增列的重复值问题(r12笔记第25天),但是在后续我想了下,还有很多地方需要解释,一个就是从库的自增列是如何维护的,是否重启从库,自增列会受到影响。

我们继续来测试一下。首先复现这个问题。

创建表t1,插入3行数据。

use test;
[test]> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
[test]> select *from t1;               
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+

因为存在3行数据,这个时候自增列的值是4.

[test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

我们删除id值最大的记录id=3

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.02 sec)

这个时候会发现AUTO_INCREMENT=4的值不会有任何变化。 我们来挖掘一下binlog的内容,就会发现insert语句很特别。

# /usr/local/mysql_5.7.17/bin/mysqlbinlog --socket=/home/data/s1/s1.sock --port=24801 -vv  /home/data/s1/binlog.000001
可以看到insert语句是MySQL独有的语法形式。
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2271

delete也会基于行级变更,定位到具体的记录的方式来删除。

### DELETE FROM `test`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ # at 2509 我们重启一下数据库。

# mysqladmin --socket=/home/data/s1/s1.sock --port=24801 shutdown # /bin/sh /usr/local/mysql_5.7.17/bin/mysqld_safe --defaults-file=/home/data/s1/s1.cnf &

重启之后就会发现情况发生了变化,原来的自增值4现在变为了3,这个也是基于max(id)+1的方式来计算的。

mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

这个时候我们来关注一下从库,从库的自增列值会变化吗? mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

这个时候就会发现重启数据库以后,主从的自增列的值不同了。 那么我们来进一步测试,在主库插入一条记录,这样自增列的值就是4.

mysql> insert into t1 values (null,2); Query OK, 1 row affected (0.01 sec)

自增列的值为4,而从库的自增列的值依旧没有任何变化。

继续插入一条记录,这个时候主库的自增列就会是5

mysql> insert into t1 values (null,2); Query OK, 1 row affected (0.00 sec)

而从库呢,这个时候自增列会持续发生变化吗?我们来验证一下,这个时候从库的自增列又开始生效了。

mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 还有一点需要注意,那就是指定了自增列的值,这一点上和Oracle有一定的差距,但是又很相似。 这个时候数据库主库中的数据如下:

mysql> select * from t1; +----+------+ | id | a | +----+------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 5 | 2 | +----+------+ 5 rows in set (0.00 sec)

为了方便测试,我们继续插入一条数据,这一次我指定了id值。

mysql> insert into t1 values(6,2); Query OK, 1 row affected (0.00 sec)

让人感到安慰的是,这张情况下自增列还是会持续增加。

mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

此时查看从库,这个自增列也还是7,, 通过这个案例,我们能够看到在MySQL会存在这样一类问题,实际上在多环境历史数据归档的情况下,如果主库重启,很可能会出现数据不一致的情况。

我也在MySQL的官方bug列表中看到很多人在讨论这个问题,看来很多人碰到这个坑。而这个问题其实细究起来实现也不是一个很繁琐的工作,为什么一直没有修复。

这个问题在MySQL很久以前就有,在现在依旧存在,什么时候会修复呢,根据官方的计划会在8.0中修复。让我们拭目以待。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:杨建荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-04-17

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 关于表联结方法(一)(r3笔记第57天)

    分类: Oracle 在sql语句中,如果from后面有多个表时,关于表的联结方法是很重要的一个环节。 大体有nested loop join,hash jo...

    jeanron100
  • 简单分析shared pool(三) (r5笔记第94天)

    提到shared pool,都会不由得和sql语句的解析过程联系起来,因为shared pool所做的主要工作就是解析sql语句,生成执行计划,在之前的两篇中...

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

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

    jeanron100
  • 机器学习+颜色=智能配色,解读Brandmark的解决方案color-wheel

    当我们提起机器学习和颜色的时候,首先想到的可能是黑白照片着色和风格迁移2类应用。虽然这些像素级技术适用于照片,但它们对于艺术的插画风格的图片效果并不是很好。Br...

    mixlab
  • 测试驱动之一个登录引发的思考与总结(七)

    测试驱动开发模式在今天已经不是什么新鲜事了,它要求开发在写业务代码的时候,先写出测试代码,,同时单元测试例子决定了如何来写产品的代码,并且不断的成功的执...

    无涯WuYa
  • 给Java程序猿们推荐一些值得一看的好书

    三哥
  • facebook atc弱网环境搭建和踩

    Augmented Traffic Control(又名atc)是一种模拟网络状况的工具。由facebook开源,是一个允许开发人员控制设备与互联网连接的项目。...

    py3study
  • 张嘴伸手,闭嘴收手,东大和庆大联合开发FaceDrive,超级机械臂只用表情就能控制

    或许不少人会选择章鱼博士,作为蜘蛛侠的死对头,拥有四只机械臂的章鱼博士从各种意义上说都十分“难缠”,当然,作为反派,他也足够让观众留下深刻的印象。

    大数据文摘
  • jdk下httpserver源码解析

      所以今天就由我来为大家解析一下httpserver的源码。(这里我会去掉其中的https部分的源码,只讲http部分,对httpserver中https的实...

    不会飞的小鸟
  • 【M01N】资源约束委派和NTLM Relaying的组合拳接管域内任意主机系统权限

    【声明:本文所述相关技术仅限研究和学习使用,请遵守国家网络安全法律法规,勿用于入侵等非法用途,使用本文相关技术造成的法律问题与本公司无关。】

    绿盟科技研究通讯

扫码关注云+社区

领取腾讯云代金券