专栏首页杨建荣的学习笔记MySQL中GTID和自增列的数据测试(r12笔记第38天)

MySQL中GTID和自增列的数据测试(r12笔记第38天)

昨天的一篇文章MySQL自增列主从不一致的测试(r12笔记第37天),今天有不少网友向我确认一些细节,我想最近正好在看GTID的东西,可以揉在一起来说说。

GTID这个概念看似简单,实际上还是有不少的门道。

我们来从架构的设计角度来看看存在哪些场景需要考虑GTID的变化。

一主两从的架构模式下GTID的变化

我们就以一主两从的架构为基准进行阐述。在这个架构模式下我们会用到MHA的方案。

如果这个时候Master节点宕机了,MHA就会开启检查机制。

这个时候Slave 1节点就会变为新的Master,Slave 2会从Slave 1上重新应用数据变更,这个时候GTID是怎么变化的,从库的Executed GTID Set到底是一个还是两个。

这个场景继续往下延伸。如果宕机的主库启动之后,假设是硬件问题,比如电源故障灯原因,Master节点启动了,那么Master节点的重新加入主从环境中GTID是如何变化的。这样就是下面的架构图了。

而我们把这个问题继续细化,那就是和自增列值的问题结合起来。看看在这种场景下,MySQL的实现方式是否会出现数据不一致,无法复制的情况。两者结合起来算是一个相对完整的测试场景了。当然我要标记为第一篇,因为还会有第二篇出来。

我们看看如何操作。

一主两从的架构模式下GTID的实践

一主两从我们标识为主(Master节点),从库1(Slave 1),从库2 (Slave 2),大体的测试步骤如下:

  1. 初始化一主两从
  2. Master节点初始化数据,测试自增列值
  3. 配置MHA,Master节点宕机
  4. MHA切换,Slave 1节点升为主库,Slave 2节点为从库
  5. Master节点启动
  6. Master节点加入主从复制环境

步骤1:初始化,得到一主两从的GTID情况

步骤1相对简单,可以使用sandbox或者是快速脚本的方式搭建。

搭建完成后,先来看看Gtid的情况。

mysql> show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 1475 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7 查看server_uuid的情况如下:

mysql> show global variables like 'server_uuid%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_uuid | 4f7b0b93-2400-11e7-99cb-782bcb377193 +----------------+--------------------------------------+ 3 rows in set (0.01 sec)

我们后续的测试都会参考这个值。 Slave 1节点的情况如下,和Master节点的server_uuid明显不同。这个信息可以在初始化的目录auto.cnf可以得到。

mysql> show global variables like 'server%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 24802 | | server_id_bits | 32 | | server_uuid | 5433468e-2400-11e7-a834-782bcb377193 +----------------+--------------------------------------+

查看master status的信息如下,这一点可以看出是和Master节点的Gtid Set值相同,证明这个Gtid的值是一个唯一性标识,当然从GTID的全称就是全局事务标识。

mysql> show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 438 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7 1 row in set (0.00 sec)

我们来看看show slave status的结果。 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.127.128.78 Master_User: rpl_user 。。。 Retrieved_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:6-7 Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-7 。。。 1 row in set (0.00 sec)

步骤2:初始化Master节点,测试自增列问题

步骤2我们来初始化一下Master节点。就创建一个数据库test create database test;Slave 1节点的情况如下,可以看到末尾的事务ID序号开始增加。 mysql> show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 589 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-8

下面的初始化就是关键了,我们会测试自增列的情况,来复现一个经典问题。创建一个表t1,然后插入3条记录。 mysql> drop table if exists t1; mysql> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb; mysql> insert into t1 values (1,2); mysql> insert into t1 values (null,2); mysql> insert into t1 values (null,2); mysql> select *from t1; +----+------+ | id | a | +----+------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | +----+------+

毫无疑问,这个时候自增列的值是4.

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

在Slave 1节点和Slave 2节点得到的数据情况是一致的,都是4 然后我们做下面的变更,删除表中id=3的值。这个情况也很容易理解,那就是自增列不会变化。

mysql> delete from t1 where id=3; 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 Slave 1节点和Slave 2节点也是如此,自增列值都是4

步骤3:配置MHA,Master节点宕机

这个步骤可以参考 sandbox和MHA快速测试(r12笔记第32天),对MHA的配置有一个基本的介绍,可以使用如下的两个脚本来做基本的检验,app1.cnf就是基础的配置文件。内容大体如下:

