215_mysql_复制技术_GTID

一 复制的概述

1 复制： 将来自Mysql server的数据变更，通过其逻辑的二进制日志，传输到其它的一个或多个的MySQL server（slave/从库）中，slave通过回放这些逻辑的二进制日志来完成数据库的同步

特点：

复制是异步的(主库传binlog到从库后,并不关心从库是否收到), 从库是否收到也不影响主库的读写

安全性无法得到100%保证，如果安全性诉求可以考虑半同步复制

2 数据同步的方法

传统复制；基于File & position

GTID复制：利用GTID 寻找相对应的binlog日志记录，不关心其具体的position

3 数据同步类型

3.1 异步复制：一主多从库

3.2 半同步复制： 主库会话再提交事务之前，会等至少一个从库返回收到binglog的ACK消息（确认收到,并将事务的事件记录到从库的relay log中）

3.3 延迟复制： 避免逻辑损坏

3.4 同步复制：保证写操作完全同步到其它数据节点，而不仅仅是binlog的接收(NDB cluster, Percona XtraDB cluster PXC, MySQL group Replication MGR)

4 复制格式

SBR (statement based replication) 记录SQL语句的原始文本, 日志量较小，容易出现主备异常

PBR (row based replication) 记录实际发生的SQL语句，日志量较大, 可以保证主从库数据的一致性

Mix based replication  mysql自己判断,不影响使用SBR, 影响一致性使用 PBR

二 复制基本原理

1 概述：

基于binlog(变更操作记录日志 DDL DML 等) ，利用这些日志在从库进行回放,

2 复制小细节

2.1 主从之间复制，从库携带所需的二进制信息向主库发起请求（主库不主动，主库也不知道从库要啥）

2.2 建立连接后，新增binlog 主库主动发送给从库（主库binlog dump发binlog从库IO线程）从库不知道主库啥时候有数据变更

2.3 每个从库的复制都是独立运行的(主库binlog dump, 从库IO/SQL线程)

3 复制过程

3.1 新建连接 从库主动获取binlog

3.2 非新建连接, 数据变更-binlog --- binlog dump线程 –从库IO 线程（主库 show processlist 可以看到binlog dump线程）

3.3 binlog dump线程读取binlog中的每个事件时,会获取binlog日志上的锁，读完释放（不关心binglog是否发给了从库）

3.4 从库IO线程接收 主库binlog-dump线程发来的binlog, 并将其写入relay-log中

3.5  从库SQL线程读取 relay-log，按读取顺序进行回放写入从库

三 复制格式（binlog_format）

1 statement

优点：日志量少，减少存储空间, 包括数据变更语句，方便进行数据库审计

缺点：

执行结果不确定的DML会导致主从异常 ;

UDF用户自定义函数/存储过程结果不确定;

DML语句中 不建议使用order by 的limit子句（主从执行排序结果可能不同，结果不确定）

部分函数无法正确复制(LOAD_FILE(), UUID(), RAND()等)

Insert into select / 未使用索引的 update语句产生的杭锁会多

2 row 模式

2.1 优点

2.1.1 记录数据行的变化，安全

2.1.2 insert into select 产生行锁少， update/delete 语句中where条件，只会记录满足条件的SQL结果，

2.2 缺点：

2.2.1 生产日志量较多, 可以使用binlog_row_image=minimal 来减少二进制日志

2.2.2  如果blob值，row模式复制时间比statement时间长

2.2.3 无法直接看到具体SQL语句， 可以在主库中启用：binlog_rows_query_log_events(binlog中写入一个rows_query_log_event类型的事件记录原始SQL文本)

2.3 row格式补充

Row的复制中，不会复制临时表,临时表侄女呗创建临时表的线程访问, 不记录在binlog中, statement会记录相关语句(5.7.25会记录 DROP TEMPORARY TABLE IF EXISTS)

Row 格式：如果修改行数多,会将行数据变更拆分到多个事件中, 从库回放时候可能会产生更多的行锁减少了并发

Binlog编码用的是 Base64，解码时候： mysqlbinlog –base64-output=decode-rows –verbose选项

Slave_exec_mode= strict 默认，如果是idempotent时如果主键发生错误会被跳过，有可能导致主从失败

