概念参考博文《MySQL-5.6版本GTID的主从复制》https://www.cnblogs.com/abobo/p/4242417.html
配置参考博文《散尽浮华：Mysql5.6主从同步引用新特性-GTID》https://www.cnblogs.com/kevingrace/p/5569753.html
综合参考博文《阿里云：MySQL5.7杀手级新特性：GTID原理与实战》https://yq.aliyun.com/articles/57731

一、GTID复制基础

1.什么是GTID

GTID即全局事务ID（global transaction identifier），MySQL-5.6.2开始支持，MySQL-5.6.10后完善，GTID 分成两部分：

  • UUID：MySQL实例的唯一标识，UUID保存在mysql数据目录的auto.cnf文件中，这是一个非常重要的文件，不能删除，这一部分是不会变的。
  • TID：事务ID，代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式：

1
2
3
4
5
6
7
8
9
10
11
mysql> show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------------+----------+--------------+------------------+------------------------------------------+
| log-bin-3306.000007 |      720 |              |                  | a3fad9de-a5f9-11e8-8881-000c2900f08d:1-2 |
+---------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.01 sec)

# UUID:a3fad9de-a5f9-11e8-8881-000c2900f08d
# TID:1-2
# GTID:a3fad9de-a5f9-11e8-8881-000c2900f08d:1-2

==在整个复制架构中GTID是不变化的，即使在多个连环主从中也不会变。==例如：

  • ServerA —>ServerB —->ServerC ，GTID从在ServerA ,ServerB,ServerC 中都是一样的。

2.GTID的概述

  1. 全局事物标识：global transaction identifieds。
  2. ==GTID事务唯一性的，且一个事务对应一个GTID==。
  3. 一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
  4. GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是==使用master_auto_postion=1的方式自动匹配GTID断点进行复制==。
  5. MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。
  6. 在传统的slave端，binlog是不用开启的，但是==在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）==。

3.GTID复制的优点

  1. 更简单的实现failover，不用以前那样在需要找log_file和log_Pos。
  2. 更简单的搭建主从复制。
  3. 比传统复制更加安全。
  4. GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。

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

==要点：==

  • slave在接受master的binlog时，会校验master的GTID是否已经执行过（一个服务器只能执行一次）。
  • 为了保证主从数据的一致性，多线程只能同时执行一个GTID。

5.GTID复制的限制

  1. ==不支持非事务引擎（MyISAM）==
  2. 不支持create table … select 语句复制(主库直接报错)
    原理：（ 会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
    由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）
  3. 不允许一个SQL同时更新一个事务引擎表和非事务引擎表
  4. ==在一个复制组中，必须要求统一开启GTID或者是关闭GTID==
  5. 开启GTID需要重启（5.7除外）
  6. ==开启GTID后，就不再使用原来的传统复制方式==
  7. 对于create temporary table 和 drop temporary table语句不支持
  8. 不支持sql_slave_skip_counter

二、GTID复制参数

GTID相关参数解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like "%gtid%";  
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |    # enforce_gtid_consistency=ON,保证GTID安全的参数，对于出现GTID复制的限制从库会报错，即便是gtid_mode=OFF也会校验限制（必须ON）
| gtid_executed_compression_period | 1000      |    # gtid_executed,执行过的所有GTID，全局和seeeion级别都可以用。贴士：show  master status\G;输出结果中的Executed_Gtid_Set和gitd_executed一致。reset master时，此值会被清空。
| gtid_mode                        | ON        |    # gtid_mode=ON,是否开启GTID功能（必须ON）
| gtid_next                        | AUTOMATIC |    # gtid_next,这个时session级别的参数：[master]>show session variables like '%gtid_next%';
| gtid_owned                       |           |    # gtid_owned,正在运行的gtid，全局和session级别都可用，全局表示所有服务器拥有GTIDs，session级别表示当前client拥有所有GTIDs。（此功能用的少）
| gtid_purged                      |           |    # gtid_purged,丢弃掉的GTID，全局参数，设置在binlog中，已经purged的GTIDs，并且purged掉的GTIDs会包含到gtid_executed中。贴士：从而导致slave不会再去master请求这些GTIDs，并且Executed_Gtid_Set为空时，才可以设置此值。
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

关于GTID_MODE的4中模式

综合参考博文《阿里云：MySQL5.7杀手级新特性：GTID原理与实战》https://yq.aliyun.com/articles/57731

