MySQL GTID复制配置篇

一、GTID

MySQL 5.6 的新特性之一，加入了全局事务 ID（Global Transaction ID）来强化数据库的主备一致性，故障恢复，以及容错能力。用于取代过去通过 binlog 文件偏移量定位复制位置的传统方式。MySQL 会为每一个 DML/DDL 操作增加一个唯一标记叫做 GTID，这个标记在整个复制环境中都是唯一的。主从环境中主库的 dump 线程可以直接通过 GTID 定位到需要发送的 binary log 位置，而不再需要指定 binary log 的文件名和位置，因此切换极为方便。

二、基于 GTID 的主从复制

基于 GTID 的复制有什么优点？

GTID 的使用不单单是用单独的标识符替换旧的二进制日志文件/位置，它也采用了新的复制协议。旧的协议往往简单直接，即：首先从服务器上在一个特定的偏移量那里连接到一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。新协议稍有不同：支持以全局统一事务 ID 为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID 复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

GTID 相关操作：默认情况下将一个事务记录进二进制文件时，首先记录它的GTID，而且GTID和事务相关信息一并要发送给从服务器，由从服务器在本地应用认证，但是绝对不会改变原来的事务ID号。因此在GTID的架构上就算有了N层架构，复制是N级架构、事务ID依然不会改变，有效的保证了数据的完整和安全性。

你可以使用基于语句的或基于行的复制与GTIDs ，但是，为了获得最佳效果，我们建议你使用基于行（ROW）的格式。

另外支持启用GTID，对运维人员来说应该是一件令人高兴的事情，在配置主从复制，传统的方式里，你需要找到binlog和pos点，然后change master to指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在MySQL 5.6里，如果使用了GTID，启动一个新的复制从库或切换到一个新的主库，就不必依赖log文件或者pos位。只需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。

1）在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后change master to指向新的master，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道master的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。

2）多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制，当只有1个库，多线程复制是没有用的。

基于 GTID 复制实现的工作原理？

1）master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。

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

3）sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录（所以MySQL5.6 SLAVE必须要开启二进制日志记录）。

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

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

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

2.1 实验环境配置

1）Master/Slave 时间同步。

2）Master/Slave 关闭防火墙和 Selinux。

3）Master/Slave 主机名设定。

4）Master/Slave 安装 MySQL 5.7

看：MySQL 5.7多方式安装

5）Master/Slave准备标准目录

6）数据库初始化

MASTER节点（3306）

1）配置主配置文件

2）启动MASTER

3）查看GTID是否正常启用

查看GTID相关参数，可以通过MySQL的几个变量查看相关的GTID信息。

gtid_executed

在当前实例上执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。所以，设置 set sql_log_bin=0 后执行的事务不会生成 binlog 事件，也不会被记录到 gtid_executed 中。执行 RESET MASTER 可以将该变量置空。

gtid_purged

binlog 不可能永远驻留在服务上，需要定期进行清理（通过 expire_logs_days 可以控制定期清理间隔），否则迟早它会把磁盘用尽。gtid_purged 用于记录已经被清除了的 binlog 事务集合，它是 gtid_executed 的子集。只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为 gtid_purged 相同的值。gtid_executed 为空意味着要么之前没有启动过基于 GTID 的复制，要么执行过 RESET MASTER。执行 RESET MASTER 时同样也会把 gtid_purged 置空，即始终保持 gtid_purged 是 gtid_executed 的子集。

gtid_next

会话级变量，指示如何产生下一个 GTID。可能的取值如下:

• AUTOMATIC

自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。

• ANONYMOUS

设置后执行事务不会产生 GTID。

• 显式指定的 GTID

可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则，下次执行事务时会报错。

4）创建复制用户

SLAVE节点（3307）

1）配置主配置文件

2）启动SLAVE

3）连接MASTER

4）启动复制线程

2.4 验证主从复制

MASTER主机

SLAVE主机

可以看到 IO 和 SQL 线程都为 YES，另外 Retrieved_Gtid_Set 接收了 5 个事务，Executed_Gtid_Set 执行了 5 个事务。

主的数据库已经复制过来了。

三、对主从复制配置中定义的参数进行介绍

log-bin = mysql-bin

从服务器是否开启二进制日志，默认关闭。

binlog-format  =  row

启用基于行的二进制日志的记录，对于复制更容易校验，不容易出错。

log-slave-updates = 0 | 1

Slave更新操作是否记入二进制日志，如果开启则必须要开启log-bin，开启二进制日志可以做级联复制。

sync-binlog = 0 | 1

是否立即同步二进制日志到硬盘，默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)。

