Mysql MGR 集群模式介绍

什么是MGR

MySQL是目前最流行的开源关系型数据库，MySQL 5.7.17提出的MGR（MySQL Group Replication）既可以很好的保证数据一致性又可以自动切换，具备故障检测功能、支持多节点写入，MGR是一项被普遍看好的技术。

MGR （MySQL Group Replication）是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 、--relay-log-inforepository=TABLE），--log-slave-updates打开

3、MGR不支持大事务，事务大小最好不超过143MB，当事务过大，无法在5秒的时间内通过网络在组成员之间复制消息，则可能会怀疑成员失败了，然后将其驱逐出局

4、目前一个MGR集群最多支持9个节点

5、不支持外键于save point特性，无法做全局间的约束检测与部分事务回滚

6、二进制日志不支持Binlog Event Checksum

MGR适用场景

1. 金融交易、重要数据存储、对主从一致性要求高的场景
2. 核心数据总量未过亿读多写少的应用场景，如互联网电商

虚拟机与CentOS环境准备

下载CentOS7 镜像

阿里云Centos镜像站

选择：CentOS-7-x86_64-Everything-2009.iso 下载

虚拟机环境准备

• 三台CentOS7虚拟机，2H2G内存，10G硬盘
• CentOS7要求安装Yum与Vim，采用“服务器”模式安装已内置

单主MGR拓扑

• 192.168.31.230-Master服务器 （hostname:n0）
• 192.168.31.231-Slave服务器 （hostname:n1）
• 192.168.31.232-Slave服务器 （hostname:n2）

自定义(固定)虚拟机ip见VMWare下centos7将动态ip修改为静态ip

将hosts文件写入n0/n1/n2节点与ip对应关系，后面配置采用域名访问

sudo cat > /etc/hosts <<-'EOF'
192.168.31.230 n0
192.168.31.231 n1
192.1668.31.232 n2
EOF

n0服务器执行：hostnamectl set-hostname n0

n1服务器执行：hostnamectl set-hostname n1

n2服务器执行：hostnamectl set-hostname n2

重启网络将 IP 地址生效

service network restart

开放防火墙，6032是ProxySQL的管理端口，6033是ProxySQL的客户端接入端口

firewall-cmd --zone=public --add-port=6032/tcp --permanent
firewall-cmd --zone=public --add-port=6033/tcp --permanent
firewall-cmd --reload

修改linux系统配置 /etc/selinux/config 文件，关闭selinux防火墙，开启后端口将无法访问，目前只是测试环境，我就直接这样设置了

SELINUX=disabled

修改后重启服务器

MGR单主集群搭建

Mysql 安装及配置

采用MySQL官方YUM仓库方式安装

#将下面代码片段在每一台节点上运行
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 
#安装MySQL 8.0.28
#自动安装MySQL 8.0.28  
yum install -y mysql-community-server 

三台服务器安装完成后，以此执行对应命令

n0服务器执行以下命令

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模式
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="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
#确定服务器是否应该在服务器启动期间启动组复制。
loose-group_replication_start_on_boot=OFF
#大坑2：
#为复制组中其他的成员提供的网络地址，指定为“主机:端口”的格式化字符串。
#很多人想当然认为端口应该是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
systemctl start mysqld

n1 服务器执行以下命令

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
systemctl start mysqld

n2 服务器执行以下命令

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
systemctl start mysqld

此时，三个数据库的配置文件已经修改，并且已经启动

因为数据库初始密码比较复杂，所以在这里对数据库初始密码进行修改

使用：grep 'temporary password' /var/log/mysqld.log 查看初始密码

然后使用 mysql -uroot -p'password'登录，执行以下命令

# 修改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@'%';
# 创建一个远程连接用户，这个用户在Navicate，JDBC登录的时候使用，直接使用root不安全
CREATE USER 'remote'@'%' IDENTIFIED WITH mysql_native_password BY 'asAS123456!';
# 为remote用户赋予所有数据库资源的访问权限
grant all privileges on *.* to remote@'%';
# 让刚才的修改生效
FLUSH PRIVILEGES;
#删除已产生的Binlog
#一定要RESET MASTER，它会删除刚才已产生的Binlog
#因为刚才Binglog包含创建用户这种高权限操作，用于主从同步的rpl_user账户是没有权限执行的
#这就会导致RelayLog重放无法正确执行，导致从属服务器卡死在"RECEVERING"状态
#利用RESET MASTER删除这些无法执行的binlog，就没问题了
RESET MASTER;

安装MGR插件

在每一台节点，在MySQL提示符状态下，使用INSTALL PLUGIN安装MGR插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

运行下面的SQL，看到结果出现PLUGIN_STATUS=ACTIVE，说明安装成功了

SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication' \G;

我们在 my.cnf 配置文件中把 group_replication_bootstrap_group 参数设置成 OFF，在 primary 服务器启动时并不会直接启动复制组，通过下面的命令动态的开启复制组使我们的集群更安全。在主数据库(n0)执行以下命令

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

从节点运行下面的命令，指定主从账户与指定通信频道，并且开启组网同步

CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="asAS123456!" FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

集群状态可以通过以下sql语句进行查询

SELECT * FROM performance_schema.replication_group_members;

至此，一个MGR单主集群就已经构建完成了，我们可以在主节点(n0)建立数据库，数据表，查看n1,n2节点是否同步。比如我在主节点(n0)创建了一个数据库test，并且创建了一张表tables1

进入n1和n2数据库，对应也出现了test数据库和table1表

MGR 故障转移

查询目前主节点为n0

