MySQLGTID全面总结--688IT编程网

MySQLGTID全⾯总结

⽬录

01 GTID简介

02 GTID⼯作原理

03 GTID的优缺点

04 测试环境搭建

05 开始测试

1 测试复制的故障转移

2 复制错误跳过

01 GTID简介

GTID，全称Global transaction identifiers，也称之为全局事务ID。MySQL-5.6.2开始⽀持，MySQL-5.6.10后完善，GTID 分成两部分，⼀部分是服务的UUid,UUID保存在mysql数据⽬录的autof⽂件中,

这是⼀个⾮常重要的⽂件，不能删除，这⼀部分是不会变的。下⾯是⼀个uuid的值举例：

[root@dev01 mysql]# cat autof

[auto]

server-uuid=ac1ebad0-ef76-11e7-872b-080027a03bb6

另外⼀部分就是事务ID了，随着事务的增加，值依次递增。也就是说，GTID实际上是由UUID+TID组成的。其中UUID是⼀个MySQL实例的唯⼀标识。TID代表了该实例上已经提交的事务数量。如下所⽰为⼀个GTID的例⼦：

3db33b36-0e51-409f-a61d-c99756e90155:1-14

02 GTID⼯作原理

1、master更新数据时，会在事务前产⽣GTID，⼀同记录到binlog⽇志中。

2、slave端的i/o 线程将变更的binlog，写⼊到本地的relay log中。

3、sql线程从relay log中获取GTID，然后对⽐slave端的binlog是否有记录。

4、如果有记录，说明该GTID的事务已经执⾏，slave会忽略。

5、如果没有记录，slave就会从relay log中执⾏该GTID的事务，并记录到binlog。

6、在解析过程中会判断是否有主键，如果没有就⽤⼆级索引，如果没有就⽤全部扫描。

03 GTID的优缺点

优点：

1.⼀个事务对应⼀个唯⼀GTID，⼀个GTID在⼀个服务器上只会执⾏⼀次

2.GTID是⽤来代替传统复制的⽅法，GTID复制与普通复制模式的最⼤不同就是不需要指定⼆进制⽂件名和位置

3.减少⼿⼯⼲预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升⼀台备机为主机

缺点：

1.不⽀持⾮事务引擎

2.不⽀持create table ... select 语句复制(主库直接报错)

原理：（会⽣成两个sql，⼀个是DDL创建表SQL，⼀个是insert into 插⼊数据的sql。

由于DDL会导致⾃动提交，所以这个sql⾄少需要两个GTID，但是GTID模式下，只能给这个sql⽣成⼀个GTID ）

3.不允许⼀个SQL同时更新⼀个事务引擎表和⾮事务引擎表

4.开启GTID需要重启（

5.7除外）

5.对于create temporary table 和 drop temporary table语句不⽀持

6.不⽀持sql_slave_skip_counter

04 测试环境搭建

节点：

server1 192.168.197.128 3306 Master

server2 192.168.197.137 3306 Slave

server3 192.168.197.136 3306 Slave

开启GTID需要启⽤这三个参数：

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates = 1

搭建测试环境的步骤如下：

1.在主节点上创建复制⽤户，开启主节点的GTID选项;

mysql> grant replication slave on *.* to 'repluser'@'%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.00 sec)

2.从节点上进⾏change master to操作，搭建主从，如下：

mysql> change master to

> master_host='192.168.197.128',

-> master_user='repluser',

-> master_password='123456',

-> master_port=3306,

-> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

3.搭建成功后，在主节点197.128上查看从节点是否加⼊：

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

+-----------+------+------+-----------+--------------------------------------+

| 3 | | 3306 | | 969488f5-c486-11e8-adb7-000c29bf2c97 |

| 2 | | 3306 | | bb874065-c485-11e8-8b52-000c2934472e |

+-----------+------+------+-----------+--------------------------------------+

rows in set (. sec)

查看连接：

mysql> show processlist;

+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+

+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+

+----+----------+------------------+------+------------------+------+---------------------------------------------------------------+------------------+

rows in set (. sec)

4.三台测试环境的UUID分别是：

197.128

mysql> select @@server_uuid;

+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| bd0d-8691-11e8-afd6-4c3e51db5828 |

+--------------------------------------+

row in set (0.00 sec)

197.137

mysql> select @@server_uuid;

+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| bb874065-c485-11e8-8b52-000c2934472e |

+--------------------------------------+

row in set (0.00 sec)

197.136

mysql> select @@server_uuid;

+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| f5-c486-11e8-adb7-000c29bf2c97 |

+--------------------------------------+

row in set (0.00 sec)

05 开始测试

测试环境主要分为以下⼏个⽅⾯：

a.测试复制的故障转移

b.复制错误跳过

1 测试复制的故障转移

先来看看测试复制的故障转移：

（1）⾸先将server 3的复制过程停掉

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

（2）在server 1上创建⼀些数据

mysql> create table yyy.a(id int);

Query OK, 0 rows affected (0.03 sec)

mysql> create table yyy.b(id int);

Query OK, 0 rows affected (0.02 sec)

mysql> create table yyy.c(id int);

Query OK, 0 rows affected (0.02 sec)

（3）在另外两台上⾯查看数据结果：

