如何理解MySQL中GTID和自增列的数据测试

70次阅读

没有评论

共计 10848 个字符，预计需要花费 28 分钟才能阅读完成。

今天就跟大家聊聊有关如何理解 MySQL 中 GTID 和自增列的数据测试，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

昨天的一篇文章，今天有不少网友向我确认一些细节，我想最近正好在看 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）, 大体的测试步骤如下：

初始化一主两从

Master 节点初始化数据，测试自增列值

配置 MHA,Master 节点宕机

MHA 切换，Slave 1 节点升为主库，Slave 2 节点为从库

Master 节点启动

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 的情况如下：

Slave 1 节点的情况如下, 和 Master 节点的 server_uuid 明显不同。这个信息可以在初始化的目录 auto.cnf 可以得到。

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-14server_uuid 的部分还是原来的设置。

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 的理解就会更加清晰一些。对于自增列的问题也会更加明确，确确实实目前能够解决数据不一致的情况。

看完上述内容，你们对如何理解 MySQL 中 GTID 和自增列的数据测试有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。

正文完