14：主从复制-配置主从延时、半同步复制、过滤复制、延时从库、GTID复制、多源复制

最新推荐文章于 2024-06-29 10:01:12 发布

xiaoleinb

最新推荐文章于 2024-06-29 10:01:12 发布

阅读量298

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/xiaoleinb/article/details/111997376

版权

mysql 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

1.主从延时
1.1 什么是主从延时?
主库做了操作,从库好久没跟上.

1.2 怎么监控?
通过日志的时间戳间隔:
Seconds_Behind_Master: 0

通过日志量判断?
非GTID :
(主库show master status–> Position) - (从库 show slave status—> Exec_Master_Log_Pos) = 日志量差异

GTID:
可以用GTID号码判定.

1.3 原因?

1.3.0 外部 :
网络慢 .
主从硬件配置差异大.
参数.
版本(只支持从低到高.不支持从高到低).

1.3.1 主库 :
dump 是串行工作的.如果主库并发事务量高,或者大事务时.传输时就会有较高延时.
解决方案: 5.6+版本,加入了Group Commit(GC)技术.两个指标时间延时+个数控制进行组提交.但是依然怕大事务.

binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000

1.3.2 从库 :
SQL默认是串行工作的. 主库的并发事务量大或者大事务.都会导致 SQL线程回放慢.
解决方案:
5.6版本: 加入了SQL线程并发回放机制. 以database级别进行并发回放.
意思是,只要主库中的事务是来自于不同库的操作,可以并发回放.

5.7+版本: 加入了Logical_clock模式,使得在主库能够group commit的事务(last_committed=8),并且根据sequence_number=9在从库并发回放
slave_parallel_type = DATABASE / logical_clock
slave_parallel_workers = 0
8.0+ writesets 写集合方式. MGR.

综上所述:
1. 历史遗留的延时问题,在版本升级过程中基本解决了.
2. 所以主从延时,我们面临的问题就是优化业务. 所以减少大事务,锁问题,性能较差SQL才是优化主从延时的重点.

主从复制分类
1.异步复制（传统主从复制）

1)从库接IO线程收到主库的binlog日志后马上回复ACK
2)如果此时从库宕机，存在从库TCP/IP缓存中的二进制日志会丢失，无法保证主从日志的一致性问题

2.半同步复制
1）mysql5.5版本之前
a.主库待从库TCP/IP缓存内的binlog存入磁盘的relay-log落地才commit成功
b.半同步复制可以保证主从数据的一致性，有了一定安全性的保证。
c.牺牲业务的性能来保证数据的安全，船型传输binlog速度很慢
2）mysql5.6版本及以后的版本
MySQL5.6之后出现了组提交（group commit）的概念，通过组提交，使每次传输的binlog日志的效率提高，从而使半同步在业务性能上有所提升。组提交依赖于GTID（全局事务ID）特性来实现。

3）.同步复制
主库直接到从库SQL进程执行relay-log成功，才能commit成功。这种做法阻塞主库业务的进行

2.主从数据最终一致性保证—(增强)半同步复制
commit原理：
commit分为两个阶段：（称之为2pc）
prepare、commit

当执行commit时首先会在redo prepare中执行一下动作：
当然以下两个动作是由一个参数来控制的：innnodb_flush_log_at_trx_commit=1,此参数的意思是只要有提交，就将所有			  											状态都执行一下动作：
flush_log（刷新到文件系统缓存）
sync_log（同步到磁盘的ib_logfile）中

随后进入下一个环节：binlog prepare
然后到达binlog commit,在这里会执行flush以及sync操作。

最终到达redo commit环节：
将所有带有prepare标记的全部修改为commit状态

此时在到达redo commit环节时如果发生宕机后，系统下次再次开启的时候会做以下处理：
带有commit标签的，系统会将其加载到内存中进行redo重做
带有prepare状态的，系统在将其拿到内存中重做时发现时prepare状态，这时就会在binlog中找到对应的事务，binlog中对应的事务必然是commit状态的，
所以最终会进行redo前滚操作。
如果是没有commit标签，也不带prepare标签系统会怎么做呢？
首先将其加载到内存中，然后进行redo，随后在undo回滚掉。

2.1 半同步复制-after_commit 和 after_sync

5.5开始支持半同步复制,但是没有GC机制,性能极差,机会没人用
5.6 版本时 ,加入了GC机制,半同步复制开始被接收.使用的是after_commit机制,但是是在redo commit之后进行等待ACK确认.
这里会有一个痛点,如果主库redo commit阶段宕机宕机了,从库又获取到了binlog,会出现从库比主库数据"多"的问题.导致数据不一致.
5.7版本+以后,加入了after_sync机制,在binlog commit(binlog sync disk)阶段,等待从库ACK,不管谁宕机,都能保证最终一致性.

另外:
不管哪种方式,还会出现,如果ACK超时,会被切换为异步复制的模式.还是有数据不一致的风险.
如果公司能容忍,可以使用这种架构,建议使用增强半同步+GTID模式.
如果不能容忍,可以使用MGR PXC .

简化总结
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；

2.2 增强半同步复制配置
2.2.1 加载插件

INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

2.2.2 查看是否加载成功:
show plugins;
启动:

SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

2.2.3 重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

2.2.4 查看是否在运行

show status like ‘Rpl_semi_sync_master_status’;
show status like ‘Rpl_semi_sync_slave_status’;

2.2.5 其他的优化参数：

show variables like ‘%semi%’;

rpl_semi_sync_master_enabled =ON
rpl_semi_sync_master_timeout =1000
rpl_semi_sync_master_trace_level =32
rpl_semi_sync_master_wait_for_slave_count =1
rpl_semi_sync_master_wait_no_slave =ON
rpl_semi_sync_master_wait_point =AFTER_SYNC
rpl_semi_sync_slave_enabled =ON
rpl_semi_sync_slave_trace_level =32

