ProxySQL + Mysql MGR实现数据库高可用

较真的菜鸟

已于 2023-03-19 22:11:23 修改

阅读量731

点赞数

分类专栏：数据库文章标签：数据库 mysql java

于 2022-09-04 18:58:42 首次发布

本文链接：https://blog.csdn.net/weixin_43790613/article/details/126090166

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

文章目录

Mysql MGR介绍
MGR的安装
故障转移
- 选举策略:
安装过程中的异常
ProxySQL

Mysql MGR介绍

MGR集群介绍

Mysql5.7.17提出的MGR(Mysql Group Replication)既可以很好的保证数据一致性又可以自动切换，具备故障监测功能、支持多节点写入，MGR是一项普遍被看好的技术。MGR是Mysql自带的一个插件，可以灵活部署。Mysql MGR集群是多个Mysql Server节点共同组成的分布式集群，每个server都有完整的副本，它是基于ROW格式的二进制日志文件和GTID特性。

MGR的优点

强一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证。
高容错性，只要不是大多数节点坏掉就可以正常工作，有自动监测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理。
高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除，其他节点自动更新组信息，自动维护新的组信息。
高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。

MGR的使用约束

仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突监测
必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主和writeset；主从状态信息存于表中，（- -master-info-repository=TABLE、- -replay-log-info-repository=TABLE）,- -log-slave-updates打开
MGR不支持大事务，事务大小最好不要超过143MB，即一次事务总的修改数据不宜过大，当事务过大，无法在5秒内通过网络在组成员之间复制信息，则可能会怀疑成员失败了，然后将其驱逐出局、
目前一个MGR集群最多支持9个节点
不支持外键于save point特性，无法做全局间的约束检测与部分事务回滚
二进制文件不支持Binlog Event Checksum

MGR的安装

服务器设置

此为前提，因为后面主从同步使用的通信用户是通过域名访问的，如果配置集群loose-group_replication_group_seeds直接用ip，启动插件group_replication会报Access denied for user 'rpl_user'@'localhost'

修改hosts，三台机器都是同样的命令

sudo cat > /etc/hosts <<-'EOF'
192.168.1.128 n3
192.168.1.163 n1
192.168.1.123 n2
EOF

修改当前主机的名字，不同的机器修改成对应的n几

hostnamectl set-hostname n1

my.cnf的配置

主库在my.cnf中添加配置

# 在my.cnf中追加MGR配置
sudo cat >> /etc/my.cnf  <<- 'EOF'
# 设置mysql插件所在目录，因为MGR基于插件，所以必须设置插件路径
plugin_dir=/usr/local/mysql/lib/plugin
# 服务器编号，集群中唯一
server_id=1
# 开启binlog的GTID模式
gtid_mode=ON
# 开启后mysql只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行
enforce_gtid_consistency=ON
# 关闭binlog校验
binlog_checksum=NONE

# 定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为XXHASH64
transaction_write_set_extraction=XXHASH64
# 确定组复制时是否应该应用SSL，通常设置为“开”，默认为“关”
loose-group_replication_recovery_use_ssl=ON
# 该服务器的实例所在复制组的名称，必须是有效的UUID，所有节点必须相同
loose-group_replication_group_name="8d814d56-5b91-94d7-ce5c-66a5d8243293";
# 确定服务器是否应该在服务器启动期间启动组复制
loose-group_replicaiton_start_on_boot_OFF

# 为复制组中其他成员提供的网络地址，指定为"主机:端口"MGR需要开启新端口24901作为同步数据,n1为域名
loose-group_replication_local_address="n1:24901";
# 用于建立新成员到组的连接组成员列表
# 这个列表指定为有逗号分割的ip和端口组成
loose-group_replication_group_seeds="n1:24901,n2:24901,n3:24901"
# 配置此服务器为引导组,这个选项必须只在一个节点上设置
# 并且仅当第一次启动组或者重新启动整个组时，成功引导组启动后，将此选项设置为关闭
loose-group_replication_bootstrap_group=OFF
EOF

# 重启服务
service mysql restart

从库中的修改

sudo cat >> /etc/my.cnf  <<- 'EOF'
plugin_dir=/usr/local/mysql/lib/plugin
# 服务器编号，集群中唯一
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

