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

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

  昨天的一篇文章，今天有不少网友向我确认一些细节，我想最近正好在看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的情况如下：

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-f可以得到。

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-f就是基础的配置文件。内容大体如下：

[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-f主从复制的检查。

# masterha_check_repl  --conf=app1-f  

检查无误后，我们启动MHA manager服务。

nohup masterha_manager --conf=/home/mha/conf/app1-f  > /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-f --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 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-f &启动之后有很多细节需要确认，一个是关于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和自增列的数据测试有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。