MySQL-Replication 复制

Chapter 16 Replication
https://dev.mysql.com/doc/refman/5.7/en/replication.html

---

GTID 全局事务ID

GTID (Global Transaction IDentifier) 是全局事务标识。它具有全局唯一性，一个事务对应一个 GTID. 唯一性不仅限于主服务器，GTID 在所有的从服务器上也是唯一的。一个 GTID 在一个服务器上只执行一次，从而避免重复执行导致数据混乱或主从不一致。

在 MySQL 5.6 里面，MySQL 会通过内部机制自动匹配 GTID 断点，不再寻找 binlog 和 pos 点。我们只需要知道主节点的 ip, 端口，以及账号密码就可以自动复制。

从 MySQL 5.6 版本开始提供的基于 GTID 的主从复制简化了主从复制维护的难度，提高复制的可运维性，不再依赖 binlog 文件名和文件中的位置。

GTID 格式

GTID 由两部分组成：GTID = source_id:transaction_id, 例如：fb0bd50c-c006-11ea-ad10-912d2f656ff9:23
source_id 是产生 GTID 的服务器，即是 server_uuid, 在第一次启动时生成并保存到 DATADIR/auto.cnf 文件里。源码在 sql/mysqld.cc: generate_server_uuid()
transaction_id 是序列号（sequence number），在每台 MySQL 服务器上都是从 1 开始自增长的顺序号，是事务的唯一标识。

GTID 的集合是一组 GTIDs, 可以用 source_id:stardTID-endTID 范围表示，例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

此外，多个 单GTID 以及多个 GTID范围 可以合并在一起用一个表达式表示，之前应冒号 : 分割，例如 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49

更进一步，来自多个源 server_uuid 的 GTID 可以用逗号分割合并在一起表示，例如：
2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19

GTID 如何产生

GTID 的生成受 GTID_NEXT 控制。

在主服务器上 GTID_NEXT 默认值是 AUTOMATIC, 即在每次事务提交时自动生成 GTID. 它从当前已执行的 GTID 集合（即 gtid_executed ）中，找一个大于 0 的未使用的最小值作为下个事务 GTID.
所以，同一个 server_uuid 的 transaction_id 并不一定是严格递增的。 因为 GTID_NEXT 的选择策略并不是在之前最大 transaction_id 上加 1, 而是找一个之前没用过的 id 来补缺口。

同时在实际的更新事务记录之前，将 GTID 写入到 binlog（set GTID_NEXT 记录）。 在 Slave 上，从 binlog 先读取到主库的 GTID (即 get GTID_NEXT 记录)，而后执行的事务采用该 GTID.

基于GTID的binlog复制流程

1、主服务器更新数据时，会在事务前产生 GTID, 一同记录到 binlog 日志中。
2、binlog 传送到从服务器后，被写入到本地的 relay log 中。从服务器读取 GTID, 并将其设定为自己的 GTID（即 GTID_NEXT 变量）。
3、sql 线程从 relay log 中获取 GTID, 然后对比从服务器端的 binlog 是否有记录。
4、如果有记录，说明该 GTID 的事务已经执行，从服务器会忽略。
5、如果没有记录，从服务器就会从 relay log 中执行该 GTID 的事务，并记录到 binlog.

MySQL · 引擎特性 · 基于GTID复制实现的工作原理
http://mysql.taobao.org/monthly/2020/05/09/

gtid_executed

gtid_executed 已经执行的 gtid 集合（gtid-sets）。
全局参数，GTID集合包含所有在该服务器上执行过的事务编号和使用set gtid_purged语句设置过的事务编号，使用SHOW MASTER STATUS和SHOW SLAVE STATUS命令得到的Executed_Gtid_Set列值就取自于全局参数gitd_executed。
SHOW GLOBAL VARIABLES LIKE ‘gtid_purged’;

当复制主库关闭binlog时：

1. 事务提交不会生成GTID，mysql.gtid_executed表/gtid_executed变量/gtid_purged变量均不更新。

当复制主库开启binlog时：

1. 事务提交需要生成Binlog，GTID在Binlog的ordered_commit flush阶段生成。
2. 表mysql.gtid_executed在实例重启或flush log或binlog文件写满等造成binlog发生切换是保存上一个binlog执行过的全部gtid，属于非实时更新。
3. 全局变量gtid_executed在事务commit阶段更新,属于实时更新。
4. 全局变量gtid_purged在执行purge binary log命令或binlog超过保持期进行清理binlog时更新，属于非实时更新。

gtid_purged

