一 复制的概述

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