1、GTID简介​

1.1 GTID​

全称Global transaction identifiers，也称之为全局事务ID。​

MySQL-5.6.2开始支持，MySQL-5.6.10后完善，GTID 分成两部分，一部分是服务的UUid,UUID保存在mysql数据目录的auto.cnf文件中，这是一个非常重要的文件，不能删除，这一部分是不会变的。​

下面是一个uuid的值举例：​

[root@dev01 mysql]# cat auto.cnf ​

[auto]​

server-uuid=067cd5c2-b0eb-11ed-97fa-246e9657f7a0​


另外一部分就是事务ID了，随着事务的增加，值依次递增。也就是说，GTID实际上是由UUID+TID组成的。​

其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量。​

如下所示为一个GTID的例子：​

067cd5c2-b0eb-11ed-97fa-246e9657f7a0:1-14​

1.2 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. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

​

1.3 GTID的优缺点​

优点​

1、一个事务对应一个唯一GTID，一个GTID在一个服务器上只会执行一次。​

2、GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置、可以更简单的实现failover，不用以前那样在需要找log_file和log_pos。​

3、减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机。​

4、更简单的搭建主从复制、比传统的复制更加安全。​

5、GTID是连续的没有空洞的，保证数据的一致性，零丢失。​

缺点​

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、仅支持ASSIGN_GTIDS_TO_ANYMOUS_TRANSSACTIONS=OFF的sql_replica_skip_counter​


1.4 参数说明​

①gtid_mode：控制是否启用gtid，on/on_permissiv/off_perissiv/off；​

②enforce_gtid_consistency：用于保证GTID一致性的，有on/off/warn三个值​

：不允许任何事务违反gtid一致性；​

：允许所有事务违反gtid一致性；​

：允许所有事务违反gtid一致性，但是这种情况下会生成告警，mysql5.7.6新增。​

③gtid_executed：它是一个gtid的范围，表示的是已经执行过的所有的gtid事务集或者是由于语句人为设置的gtid。​

④gtid_purged：表示的是已经执行过但是已经被清理掉的gtid，也是一个范围，它是executed的一个子集。在以下几种情况下，gtid_purged会有值：​

禁用二进制日志的情况下，提交事务的GTID；​

写入二进制日志的GTID已经被删除了；​

使用语句显示的指定gtid purged：set @@global.gtid_purged=...。​

⑤gtid_next：是会话级别的变量，对于提交的事务，会自动分配新的gtid，默认值为automatic，也可以显示指定gtid_next，来指定下一个事务的GTID号。​

⑥gtid_owned：该变量主要提供内部使用，保存的是服务器上当前正在使用的所有GTID列表，以及拥有它的线程的ID。​


Retrieved_Gtid_Set：从库已经接收到主库的事务编号（从库的IO线程已经接受到了）​

Executed_Gtid_Set：已经执行的事务编号（从库的执行sql线程已经执行了的sql）​


Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:4-5​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-5​

上面说明实际接收4-5，实际已经执行了1-5，说明之前已经接收过1-3.​


2、GTID 复制搭建​

2.1 环境信息​

环境名称​

主机名称​

Mysql版本​

IP​

端口​

OS 版本​

主库​

master​

Mysql8.0.30​

172.16.134.24​

3310​

Centos 7.2​

从库​

Slave01​

Mysql8.0.30​

172.16.134.25​

3310​

Centos 7.2​

级联从库​

Slave02​

Mysql8.0.30​

172.16.134.26​

3310​

Centos 7.2​


2.2 主库配置文件​

[root@master ~]# vi /etc/mysql3310/my.cnf​

gtid_mode = on​

enforce_gtid_consistency = 1​

log_slave_updates = 1​


2.3 主库重启​

[root@master ~]# mysqld_safe --defaults-file=/u01/mysql3310/my.cnf &​


2.4 主库创建复制用户​

mysql8 授权用户必须先创建，创建和授权不能使用同一语句​

mysql> createuser repl@'%' identified with mysql_native_password by 'rep1123';​

mysql> grant replication slave on *.* to repl@'%';​

mysql> flush privileges;​


2.5 从库配置文件​

salve01，salve02 两库均配置​