归纳总结：

  1. 当master产生Normal_GTID的时候（ON_PERMISSIVE，ON），如果slave的gtid_mode（OFF）不能接受Normal_GTID，那么就会报错
  2. 当master产生ANONYMOUS_GTID的时候（OFF_PERMISSIVE，OFF），如果slave的gtid_mode（ON）不能接受ANONYMOUS_GTID，那么就会报错
  3. ==设置auto_position的条件： 当master gtid_mode=ON时，slave可以为OFF_PERMISSIVE，ON_PERMISSIVE，ON。除此之外，都不能设置auto_position = on==

三、通过备份搭建GTID slave

开启GTID的必备条件

  • master,slave都配置的参数

5.6gtid参数都是静态参数，需要重启mysqld

1
2
3
4
5
6
7
8
[mysqld]
gtid_mode = ON                  # 打开gtid模式
log_bin = ON                    # 所有节点都要记录binlog
log-slave-updates = ON          # 开启记录binlog
enforce_gtid_consistency = ON   # gtid复制安全性参数

# 建议binlog改成行级模式，确保主从复制的一致性
binlog_format = row

1.通过mysqldump

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# master mysqldump
#mysqldump -uroot -p -S /data/mysql_data_3306/mysql_3306.sock -A --master-data=2 --single-transaction --set-gtid-purged -B > mysql207.sql
mysqldump -uroot -p -S /data/mysql_data_3306/mysql_3306.sock -A --master-data=2 --single-transaction --set-gtid-purged -B > mysql207.sql

# 导入sql到新实例后，CHANGE MASTER TO
stop slave;
CHANGE MASTER TO
MASTER_HOST='192.168.x.x',
MASTER_PORT=3306,
MASTER_USER='newslave',
MASTER_PASSWORD='123456',
MASTER_AUTO_POSITION=1;
start slave;

# MASTER_AUTO_POSITION=1;   # 这条命令是重点，1开启GTID复制

GTID复制错误

1.关于enforce_gtid_consistency=1引起的GTID复制错误

故障描述

[ERROR] Slave SQL: Error ‘CREATE TABLE … SELECT is forbidden when @@GLOBAL’ Error_code: 1786

  • 这套YD的高可用数据库是gtid_mode=OFF的，主库enforce_gtid_consistency=0，从库enforce_gtid_consistency=1
  • enforce_gtid_consistency是校验GTID复制安全性的参数，即便gtid_mode=OFF，把该参数设置成1仍然会检测主从复制过程中对GTID不安全的因素(CREATE TABLE ... SELECT )，所以会报这个错误。

总结

ucloud高可用是基于file+pos的传统复制，而高可用下再挂的从库才是基于GTID的复制。所以控制台创建的机器是强制要求gtid_mode=ON，enforce_gtid_consistency=1

  • 虽然没再建从库，两个GTID的开关开着，能够在主从复制的过程中暴露出从库同步失败的错误，如sql语句，不支持事务的MyISAM引擎等，从而去优化。
  • 再联想到自建库，若要从传统的赋值升级成GTID的复制，同样也要采用这种方式，打开GTID，只要Auto_Position: 0都是传统复制，让主从复制暴露出问题，进而优化。
  • 千万不能直接上，否则可能会经常出现主从复制异常的隐患。

enforce_gtid_consistency参数何时才能开？

==enforce_gtid_consistency是静态参数，重启服务生效。==

  1. gtid_mode=on,enforce_gtid_consistency必须为1，否则服务起不来
  2. gtid_mode=off,enforce_gtid_consistency可以为1，用于校验GTID安全性，意义不大，因为都是静态参数要么就全开。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    mysql> show variables like "%gtid%";
    +----------------------------------+------------------------------------------+
    | Variable_name                    | Value                                    |
    +----------------------------------+------------------------------------------+
    | binlog_gtid_simple_recovery      | ON                                       |
    | enforce_gtid_consistency         | ON                                       |
    | gtid_executed_compression_period | 1000                                     |
    | gtid_mode                        | OFF                                      |
    | gtid_next                        | AUTOMATIC                                |
    | gtid_owned                       |                                          |
    | gtid_purged                      | a3fad9de-a5f9-11e8-8881-000c2900f08d:1-2 |
    | session_track_gtids              | OFF                                      |
    +----------------------------------+------------------------------------------+
    8 rows in set (0.01 sec)

    # 一开一关根本没意义，因为两个都是静态参数
    # 校验完你以后要用呢？还得重启去开gtid_mode=ON

2.mysql5.7 gtid_mode->dynamic

  • 在mysql5.7是支持动态修改GTID_MODE的
  • 需要注意的是GTID_MODE需要逐级修改
1
2
3
4
5
6
7
mysql> set global gtid_mode=ON;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
mysql> set @@GLOBAL.GTID_MODE=ON;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

set @@GLOBAL.GTID_MODE=ON;
OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON