MySQL高可用之ProxySQL + MGR 实现读写分离实战

# 部署MGR ## 1、MGR 前置介绍 阿里云[RDS](https://so.csdn.net/so/search?q=RDS&spm=1001.2101.3001.7020)集群方案用的就是`MGR`模式！  ### 1.1、什么是 MGR - MGR（MySQL Group Replication）是MySQL 5.7.17版本诞生的，是MySQL自带的一个插件，可以灵活部署。 - 保证数据一致性又可以自动切换，具备故障检测功能、支持多节点写入。 - 集群是多个MySQL Server节点共同组成的分布式集群，每个Server都有完整的副本，它是基于ROW格式的二进制日志文件和GTID特性。 ### 1.2、MGR 优点 - 强一致性：基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证。 - 高容错性：只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制。 - 高扩展性：节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息。 - 灵活性：有单主模式和多主模式。单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。工作中优先使用单主模式！ ### 1.3、MGR 缺点 - 仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测。 - 必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set；主从状态信息存于表中（–master-info-repository=TABLE 、–relay-log-inforepository=TABLE），–log-slave-updates打开。 - MGR不支持大事务，事务大小最好不超过143MB，当事务过大，无法在5秒的时间内通过网络在组成员之间复制消息，则可能会怀疑成员失败了，然后将其驱逐出局。 - 目前一个MGR集群最多支持9个节点。 - 不支持外键于save point特性，无法做全局间的约束检测与部分事务回滚。 - 二进制日志不支持Binlog Event Checksum。 ### 1.4、MGR 适用场景 - 金融交易、重要数据存储、对主从一致性要求高的场景。 - 核心数据总量未过亿。 - 读多写少，如：互联网电商。 ## 2、MySQL MGR 搭建流程 ### 2.1、环境准备 本次集群搭建，我使用3台阿里云[ECS](https://so.csdn.net/so/search?q=ECS&spm=1001.2101.3001.7020)服务器（`CentOS 7.9，2核2G，20G硬盘`），每台服务器都分配公网IP，开放安全组：`22（SSH）、3306（MySQL）、24901（MGR）`。我的[服务器配置](https://so.csdn.net/so/search?q=%E6%9C%8D%E5%8A%A1%E5%99%A8%E9%85%8D%E7%BD%AE&spm=1001.2101.3001.7020)如下： ```csharp Master服务器（hostname:n0)：172.21.180.98 Slave服务器1（hostname:n1)：172.21.180.99 Slave服务器2（hostname:n2)：172.21.180.100 ```  ### 2.2、搭建流程 #### 2.2.1、配置系统环境 将Hosts文件写入n0/n1/n2节点与内网IP对应关系，后面配置采用域名访问： ```csharp # 3台服务器都执行 sudo cat > /etc/hosts <<-'EOF' 172.21.180.98 n0 172.21.180.99 n1 172.21.180.100 n2 EOF ``` 分别为三台服务器依次设置主机名称，三台服务器执行命令： ```csharp # 第1台服务器 hostnamectl set-hostname n0 # 第2台服务器 hostnamectl set-hostname n1 # 第3台服务器 hostnamectl set-hostname n2 ``` #### 2.2.2、安装 MySQL 下载 MySQL 官方YUM仓库源（这个并不是安装MySQL）： ```csharp # 3台服务器都执行 cd /home/ wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm yum localinstall -y mysql80-community-release-el7-5.noarch.rpm ``` 修改仓库配置，将下图中gpgcheck置为0： ```csharp vi /etc/yum.repos.d/mysql-community.repo ```  安装 MySQL 8.0.26： ```csharp # 3台服务器都执行 yum install -y mysql-community-server-8.0.26 ``` #### 2.2.3、配置启动 MySQL 主节点n0执行：直接CV就行，不要墨迹！ ```csharp # 修改 MySQL 配置 sudo cat >> /etc/my.cnf <<-'EOF' # 使用mysql_native_password密码策略，防止navicat连不上mysql8 default_authentication_plugin=mysql_native_password # 设置MySQL插件目录：MGR基于插件，必须设置插件路径 plugin_dir=/usr/lib64/mysql/plugin # 服务器编号，Master=1 server_id=1 # 开启binlog的GTID模式（MGR强制要求） gtid_mode=ON # 开启后MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行 enforce_gtid_consistency=ON # 关闭binlog校验（MGR强制要求） binlog_checksum=NONE # 定义用于事务期间哈希写入提取的算法，组复制模式下必须设置为 XXHASH64。 transaction_write_set_extraction=XXHASH64 # 确定组复制恢复时是否应该应用 SSL，通常设置为“开”，但默认设置为“关”。 loose-group_replication_recovery_use_ssl=ON # 服务器实例所在复制组名称，必须是有效的 UUID，所有节点必须相同。 loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee" # 确定服务器是否应该在服务器启动期间启动组复制。 loose-group_replication_start_on_boot=OFF # 为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。 # 很多人想当然认为端口应该是3306，起始不然，MGR需要开启新端口24901同步交换 # 所以这里不要写错，同时，前面我们配置了hosts文件做了主机名与IP的映射，这里直接写主机名即可 loose-group_replication_local_address="n0:24901" # 用于建立新成员到组的连接组成员列表。 # 这个列表指定为由分隔号间隔的组成员网络地址列表，类似 host1:port1、host2:port2 的格式。 # 同样采用n0~n2的主机名替代 loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901" # 配置此服务器为引导组，这个选项必须仅在一台服务器上设置， # 并且仅当第一次启动组或者重新启动整个组时。成功引导组启动后，将此选项设置为关闭。 loose-group_replication_bootstrap_group=OFF EOF ``` 从节点n1执行：直接CV就行，不要墨迹！ ```csharp sudo cat >> /etc/my.cnf <<-'EOF' default_authentication_plugin=mysql_native_password plugin_dir=/usr/lib64/mysql/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="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF # 设置本机地址n1:24901 loose-group_replication_local_address="n1:24901" loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901" loose-group_replication_bootstrap_group=OFF EOF ``` 从节点n2执行：直接CV就行，不要墨迹！ ```csharp sudo cat >> /etc/my.cnf <<-'EOF' default_authentication_plugin=mysql_native_password plugin_dir=/usr/lib64/mysql/plugin #设置唯一的服务器编号 server_id=3 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="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF #设置本机地址n2:24901 loose-group_replication_local_address="n2:24901" loose-group_replication_group_seeds="n0:24901,n1:24901,n2:24901" loose-group_replication_bootstrap_group=OFF EOF ``` 三台服务器，依次启动 MySQL ```csharp # 3台服务器都执行 systemctl start mysqld ``` #### 2.2.4、修改密码、设置主从同步 三台服务器，依次通过该命令，获取数据库连接密码： ```csharp # 获取数据库密码 grep 'temporary password' /var/log/mysqld.log ``` 三台服务器，连接到数据库控制台中： ```csharp # 连接数据库 mysql -uroot -p'密码' ``` 三台数据库控制台中，都执行下述命令（3台服务器都执行）： ```csharp # 修改root密码为asAS123456! ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'asAS123456!'; # 创建rpl_user账户，此账户用于实现主从数据同步 CREATE USER rpl_user@'%' IDENTIFIED BY 'asAS123456!'; # 赋予主从同步权限 GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; # 创建一个远程连接用户，这个用户用在Navcate、JDBC登录的时候，直接用root登录不好 create user 'remote'@'%' identified with mysql_native_password by 'asAS123456!'; # 为remote用户赋予所有数据库资源的访问权限，熟悉grant的小伙伴可以自己调整 grant all privileges on *.* to remote@'%'; # 让刚才的修改生效 FLUSH PRIVILEGES; # 删除已产生的Binlog # 一定要RESET MASTER，它会删除刚才已产生的Binlog # 因为刚才Binglog包含创建用户这种高权限操作，用于主从同步的rpl_user账户是没有权限执行的 # 这就会导致RelayLog重放无法正确执行，导致从属服务器卡死在"RECEVERING"状态 # 利用RESET MASTER删除这些无法执行的binlog，就没问题了 RESET MASTER; ``` #### 2.2.5、安装 MGR 插件 在三台服务器的MySQL控制台中，安装MGR插件，执行命令： ```csharp # 3台服务器都执行 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; ``` 在主服务器的MySQL控制台上，执行下述命令： ```csharp # 注意：只在主服务器上运行 # 我们在 primary.cnf 配置文件中把 group_replication_bootstrap_group 参数设置成 OFF # 在 primary 服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组使我们的集群更安全 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; ``` 在两个从服务器MySQL控制台上，执行下述命令： ```csharp # 指定主从账户与指定通信频道 CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery'; # 开启组网数据同步 START GROUP_REPLICATION; ``` 当两个从节点都运行完毕后，运行下面SQL结果进行验证： ```csharp SELECT * FROM performance_schema.replication_group_members; ``` 出现以下情况，每个节点都是`ONLINE`状态，说明集群搭建成功：  ## 3、MySQL MGR 故障转移 上面已经将MySQL MGR集群搭建完毕，并且节点都是`ONLINE`状态。  ### 3.1、主节点n0下线，重新选举 首先，在主服务器n0上执行停止mysql命令，如下： ```csharp systemctl stop mysqld; ``` 此时，在从节点n1查看集群状态发现，n1被选举为主节点：  这是由于MGR集群选举策略为： ```csharp ·优先低版本节点 ·版本一样，优先权重大的节点 ·版本与权重一样，按照 server uuid 的字母顺序选主 ``` 在n1从节点上，通过命令查看故障转移日志： ```csharp # 查看MySQL日志 tail -n 50 /var/log/mysqld.log ``` n1日志解析如下： ```csharp # n0:3306（主节点n0）从组中被移除掉 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: n0:3306' # 重新选举新的 Primary 主节点 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address n0:3306 left the group. Electing new Primary.' # n1:3306（从节点n1）被选举为主节点，执行之前未完成的事务处理 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address n1:3306 was elected. The new primary will execute all previous group transactions before allowing writes.' # 组成员目前只剩 n1:3306, n2:3306 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to n1:3306, n2:3306 on view 17172171443362674:4.' # 关闭 n1 节点的只读状态 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.' # 设置 super_read_only=OFF [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.' # 当前节点（n1）以主节点身份工作 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.' ``` 在n2从节点上，通过命令查看故障转移日志： ```csharp # 查看MySQL日志 tail -n 50 /var/log/mysqld.log ``` n2日志解析如下： ```csharp # n0:3306（主节点n0）从组中被移除掉 [Warning] [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: n0:3306' # 重新选举新的 Primary 主节点 [System] [MY-011500] [Repl] Plugin group_replication reported: 'Primary server with address n0:3306 left the group. Electing new Primary.' # n1:3306（从节点n1）被选举为主节点，执行之前未完成的事务处理 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address n1:3306 was elected. The new primary will execute all previous group transactions before allowing writes.' # 组成员目前只剩 n1:3306, n2:3306 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to n1:3306, n2:3306 on view 17172171443362674:4.' # 当前节点（n2）作为主节点（n1）的从成员身份工作 [System] [MY-011511] [Repl] Plugin group_replication reported: 'This server is working as secondary member with primary member address n1:3306.' ``` ### 3.2、新主节点n1下线，集群不可用 当在新晋升的主节点n1上执行停止MySQL操作： ```csharp systemctl stop mysqld; ``` 此时，在n2上通过命令查看发现，n1主节点尽管已经下线，但n2查看集群状态时还在显示，因为只有1个节点的情况下，少于`n/2+1`的规则，导致整体 MGR 集群失效，n2节点无法产生重新选举，同时n2的日志也不会有任何新内容产生： ```csharp SELECT * FROM performance_schema.replication_group_members; ```  ### 3.3、恢复 MGR 集群 恢复流程很简单，先将三台服务器的MySQL各自重启： ```csharp # 3台服务器都执行 systemctl restart mysqld; ``` 然后重复执行 `2.2.5` 章节流程即可恢复 MGR 集群。 ## 4、单主模式和多主模式 ### 4.1、模式介绍 #### 4.1.1、单主模式 在单主模式下, 组复制具有自动选主功能，每次只有一个 server成员可以作为主节点。 单主模式 group 内只有一台节点可写可读，其他节点只可以读。对于group的部署，需要先跑起primary主节点，然后再跑起其他的节点，并把这些节点加进group。其他的节点就会自动同步primary节点上面的变化，然后将自己设置为只读模式。 当primary主节点意外宕机或者下线，在满足大多数节点存活的情况下，group内部发起选举，选出下一个可用的读节点，提升为primary节点。  #### 4.1.2、多主模式 在多主模式下，所有的 MySQL 节点都可以同时接受读写操作。group内的所有节点都是primary主节点，同时可以进行读写操作，并且数据是最终一致的。  ### 4.2、模式切换 之前我们搭建的 MySQL MGR 集群就是单主模式（默认），那么如何切换为多主模式呢？按照如下操作进行。 #### 4.2.1、单主 ——> 多主 从 n0 ~ n2 停止组复制，开启多主模式（3个节点都执行）： ```csharp # 停止组复制 stop group_replication; # 是否启用单主模式，默认ON，OFF代表多主 set global group_replication_single_primary_mode=OFF; # 是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝 # 不支持外键的级联操作 # 不支持“串行化Serializable” set global group_replication_enforce_update_everywhere_checks=ON; ``` 在 n0 主节点启用组复制： ```csharp # 只在 n0 上执行 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; ``` 在 n1，n2 节点上启用组复制： ```csharp # 只在 n1, n2 上执行 START GROUP_REPLICATION; ``` 此时，可以看到三台MySQL都是主节点： ```csharp SELECT * FROM performance_schema.replication_group_members; ```  #### 4.2.2、多主 ——> 单主 从 n0 ~ n2 停止组复制，开启单主模式（3个节点都执行）： ```csharp # 停止组复制 stop group_replication; # 是否开启条件检查，因为多主的约束更为严格，不符合要求的直接拒绝 # 不支持外键的级联操作 # 不支持“串行化Serializable” set global group_replication_enforce_update_everywhere_checks=OFF; # 是否启用单主模式，默认ON，OFF代表多主 set global group_replication_single_primary_mode=ON; ``` 在 n0 主节点启用组复制： ```csharp # 只在 n0 上执行 SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; ``` 在 n1，n2 节点上启用组复制： ```csharp # 只在 n1, n2 上执行 START GROUP_REPLICATION; ``` 此时，可以看到三台MySQL变成了主从模式： ```csharp SELECT * FROM performance_schema.replication_group_members; ```  MGR部署参考：https://blog.csdn.net/weixin\_46594796/article/details/139321738 # 部署ProxySQL 实现MGR读写分离 ## 1、ProxySQL 介绍 ### 1.1、ProxySQL 如何工作 ```csharp ProxySQL 和 ShardingJDBC 类似竞品关系，国内使用 ShardingJDBC 更多，国外使用 ProxySQL 更多。 ProxySQL 主要能够提供：读写分离 + 故障转移 ShardingJDBC 主要能够提供：读写分离 + 分库分表 所以很多场景会考虑将 ProxySQL 和 ShardingJDBC + MySQL MGR 一起使用，通过 ProxySQL 提供读写分离 + 故障转移，通过 ShardingJDBC 提供分库分表（不需要再提供读写分离），非常优雅！ ``` 当我们搭建好了MGR集群，由于组复制的存在，保证每个节点的数据都是强一致的。不会出现主节点有数据，而从节点没有数据的情况。主节点可读可写，从节点只负责读操作，这是经典的MGR集群方案：  不过，试想这样一种情况，当主节点发生宕机，MGR集群虽然虽然可以自动进行故障转移，重新选举新的主节点，但是Java应用不能感知到数据库发生了宕机现象，依旧会把写操作发送给宕机的主节点，此时就会发生错误。  针对这种情况，`ProxySQL`就发挥出了作用，通过ProxySQL自动对MGR集群进行健康状态感知。  使用ProxySQL时，如果主服务器发生了宕机，会将写入请求转移到故障转移到新晋升的主节点上，并且提供了读写分离功能。  ### 1.2、ProxySQL 工作原理 ProxySQL对于MGR集群节点，都新增了一个数据库用户，例如：monitor。ProxySQL通过monitor用户定时发送select查询请求，判断当前数据库节点是否可以，如果出现3次不能执行select查询，就认为该节点产生故障，就需要从集群中移除。  不过此时有个问题，就是ProxySQL如何判断哪个节点是主，哪个节点是从呢？ProxySQL要求每个MGR集群中的节点都在各自服务器上创建视图，作用是收集当前节点运行状态，判断当前节点是主是从。  当ProxySQL知道了主从之后，此时Java应用进行SQL操作，就会被ProxySQL进行路由分发了！  ## 2、ProxySQL 安装 ```csharp 我这里使用一台阿里云ECS（2核2G），开放22、6032、6033端口号。 ``` 首先，下载ProxySQL安装包，进行安装操作： ```csharp # 下载 ProxySQL 安装包（这里是通过oss直接下载的，也可以通过官方下载，不过很慢） wget --no-check-certificate https://manongbiji.oss-cn-beijing.aliyuncs.com/ittailkshow/mgr/download/proxysql-2.2.0-1-centos7.x86_64.rpm # 安装 ProxySQL cd /home/ yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm ``` 启动 ProxySQL 服务，可以发现启动完毕： ```csharp # 启动 ProxySQL service proxysql start ```  安装MySQL YUM仓库源： ```csharp cd /home/ wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm yum localinstall -y mysql80-community-release-el7-5.noarch.rpm sudo yum makecache fast ``` 将 MySQL 8 下载地址调整为腾讯云镜像（否则下载速度KB维度）： ```csharp # 修改配置 vim /etc/yum.repos.d/mysql-community.repo ``` ```csharp # 将 mysql80-community 的 baseurl 内容替换如下 https://mirrors.cloud.tencent.com/mysql/yum/mysql-8.0-community-el7-x86_64/ # 将 mysql80-community 的 gpgcheck 内容替换为 0 ```  配置好镜像后，安装好MySQL YUM源，就可以下载MySQL客户端了： ```csharp yum install -y mysql-community-client ``` ## 3、ProxySQL + MGR 读写分离 ### 3.1、读写分离配置 通过上述操作，前置准备已完成，此时链接上ProxySQL： ```csharp mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' ```  接着，将MGR集群节点配置到ProxySQL中，使用的是MGR节点的内网ip： ```csharp # 我的MGR集群的三个节点内网IP分别是：172.21.180.98 | 172.21.180.99 | 172.21.180.100 insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.98',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.99',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.100',3306); # 启用上述配置 + 持久化保存 load mysql servers to runtime; save mysql servers to disk; ```  接下来这步需要在`MGR主节点`上进行，配置会通过主节点同步到其他从属节点： ```csharp # 使用 sys 数据库 use sys; # MySQL降低密码强度 set global validate_password.policy=0; set global validate_password.length=4; # 创建角色（monitor监听运行状态，proxysql是java应用连接账号） CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025"; CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025"; GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ; GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ; FLUSH PRIVILEGES; ``` 从这里使用Navicat进入sys数据库，连接到MGR主节点上，创建视图（一段一段执行，用Navicat是因为避免控制台出现ERROR）：  ```csharp # 创建函数 CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id); ``` ```csharp # 函数：判断是否是主分区 CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC BEGIN RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id()); END ``` ```csharp # 创建视图 CREATE VIEW gr_member_routing_candidate_status AS SELECT sys.gr_member_in_primary_partition() as viable_candidate, IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only', 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only, Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id(); ``` 此时，回到ProxySQL服务器控制台上，设置监控账号： ```csharp set mysql-monitor_username='monitor'; set mysql-monitor_password='monitor@1025'; insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1); ``` 设置读写组： ```csharp # 主负责写、从负责读，当MGR主库切换后，代理自动识别主从。 # ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40， # 注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。 insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100); ``` 启用规则： ```csharp load mysql servers to runtime; save mysql servers to disk; load mysql users to runtime; save mysql users to disk; load mysql variables to runtime; save mysql variables to disk; ``` 状态校验，编号为10是主节点，编号为30是从节点： ```csharp select hostgroup_id, hostname, port,status from runtime_mysql_servers; ```  进行读写分离配置： ```csharp # select for update走主节点，其他select走从节点 # 其他 insert update delete 走主节点 insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1); ``` ```csharp # 配置加载 + 持久化 load mysql query rules to runtime; save mysql query rules to disk; ``` ### 3.2、读写分离测试 这里注意，进行读写分离实战测试时，要连接`ProxySQL 6033`端口，6032是Admin，6033才是客户端（Java程序也连6033！）： ```csharp # 链接 ProxySQL 6033 mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033 # 创建一个test数据库，查询一条数据，写一条数据 use test; select * from test; insert into test values(20); select * from test for update; # 回到6032，查看路由日志 mysql -uadmin -padmin -h127.0.0.1 -P6032 select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 10; ``` 可以看到，读写分离成功！  ### 3.3、SpringBoot 整合 ```csharp 到这里说一下大家可能关注的点，就是ProxySQL + MySQL MGR`整合完毕后，如何通过SpringBoot进行整合，其实很简单。 ``` 只需要将SpringBoot的yml配置文件中配置连接到ProxySQL就可以了 ```csharp spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://123.56.41.203:6033/quick_chat?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&rewriteBatchedStatements=true username: proxysql password: proxysql@1025 ``` 如果运行期间出现以下错误，要么mysql驱动版本号不对，要么就是ProxySQL需要调整了： ```csharp proxysql Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size' ``` 如果要调整`ProxySQL`，请执行如下命令，即可解决上述问题： ```csharp # 连接到Proxy 6032 mysql -uadmin -padmin -h127.0.0.1 -P6032 # 解决 Unknown system variable 'query_cache_size' 问题 update global_variables set variable_value='8.0.4 (ProxySQL)' where variable_name='mysql-server_version'; load mysql variables to run;save mysql variables to disk; ``` ProxySQL搭建参考：https://xuzhibin.blog.csdn.net/article/details/139408078?spm=1001.2014.3001.5502 其他资料：https://www.cnblogs.com/kebibuluan/p/18328618