gtid_mode = on​

enforce_gtid_consistency = 1​

log_slave_updates = 1​

read-only=on​

relay_log=/u01/mysql3310/relaylog/mysql-relay-bin​


2.6 添加指向主库相关配置信息​

salve01，salve02 两库均操作​

Mysql>change replication source to ​

source_host='172.16.134.24',​

source_port=3310,​

source_user='repl',​

source_password='rep1123',​

source_auto_position=1;​


2.7 开启从库复制​

salve01，salve02 两库均操作​

Mysql>start replica;​


2.8 查看复制状态​

mysql> show slave status \G​

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

Slave_IO_State: Waiting for source to send event​

Master_Host: 172.16.134.24​

Master_User: repl​

Master_Port: 3310​

Connect_Retry: 60​

Master_Log_File: mysql-bin.000010​

Read_Master_Log_Pos: 502​

Relay_Log_File: mysql-relay-bin.000002​

Relay_Log_Pos: 718​

Relay_Master_Log_File: mysql-bin.000010​

Slave_IO_Running: Yes​

Slave_SQL_Running: Yes​

Replicate_Do_DB: ​

Replicate_Ignore_DB: ​

Replicate_Do_Table: ​

Replicate_Ignore_Table: ​

Replicate_Wild_Do_Table: ​

Replicate_Wild_Ignore_Table: ​

Last_Errno: 0​

Last_Error: ​

Skip_Counter: 0​

Exec_Master_Log_Pos: 502​

Relay_Log_Space: 928​

Until_Condition: None​

Until_Log_File: ​

Until_Log_Pos: 0​

Master_SSL_Allowed: No​

Master_SSL_CA_File: ​

Master_SSL_CA_Path: ​

Master_SSL_Cert: ​

Master_SSL_Cipher: ​

Master_SSL_Key: ​

Seconds_Behind_Master: 0​

Master_SSL_Verify_Server_Cert: No​

Last_IO_Errno: 0​

Last_IO_Error: ​

Last_SQL_Errno: 0​

Last_SQL_Error: ​

Replicate_Ignore_Server_Ids: ​

Master_Server_Id: 1​

Master_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0​

Master_Info_File: mysql.slave_master_info​

SQL_Delay: 0​

SQL_Remaining_Delay: NULL​

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates​

Master_Retry_Count: 86400​

Master_Bind: ​

Last_IO_Error_Timestamp: ​

Last_SQL_Error_Timestamp: ​

Master_SSL_Crl: ​

Master_SSL_Crlpath: ​

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1​

Auto_Position: 1​

Replicate_Rewrite_DB: ​

Channel_Name: ​

Master_TLS_Version: ​

Master_public_key_path: ​

Get_master_public_key: 0​

Network_Namespace: ​

1 row in set, 1 warning (0.01 sec)​


3、故障切换演练​

3.1 主从切换演练​

场景​

模拟主库down机、slave01数据同步完成、slave02数据未同步完成​

slave01升级为主库、slave02切换主库为slave02，观察slave02是否同步未完成的事务​

3.1.1 slave02停止复制​

Mysql>stop replica;​


3.1.2 主库创建测试数据​

Insertintotest values(10,’yuio’);​

Insertintotest values(11,’jkkio’);​


3.1.3 查询数据​

Slave01​

mysql> select * from test;​

+------+---------+​

| id | name |​

+------+---------+​

| 1 | wang |​

| 2 | lei |​

| 3 | jkl |​

| 4 | uyiop |​

| 5 | yuiop |​

| 6 | yuiolll |​

| 7 | hjkl |​

| 8 | tyuio |​

| 9 | jkl |​

| 10 | yuio |​

| 11 | jkkio |​

+------+---------+​

11 rows in set (0.00 sec)​

Slave02​

mysql> select * from test;​

+------+---------+​

| id | name |​

+------+---------+​

| 1 | wang |​

| 2 | lei |​

| 3 | jkl |​

| 4 | uyiop |​

| 5 | yuiop |​

| 6 | yuiolll |​

| 7 | hjkl |​

| 8 | tyuio |​

| 9 | jkl |​

+------+---------+​

9 rows in set (0.00 sec)​

