MySQL基于GTID复制 - 星哥说事

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

一、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（强制）。

二、GTID 的组成部分：

前面是 server_uuid：后面是一个序列号

例如：server_uuid：sequence number

7800a22c-95ae-11e4-983d-080027de205a:10

UUID：每个 mysql 实例的唯一 ID，由于会传递到 slave，所以也可以理解为源 ID。

Sequence number：在每台 MySQL 服务器上都是从 1 开始自增长的序列，一个数值对应一个事务。

三、GTID 比传统复制的优势：

1、更简单的实现 failover，不用以前那样在需要找 log_file 和 log_Pos。

2、更简单的搭建主从复制。

3、比传统复制更加安全。

4、GTID 是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。

四、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、slave 在接受 master 的 binlog 时，会校验 master 的 GTID 是否已经执行过（一个服务器只能执行一次）。

2、为了保证主从数据的一致性，多线程只能同时执行一个 GTID。

六、使用 GTID 搭建 mysql 的主从复制的主要参数：

[mysqld]
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=2003306    #每天实例的 server_id 都要不一样
 
#binlog
log-bin=mysqlbin
log-slave-updates=1   # 允许下端接入 slave
binlog_format=row      #强烈建议，其他格式可能造成数据不一致
 
#relay log
skip_slave_start=1
注意：建议使用 mysql-5.6.5 以上的最新版本。

启动 GTID 的两种方法：

方法一、

1、如果是在已经跑的服务器，你需要重启一下 mysql server。
2、启动之前，一定要先关闭 master 的写入，保证所有 slave 端都已经和 master 端数据保持同步。
3、所有 slave 需要加上 skip_slave_start= 1 的配置参数，避免启动后还是使用老的复制协议。

方法二、

1、如果是新搭建的服务器，直接启动就行了。

七、master-slave 搭建的注意事项：

（一）、使用 GTID 的方式，把 salve 端挂载 master 端：

1、启动以后最好不要立即执行事务，而是先 change master 上。
2、然后在执行事务，当然知不是必须的。
3、使用下面的 sql 切换 slave 到新的 master。

stop slave;

CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
master_auto_position = 1;

(二)、如果给已经运行的 GTID 的 master 端添加一个新的 slave

有两种方法：

方法一、适用于 master 也是新建不久的情况。

1、如果你的 master 所有的 binlog 还在。可以选择类似于上面的方法，安装 slave，直接 change master to 到 master 端。

2、原理是直接获取 master 所有的 GTID 并执行。

3、优点：简单方便。

4、缺点：如果 binlog 太多，数据完全同步需要时间较长，并且 master 一开始就启用了 GTUD。

方法二、适用于拥有较大数据的情况。（推荐）

1、通过 master 或者其他 slave 的备份搭建新的 slave。（看第三部分）

2、原理：获取 master 的数据和这些数据对应的 GTID 范围，然后通过 slave 设置 @@global.gtid_purged 跳过备份包含的 gtid。

3、优点：是可以避免第一种方法的不足。

4、缺点：相对来说有点复杂。

（三）、通过备份搭建新的 slave：（方法二的扩展）

两种方法：

方法一、mysqldump 的方式：

1、在备份的时候指定--master-data=2（来保存 binlog 的文件号和位置的命令）。
2、使用 mysqldump 的命令在 dump 文件里可以看到下面两个信息：SET @@SESSION.SQL_LOG_BIN=0;
  SET @@GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8';
3、将备份还原到 slave 后，使用 change master to 命令挂载 master 端。

注意：在 mysql5.6.9 以后的命令才支持这个功能。

方法二、percona Xtrabackup

1、Xtrabackup_binlog_info 文件中，包含global.gtid_purged='XXXXXX:XXXX' 的信息。
2、然后到 slave 去手工的 SET GLOBAL.GTID_PURGED='XXXXXX:XXXX'。
3、恢复备份，开启 change master to 命令。