[server default] manager_workdir=/home/mha/manager manager_log=/home/mha/manager/app1/manager.log port=24801 -指定的端口 user=mha_test password=mha_test --需要提前创建 repl_user=rpl_user repl_password=rpl_pass master_ip_failover_script= /home/mha/conf/master_ip_failover2 # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report # master_ip_online_change_script= /script/masterha/master_ip_online_change [server1] hostname=10.127.128.78 port=24801 candidate_master=1 [server2] hostname=10.127.128.78 candidate_master=1 port=24802 [server3] hostname=10.127.128.78 candidate_master=1 port=24803ssh的互信检查。

# masterha_check_ssh --conf=app1.cnf主从复制的检查。

# masterha_check_repl --conf=app1.cnf 检查无误后,我们启动MHA manager服务。

nohup masterha_manager --conf=/home/mha/conf/app1.cnf > /tmp/mha_manager.log 2>&1 & 然后我们查到对应的进程号,直接Kill即可。

[root@grtest app1]# ps -ef|grep 24801 mysql 2168 1918 0 14:29 pts/7 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/home/data/s1/s1.cnf --basedir=/usr/local/mysql_5.7.17 --datadir=/home/data/s1 --plugin-dir=/usr/local/mysql_5.7.17/lib/plugin --user=mysql --log-error=/home/data/s1/grtest.err --pid-file=/home/data/s1/grtest.pid --socket=/home/data/s1/s1.sock --port=24801 root 3623 12108 0 14:40 pts/7 00:00:00 grep 24801 [root@grtest app1]# kill -9 1918 2168我们简单描述一下,Master节点杀掉后,主库的表t1的自增列值如果启动之后就会是3,即上一次的max(id)+1开始计算。而从库的自增列值为4,这个该怎么平衡呢?

步骤4:MHA切换,Slave1节点为主库

整个切换的过程是自动完成的,MHA会检测心跳,然后自动开始切换主从复制关系。整个过程GTID就是一个需要注意的地方。 Started automated(non-interactive) failover. Invalidated master IP address on 10.127.128.78(10.127.128.78:24801) Selected 10.127.128.78(10.127.128.78:24802) as a new master. 10.127.128.78(10.127.128.78:24802): OK: Applying all logs succeeded. 10.127.128.78(10.127.128.78:24802): OK: Activated master IP address. 10.127.128.78(10.127.128.78:24803): OK: Slave started, replicating from 10.127.128.78(10.127.128.78:24802) 10.127.128.78(10.127.128.78:24802): Resetting slave info succeeded. Master failover to 10.127.128.78(10.127.128.78:24802) completed successfully.

于是Slave 1节点就正式接管环境。

查看新主库Slave 1节点的信息如下,这个GTID还是原来Master节点的。

mysql> show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 1895 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

server_uuid的部分还是原来的设置。

mysql> show global variables like 'server%'; +----------------+--------------------------------------+ | Variable_name | Value | +----------------+--------------------------------------+ | server_id | 24802 | | server_id_bits | 32 | | server_uuid | 5433468e-2400-11e7-a834-782bcb377193 | +----------------+--------------------------------------+

这个地方需要关注,那就是查看自增列的情况,因为原来是从库,所以得到的最新值为4.

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

在这种情况下Slave 2节点就会重新调整复制关系, mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.127.128.78 Master_User: rpl_user Master_Port: 24802 。。。 Retrieved_Gtid_Set: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14 Auto_Position: 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=4 DEFAULT CHARSET=latin1

这里可能会有些疑惑,而且对于GTID的理解会有一些误差,我们在Slave 1节点上插入一行数据。 mysql> insert into t1 values(null,2);这个时候查看自增列的情况如下,会逐步递增。

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

这个时候就需要重新查看下Gtid的情况了。可以看到这里有原来Master节点的server_uuid,也有当前新主库的server_uuid值。

mysql> show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 2133 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14, 5433468e-2400-11e7-a834-782bcb377193:1

新的从库Slave 2节点的信息如下:

节点3: mysql> mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.127.128.78 Master_User: rpl_user Master_Port: 24802 。。。 Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1 Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14, 5433468e-2400-11e7-a834-782bcb377193:1 Auto_Position: 1 。。。 所以可以发现failover以后的自增列值不会受到影响,而且GTID set会包含当前主库和原来的主库信息。

步骤5:Master节点启动

启动Master节点步骤相对简单。

# /usr/local/mysql_5.7.17/bin/mysqld_safe --defaults-file=/home/data/s1/s1.cnf &

启动之后有很多细节需要确认,一个是关于master status的信息。