Slave01 比slave02 数据新

​

3.1.4 主库down机​

Mysql>mysqladmin –uroot –proot shutdown​


3.1.5 设置新主库​

设置slave01 为slave02的主库，因为slave01的数据是完整的​

按照普通复制方法，需要计算主库的log_pos和从库设置成主库的log_pos，可能出现错误​

因为同一事务的GTID在所有节点上的值一致，那么根据slave02当前停止点的GTID就能定位到server01上的GTID，所以直接在slave02上执行change即可​

change replication source to ​

source_host='172.16.134.25',​

source_port=3310,​

source_user='repl',​

source_password='rep1123',​

source_auto_position=1;​


3.1.6查询同步结果​

mysql> select * from test;​

+------+---------+​

| id | name |​

+------+---------+​

| 1 | wang |​

| 2 | lei |​

| 3 | jkl |​

| 4 | uyiop |​

| 5 | yuiop |​

| 6 | yuiolll |​

| 7 | hjkl |​

| 8 | tyuio |​

| 9 | jkl |​

| 10 | yuio |​

| 11 | jkkio |​

+------+---------+​

11 rows in set (0.00 sec)​

数据被正常同步​


3.2 主从复制报错修复​

场景​

模拟从库删除测试表、主库对表进行插入操作​

观察从库复制是否报错​


3.2.1 场景模拟​

从库：​

mysql> drop table test_01;​

Query OK, 0 rows affected (0.06 sec)​

主库：​

mysql> insert into test_01 values(5,'uio');​

Query OK, 1 row affected (0.00 sec)​


3.2.2 查看从库同步状态​

mysql> show slave status \G​

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

Slave_IO_State: Waiting for source to send event​

Master_Host: 172.16.134.24​

Master_User: repl​

Master_Port: 3310​

Connect_Retry: 60​

Master_Log_File: mysql-bin.000011​

Read_Master_Log_Pos: 899​

Relay_Log_File: mysql-relay-bin.000002​

Relay_Log_Pos: 726​

Relay_Master_Log_File: mysql-bin.000011​

Slave_IO_Running: Yes​

Slave_SQL_Running: No​

Replicate_Do_DB: ​

Replicate_Ignore_DB: ​

Replicate_Do_Table: ​

Replicate_Ignore_Table: ​

Replicate_Wild_Do_Table: ​

Replicate_Wild_Ignore_Table: ​

Last_Errno: 1146​

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.​

Skip_Counter: 0​

Exec_Master_Log_Pos: 550​

Relay_Log_Space: 1285​

Until_Condition: None​

Until_Log_File: ​

Until_Log_Pos: 0​

Master_SSL_Allowed: No​

Master_SSL_CA_File: ​

Master_SSL_CA_Path: ​

Master_SSL_Cert: ​

Master_SSL_Cipher: ​

Master_SSL_Key: ​

Seconds_Behind_Master: NULL​

Master_SSL_Verify_Server_Cert: No​

Last_IO_Errno: 0​

Last_IO_Error: ​

Last_SQL_Errno: 1146​

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.​

Replicate_Ignore_Server_Ids: ​

Master_Server_Id: 1​

Master_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0​

Master_Info_File: mysql.slave_master_info​

SQL_Delay: 0​

SQL_Remaining_Delay: NULL​

Slave_SQL_Running_State: ​

Master_Retry_Count: 86400​

Master_Bind: ​

Last_IO_Error_Timestamp: ​

Last_SQL_Error_Timestamp: 230227 14:53:19​

Master_SSL_Crl: ​

Master_SSL_Crlpath: ​

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-7​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6​

Auto_Position: 1​

Replicate_Rewrite_DB: ​

Channel_Name: ​

Master_TLS_Version: ​

Master_public_key_path: ​

Get_master_public_key: 0​

Network_Namespace: ​

1 row in set, 1 warning (0.01 sec)​

复制报错​

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-7​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6​


事务接收了1-7，但7没有执行成功。f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7​

在主库继续进行其他事务，观察gitd是否复制成功​

mysql> create table test02_01(id int ,name varchar(10));​

Query OK, 0 rows affected (0.08 sec)​


mysql> insert into test02_01 values(1,'jkl');​

