MySQL教程（13）基于Position或GTID实现主从复制

一、MySQL主从复制概述

主从复制是MySQL高可用与横向扩展的基础方案，其核心依赖于 MySQL 自身的 Binlog 机制。主节点的 Binlog 记录了数据库上所有的 DDL 与 DML 操作（SELECT、SHOW 等不产生数据变更的语句不会被记录，临时表的操作同样不在记录范围内），从库将主库 Binlog 复制到自己的Relay Log，再依据 Relay Log 执行相同的操作，从而实现数据同步。

1、主从复制主要应用场景

· 提升查询性能：所有数据变更在主节点完成，查询请求通过从节点完成，尤其是一些 OLAP 报表类任务也非常适合通过从节点完成

· 提升数据安全：从节点可以作为主节点的备份，结合半同步复制或延迟复制保障数据安全，日常备份任务也可以在从节点上执行，降低主节点的性能压力

· 远程数据分发：跨区域数据同步，适合对数据实时性要求不高的场景，对实时性有要求的请求则继续走主节点或同区域节点

· 滚动升级：先升级从库并进行测试，再切换主从关系，实现不停机的在线升级

· 自动化高可用：当主节点发生故障时，通过数据库中间件或手动切换的方式快速恢复服务

2、主从复制常见方式

· 主从架构：常见的主从架构包括一主一从、一主多从、级联复制。需要注意的是在进行 Binlog 传输时会有IO和带宽消耗，如果是一主多从的架构，从节点的数量不宜过多

· 复制模式：主从复制在进行数据同步时分为异步复制、半同步复制和同步复制。异步复制是主库写完 Binlog 就立刻返回给客户端，不关心从库有没有真正收到日志并完成应用，性能最好但存在数据丢失风险；半同步复制是主库写完 Binlog 后，需要等待至少一个从库确认已经收到并写入 Relay Log 才返回给客户端；全同步复制是主库必须等所有从库都执行完事务才返回，数据一致性最强，但性能影响也最大

二、MySQL主从复制配置

在部署主从架构前先确保主从节点时间同步，并且数据库版本一致（从库版本可以高于主库版本，但是不能低于主库版本）

1、基于position方式实现主从同步

基于position是传统的主从配置方式，从库记录了当前同步到的 Binlog 偏移量，比如 mysql-bin.000023，pos 4521。这种方式简单易懂，但是当进行主从切换或者级联复制时，需要手动找到新主库对应的偏移量，操作繁琐

· 数据库配置部分

由于主从可能发生切换，所以建议在配置上保持一致，可以参考《MySQL教程（2）MySQL 生产环境配置文件模板及参数详解》，这里摘取部分配置进行说明

### Binlog ###
sync_master_info = 1                            # 每次事务提交后立即将 master.info 刷新到磁盘，防止主库宕机时丢失同步状态
log_bin = /data/mysql3310/log/binlog/mysql-bin  # Binlog存储路径及文件名前缀
binlog_format = row                             # 使用 row 格式记录 Binlog，记录每行数据的实际变更，比 statement 模式更安全可靠
binlog_rows_query_log_events = on               # 在 Binlog 中额外记录原始 SQL 语句
max_binlog_size = 1024M                         # 单个 Binlog 文件的最大大小，超过后自动切换到下一个文件
expire_logs_days = 10                           # Binlog 文件的自动清理天数，10 天前的文件会被自动删除
### Relay Log ###
relay-log = /data/mysql3310/log/relaylog/relay-bin  # Relay Log 文件的存储路径及文件名前缀
relay_log_recovery = 1                              # 从库崩溃重启后自动从最新的 Relay Log 恢复，避免使用可能损坏的旧文件
relay-log-purge = 1                                 # 自动删除已应用的 Relay Log
### Replication ###
master_info_repository = TABLE          # 将 master.info 持久化到系统表，避免宕机后状态不一致
relay_log_info_repository = TABLE       # 将 relay-log.info 持久化到系统表，原因同上
skip_slave_start = 1                    # 从库启动时不自动开启复制线程，需手动执行 START SLAVE，便于启动后先检查状态
log_slave_updates = 1                   # 从库将复制执行的事务也写入自己的 Binlog，级联复制或从库切主时必须开启
slave_parallel_type = LOGICAL_CLOCK     # 并行复制调度策略，基于主库提交时序判断事务能否并行，比 DATABASE 模式并行度更高
slave_parallel_workers = 8              # 并行复制的工作线程数，通常与主库核心数相当
slave_preserve_commit_order = 1         # 保证从库事务提交顺序与主库一致，开启并行复制时建议同时开启
slave_net_timeout = 10                  # 从库检测主库连接超时的时间（秒），超时后触发重连
#read_only = 1                           # 从库只读，建议从库打开
#super_read_only = 1                     # SUPER权限的用户也只读，建议从库打开
transaction_write_set_extraction = XXHASH64                     # 事务哈希算法，用于 WRITESET 并行复制和冲突检测
binlog_transaction_dependency_tracking = WRITESET_SESSION       # 同会话内事务保持顺序，跨会话无冲突事务可并行回放