在MySQL 5.7.7之前，默认值为sync_binlog=0。MySQL 5.7.7及更高版本使用默认值为1，这是最安全的选择，但如上所述可能会影响性能。

server-id = 1

同一个复制拓扑中的每个服务器的id号必须唯一。

read-only = 0 | 1

锁定从服务器为只读，对于super用户不生效，如对只有select、update、insert、delete权限的用户生效。

skip-slave-start

告诉从服务器当服务器启动时不启动从服务器线程，使用START SLAVE语句在以后启动线程。

这四个参数是启用binlog/relaylog的校验，防止日志出错。

binlog-rows-query-log-events = 1

启用二进制日志记录事件相关的详细信息，可降低故障排除的复杂度。

relay_log_purge = 0 | 1

开启relay log自动purge操作，默认就是开启的。

relay_log_recovery = 0 | 1

当Slave发生crash导致重连master时，其不根据master-info.log的信息进行重连，而是根据relay-info中执行到master的位置信息重新开始拉master上的日志数据（不过需要确保日志依然存在于master上，否则就。。。）。

slave-paralles-workers = 0

默认是0，不开启从服务器的多线程复制，MySQL5.6中从服务器设置多线程复制只能针对多个库才有效，如果没有多个库开启多线程只会增加系统开销。（Mariadb中此参数为slave-paralles-threads）。

binlog-checksum = CRC32

二进制日志的校验算法。

master-verify-checksum = 1

启用此选项后，从库将检查从中继日志读取的校验和，如果发生不匹配，则从库将停止并出现错误。默认禁用。

slave-sql-verify-checksum = 1

启动此选项后，从库线程使用从中继日志读取的校验和来验证数据。在不匹配的情况下，从库停止并出现错误。设置此变量将立即对所有复制通道生效，包括运行通道。

report-port = 3306

report-host = master_ip/slave_ip

#提供复制报告的端口，和数据库端口一致。提供复制报告的主机，设置为当前主机的主机IP。当在从库设置了这两个参数时，在主库使用show slave hosts时才可以看到完整的从库信息。

master-info-repository = file | table

relay-log-info-repository = file | table

MySQL 5.6开始支持在SLAVE上把master信息和relay信息记录在事务表，用于解决从库宕机后的主从数据一致性问题。另外，如果你使用MySQL5.7的多源复制的话就必须的要求把master和relay信息存储到事务表中。具体当Slave发送Crash导致重连master时怎么会导致数据不一致性看这篇文章：http://www.ywnds.com/?p=7326。

sync_master_info = 10000

此变量对从库的影响取决于从库master_info_repository是否设置为FILE或TABLE，如以下段落所述。

master_info_repository = FILE。如果sync_master_info值大于0，则从库在每次事件之后将其master.info文件同步到磁盘（使用 fdatasync()）。sync_master_info如果为0，则MySQL服务器不执行master.info文件到磁盘的同步，相反，服务器依赖于操作系统与其他文件一样定期刷新其内容到磁盘。

master_info_repository = TABLE。 如果sync_master_info值大于0，则在每个sync_master_info事件之后，从库更新其主信息存储到表。如果为0，则表不会更新。

默认值为sync_master_info=10000，设置此变量将立即对所有复制通道生效，包括正在运行的通道。

sync_relay_log_info = 10000

此变量对从库的影响取决于从服务器的relay_log_info_repository设置（FILE或 TABLE），如果是这样TABLE，还判断中继日志信息表使用的存储引擎是否是事务性的（如InnoDB）还是不非事务性的（如MyISAM）。这些因素使从库对sync_relay_log_info的值大于零的行为的影响如下表所示：

relay_log_info_repository = FILE。如果sync_relay_log_info值大于0，则从库在每次事件之后将其relay-log.info文件同步到磁盘（使用 fdatasync()）。sync_relay_log_info如果为0，则MySQL服务器不执行relay-log.info文件到磁盘的同步，相反，服务器依赖于操作系统与其他文件一样定期刷新其内容到磁盘。

relay_log_info_repository = TABLE。 不管sync_relay_log_info值是大于0或等于0，则在每个sync_relay_log_info事件之后，从库都会更新信息存储到表，前提此表引擎是事务表。如果是非事务表（如Myisam），当sync_relay_log_info=0，则表不会更新。

默认值为sync_relay_log_info=10000，设置此变量将立即对所有复制通道生效，包括正在运行的通道。

sync_relay_log = 10000

如果此变量的值大于 0，则在每个 sync_relay_log 事件写入中继日志后，MySQL 服务器将其中继日志同步到磁盘（使用fdatasync() ）。设置此变量将立即对所有复制通道生效，包括正在运行的通道。