不能使用 @@server_id ，row会记录实际值而不是变量名， 如果坚持使用 可以考虑用statement

推荐使用表级别复制（--replicate-do-table , --replicate-ignore-table）不推荐使用（--replicate-do-db, --replicate-ignore-db和--replicate-rewrite-db）

3 对安全/不安全语句的处理(某些函数,RAND(), UUID()/自定义函数等)

Row：对安全/不安全处理一致

MIX: 不安全语句用row  安全语句用statement

Statement  对不安全的语句告警, 甚至拒绝执行 （XA分布式事务， 语句执行顺序有不同会导致死锁）

四 GTID复制 (Global Transaction Identifier 全局事务标识符)

4.1 GTD 特性

GTID复制时,主库提交的事务可以被任意从库应用识别与跟踪, 用GTID来定位对应的二进制日志文件和位置，可方便看主从的一致性

GTID Set信息在主库和从库中都会保存, 可以通过GTID SET信息来追踪二进制日志的来源，GTID不能重复，仅能执行一次，后续重复的会被跳过执行

5.7后新增 mysql.gtid.executed表来持久化GTID信息

4.2 GTID的格式和存储

4.2.1 格式：GTID = source_id : transaction_id  复制状态表PERFORMANCE_SCHEMA.replicatioin_applier_status_by_worker表

Source_id: 标识事务的源server，为master的 server_uuid

Transaction_id: master 上提交事务的顺序

4.2.2 GTID SET: GTID SET 由一个或多个GTID列表/范围组成的集合, 使用方式

1 系统变量 gtid_executed 和 gtid_purged存储的值需要使用 GTID_SET

2 START SLAVE 子句 UNTIL SQL_BEFORE_GTIDS 和 UNTIL SQL_AFTER_GTIDS使用

3 内置函数 GTID_SUBSET() 和 GTID_SUBSTRACT() 的输入参数使用 GTID SET

4.3 GTID的存储

Mysql.gtid_executed 持久化GTID，包括： 实例的UUID, 对应的GTID SET的起始和结束事务ID(顺序号) reset master 时候 该表会被置空

4.3.1 GTID_mysql.gtid.executed存儲

1 未开启，在每个事务未提交时,刷一次GTID到 mysql.gtid.executed

2 开启 log_bin & log_slave_updates 只有在binlog切换和关闭msyqld进程时候 刷入 mysql.gitd.executed

3 如果意外宕机当前的GTID不会刷入mysql.gitd.executed表中，恢复后会扫binlog补齐相关GTID 最后记录的GTID不会被记录

4 GTID的完整记录由系统变量 gtid_executed全局提供, 始终使用@@global.gtid.executed 来表示最新的GTID状态

5 通过系统变量 gtid_executed_compression_period(每隔多少事务压缩一次), 当设置为0时, 表示关闭压缩

6 负责压缩该数据的线程是：thread/sql/compress.gitd_table (大部分时间在睡觉/休眠)

4.3.2 GTID的生命周期

1 主库执行事务并提交, 分配GTID (序号顺序写入)

2 基于分配了GTID的事务, 通过二进制日志中的Gtid_log_event将GTID 写入binlog做原子保留/mysqld关闭 写入mysql.gtid.executed表中保留

3 分配的GTID 更新给全局变量 @@global.gtid.executed

4  binlog 传到slave后，从库读取到的GTID并将其设置为系统变量 gtid_next， 表示从库必须用此GTID记录下一个事务（会话上下文中设置）

5 从库验证GTID是否已经被使用和属于哪个线程,  @@global.gtid.owned表示每个GTID以及拥有它的线程ID, 如果GTID被重复持有，不会报错,会自动跳过;

6 如果GTID尚未使用，从库会应用该事务：binlog的 GTID EVENT的语句”SET @@SESSION.GTID_NEXT=XXX” 从库会将gtid_next 设置为XXX

7 如果从库开启log_bin和log_slave_updates 从库回放后会将这些GTID写入自身binlog保留，切binlog或关闭mysqld线程时保存在mysql.gitd_executed

8 如果从库未开启log_bin和log_slave_updates，事务提交刷表, 5.7版本中DML是原子操作保留DDL不是，8.0 DDL&DML都是原子操作

9 从库提交事务后，GTID通过非原子化方式添加到从库的@@global.gtid.executed中