· 在主库建立复制账号并授予权限

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.36.11' IDENTIFIED BY '123456789';

· 查看主库当前position

position实际对应的是binlog的File Size，从库会从该位置进行复制

mysql > show master status \G

· 从库指定主库position

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.145.85',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=0;
#MASTER_HOST：MASTER服务器的IP
#MASTER_USER：有slave权限的用户，就是GRANT所授权的用户
#MASTER_PASSWORD：从库用户的密码
#MASTER_LOG_FILE：在主库上执行show master status语句可以查看日志名
#MASTER_LOG_POS=333：这个位置决定了从库从哪个位置开始复制，实测写0也可以，从头复制

· 启动从库复制线程

启动后查看下状态，必须确保 Slave_IO_Running、Slave_SQL_Running的状态都为YES

mysql > start slave;  #启动从库
mysql > show slave status \G  #查看从库状态，确保IO_THREAD

2、基于GTID方式实现主从同步

GTID（全局事务标识）是从 MySQL 5.6 开始引入，InnoDB 引擎会对每个事务都使用一个全局唯一编号进记录，GTID拥有全局唯一性和幂等性，GTID按照递增方式发生变化，一个完整的GTID包含了UUID和GTID两部分，UUID即Server_uuid，用于区分事务来自于集群中哪个节点。从5.7版本开始，即便没有配置GTID也会存在匿名事务，只不过这个匿名事务不包含GTID信息，不能用于主从复制。MySQL在启动的时候通过读取 auto.cnf 获取UUID，如果auto.cnf文件丢失则会自动生成新的UUID，如下为GTID示例

# 同一个实例的事务GTID会进行合并，比如来自同一个实例的1-3号事务，会写为1-3。事务之间如果不连续，则用逗号分隔
2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3
2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-3,11,48-51

因为GTID的全局唯一性，从库只需要知道自己已经执行过哪些GTID，然后会自动执行该GTID之后的事务，不再需要指定Position。这样一来可以大幅简化主从复制部署与维护的复杂度。因为不同节点之间的GTID还有连续性和幂等性，这样在进行主从切换时也更方便，不再有每个节点position不同的情况，从库不需要关心偏移量就能自动找到新主库上的同步点。在实际使用中，GTID 模式已经是目前最优选择，尤其是配合 MHA、orchestrator 等高可用工具做自动故障切换时已经是必选项

需要注意启用GTID以后，不再支持create table...select...语句，因为该语句实际是先CREATE TABLE再INSERT会生成2个GTID，违背了GTID下一个事务只有一个GTID的特性。并且也不支持使用CREATE TEMPORARY TABLE临时表相关操作

· 在所有节点开启GTID

MySQL 5.7开始支持在线配置GTID，无需重启服务。但是为了确保MySQL能正确处理从匿名事务更改为GTID事务的过程，gtid_mode的值只能按照顺序逐一提升或下降，如可以从OFF_PERMISSIVE逐步升级为ON_PERMISSIVE和OFF，但是不能直接调整为ON，关于GTID在线开启见https://www.ywdba.cn/post-647.html