# 此参数决定primary节点到secondary的请求是否是基于RSA秘钥对的密码交换所需的公钥
loose-group_replication_recovery_get_public_key=ON

loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="8d814d56-5b91-94d7-ce5c-66a5d8243293";
loose-group_replicaiton_start_on_boot=OFF

# 为复制组中其他成员提供的网络地址，指定为"主机:端口"MGR需要开启新端口24901作为同步数据
loose-group_replication_local_address="本机ip:24901";
loose-group_replication_group_seeds="ip:port,ip:port,..."
loose-group_replication_bootstrap_group=OFF
EOF

# 重启服务
service mysql restart

设置主从同步

主从都要设置

# 创建rpl_user账号，此账号用于实现主从数据同步
CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
# 赋予主从同步权限
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

CHANGE MASTER TO MASTER_USER="rpl_user",MASTER_PASSWORD="123456" FOR channel 'group_replication_recovery';

# 修改生效
FLUSH PRIVILEGES;
# 删除已产品的binlog，因为刚才binlog包含创建用户这种高权限操作，用于主从同步的账号没有权限执行的
reset master;

安装MGR

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

安装完成后可以通过SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='group_replication' \G; 查看安装结果， PLUGIN_STATUS: ACTIVE为这个则安装成功

进行组网数据同步

组网同步需要主从运行不同的脚本
我们在my.cnf配置文件中把group_replication_bootstrap_group参数设置成OFF，在primary服务器启动时不会直接启动复制组，通过下面的命令动态的开启复制组使我们的集群更安全
主库执行，每次重启主库都要执行此步骤

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;

多主模式

在单主模下,组制具有自动选主功能，每次只有一个server成员接受更新，单写模式group内有一合节点可读可写，其节点只可以读，对group的部署，需要先起primary节点，然后再起其他的节点，并把这节点加进group，其他的节点就会自动同步primary节点上面的变化，然后将自己没置为读式，当primary节点意外宕机或者下线，在满是大多数节点存活的情况下，group内部发起进举，选出下一可用的读节点，提升为primary节点。primary选举根据group内剩下存活节点的UUID按字典序升序来选择，然后选择排在最前的节点作为新的primary节点。在多主模式下，所有的 server 成员都可以同时接受更新。group内的所有机器都是primary节点，同时可以进行读与写操作，并且数据是最终一致的

运行时切换为多主模式

第一步，n0~n2节点停止组复制，并开启多主模式

stop group_replication;
#是否启用单主模式，默认ON，OFF代表多主
set global group_replication_single_primary_mode=OFF;
#是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝
#不支持外键的级联操作
#不支持“串行化Serializable”
set global group_replication_enforce_update_everywhere_checks=ON;

第二步，n0节点引导启用组复制

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

第三步，n1、n2启用组复制，加入MGR集群

START GROUP_REPLICATION;

运行时切回单主模式

第一步，n0~n2节点停止组复制，切回单主模式

STOP GROUP_REPLICATION;
#是否启用单主模式，默认ON，OFF代表多主
set global group_replication_single_primary_mode=ON;
set global group_replication_enforce_update_everywhere_checks=OFF;

故障转移

关闭主数据，模拟故障
主库出现故障后的选举策略

选举策略:

优先低版本节点
如果版本一样，优先权重大的节点
如果版本与权重一样，按照server uuid的字母顺序选主
如果旧的主库重新恢复后，旧的主库会变成新的主库的从库

安装过程中的异常

Plugin group_replication reported: 'This member has more executed transactions than those present in the group，提示此成员的已执行事务多于组中存在的事务，在此成员上执行reset master即可
Access denied for user ‘rpl_user’@‘localhost’，

ProxySQL

proxySQL只是作为一个中间件将对应的操作转发到主库或者从库，并且轮询各个节点的状态，如果主库挂掉，会查询新的主库，并把对应的操作转发到新的主库

ProxySQL组成
　　　　服务脚本：/etc/init.d/proxysql
　　　　配置文件：/etc/proxysql.cnf
　　　　主程序：/usr/bin/proxysql
　　　　基于SQLITE的数据库文件：/var/lib/proxysql/
　　　　启动ProxySQL：service proxysql start启动后会监听两个默认端口
　　　　　　6032：ProxySQL的管理端口
　　　　　　6033：ProxySQL对外提供服务的端口