注意：如果系统运行了很久，无法找到 GTID 的变好了，可以通过上面的方式进行查找。

八、GTID 如何跳过事务冲突：

1、这个功能主要跳过事务，代替原来的 set global sql_slave_skip_counter = 1。

2、由于在这个 GTID 必须是连续的，正常情况同一个服务器产生的 GTID 是不会存在空缺的。所以不能简单的 skip 掉一个事务，只能通过注入空事物的方法替换掉一个实际操作事务。

3、注入空事物的方法：

stop slave;
set gtid_next='xxxxxxx:N';
begin;commit;
set gtid_next='AUTOMAIC';
start slave;

4、这里的 xxxxx:N 也就是你的 slave sql thread 报错的 GTID，或者说是你想要跳过的 GTID。

九、GTID 的参数注释：

[master]>show global variables like '%gtid%';

1、enforce_gtid_consistency：开启 gtid 的一些安全限制（介意开启）。2、gtid_executed：全局和 seeeion 级别都可以用。用来保存已经执行过的 GTIDs。贴士：show  master status\G; 输出结果中的 Executed_Gtid_Set 和 gitd_executed 一致。reset master时，此值会被清空。3、gtid_owned：全局和 session 级别都可用，全局表示所有服务器拥有 GTIDs，session级别表示当前 client 拥有所有 GTIDs。（此功能用的少）4、gtid_mode：是否开启 GTID 功能。5、gtid_purged：全局参数，设置在 binlog 中，已经 purged 的 GTIDs，并且 purged 掉的 GTIDs 会包含到 gtid_executed 中。

贴士：从而导致 slave 不会再去 master 请求这些 GTIDs，并且 Executed_Gtid_Set 为空时，才可以设置此值。

6、gtid_next：这个时 session 级别的参数：[master]>show session variables like '%gtid_next%';

十、关于 GTID 的一些功能限制：

（一）、更新非事务引擎：

1、Case 重现：
master：对一个 innodb 表做一个多 sql 更新的事物，效果是产生一个 GTID。
slave：对应的表是 MYISAM 引擎，执行这个 GTID 的第一个语句后就会报错，因为非事务引擎一个 sql 就是一个事务。

2、错误编号：
last_Errno:1756

3、异常恢复方案：
（1）、简单的 stop slave; start slave; 就能够忽略错误。但是这个时候主从的一致性已经出现问题。需要手工的把 slave 差的数据补上。
（2）、首先将引擎调整为一样的，slave 也改为事务引擎。

（二）、create table ….select statements

1、case 重现：
master：直接执行一个 create table select * from table; 的 sql

2、报错：
error 1786

3、原理：
由于 create table …select 语句会生成两个 sql，一个是 DDL 创建表 SQL，一个是 insert into 插入数据的 sql。由于 DDL 会导致自动提交，所以这个 sql 至少需要两个 GTID，但是 GTID 模式下，只能给这个 sql 生成一个 GTID，如果强制执行会导致和上面更新非事务引擎一样的结果。

（三）、一个 sql 同事操作 innodb 引擎和 myisam 引擎：

case 重现：t1 表是 innodb，t2 表是 myisam
1、update t1,t2 set t1.id=1000,t2.id=1000 where t1.id=t2.id;
2、报错：1785
3、原理和第二个相同。

（四）、在一个 replication grouop 中，所有的 mysql 必须要统一开启或者关闭 GTID 功能。

1、case 重现：
将一个未开启 gtid 的 slave 通过原始的 binlog 和 pos 方式连接到开启 GTID 的 master。

2、报错：
The slave IO thread stops because the master has @@GLOBAL.GTID_MODE ON and this server has @@GLOBAL.GTID_MODE OFF。

（五）、在一个 replication group 中，如果开启 GTID 以后，就不再允许使用 classic 的复制方式：

1、case 重现：
将一个开启 gtid 的 slave 通过原始的 binlog 和 pos 方式连接到开启 GTID 的 master。

