mysql replication
复制是指将主库的ddl,dml等操作通过binlog日志,传输到复制服务器,副本进行回放这些日志,从而使得从库和主库数据保存同步的工作模式
复制的主要应用场景
1 备份
2 高可用
3 读写分离
4 分布式架构
5 迁移升级
复制架构
传统 1主1从 1主多从 级联主从 双主
演变 (增强)半同步 过滤 延时 GTID MTS(多sql线程回放relay log)
新型 多源复制(5.7+支持)
MGR 组复制 5.7.17+支持 8.0增强
复制前提
2台以上的数据库实例 具备不同的server_id，server_uuid
主库开启binlog 创建复制用户
备份 主库数据恢复到从库
告知从库复制信息
启动线程
查看复制状态
1在主库创建用户并备份给从库
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave , replication client on *.* to repl@'10.4.7.%';
2找到备份点的位置号，在从库开启主从
mysql> change master to
-> master_host='10.4.7.11',
-> master_port=3306,
-> master_user='repl',
-> master_password='123',
-> master_log_file='mysql-bin.000007',
-> master_log_pos=156,
-> master_connect_retry=10;
mysql> start replica;
查看主从状态
show slave status \G
传统复制和GTID转换
原来的数据库可能是升级来的，可能没有开启gtid
1查看各个节点的gtid模式有没有开启
select @@enforce_gtid_consistency;
select @@gtid_mode;
2在线在所有节点测试是不是有不兼容一致性的事务存在（需要观察）
没有报错修改成on
set global enforce_gtid_consistency=warn;
3生成新的事物为匿名方式，
set global gtid_mode=off_permissive;
4修改生成新的事务使用gtid
set global gtid_mode=on_permissive;
5查看各个节点剩余匿名事务量
show status like 'ongoing_anonymous_transaction_count';
6修改gtid为on
set global gtid_mode=on;
7在从库重新连接主从
stop slave;
change master to master_auto_position=1;
start slave;
show slave status \G
主从复制原理
重要文件和线程
1主库
binlog
binlogdump线程
2从库
接受主库binlog 存放在reaylog
master.info文件和relaylog.info文件(可以放到表里面)
io线程
sql线程
1执行change master to 把主库的信息告诉从库，存放在master.info
在mysql库下
show variables like '%master%';
master_info_repository | TABLE
2start slavg 启动两个线程
3io线程读取master.info里面的信息连接主库
4主库连接层负责用户名和密码验证并且分配一个binlogdump线程
5dump线程拿到io线程提供的复制起点，去主库binlog去拿复制起点给io线程
6binlog一旦产生新的会会通知dump线程，dump传输给io线程存储到relaylog日志文件mastr.info也会随之更新
7sql线程会等待relaylog落地通知sql线程会去relaylog.info去看上次回放到哪里
show slave hosts;
主库可以查看从库注册的信息
ip地址是看不到的如果想看到
需要在配置文件加 report_host=自己的地址需要把自己的地址暴露给别的节点，在有一些高可用需要的
传统复制和gtid复制原理
1主库在记录二进制日志的时候会生成一个gtid信息
2搭建主从的时候不需要再写文件位置点了，需要加一个新的参数 master_auto_position=1
会先读取从库的binlog找有没有gtid信息，再去读relaylog的gtid信息如果没有找到，就会通知主库从1号复制
set-gtid-purged要打开备份的时候默认是auto是开启的pxb备份新版会把主库的binlog也备份走
异步和半同步的区别
rpl_semi_sync_master_wait_point= AFTER_SYNC;
主从dump线程把数据交给io线程是不会管io线程是否接收到
半同步（after_commit）是通过阻塞commit阶段实现，是通过redo,commit状态成功状态等待从库返回ack(有超时时间),
如果等待ack状态 主库宕机，极端情况从库数据，比主库多
（after_sync）
增强半同步 也是提供阻塞commit阶段实现，是通过binlog提交成功后等待从库返回ack(有超时时间) redo还未commit不管那个机器宕机都不会影响一致性
5.7加入
如果binlog一旦提交成功就会在binlog的末尾加一个xid，代表事务成功
超时就会退化成异步
会影响到事务的并发度
group commit组提交，可以一定程度降低
FLUSH 阶段
1) 持有Lock_log mutex [leader持有，follower等待]
2) 获取队列中的一组binlog(队列中的所有事务)
3) 将binlog buffer到I/O cache
4) 通知dump线程dump binlog
SYNC阶段
1) 释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]
2) 将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)
COMMIT阶段
1) 释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]
2) 遍历队列中的事务，逐一进行innodb commit
3) 释放Lock_commit mutex
4) 唤醒队列中等待的线程
设置参数
binlog_group_commit_sync_no_delay_count= 组内的数量
binlog_group_commit_sync_delay=多久提交一次
主从监控
主库线程查看
show processlist;
show slave hosts;
从库线程查看
show slave status\G
io线程故障
Last_IO_Errno: 0
Last_IO_Error
看从库的这俩指标
可以用远程用户连接一下
日志丢失
测试环境 关闭主从，清空连接信息，清空从库binlog，从新连接
生产环境：从新搭建主从
sql线程故障
如何把relaylog分离
show variables like '%relay%';
relay_log | k8s3-relay-bin 前缀名字
relay_log_basename | /data/mysql/data_3306/k8s3-relay-bin 路径
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE 文件类型
relay_log_purge | ON 回放完成的文件会自动清理如果relaylog损坏推荐重构
回放日志出现问题：检查平台，版本，参数 SQL_mode 调整一致
被修改的对象不存在（库，表，用户）
异步复制，双主架构，导致数据不一样
从库已经有了，主库在执行
处理方式
跳过报错
1先停止从库
stop slave
2 set gtid_next='gtid号'; #下个gtid啥都没做
3begin;commit;
4set gtid_next='AUTOMATIC';
5start slave;
校验数据
双主架构设计不好
只能按照一张表的数据去校验
规避方法
从库设置只读
2不使用双主结构 pxc MGR替代
3半同步 增强半同步等
4使用pt工具相关工具校验数据并同步
主从复制延时方法 show slave status\G
Seconds_Behind_Master: 0(作为参考)
和日志时间戳有关系从库对比从库用io线程获取日志存到relaylog中把获取时间的时间戳也会存储简称ts1
从库回放日志生成的时间戳(t2)用t2减去t1就计算出来了
导致显示不准确的情况
1时间被修改
2 如果长时间事务不提交 瞬间变大 瞬间归零的现象
3 MTS回放也会导致Seconds_Behind_Master
用那些方法更准确
gtid 通过binlog的gtid判断
pt-hearbeat
定位慢在哪里了
先看执行到那个了，下个卡了
大事务的话杀掉会很慢，或者重启
那些原因会导致延时
1主库写成功了，从库拿不到日志，主库并发好多操作往binlog写发的慢，从库收的也慢可以做出组提交(5.6版本需要手动配5.7自动底层实现)
2大事务也会导致传输的慢(组提交或者大事务拆分)
3io线程写relaylog慢硬盘问题MTS
4只能一条一条回放，因为只有一个sql线程(事务回放的顺序)多sql线程一直延续到5.7.22才解决
5.6增加基于不同的库并发不同的sql，同一个库还是串行回放
5.7新增在主库并发提交的是没有冲突的，从库基于每一组并发sql对一些一组内的打一些seq号，从库回放
参数
show variables like '%para%';
| innodb_parallel_read_threads | 4 |
| slave_parallel_type | DATABASE | 建议LOGICAL_CLOCK
| slave_parallel_workers | 0 并发回放sql线程个数5.7.22以后基于writeset
binlog_transaction_dependency_tracking = writeset
transaction_write_set_extraction =XXHASH64
会生成一个数据库对象，vector维特(向量)一个64位int 的变量存储已经提交事务的hash值提交的事务包含主键或者唯一键
做hash 判断当前提交的事务是否与已经提交的事务更新了同一行
当事务每次提交时，会计算修改的每个行记录的WriteSet值，然后查找哈希表中是否已经存在有同样的WriteSet
若无，WriteSet插入到哈希表，写入二进制日志的last_committed值保持不变，意味着上一个事务跟当前事务的last_committed相等，那么在slave就可以并行执行 若有，更新哈希表对应的writeset的value为sequence number，并且写入到二进制日志的last_committed值也要更新为sequnce_number。意味着，相同记录（冲突事务）回放，last_committed值必然不同，必须等待之前的一条记录回放完成后才能执行
延时从库
主从只能解决物理损坏
逻辑损坏延时从库可以延时2个小时
哪里延时
日志该拿就拿，sql线程延时判断时间戳信息
如何恢复
10点删库
延时从库只执行了8点的日志
11点我们发现的，把延时从库恢复到某一个时间点
此时我们恢复一个小时的binlog就和原来数据库数据一样
延时从库配置
mysql可以单独停止某一个线程
1 stop slave sql_thread;
2延时sql线程
change master to master_delay = 多少秒;
2启动sql线程
start slave
在show slave status;查看
SQL_Delay: 0
SQL_Remaining_Delay: NULL恢复思路
1及时监控
2 立即停止延时从库sql线程关闭 需要对业务挂维护页面
3停止所有线程
4在延时从恢复数据库数据
show relaylog events in '文件';
修改延时从的参数置为0
change master to master_delay = 0 ;
start slave until sql_before_gtids = '跳过那个日志'
把a业务切到延时从
过滤复制
基本原理主机点有abc三个库只想复制a库数据
有两种方案
1在主库方面 在binlogdump线程做a库的发送只能基于库级别
两个参数
show master status;
Binlog_Do_DB 白名单
Binlog_Ignore_DB黑名单
2在从库控制在回放的时候选择想要的日志回放
show slave status \G
库级别
Replicate_Do_DB:
Replicate_Ignore_DB:
表级别
Replicate_Do_Table:
Replicate_Ignore_Table:
模糊匹配
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
1配置文件配置并重启
2关闭sql线程
stop slave sql_thread;
change replication filter Replicate_Do_DB = (库名，库名)
start slave sql_thread;多源复制(MSR)
5.7以后才有
多源复制通常配合过滤复制才有
多主1从多套不同的数据库汇总到一台机器
两主1从
1创建复制用户每个主库都要创建
set sql_log_bin=0;
create user repl@'10.4.7.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.4.7.%';
2从库执行连接语句有几个主库就指定几个master
change master to master_host='10.4.7.13',
master_user='repl',
master_password='123',
master_auto_position=1 for channel 'master_2';
3启动线程几个主库就指定几个master
start slave for channel 'master_1';
4查看或者监控方法
show slave status for channel 'master_1'\G
多源复制过滤
change replication filter Replicate_Do_DB =(库名，库名) for channel 'master_2'
MGR组复制
pxc Percona XtraDB Cluster
MGC MariaDB Galera Cluster
5.7.17+以后出现的
理念用多台机器组成一个复制组
1主库发生新事务，事务执行期间将binlog刷新到binlog cache
2生成WriteSet(db_id,database_id,table_id,主键,主键值,对应的日志)
3具体在MYSQL_BIN_LOG::prepare之后但是在MYSQL_BIN_LOG::ordered_commit之前，即事务相关的BINLOG Event还在BINLOG CACHE没有写入到BINLOG FILE前
通过gcs_module将所有的日志事件 发送给各个节点
4通过certify验证(paxos协议)，通过投票机制，判断事务是否满足半数以上节点通过
5主库正常commit
6此时各个从库开始回放relaylog
1、事务操作生成的map event/query event/dml event等写入BINLOG CACHE中(内存) 2、将Write Set写入到Rpl_transaction_write_set_ctx中(内存)
在事务提交时，具体在MYSQL_BIN_LOG::prepare之后但是在MYSQL_BIN_LOG::ordered_commit之前，即事务相关的BINLOG Event还在BINLOG CACHE没有写入到BINLOG FILE前，将BINLOG CACHE中和Rpl_transaction_write_set_ctx中的数据进行处理并写入到transaction_msg中，由gcs_module负责发送transaction_msg到各个节点，等待各节点进行事务认证。
由于transaction_msg中包含BINLOG信息，并在事务认证期间发送给MGR各节点，因此无需等待主节点的BINLOG落盘后再发送给备用节点。
每个MGR群集中的节点上，都存在IO线程和SQL线程，IO线程会解析transaction_msg获取到BINLOG EVENT并保存到RELAY LOG中，再由SQL线程执行重放到辅助节点上。
从MGR复制原理上看，当主节点事务提交时，辅助节点上可能还未重放该事务对应的BINLOG，因此MGR仍属于异步复制。
MGR部署过程
1生成一个集群的uuid
cat /proc/sys/kernel/random/uuid
2配置文件参数
report_host=10.0.0.51 暴露自己的ip
report_port=3306
efault_authentication_plugin=mysql_native_password
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12" 集群的uuid统一的，将来会生成统一的GTID
loose-group_replication_start_on_boot=OFF 节点启动之后是否会自动拉起MGR第一次配置不自动拉起来，搭建成功修改
loose-group_replication_local_address= "10.0.0.51:33061" 当前节点的地址加上一个端口集群内部的一个端口本地使用
loose-group_replication_group_seeds="10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063" 整个集群的种子节点
loose-group_replication_bootstrap_group=OFF 配置是否自动引导组
loose
代表在没有MGR的时候不会影响数据库启动
3修改本地用户插件
"ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
4安装MGR插件
mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
5设置账号所有节点
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123';
CREATE USER repl@'localhost' IDENTIFIED BY '123';
CREATE USER repl@'127.0.0.1' IDENTIFIED BY '123';
GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
grant replication slave,replication client on *.* to repl@'localhost' ;
grant replication slave,replication client on *.* to repl@'127.0.0.1' ;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;6启动MGR单主模式主库执行只能有一个引导节点其他节点加入就好
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON; 引导节点
START GROUP_REPLICATION; 启动MGR
SET GLOBAL group_replication_bootstrap_group=OFF; 关掉引导节点
SELECT * FROM performance_schema.replication_group_members;7加入其他节点
reset master; 清理日志
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
START GROUP_REPLICATION;
其他节点加入是无感知的
reset master;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
START GROUP_REPLICATION;SECONDARY 只读模式
PRIMARY 主模式
如果要重置节点可以使用以下命令
STOP GROUP_REPLICATION;
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
start GROUP_REPLICATION;单主模式切换多主模式MGR支持在线修改
读节点执行
stop GROUP_REPLICATION;
set global group_replication_enforce_update_everywhere_checks=1;
set global group_replication_single_primary_mode=OFF;
检查
select @@group_replication_single_primary_mode,@@group_replication_enforce_update_everywhere_checks;
修改参数
set global read_only=0;
set global super_read_only=0;group_replication_single_primary_mode=0 #这个参数很好理解，就是关闭单master模式 group_replication_enforce_update_everywhere_checks=1 #这个参数设置多主模式下各个节点 严格一致性检查
停止所有组复制修改参数
组复制停止了，数据库也是可以继续业务的
所有节点
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
随便选取一个引导节点作为主节点启动
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
其他节点执行
START GROUP_REPLICATION;
查看组信息
SELECT * FROM performance_schema.replication_group_members;切换单主模式
所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;选取一个节点作为主节点
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
其他节点执行
START GROUP_REPLICATION;
一般是单主模式，
如果主挂掉，会选择一台从作为主
在线切换主
SELECT group_replication_set_as_primary('3795b6c9-5efc-11ec-9214-000c29b96a5c');
查询到的MEMBER_ID
在线函数修改全主模式
SELECT group_replication_switch_to_multi_primary_mode();
8.0支持在线修改单主模式
SELECT group_replication_switch_to_single_primary_mode();
8.0支持MGR日常运维
监控
SELECT * FROM performance_schema.replication_group_members;
如果主库宕机太久,需要把数据恢复到原来的主库
加新节点步骤
1备份恢复到新节点
2直接启动MGR 可以在线修改
MGR限制
仅支持innodb存储引擎
表中必须有主键或者null的唯一键
网络限制只支持ipv4网络
MGR忽略表锁和命名锁
多主模式下 对一个对象进行的并发ddl dml操作导致冲突部分成员节点无法检测到 最终可能导致数据不一致
不支持过滤复制
多主模式下 可能会导致死锁
MGR最多支持9个节点
不支持超大事务
MGR在8.0读写一致性的保障
group_replication_consistency EVENTUAL 默认值(最终一致性)，开启事务（T2），事务执行前不会等待先序事务（T1）的回放完成，也不
会影响后序事务等待该事务回放完成。BEFORE （本地强一致性）开启事务（T2），在开始前首先要等待先序事务（T1）的回放完成，确保此事务 将在最新的数据上执行。
AFTER（全局强一致性），开启事务（T1），只有等该事务回放完成。其他后序事务（T2）才开始执行，这样 所有后序事务都会读取包含其更改的数据库状态，而不管它们在哪个成员上执行。
BEFORE_AND_AFTER 开启事务（T2），需要等待前序事务的回放完成（T1）；同时后序事务（T3）等待该 事务的回放完成；
BEFORE_ON_PRIMARY_FAILOVER,在发生切换时，连到新主的事务会被阻塞，等待先序提交的事务回放完 成；这样确保在故障切换时客户端都能读取到主服务器上的最新数据，保证了一致性
针对不同应用场景应当如何选择MGR读写一致性的相关方式，官方提供了几个参数以及与其相对应的应用场
景：
AFTER
适用场景1：写少读多的场景进行读写分离，担心读取到过期事务，可选择AFTER。
适用场景2：只读为主的集群，有RW的事务需要保证提交的事务能被其他后序事务读到最新读数据，可选择
AFTER。
BEFORE
适用场景1：应用大量写入数据，偶尔进行读取一致性数据，应当选择BEFORE。
适用场景2：有特定事务需要读写一致性，以便对敏感数据操作时，始终读取最新的数据；应当选择BEFORE。
BEFORE_AND_AFTER
适用场景：有一个读为主的集群，有RW的事务既要保证读到最新的数据，又要保证这个事务提交后，被其他后
序事务读到；在这种情况下可选择BEFORE_AND_AFTER。腾讯云开发者