备注：

1 多线程复制的从库（slave_parallel_workers >0）可以并行应用事务，复制的事务可以无序提交, 除非设置(slave_preserve_commit_order=1)

2  系统变量gtid_purged(@@global.gtid.purged) 中的GTID SET 表示已经提交的且在server binlog内不存在的GTID，SET @@global.gtid.purged设置GTID SET

4.3.3 GTID 自动定位

CHANGE MASET TO   启用 MASERT AUTO POSITION 选项,激活自动定位以链接主库， 从库开启 log_bin, log_slave_updates

1 从库发送一个 GTID SET, 其中包括从库已经收到, 已提交或两者都已完成的事务.

2 此时GTID SET = @@global.gtid.executed中的GTID SET 与 接收事务（performance_schema.replication_connection_status表中received_transaction_set）并集

3 如果在复制期间，主库发现从库接收或提交了与主库UUID相同的事务，但主库本身没有其记录，会报错 ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER

4.3.4 GTID 复制限制

1 GTID依赖事务 更新操作涉及非事务引擎,不能同时操作支持Innodb和MyISAM 引擎, 同时操作会导致多个GTID 分配给同一个事务,主从 相同的表使用不同存储引擎，如果非事务表定义了触发器，可能导致事务与GTID的一一对应关系破坏

2 使用GTID时候，create table 语句不允许使用 select 语句

3 临时表(enfore_gtid_consisitency 为on) 时，事务/存储过程/存储函数/触发器 不支持CREATE /DROP TEMPORARY TABLE语句

4 GTID模式下，如果对mysql升级 mysql_upgrade升级 不能启用binlog 记录（--write-binlog选项）

4.4 GTID & Binlog关系

- Previous_gtid_log_event Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs 在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。 换句话说，如果你手动set global gtid_purged=xx； 那么xx是不会记录在Previous_gtid_log_event中的。 - GTID和Binlog之间的关系是怎么对应的呢? 如何才能找到GTID=? 对应的binlog文件呢？ 假设有4个binlog: bin.001,bin.002,bin.003,bin.004 bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40 bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80 bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120 bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160 假设现在我们要找GTID=$A，那么MySQL的扫描顺序为: - 从最后一个binlog开始扫描（即: bin.004） - bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中 - bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止

4.5 持久化参数

重要参数如何持久化

1) 如何持久化gtid_executed    (前提是log-bin=mysql-bin, log_slave_update=1 )

gtid_executed = mysql.gtid_executed        #正常情况下

或者

gtid_executed = mysql.gtid_executed + last_binlog中最后没写到mysql.gtid_executed中的gtid_event     #恢复情况下

2) 如何持久化重置的gtid_purged值?

reset master; set global gtid_purged=$A:a-b;

由于有可能手动设置过gtid_purged=$A:a-b, binlog.index中，last_binlog的Previous-GTIDs并不会包含$A:a-b

由于有可能手动设置过gtid_purged=$A:a-b, binlog.index中，first_binlog的Previous-GTIDs肯定不会出现$A:a-b

重置的gtid_purged = @@global.gtid_executed(mysql.gtid_executed:注意，考虑到这个表的更新触发条件，所以这里用@@global.gtid_executed代替 last_binlog的Previous-GTIDs  (last_binlog所有的gtid_event)

用 $reset_gtid_purged 来表示重置的gtid

3) 如何持久化gtid_purged  (前提是log-bin=mysql-bin, log_slave_update=1 )

1 gtid_purged=binlog.index:first_binlog的Previous-GTIDs + $reset_gtid_purged

- MySQL 5.6 版本，在my.cnf文件中添加: 1 gtid_mode=on (必选) #开启gtid功能 2 log_bin=log-bin=mysql-bin (必选) #开启binlog二进制日志功能 3 log-slave-updates=1 (必选) #也可以将1写为on 4 enforce-gtid-consistency=1 (必选) #也可以将1写为on - MySQL 5.7或更高版本，在my.cnf文件中添加: gtid_mode=on (必选) enforce-gtid-consistency=1 （必选） log_bin=mysql-bin （可选） #高可用切换，最好开启该功能 log-slave-updates=1 （可选） #高可用切换，最好打开该功能

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，否则下次执行事务时会报错

MySQL SQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。