2、报错：
ERROR 1776(HY000)：Parameters MASTER_LOG_FILE，MASTER_LOG_POS，RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active。

（六）、GTID_MODE 是 not online 的：

需要重启才能生效，官方暂时不支持平滑的从 classic replication 切换到 GTID replication。
贴士：
由于 GTID 开启需要重启系统，一个复制组中所有的实例必须统一开启或者关闭 GTID, 开启 GTID 以后不能在使用 classic 复制。
问题：
也就是说在线业务必须统一关闭，然后再启动，会导致服务中断。

解决方案：
1、针对这种情况，社区有两种对应的平滑升级的方案：
一种是 booking.com 出品，这两个差别在淘宝 9 月份数据库月报里有说明，加了一个桥接的服务器，既可以运行 GTID 模式下，也可以运行 classic 模式下。
另外一种是 facebook.com 出品。所有的 slave 可以在开启 GTID 模式的情况下，可以连接到没有开启 GTID 模式的 master。

2、可以关闭一个部分，停止写操作，但是读不用，将另一部分改成 GTID 模式。

（七）、Temporary tables。

1、create temporary table 和 drop temporary table 语句一样在 GTID 环境下不支持。
如果 –enforce_gtid_consistency 参数开启，并且 autocommit=1，那么可以使用。

（八）、关于 Errant transaction

1、Errant transaction：所谓的 errant transaction 也就是没有规范的从 master 执行，而是直接从 slave 执行的事务。
2、由于 GTID 协议的原因，最开始已经提过（参见 GTID architecture）。
3、如果 slave 有 errant transaction 产生，由于 GTID 协议中的规则，很容易导致 failover 失败。主要有两种情况：

a、在 slave 上做了无用的或者临时的 errant transaction 操作，如果该 slave 升级成为 master 的话，连接到它的所有数据库都会获取到这个事务。如果一样就会产生冲突。b、由于做了这个 errant transaction 这个事务以后，其他的 slave 还没有获取这个 errant transaction 的 GTID，需要从 master 上发同步给其他的 slave，但是主的 binlog 又被删掉了，这时将会报错。

4、总之：尽量避免产生 errant transaction。可以通过：set sql_log_bin=off 的方式在 slave 执行 sql，但是也要考虑到数据一致性。

··············跳过错误
从库已经执行过的事务是 ’e10c75be-5c1b-11e6-ab7c-000c296078ae:1-5′, 执行出错的事务是 ’e10c75be-5c1b-11e6-ab7c-000c296078ae:6’，当前主备的数据其实是一致的，可以通过设置 gtid_next 跳过这个出错的事务。

在从库上执行以下 SQL：

mysql> set gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:6';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

设置 gtid_next 的方法一次只能跳过一个事务，要批量的跳过事务可以通过设置 gtid_purged 完成。

十一、GTID 与 crash safe salve

crash safe slave 是 MySQL 5.6 提供的功能，意思是说在 slave crash 后，把 slave 重新拉起来可以继续从 Master 进行复制，不会出现复制错误也不会出现数据不一致。

1、基于 binlog 文件位置的复制

在基于 binlog 文件位置的复制下，要保证 crash safe slave，配置下面的参数即可。
relay_log_info_repository = TABLE
relay_log_recovery = ON

这样可行的原因是，relay_log_info_repository = TABLE 时，apply event 和更新 relay_log_info 表的操作被包含在同一个事务里，innodb 要么让它们同时生效，要么同时不生效, 保证位点信息和已经应用的事务精确匹配。同时 relay_log_recovery = ON 时，会抛弃 master_log_info 中记录的复制位点，根据 relay_log_info 的执行位置重新从 Master 获取 binlog，这就回避了由于未同步刷盘导致的 binlog 文件接受位置和实际不一致以及 relay log 文件被截断的问题。

在同时使用 MTS(multi-threaded slave)时，为保证 crash safe slave 基于 binlog 文件位置的复制还需要设置 sync_relay_log=1, 因为 MySQL 在 Crash 恢复时必须先通过读取 relay log 补齐 MTS 导致的事务空洞。