server

mysql> show tables from yyy;

+---------------+

| Tables_in_yyy |

+---------------+

| a |

| b |

| c |

+---------------+

rows in set (0.00 sec)

server

mysql> show tables from yyy;

Empty set (0.00 sec)

(4)此时可以发现，server 2 的数据相⽐较server 3，它的数据⽐较新，此时停⽌server 1，模拟主服务器宕机：

[root@work_NAT_1 init.d]# service mysqld stop

Shutting [ OK ]

（5）此时我们发现其他两个节点已经不能访问server 1了

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Reconnecting after a failed master event read

Master_Host: 192.168.197.128

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1364

Relay_Log_File: mysql-relay-bin.000004

Relay_Log_Pos: 1569

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 1364

Relay_Log_Space: 2337

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repluser@192.168.197.128:3306' - retry-time: 60 retries: 1

Last_SQL_Errno: 0

（6）我们需要设置server 2为server 3的主库，因为server 2的数据⽐较新。此时如果采⽤以前的办法，需要计算之前主库的log_pos和当前要设置成主库的log_pos，很有可能出错。所以出现了⼀些⾼可⽤性的⼯具如MHA，MMM等解决问题。

在MySQL5.6之后，很简单的解决了这个难题。因为同⼀事务的GTID在所有节点上的值⼀致，那么根据server3当前停⽌点的GTID就能定位到server2上的GTID，所以直接在server3上执⾏change即可：

mysql> change master to

-> master_host='192.168.197.137',

-> master_user='repluser',

-> master_password='123456',

-> master_port=,

-> master_auto_position=;

Query OK, rows affected, warnings (0.01 sec)

（7）此时查看server 3上的数据，可以发现，数据已经同步过来了;

2 复制错误跳过

上⾯的测试中，最终的结果是server 2是主节点，server 3是从节点，下⾯我们来验证复制错误跳过的办法。

(1)⾸先我们在从节点上执⾏⼀个drop的语句，让两边的数据不⼀致，如下：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| DBAs |

| customer |

| inc_db |

| mysql |

| performance_schema |

| sys |

| testdb |

| yeyz |

| yyy |

+--------------------+

rows in set (. sec)

mysql> drop database yyy;

Query OK, rows affected (. sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| DBAs |

| customer |

| inc_db |

| mysql |

| performance_schema |

| sys |

| testdb |

| yeyz |

+--------------------+

rows in set (. sec)

（2）然后我们在server 2上执⾏drop database yyy的操作，如下：

mysql> drop database yyy;

Query OK, 3 rows affected (0.02 sec)

（3）此时我们看到server 3上已经出现了主从不同步的错误警告，因为它上⾯并没有yyy的数据库(前⼀步已经删除)，错误情况如下;

mysql> show slave status\G

*************************** . row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.197.137

Master_User: repluser

Master_Port:

Connect_Retry:

Master_Log_File: mysql-bin.

Read_Master_Log_Pos:

Relay_Log_File: mysql-relay-bin.

Relay_Log_Pos:

Relay_Master_Log_File: mysql-bin.

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno:

Last_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'

Skip_Counter:

Exec_Master_Log_Pos:

Relay_Log_Space:

Last_SQL_Error: Error 'Can't drop database 'yyy'; database doesn't exist' on query. Default database: 'yyy'. Query: 'drop database yyy'

Replicate_Ignore_Server_Ids:

Master_Server_Id:

Master_UUID: bb874065-c485-e8-b52-c2934472e

Master_Info_File: mysql.slave_master_info

Retrieved_Gtid_Set: bd0d--e8-afd6-c3e51db5828:-,

bb874065-c485-e8-b52-c2934472e:

Executed_Gtid_Set: db33b36-e51-f-a61d-c99756e90155:-,

bd0d--e8-afd6-c3e51db5828:-,

f5-c486-e8-adb7-c29bf2c97:

Auto_Position:

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

row in set (0.00 sec)

(4)当我们使⽤传统的⽅法来跳过这个错误的时候，会提⽰出GTID模式下不被允许，如下：

mysql> set global sql_slave_skip_counter=;

ERROR (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction 那么这种⽅式下应该如何跳过这个错误呢？

(5)因为我们是通过GTID来进⾏复制的，也需要跳过这个事务从⽽继续复制，这个事务可以到主上的binlog⾥⾯查看：因为不知道哪个GTID上出错，所以也不知道如何跳过哪个GTID。但是我们可以在show slave status⾥的信息⾥到在执⾏Master⾥的POS:2012，也就是上述第(3)步第18⾏代码。现在我们拿着这个pos:2012去server 2的⽇志⾥⾯，可以发现如下信息：

# at 2012

#190305 20:59:07 server id 2 end_log_pos 2073 GTID last_committed=9 sequence_number=10 rbr_only=no

SET @@SESSION.GTID_NEXT= 'bb874065-c485-11e8-8b52-000c2934472e:1'/*!*/;

# at 2073

#190305 20:59:07 server id 2 end_log_pos 2158 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=/*!*/;

drop database yyy

/*!*/;

（6）我们可以看到GTID_NEXT的值是

，然后我们通过下⾯的⽅法来重新恢复主从复制：

mysql> stop slave;