开启 GTID 后需要关注的相关表与变量

· mysql.gtid_executed：该表存储GTID信息，数据库实例启动时会读取该表信息并赋值给gtid_executed变量（在MySQL 5.6时期是使用binlog中的GTID_LOG_EVENT来持久化GTID信息的，所以必须配置 log_slave_updates=1 才能使用GTID的主从方式）

· gtid_executed：该变量用于实时查看当前数据库实例已经执行过的GTID集合。执行show master status或show slave status时返回的Executed_Gtid_Set的值就来自于该变量

· gtid_purged：该变量记录了已经被删除或丢弃的GTID事务（Binlog过期或人为操作删除都算）。在部署从库的时候通常需要使用set global gtid_purged命令来设置该变量，表示从库不需要再执行这些GTID包含的操作（因为这些事务已经在备份数据中了），否则会因为日志被删除了而导致同步失败

· 数据库配置部分

采用GTID复制后从库也会产生binlog日志，通过show master logs可以看到与主库是一致的。在从库执行show binlog event in 'master-bin.000001'命令可以查看当前所执行的事务ID位置，正常情况也与主库一致

### Binlog ###
sync_master_info = 1                            # 每次事务提交后立即将 master.info 刷新到磁盘，防止主库宕机时丢失同步状态
log_bin = /data/mysql3310/log/binlog/mysql-bin  # Binlog存储路径及文件名前缀
binlog_format = row                             # 使用 row 格式记录 Binlog，记录每行数据的实际变更，比 statement 模式更安全可靠
binlog_rows_query_log_events = on               # 在 Binlog 中额外记录原始 SQL 语句
max_binlog_size = 1024M                         # 单个 Binlog 文件的最大大小，超过后自动切换到下一个文件
expire_logs_days = 10                           # Binlog 文件的自动清理天数，10 天前的文件会被自动删除
### Relay Log ###
relay-log = /data/mysql3310/log/relaylog/relay-bin  # Relay Log 文件的存储路径及文件名前缀
relay_log_recovery = 1                              # 从库崩溃重启后自动从最新的 Relay Log 恢复，避免使用可能损坏的旧文件
relay-log-purge = 1                                 # 自动删除已应用的 Relay Log
### Replication ###
master_info_repository = TABLE          # 将 master.info 持久化到系统表，避免宕机后状态不一致
relay_log_info_repository = TABLE       # 将 relay-log.info 持久化到系统表，原因同上
skip_slave_start = 1                    # 从库启动时不自动开启复制线程，需手动执行 START SLAVE，便于启动后先检查状态
log_slave_updates = 1                   # 从库将复制执行的事务也写入自己Binlog，级联复制或从库切主时必须开启
slave_parallel_type = LOGICAL_CLOCK     # 并行复制调度策略，基于主库提交时序判断事务能否并行，比 DATABASE 模式并行度更高
slave_parallel_workers = 8              # 并行复制的工作线程数，通常与主库核心数相当
slave_preserve_commit_order = 1         # 保证从库事务提交顺序与主库一致，开启并行复制时建议同时开启
slave_net_timeout = 10                  # 从库检测主库连接超时的时间（秒），超时后触发重连
read_only = 1                           # 从库只读，建议打开
super_read_only = 1                     # SUPER权限的用户也只读
transaction_write_set_extraction = XXHASH64                     # 事务哈希算法，用于 WRITESET 并行复制和冲突检测
binlog_transaction_dependency_tracking = WRITESET_SESSION       # 同会话内事务保持顺序，跨会话无冲突事务可并行回放
### GTID ###
gtid_mode = on                      #  开启 GTID 模式，每个事务分配全局唯一 ID，简化主从切换和故障恢复
enforce_gtid_consistency = 1        # 强制所有事务满足 GTID 一致性约束，禁止在 GTID 模式下无法安全复制的语句
binlog_gtid_simple_recovery = 1     # 简化 GTID 恢复流程，只扫描最新和最旧的 Binlog 即可确定已执行的 GTID 集合，加快启动速度