gtid_purged 已经清除的 gtid 集合。
全局参数，GTID集合包含从binlog中purged掉的事务ID，该集合是全局参数gtid_executed的子集。

---

reset master

https://dev.mysql.com/doc/refman/5.7/en/reset-master.html

reset master 命令删除所有 binlog index file 中记录的所有 binlog 文件，将 binlog 日志索引文件清空，创建一个新的 000001 binlog 日志文件。
reset master 清空系统变量 gtid_purged 和 gtid_executed，在 MySQL 5.7.5 及后续版本中, RESET MASTER 还会会清空 mysql.gtid_executed 数据表。
这个命令通常仅仅用于第一次用于搭建主从关系的时的主库。

注意 reset master 不同于 purge binary log 的两处地方

1. reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从 000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值
2. reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时刻 reset master 命令不被支持，reset master 将master 的binlog从000001 开始记录,slave 记录的master log 则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件。

---

同步复制与binlog系统变量

16.1.6.1 Replication and Binary Logging Option and Variable Reference
https://dev.mysql.com/doc/refman/5.7/en/replication-options-reference.html

server_id

https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#sysvar_server_id

当你使用主从拓扑时，一定要对所有MySQL实例都分别指定一个独特的互不相同的server-id。默认值为0，当server-id=0时，对于主机来说依然会记录二进制日志，但会拒绝所有的从机连接；对于从机来说则会拒绝连接其它实例。

server-id用于标识数据库实例，防止在链式主从、多主多从拓扑中导致SQL语句的无限循环：
标记binlog event的源实例
过滤主库binlog，当发现server-id相同时，跳过该event执行，避免无限循环执行。
如果设置了replicate-same-server-id=1，则执行所有event，但有可能导致无限循环执行SQL语句。

---

binlog_format(ROW) binlog格式

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format

启动参数：--binlog-format=format
默认值：ROW
可取值：MIXED, STATEMENT, ROW

---

gtid_mode(OFF) 是否开启GTID

https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_mode

启动参数：--gtid-mode=MODE
范围：global
默认值：OFF
可取值：OFF, OFF_PERMISSIVE, ON_PERMISSIVE, ON

MySQL 5.7.6 之前，只能通过启动参数 --gtid-mode=MODE 配置。从 MySQL 5.7.6 开始，可动态配置。

OFF：新的和复制事务都使用anonymous。
OFF_PERMISSIVE：新的事务都使用anonymous，而复制事务可以使用GTID或anonymous。
ON_PERMISSIVE：复制事务都使用anonymous，而新事务可以使用GTID或anonymous。
ON: 新的和复制事务都使用GTID

---

auto_increment_increment(1) 自增id间隔

https://dev.mysql.com/doc/refman/5.7/en/replication-options-source.html#sysvar_auto_increment_increment

问题：
mysql 自增 id 从小到大是 1, 3, 5, 7 … 以2递增

原因：
变量 auto_increment_increment 的值配置为 2 了

auto_increment_increment 表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

auto_increment_offset 表示自增长字段从那个数开始，他的取值范围是1 .. 65535

---

binlog_row_image(full) binlog记录哪些内容

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_row_image

命令行参数：--binlog-row-image=image_type
可选值：full, minimal, noblob
默认值：full

MySQL 5.6 新增的参数，默认值是 full.

前提：binlog 格式必须为 row 格式或者 mixed 格式，不可以是 statement 格式。

before image：前镜像，即数据库表中修改前的内容。
after image：后镜像，即数据库表中修改后的内容。

full binlog 日志记录所有前镜像和后镜像。
minimal binlog 日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。
noblob binlog 记录所有的列，就像 full 格式一样。但对于 BLOB 或 TEXT 格式的列，如果他不是唯一识别列(唯一索引列、主键列)，或者没有修改，那就不记录。

如果设置为 minimal, 可节省磁盘空间，但由于前镜像不记录修改列，只在后镜像记录修改列，如果数据出现误操作，必然不能通过 flashback或 binlog2SQL 等快速闪回工具恢复数据，因为不能通过 BinLog 生成反向 SQL 了。

---

自增ID

查看表的自增id

1、从 information_schema.tables 中查看表的自增ID

SELECT auto_increment 
FROM information_schema.tables 
where table_schema="dbName" and table_name="tableName";

2、show table status where name='tableName' 结果中 Auto_increment 列就是表的自增ID

3、show create table xx 查看建表语句，结果中的 AUTO_INCREMENT=xx 就是表的自增ID

修改表的自增ID

alter table table_name auto_increment=73;

---