此时主节点为n0，于是关闭n0服务器，模拟主节点故障，再次查看，已经推举n2为新的主节点

ProxySQL实现MySQL读写分离的原理

ProxySQL简介

ProxySQL是一个高性能、高可用、协议感知的数据库代理，它可以实现MySQL的负载均衡、连接池管理、自动故障转移、读写分离等功能。ProxySQL还可以根据查询模式和执行计划，对SQL查询进行路由、缓存和重写。

读写分离的原理

读写分离的基本原理是将数据库的读操作和写操作分配到不同的服务器，通常将主服务器（Master）用于写操作，而将从服务器（Slave）用于读操作。这样做的好处有以下几点：

1. 提高性能：通过将读写操作分散到不同的服务器，可以有效减轻单个服务器的压力，从而提高整体性能。
2. 提高可扩展性：随着业务的发展，可以通过增加从服务器的数量来应对读取压力，从而提高系统的可扩展性。
3. 提高数据安全性：读写分离可以避免因写操作导致的数据不一致，从而提高数据的安全性。

ProxySQL实现读写分离的原理

ProxySQL通过对SQL查询进行解析和路由，实现读写分离功能。具体来说，它会将所有的MySQL连接都指向它自己，然后根据配置文件中的规则将读操作路由到读库，将写操作路由到写库。这样一来，应用程序只需要连接到ProxySQL，而不需要知道实际的数据库服务器地址。

ProxySQL将查询分为两类：

1. 事务性查询（如INSERT、UPDATE、DELETE等）：这些查询会更改数据库的状态，因此需要发送到主服务器（Master）进行执行。
2. 非事务性查询（如SELECT等）：这些查询只涉及数据的读取，因此可以发送到从服务器（Slave）进行执行。

在实现读写分离的过程中，ProxySQL会自动检测并处理主从服务器的故障和切换，确保系统的高可用性。

比如ProxySQL提供的健康检查功能，用于监测MySQL服务器的状态。这个功能可以帮助ProxySQL及时发现故障的数据库服务器，并将查询路由到可用的服务器上，从而提高系统的可用性和稳定性。

健康检查的原理是通过发送心跳包或者执行一定的SQL语句来检测MySQL服务器的状态。如果MySQL服务器没有响应或者返回错误结果，ProxySQL就会将这个服务器标记为不可用，从而避免查询被路由到故障的服务器上。

ProxySQL安装与配置

1. 重新部署一台虚拟机，ip地址为：192.168.31.240
2. 开放防火墙，6032是ProxySQL的管理端口，6033是ProxySQL的客户端接入端口

firewall-cmd --zone=public --add-port=6032/tcp --permanent
firewall-cmd --zone=public --add-port=6033/tcp --permanent
firewall-cmd --reload

3. 下载ProxySQL的安装包 并 启动ProxySQL

cd /home/
wget --no-check-certificate https://manongbiji.oss-cn-beijing.aliyuncs.com/ittailkshow/mgr/download/proxysql-2.2.0-1-centos7.x86_64.rpm
yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm
service proxysql start

4. 安装MySQL YUM仓库源

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

5. 下载MySQL客户端

yum install -y mysql-community-client

6. 连接到ProxySQL Admin，它是一个伪装为MySQL的ProxySQL服务端

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

7. 加入MySQL集群节点

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.31.230',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.31.231',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.31.232',3306);
# 将insert的数据加载到运行时
load mysql servers to runtime;
# 持久化
save mysql servers to disk;

8. 在主节点，我这里主节点目前是n0，执行创建监控命令，该账户会通过主节点同步到其他从属节点

#MySQL降低密码强度
set global validate_password.policy=0;
set global validate_password.length=4;
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;

9. 在主节点上执行下面语句来创建gr_menber_routing_candidate_status视图，这是主从故障切换关键所在（建议在Navicat中运行sql）

use sys;
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);

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

CREATE VIEW gr_member_routing_candidate_status AS  
SELECT 
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();

10. 在ProxySQL中设置监控账号与程序账号

在ProxySQL服务器(240)执行以下sql语句

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);

11. 设置读写组

主负责写、从负责读，当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);

启用规则：

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;

状态校验：

select hostgroup_id, hostname, port,status  from runtime_mysql_servers;

通过下面命令查看后台执行日志，如有错误在error里面会显示

select hostname,port,viable_candidate,read_only,transactions_behind,error from mysql_server_group_replication_log order by time_start_us desc;

12. 配置读写分离规则

查询规则表字段意义如下表所示：

以下sql意义：查询结尾如果带有for update，分配到10（主写组），其他以select开头的，就分配到30（只读组）

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);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; 

13. 测试读写分离

使用proxysql连接6033，注意端口是6033

mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033

写一条数据，查询一条数据

use test;
insert into table1 values(1,'张三','20','女');
select * from table1 where id = 1;

回到6032，查看路由日志

mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest  order by digest_text desc limit 100;

可以看到，查询操作都被路由到了30（只读组），插入被分配到了10（主写组）

Spring Boot接入ProxySQL

只需要将原yml文件中的mysql配置替换为ProxySQL的连接地址和用户名密码即可

坑：java.sql.SQLException: Unknown system variable query_cache_size

解决方案：proxysql 内置的mysql版本较低，所以如果你的数据库是8.0及以上的，一定要记得修改proxysql内置mysql的版本号，登上proxysql的管理端执行以下命令，我的mysql版本为8.0.29

update global_variables set variable_value="8.0.29" where variable_name='mysql-server_version';
#修改后，需要加载到RUNTIME，并保存到disk
load mysql variables to runtime;
save mysql variables to disk;