mysql> set global binlog_group_commit_sync_delay =1;
mysql> set global binlog_group_commit_sync_no_delay_count =1000;

3.主从复制演变–过滤复制

3.1 什么是过滤复制?
选择性复制.

3.2 应用场景
业务的分离.
部分数据同步.

3.3 如何实现
主库 : 是否记录binlog来控制
binlog_do_db : 白名单
binlog_ignore_db : 黑名单
白名单:只记录白名单中列出的库的二进制日志
黑名单：不记录黑名单列出的库的二进制日志

从库 : SQL线程是否回放来控制
replicate_do_db=world
replicate_ignore_db=test

replicate_do_table=world.city
replicate_ignore_table:test.t100w

replicate_wild_do_table=oldboy.t*
replicate_wild_ignore_table=oldguo.t*
白名单：只执行白名单中列出的库或者表的中继日志
黑名单：不执行黑名单中列出的库或者表的中继日志

3.4 配置演练:
mysql> stop slave sql_thread;
mysql> change replication filter replicate_do_db = (oldguo, test);
mysql> start slave sql_thread;

4.延时从库
4.1介绍
是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时.

4.2 为什么要有延时从
什么是数据库损坏？
物理损坏
主从复制非常擅长解决物理损坏.

逻辑损坏
普通主从复制没办法解决逻辑损坏

4.3 配置延时从库
SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间。

stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;
start slave;

4.4 延时从库应用 *****

4.4.1 故障恢复思路
a. 发现问题
b. 停掉从库线程
c. 控制SQL回放日志的截止位置点.
d. 找回数据,快速恢复业务

4.4.2 故障模拟及恢复演练

stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;
start slave;

create database relaydb ;
use relaydb;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
insert into t1 values(11),(12),(13);
commit;
insert into t1 values(111),(112),(113);
commit;

drop database relaydb;

处理过程
show relaylog events in ‘db02-relay-bin.000002’
| db02-relay-bin.000002 | 1732 | Xid | 51 | 3385 | COMMIT /* xid=2212 / |
| db02-relay-bin.000002 | 1763 | Gtid | 51 | 3462 | SET @@SESSION.GTID_NEXT= ‘00bf718b-491c-11eb-81a2-000c2905f029:24’ |
| db02-relay-bin.000002 | 1840 | Query | 51 | 3575 | drop database relaydb / xid=2214 */

stop slave sql_thread;
CHANGE MASTER TO MASTER_DELAY = 0;
START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = ‘db02-relay-bin.000002’, RELAY_LOG_POS = 1763 ;

如果开启GTID，可以按照GTID方式UNTIL
START SLAVE UNTIL SQL_BEFORE_GTIDS = “188be1ed-c84c-11ea-98e7-000c29ea9d83:4”;

5.GTID复制

5.1 GTID引入
5.2 GTID介绍
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID =server_uuid : transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性

5.3 GTID核心参数
重要参数：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

gtid-mode=on --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志

5.4 GTID复制配置过程：

5.4.1 清理环境
pkill mysqld
\rm -rf /data/3306/data/*
\rm -rf /data/3306/binlog/*
\mv /etc/my.cnf /tmp
mkdir -p /data/3306/data /data/3306/binlog/
chown -R mysql.mysql /data/*

5.4.2 准备配置文件
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\d]>
EOF

slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\d]>
EOF

slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\d]>
EOF

5.4.3 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

5.4.4 启动数据库
/etc/init.d/mysqld start

5.4.5 构建主从：
master:51
slave:52,53

51:

create user repl@‘10.0.0.%’ identified with mysql_native_password by ‘123’;
grant replication slave on . to repl@‘10.0.0.%’ ;

52\53:

change master to
master_host=‘10.0.0.51’,
master_user=‘repl’,
master_password=‘123’ ,
MASTER_AUTO_POSITION=1;
start slave;

注意：
如果是已经运行很久的数据库，需要构建从库，都是需要备份恢复主库数据后再开启主从的。
mysqldump来讲，不要加–set-gtid-purged=OFF功能。

6. 5.7+版本多源复制(MSR Multi Source Replication): OLAP (在线分析处理)

6.1 架构

6.2 主机角色

主机角色	地址	端口
Master1	10.0.0.51	3306
Master2	10.0.0.52	3306
Slave	10.0.0.53	3306

6.3 配置过程

a. GTID环境准备

(1) 清理环境
pkill mysqld 
rm -rf /data/3306/*
\mv /etc/my.cnf /tmp 

(2) 创建需要的目录 
mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data

(3) 准备配置文件 
# db01 
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\\d]>
socket=/tmp/mysql.sock
EOF

# db02 
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\\d]>
socket=/tmp/mysql.sock
EOF

# db03 
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\\d]>
socket=/tmp/mysql.sock
EOF

(4) 初始化数据 
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql  --datadir=/data/3306/data 

(5) 启动数据库
/etc/init.d/mysqld start 

(6) 构建主从  
# 1. 创建复制用户(主节点)
set sql_log_bin=0;
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.*  to repl@'10.0.0.%' ;
set sql_log_bin=1;

b. 配置多源复制

CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';                

CHANGE MASTER TO MASTER_HOST='10.0.0.52',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';           

start slave for CHANNEL  'Master_1';
start slave for CHANNEL  'Master_2';

c. 多源复制监控

db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G


select * from performance_schema.replication_connection_configuration\G
SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='master_1'\G
select * from performance_schema.replication_applier_status_by_worker;

d.多源复制配置过滤

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "master_1";
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "master_2";