Query OK, 1 row affected (0.00 sec)​

从库状态​

mysql> show replica status \G​

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

Slave_IO_State: Waiting for source to send event​

Master_Host: 172.16.134.24​

Master_User: repl​

Master_Port: 3310​

Connect_Retry: 60​

Master_Log_File: mysql-bin.000011​

Read_Master_Log_Pos: 1465​

Relay_Log_File: mysql-relay-bin.000002​

Relay_Log_Pos: 726​

Relay_Master_Log_File: mysql-bin.000011​

Slave_IO_Running: Yes​

Slave_SQL_Running: No​

Replicate_Do_DB: ​

Replicate_Ignore_DB: ​

Replicate_Do_Table: ​

Replicate_Ignore_Table: ​

Replicate_Wild_Do_Table: ​

Replicate_Wild_Ignore_Table: ​

Last_Errno: 1146​

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.​

Skip_Counter: 0​

Exec_Master_Log_Pos: 550​

Relay_Log_Space: 1851​

Until_Condition: None​

Until_Log_File: ​

Until_Log_Pos: 0​

Master_SSL_Allowed: No​

Master_SSL_CA_File: ​

Master_SSL_CA_Path: ​

Master_SSL_Cert: ​

Master_SSL_Cipher: ​

Master_SSL_Key: ​

Seconds_Behind_Master: NULL​

Master_SSL_Verify_Server_Cert: No​

Last_IO_Errno: 0​

Last_IO_Error: ​

Last_SQL_Errno: 1146​

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7' at master log mysql-bin.000011, end_log_pos 868. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.​

Replicate_Ignore_Server_Ids: ​

Master_Server_Id: 1​

Master_UUID: f1b88047-a5ea-11ed-8ee1-246e9657f7a0​

Master_Info_File: mysql.slave_master_info​

SQL_Delay: 0​

SQL_Remaining_Delay: NULL​

Slave_SQL_Running_State: ​

Master_Retry_Count: 86400​

Master_Bind: ​

Last_IO_Error_Timestamp: ​

Last_SQL_Error_Timestamp: 230227 14:53:19​

Master_SSL_Crl: ​

Master_SSL_Crlpath: ​

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6​

Auto_Position: 1​

Replicate_Rewrite_DB: ​

Channel_Name: ​

Master_TLS_Version: ​

Master_public_key_path: ​

Get_master_public_key: 0​

Network_Namespace: ​

1 row in set, 1 warning (0.00 sec)​

事务，7-9未备执行，也就是说后续复制中断​

Retrieved_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9​

Executed_Gtid_Set: f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-6​


3.2.3 复制中断修复​

1、采用从库跳过错误事务修复​

1. 停止slave进程​

mysql> STOP REPLICA;​

2. 设置事务号，事务号从 Retrieved_Gtid_Set 获取，在session里设置gtid_next，即跳过这个GTID​

mysql> SET @@SESSION.GTID_NEXT= ' f1b88047-a5ea-11ed-8ee1-246e9657f7a0:7'​

3. 设置空事物​

mysql> BEGIN; COMMIT;​

4. 恢复自增事物号​

mysql> SET SESSION GTID_NEXT = AUTOMATIC;​

5. 启动slave进程​

mysql> START REPLICA;​

6、事务已经跳过，创建表已经同步​


mysql> show tables;​

+----------------+​

| Tables_in_test |​

| test |​

| test01 |​

| test02 |​

| test02_01 |​

2、重置 master 方法跳过错误（不建议采用）​

1. 停止slave进程​

mysql> STOP REPLICA;​

2. 重置master​

mysql>RESET MASTER;​

3. 设置事务号，事务号从 Retrieved_Gtid_Set 获取，在session里设置gtid_next，即跳过这个GTID​

mysql> SET @@SESSION.GTID_NEXT= ' f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9';​

上面这些命令的意思是，忽略 f1b88047-a5ea-11ed-8ee1-246e9657f7a0:1-9 之间的GTID 事务，下一次事务接着从9这个GTID开始，即可跳过上述错误。会丢失中间的数据​

4. 启动slave进程​

mysql> START REPLICA;​

6、事务已经跳过，创建表数据丢失​