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

    文章目录

    Mysql MGR介绍

    MGR集群介绍

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

    MGR的优点

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

    MGR的使用约束

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

    MGR的安装

    服务器设置

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

    1. 修改hosts，三台机器都是同样的命令
    sudo cat > /etc/hosts <<-'EOF'
192.168.1.128 n3
192.168.1.163 n1
192.168.1.123 n2
EOF

    • 1
    • 2
    • 3
    • 4
    • 5
    1. 修改当前主机的名字，不同的机器修改成对应的n几
    hostnamectl set-hostname n1

    • 1

    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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    从库中的修改

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    设置主从同步

    主从都要设置

    # 创建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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    安装MGR

    INSTALL PLUGIN group_replication SONAME 'group_replication.so';

    • 1

    安装完成后可以通过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;

    • 1
    • 2
    • 3

    从库执行

    开启组网数据同步

    START group_replication;

    • 1

    执行到此则集群安装完毕，执行可以查看集群状态

     SELECT * FROM performance_schema.replication_group_members;

    • 1

    多主模式

    在单主模下,组制具有自动选主功能，每次只有一个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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

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

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

    • 1
    • 2
    • 3

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

    START GROUP_REPLICATION;

    • 1

    运行时切回单主模式

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

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

    • 1
    • 2
    • 3
    • 4

    故障转移

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

    选举策略:

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

    安装过程中的异常

    1. Plugin group_replication reported: 'This member has more executed transactions than those present in the group，提示此成员的已执行事务多于组中存在的事务，在此成员上执行reset master即可
    2. 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对外提供服务的端口

  • 相关阅读:
    【杂记-浅谈Time To Live/TTL】
    mesos+marathon平台搭建
    [附源码]JAVA毕业设计九宫格日志网站（系统+LW）
    js（javascript）中关于查找与替换常用的实用方法
    算法竞赛进阶指南 0x21 树与图的遍历
    html、js、css3制作一款辉光管时钟
    redis安装与使用
    C++ 实现2048小游戏
    Java ~ Executor ~ LinkedBlockingQueue【总结】
    ubuntu18.04 OAK-D kalibr 双目+imu联合标定
  • 原文地址：https://blog.csdn.net/weixin_43790613/article/details/126090166