2、基于 GTID 的复制

上面的设置并不适用于基于 GTID 的复制。在基于 GTID 的复制下，crash 的 Slave 重启后，从 binlog 中解析的 gtid_executed 决定了要 apply 哪些 binlog 记录，所以 binlog 必须和 innodb 存储引擎的数据保持一致。要做到这一点，需要把 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置为 1，即所谓的 ” 双 1 ″。

另外 MySQL 启动时，会从 relay log 文件中获取已接收的 GTIDs 并更新 Retrieved_Gtid_Set。由于 relay log 文件可能不完整，所以需要抛弃已接收的 relay log 文件。因此 relay_log_recovery = ON 也是必须的。

这样，对于基于 GTID 的复制，保证 crash safe slave 的设置就是下面这样。

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
relay_log_recovery = ON

关于如何设置以确保 crash safe slave，官方文档有明确记载，见 17.3.2 Handling an Unexpected Halt of a Replication Slave。

但是其中关于 GTID 的记载中存在笔误, 将 relay_log_recovery= 1 写成了 relay_log_recovery=0 (#83711)。同时也没有提到必须设置 ” 双 1 ″，但是 ” 双 1 ″ 是必要的，否则 crash 的 Slave 重启后，可能会重复应用 binlog event 也可能会遗漏应用 binlog event（#70659）。其中遗漏应用 binlog event 的情况更可怕，因为 Slave 在不触发 SQL 错误的情况下就默默的和 Master 不一致了。

3、设置 ” 双 1 ″ 对性能的影响

出于安全考虑，强烈推荐设置 ” 双 1 ″。” 双 1 ″ 会增大每个事务的 RT，但得益于 MySQL 的组提交机制，高并发下 ” 双 1 ″ 对系统整体 tps 的影响在可接受范围内。

sysbench oltp.lua 10 张表每张表 100w 记录(qps/ 并发数)

对更新同一行这样无法有效并行的场景，” 双 1 ″ 对性能的影响非常大。

sysbench update_non_index.lua 1 张表 1 条记录(qps/ 并发数)

对不能有效并行的 Slave replay，存在同样的问题。

通过指定 tx-rate 执行 sysbench 的 update_non_index.lua 脚本压测 30 秒，完成后检查主备延迟。

可以发现在 Slave 被配置为 ” 双 1 ″ 的情况下，延迟非常严重,1000 以上的 QPS 就会出现延迟，非 ” 双 1 ″ 下 QPS 到 5000 以上才会出现延迟（主库配置为 ” 双 1 ″）。

sysbench update_non_index.lua 1 张表 100w 条记录 128 并发(延迟 /qps)

以上测试环境是 Percona Server 5.6 运行在配置 HDD 的 8 core 虚机，由于测试结果和系统 IO 能力有很大关系，仅供参考。

4、如何在非 ” 双 1 ″ 下保证 crash safe slave

如果是 MySQL 5.7 可以关闭 log_slave_updates，这样 MySQL 会将已执行的 GTIDs 实时记录到系统表 mysql.gtid_executed 中，mysql.gtid_executed 是和用户事务一起提交的，因此可以保证和实际的数据一致。

log_slave_updates = OFF
relay_log_recovery = ON

如果是 MySQL 5.6 可以采用如下变通的方式。

按照基于 binlog 文件复制时 crash safe slave 的要求设置

relay_log_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON

在 Slave crash 后，根据 relay_log_info_repository 设置相应的 gitd_purged 再开启复制，
步骤如下:

1. 启动 MySQL，但不开启复制
mysqld --skip-slave-start

2. 在 Slave 上修改为基于 binlog 文件位置的复制
change master to MASTER_AUTO_POSITION = 0

3.启动slave IO 线程
start slave io_thread
这里不能启动 SQL 线程，如果接受到的 GTID 已经在 Slave 的 gtid_executed 里了，会被 Slave skip 掉。4.检查 binlog 传输的开始位置(即 Retrieved_Gtid_Set 的值)
show slave status\G
假设输出的 Retrieved_Gtid_Set 值为 e10c75be-5c1b-11e6-ab7c-000c296078ae:7-10

5.在 Master 上检查 gtid_executed
show master status
假设输出的 Executed_Gtid_Set 值为 e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10

6.在 Slave 上设置 gitd_purged 为 binlog 传输位置的前面的 GTID 的集合
reset master;
set global gitd_purged='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-6';

7. 修改回 auto position 的复制
change master to MASTER_AUTO_POSITION = 1

8.启动 slave SQL 线程
start slave sql_thread

但是，这种变通的方法不适合多线程复制。因为多线程复制可能产生 gtid gap 和 Gap-free low-watermark position，这会导致 Salve 上重复 apply 已经 apply 过的 event。后果就是数据不一致或者复制中断，除非设置 binlog 格式为 row 模式并且 slave_exec_mode=IDEMPOTENT,slave_exec_mode=IDEMPOTENT 允许 Slave 回放 binlog 时忽略重复键和找不到键的错误，使得 binlog 回放具有幂等性，但这也意味着如果真的出现了主备数据不一致也会被它忽略。

5、MTS 下特有的问题

在同时使用 MTS(slave_parallel_workers > 1)时，即使按上面 crash safe slave 的要求设置了基于 GTID 的复制，Slave crash 后再重启还是会导致复制中断。

通过强制杀掉 MySQL 所在虚机的方式模拟 Slave 宕机，然后再启动 MySQL，MySQL 日志中有如下错误消息：
启动 slave 时也会报错

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

出现这种现象的原因在于，relay_log_recovery=1 且 slave_parallel_workers>1 的情况下，mysql 启动时会进入 MTS Group 恢复流程，即读取 relay log，尝试填补由于多线程复制导致的 gap。然后 relay log 文件由于不是实时刷新的，在 relay log 文件中找不到 gap 对应的 relay log 记录 (覆盖了 gap 的 relay log 起始和结束位置分别被称为低水位和高水位, 低水位点即 slave_relay_log_info.Relay_log_pos 的值) 就会报这个错。

实际上，在 GTID 模式下，slave 在 apply event 的时候可以跳过重复事件，所以可以安全的从低水位点应用日志，没必要解析 relay log 文件。这看上去是一个 bug，于是提交了一个 bug 报告 #83713，目前还没有收到回复。

作为回避方法，可以通过清除 relay log 文件，跳过这个错误。执行步骤如下：

reset slave;
change master to MASTER_AUTO_POSITION = 1
start slave;

在这里，单纯的调 reset slave 不能把状态清理干净，内部的 Relay_log_info.inited 标志位仍然处于未被初始化状态, 此时调用 start slave 仍然会失败。因此需要补一刀 change master。

6、Master 的 crash safe

前面一直在讲 crash safe slave，Master 的 crash safe 同样重要。要想 Master 保持 crash safe 需要按下面的参数进行设置，否则不仅会丢失事务，gtid_executed 还可能和实际的 innodb 存储引擎中的数据不一致。

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

在 Master 配置为 ” 双 1 ″ 的情况下，Master crash 后，如果没有发生 failover，可以继续作为 Master。如果发生了 failover，可以检查旧 Master 和新 Master 上由旧 Master 执行的事务集合是否一致。
show master status

如果一致，可以按 MASTER_AUTO_POSITION = 1 的方式将旧 Master 作为 Slave 和新 Master 建立复制关系。否则，考虑做事务补偿或从新 Master 上拉取备份进行恢复。

在 Master 配置不是 ” 双 1 ″ 的情况下，在 Master crash 后由于难以准确知道旧 Master 上究竟执行了哪些事务，安全的做法是实施主备切换，并从新 Master 上拉取备份，把旧 Master 作为新 Master 的 Slave 进行恢复。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-09/146668.htm