设置 sync_relay_log 为 0 会导致磁盘无法同步，在这种情况下，服务器依赖于操作系统刷新中继日志的内容，与其他文件一样。

值为1是最安全的选择，因为在发生崩溃的情况下，从中继日志中最多丢失一个事件。但是，它也是最慢的选择（除非磁盘具有电池备份的缓存，这使得同步非常快）。

slave_transaction_retries = 10

如果从库 SQL 线程失败，是因为执行事务碰到 InnoDB 死锁或事务执行时间超过 InnoDB 的 innodb_lock_wait_timeout，它会自动重试。默认值为 10，设置此变量将立即对所有复制通道生效，包括运行通道。

从 MySQL 5.7.5 起，在从库上启用多线程时，支持重试事务。在以前的版本中，从库使用多线程时，slave_transaction_retries 被视为等于 0。

slave_skip_errors = off | ddl_exist_errors | all | 

通常情况下，当从库发生错误时，复制停止，这样你就可以手动解决数据的不一致。此选项会导致从库SQL线程在 slave_skip_errors 返回选项值中列出的任何错误时继续复制，虽然不会发生错误了，但很大可能会导致主从数据不一致。所以非常不推荐使用all值来使从库忽略所有错误消息。

MySQL 5.7 支持一个额外的速记值 ddl_exist_errors，相当于错误代码列表1007、1008、1050、1051、1054、1060、1061、1068、1094、1146。附录B，错误，错误代码和常见问题列出了服务器错误代码。

gtid-mode = on

开启 GTID 复制功能（注意在 MariaDB 中此参数无效，因为 GTID 已经是标配了）。

enforce-gtid-consistency = true

启动强制 GTID 的一致性，如果开启 GTID 功能则此参数必须要开启（MaraDB 中此参数无效了）。slave 在做同步复制时，无须找到 binlog 日志和 POS 点，直接 change master to master_auto_position=1 即可，自动根据 GTID 进行同步数据。

四、如何产生 GTID？

GTID 的生成受 gtid_next 控制，在 Master 上，gtid_next 值默认是 AUTOMATIC，即在每次事务提交时自动生成新的 GTID。它从当前已执行的 GTID 集合（即 gtid_executed）中，找一个大于 0 的未使用的最小值作为下个事务 GTID。同时在 binlog 的实际的更新事务事件前面插入一条 set gtid_next 事件。以下是一条 insert 语句生成的 binlog 记录：

在 Slave 上回放主库的 binlog 时，先执行 set gtid_next …，然后再执行真正的 insert 语句，确保在主和备上这条 insert 的 GTID 相同。

一般情况下，GTID 集合是连续的，但使用多线程复制（MTS）以及通过 gtid_next 进行人工干预时会导致 GTID 空洞。

继续执行事务，MySQL 会分配一个最小的未使用 GTID，也就是从出现空洞的地方分配 GTID，最终会把空洞填上。

这意味着严格来说我们即不能假设 GTID 集合是连续的，也不能假定GTID序号大的事务在GTID序号小的事务之后执行，事务的顺序应由事务记录在binlog中的先后顺序决定。

五、如何修复 GTID 复制错误？

在基于 GTID 的复制拓扑中，要想修复 Slave 的 SQL 线程错误，过去的 SQL_SLAVE_SKIP_COUNTER 方式不再适用。需要通过设置 gtid_next 或 gtid_purged 完成，当然前提是已经确保主从数据一致，仅仅需要跳过复制错误让复制继续下去。

在从库上执行以下 SQL：

其中 gtid_next 就是跳过某个执行事务，设置 gtid_next 的方法一次只能跳过一个事务，要批量的跳过事务可以通过设置 gtid_purged 完成。假设下面的场景：

此时从库的 Executed_Gtid_Set 已经包含了主库上 1-13 和 12 的事务，再开启复制会从后面的事务开始执行，就不会出错了。注意，使用 gtid_next 和 gtid_purged 修复复制错误的前提是跳过那些事务后仍可以确保主备数据一致。如果做不到，就要考虑 pt-table-sync 或者拉备份的方式了。

<参考>

MySQL 5.6 全局事务 ID（GTID）实现原理（一）

MySQL 5.6 全局事务 ID（GTID）实现原理（二）

MySQL 5.6 全局事务 ID（GTID）实现原理（三）

http://keithlan.github.io/2018/11/24/gtid_dba_part1/

喜欢 (9)赏

[资助本站您就扫码 谢谢]

分享 (0)