· 主库进行授权操作

grant replication slave,replication client on *.* to 'repl'@'192.168.1.110' identified by '123456'

· 从库恢复主库备份

备份数据恢复后需要在从库指定gtid_purged变量信息，这个信息根据备份方式不同导致查询的方式不同。如果是mysqldump逻辑备份，在备份文件前50行会有gtid_purged变量，xtrabackup物理备份则会写入xtrabackup的备份文件中

#通过备份还原数据后通过该命令可以看到从节点和主节点GTID信息是一致的 
show master status \G  
#防止从库读取到错误的gtid_executed变量，执行该命令可以重置变量
reset master;        
#指定从库需要跳过的GTID信息，也就是让从库从该GTID之后开始进行复制
set global gtid_purged='xxxxxxxxxx:1-21';

· 从库指定同步位点信息

由于采用了GTID，从库在指定position信息时只用声明从哪个GTID后开始同步即可

change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1;
start slave;

· 验证同步状态

从库执行show slave status命令查看事务ID是否与主库一致，Retrieved_Gtid_Set表示从库已经接受到的GTID信息；Executed_Gtid_Set表示从库已经应用了的GTID信息

2.1 GTID常见问题处理

· 跳过指定事务

开启GTID后如果需要跳过一个事务不能再使用sql_slave_skip_counter参数，而是要获得从库执行的最后一个GTID操作。也就是show slave status 中的Retrieved_Gtid_Set信息

构建空事务

stop slave;
set gtid_next='xxxxxxxxxxxxxxxcb140c:10903292'
begin;
commit;
set gtid_next='xxxxxxxxxx3:4'
begin;
commit;
set gtid_next='automatic';
start slave;

· 复制线程状态为yes但是不同步

数据目录下auto.cnf存放了数据库UUID，每个节点的ID不能一样，如果一样会出现主从状态是2个yes，但是数据不同步的情况

show global variables like 'server_uuid' ;

· 报错：Master_has_purged_require_gtids

通常是主库删除了还未同步完成的Binlog导致

#在从库上手动指定二进制日志文件和位置
mysql > stop slave;
mysql > change master to master_host='192.168.1.100',master_user='repl',master_password='123456',master_log_file='master-bin.000005',master_log_pos=526,master_auto_position=0;
mysql > start slave;

· 报错：Slave has more GTIDs than the master has,using the master's SERVER_UUID

该问题代表从库获取到的GTID超过了主库，比如主库在未指定binlog文件名的同时修改了系统主机名导致binlog全部被修改，从库就会判断失败；或者主库未配置双1参数时断电，导致从库提前获取到了还未执行的GTID，解决方法如下

#全在从库执行
stop slave;
reset slave;
reset master;  #从库的binlog已经无效了，所以要执行这个命令清空binlog
change master to master_host='192.168.1.100',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1;三、MySQL主从常见维护方法

三、MySQL主从常见维护方法

· 查看主节点状态

#主要用于查看主库position信息
show master status \G

· 查看从节点状态

show slave status \G   #主要用于查看从库状态

从节点需要关注的地方比较多，主要有以下重点指标

· 从库管理常用命令

需要注意的是无论使用何种复制方式，如果需要重启服务建议先stop slave再重启MySQL服务，避免复制出错

STOP SLAVE IO_THREAD;	         #停止从库IO进程
STOP SLAVE SQL_THREAD;	 #停止从库SQL进程
STOP SLAVE;	         #停止IO和SQL进程
START SLAVE IO_THREAD;	 #启动IO进程
START SLAVE SQL_THREAD;	 #启动SQL进程
START SLAVE;	         #启动IO和SQL进程
RESET SLAVE;	         #重置从库状态，该操作会删除所有中继日志并启动一个新的中继日志，通常重做从库的时候才会执行此命令
SHOW SLAVE STATUS;	         #查看MySQL同步状态

数据库mysql