mysql> show slave status\G Empty set (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: binlog.000002 Position: 190 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14

这个地方明显不对,那是因为主从复制关系还没有调整。 我们看看这个时候的自增列值情况。纠结的问题就是自增列之为3,而Slave 1节点和Slave 2节点的自增列值为5. 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

步骤:6:Master节点加入主从复制环境

重新配置主从复制关系:

CHANGE MASTER TO MASTER_HOST='10.127.128.78', MASTER_PORT=24802, MASTER_AUTO_POSITION=1, MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass';启动新的从库,启动后会发现GTID会是两个。

mysql> start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.127.128.78 Master_User: rpl_user Master_Port: 24802 。。。 Retrieved_Gtid_Set: 5433468e-2400-11e7-a834-782bcb377193:1 Executed_Gtid_Set: 4f7b0b93-2400-11e7-99cb-782bcb377193:1-14, 5433468e-2400-11e7-a834-782bcb377193:1 Auto_Position: 1 。。。

这个时候再次查看自增列的情况。这个步骤看起来复杂一些,其实就是新的从库会去接收应用在Slave 1节点上的数据变化,相当于在Master节点插入了一条记录,导致这个自增列之继续增加。

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 --id值 恢复了 1 row in set (0.00 sec)

我们可以查看binlog的信息来进行基本的验证。 [root@grtest app1]# /usr/local/mysql_5.7.17/bin/mysqlbinlog -vv /home/data/s1/binlog.000002 ... BEGIN /*!*/; # at 310 #170418 14:44:01 server id 24802 end_log_pos 352 Table_map: `test`.`t1` mapped to number 219 # at 352 #170418 14:44:01 server id 24802 end_log_pos 392 Write_rows: table id 219 flags: STMT_END_F BINLOG ' sbX1WBPiYAAAKgAAAGABAAAAANsAAAAAAAEABHRlc3QAAnQxAAIDAwAC sbX1WB7iYAAAKAAAAIgBAAAAANsAAAAAAAEAAgAC//wEAAAAAgAAAA== '/*!*/; ### INSERT INTO `test`.`t1` ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ # at 392 #170418 14:44:01 server id 24802 end_log_pos 419 Xid = 19 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

这样一来对于GTID的理解就会更加清晰一些。对于自增列的问题也会更加明确,确确实实目前能够解决数据不一致的情况。

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL中的自增列

    当然基于MySQL自增列的实现,确实是不够优雅,在新的版本还在持续引入新的特性。比如MGR里面,自增列的步长大了许多,默认是7了,这是在设计的时候考虑了MGR的...

    jeanron100
  • 最近的几个技术问题总结和答疑(七) (r9笔记第38天)

    今天抽空整理,发现近期问我数据恢复,灾备的问题还比较多,我简单整理了一下。 问题1: 能请教一个问题么?我们用was链接的oracle数据库,是不是不建议在wa...

    jeanron100
  • 生产环境sql语句调优实战第六篇(r2笔记91天)

    生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

    jeanron100
  • 仿生眼有望助盲人恢复视力

    镁客网
  • Redis 中List 及 quicklist实现 2

    上一篇中看了List的使用方式、quicklist中的各个结构体,这一篇来看看quicklist里面的几个核心函数,quicklistCreate函数、quic...

    邹志全
  • SpanBERT:提出基于分词的预训练模型,多项任务性能超越现有模型!

    作者 | Mandar Joshi, Danqi Chen, Yinhan Liu, Daniel S. Weld, Luke Zettlemoyer, Ome...

    AI科技大本营
  • mysql无符号整型溢出

    码农二狗
  • 【DB笔试面试411】​设U1是db_datawriter角色中的成员,则U1从该角色中有_____、_____、_____。

    在SQL Server 2000中,设U1用户在某数据库中是db_datawriter角色中的成员,则用户U1从该角色中可以获得的数据操作权限有_____、__...

    小麦苗DBA宝典
  • 【SIGGRAPH Asia 2017 论文选读】基于图片风格特征的画家代表作选取

    【导读】第十届ACM SIGGRAPH Asia亚洲电脑图形及互动技术展览会将于今年11月27日至30日,在泰国的首都-曼谷隆重举行。本篇选取文章来自我们课题组...

    WZEARW
  • IT 界开源贡献 Top 10,PHP 之父居然排第 10!第一名当之无愧!

    IT界开源贡献Top 10,你认识几个呢? 林纳斯·本纳第克特·托瓦兹 Linux之父 ? 林纳斯·托瓦兹是Linux之父,他是开源理念的发起者之一,他曾经花费...

    企鹅号小编

扫码关注云+社区

领取腾讯云代金券