MySQl高可用集群搭建（MGR + ProxySQL + Keepalived）

前言

服务器规划（CentOS7.x）

IP地址	主机名	部署角色
192.168.x.101	mysql01	mysql
192.168.x.102	mysql02	mysql
192.168.x.103	mysql03	mysql
192.168.x.104	proxysql01	proxysql、keepalived
192.168.x.105	proxysql02	proxysql、keepalived

将安装包 mysql_cluster_ha_pack.zip 上传至集群所有服务器，并解压（需要包请私我）。

上传位置 /root/

一、基础配置

1.1 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

1.2 关闭selinux

# 获取selinux状态
getenforce
# 临时关闭
setenforce 0 
# 永久关闭需要重启
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/sysconfig/selinux 

1.3 修改主机名

# 临时修改
hostnamectl --transient set-hostname xxxx
# 永久修改需要重启   --static可以省略
hostnamectl --static set-hostname xxxx 

1.4 修改hosts

vim /etc/hosts

192.168.x.101 mysql01
192.168.x.102 mysql02
192.168.x.103 mysql03
192.168.x.104 proxysql01
192.168.x.105 proxysql02

二、MySQL安装

2.1 安装MySQL（mysql01、02、03）

cd /root/mysql_cluster_ha_pack/mysql
mv mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar /root
# 执行 mysql_auto_install.sh 前请确认 /opt/module/mysql 目录不存在
sh mysql_auto_install.sh
# 提示输入 root 用户密码
(Default password: root):xxxxxx

2.2 修改配置

（mysql01）

vim /etc/my.cnf
# 追加下面内容
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log-bin=binlog
binlog_checksum=NONE
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_recovery_use_ssl=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="mysql01:24901"
loose-group_replication_group_seeds="mysql01:24901,mysql02:24901,mysql03:24901"
loose-group_replication_bootstrap_group=OFF
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
max_connections=2000

（mysql02）

vim /etc/my.cnf
# 追加下面内容
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log-bin=binlog
binlog_checksum=NONE
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="mysql02:24901"
loose-group_replication_group_seeds="mysql01:24901,mysql02:24901,mysql03:24901"
loose-group_replication_bootstrap_group=OFF
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
max_connections=2000

（mysql03）

vim /etc/my.cnf
# 追加下面内容
server-id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log-bin=binlog
binlog_checksum=NONE
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="mysql03:24901"
loose-group_replication_group_seeds="mysql01:24901,mysql02:24901,mysql03:24901"
loose-group_replication_bootstrap_group=OFF
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
max_connections=2000

2.3 初始化MySQL（mysql01、02、03）

systemctl restart mysqld
# 连接mysql，输入root的密码
mysql -uroot -p
# 创建同步数据用户
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl202302';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
# 重置已产生的binlog
RESET MASTER;
# 安装MGR插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
# 运行下面的SQL，看到结果出现PLUGIN_STATUS=ACTIVE，说明安装成功了
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication' \G;
# 指定主从账户与指定通信频道
CHANGE MASTER TO MASTER_USER="rpl_user", MASTER_PASSWORD="rpl202302" FOR CHANNEL 'group_replication_recovery';

2.4 启动 MGR 集群

（mysql01）

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

（mysql02、03）

START GROUP_REPLICATION;

-- 运行下面结果进行验证。
SELECT * FROM performance_schema.replication_group_members;

日志位置：/var/log/mysqld.log

2.5 查看自身角色

show variables like '%read_only%';

（mysql01）

（mysql02、03）

与图片保持一致成功。

三、ProxySQL安装

3.1 安装Proxysql（proxysql01、02）

cd /root/mysql_cluster_ha_pack/proxysql/rpms/proxysql
rpm -ivh *.rpm --force

3.2 安装mysql客户端（proxysql01、02）

cd /root/mysql_cluster_ha_pack/proxysql/rpms/mysql_client
rpm -Uvh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -Uvh mysql-community-client-5.7.28-1.el7.x86_64.rpm

3.3 配置（proxysql01、02）

vim /etc/proxysql.cnf
# 替换部分
admin_variables=
{admin_credentials="admin:admin;cluster_kevin:proxy2023"mysql_ifaces="0.0.0.0:6032"cluster_username="cluster_kevin"cluster_password="proxy2023"cluster_check_interval_ms=1000cluster_check_status_frequency=10cluster_mysql_query_rules_save_to_disk=truecluster_mysql_servers_save_to_disk=truecluster_mysql_users_save_to_disk=truecluster_proxysql_servers_save_to_disk=truecluster_mysql_query_rules_diffs_before_sync=3cluster_mysql_servers_diffs_before_sync=3cluster_mysql_users_diffs_before_sync=3cluster_proxysql_servers_diffs_before_sync=3
}# 追加部分(注意修改IP)
proxysql_servers =                                    
({hostname="192.168.x.104"port=6032weight=1comment="proxysql01"},{hostname="192.168.x.105"port=6032weight=1comment="proxysql02"}
)

# 通过命令修改配置
sed -i 's/threads=4/threads=8' /etc/proxysql.cnf
sed -i 's/max_connections=2048/max_connections=1000000' /etc/proxysql.cnf

3.4 MySQL创建用户\视图（mysql01）

# 连接mysql，输入root的密码
mysql ‐uroot ‐p
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@2023";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@2023";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%';
FLUSH PRIVILEGES;
USE sys;
SOURCE /root/mysql_cluster_ha_pack/proxysql/addition_to_sys.sql# 查看 mysql 处于 MGR 中的角色状态
select * from sys.gr_member_routing_candidate_status;

3.5 启动

（proxysql01、02）

rm -rf /var/lib/proxysql/proxysql.db
systemctl start proxysql

（proxysql01）

# 管理员身份登录 proxysql
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 查看集群状态，配置中的集群信息是否加载进来
select * from proxysql_servers;
# 添加角色
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@2023';
INSERT INTO mysql_users(username,password,active,default_hostgroup,transaction_persistent ) VALUES ('proxysql','proxysql@2023',1,10,1);
# 添加后端节点 注意修改成对应的ip！
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.102',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.x.103',3306);
# 设置读写主机组
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);
# 设置读写分离规则
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 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;
load mysql query rules to runtime;
save mysql query rules to disk;

日志位置：/var/lib/proxysql/proxysql.log

3.6 查看 MGR 状态（proxysql01、02）

# 管理员身份登录 proxysql
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '# 状态校验
select hostgroup_id,hostname,port,status from runtime_mysql_servers;

除了 hostname 其余应保持一致。

四、Keepalived安装

4.1 安装Keepalived（proxysql01、02）

cd /root/mysql_cluster_ha_pack/keepalived/rpms/gcc
rpm -Uvh *.rpm --nodeps --force
cd /root/mysql_cluster_ha_pack/keepalived/rpms/gcc-c++
rpm -Uvh *.rpm --nodeps --force
cd /root/mysql_cluster_ha_pack/keepalived
tar -zxf keepalived-2.2.7.tar.gz -C /usr/local/src
cd /usr/local/src/keepalived-2.2.7/
./configure --prefix=/usr/local/keepalived
make & make install
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived
mkdir /etc/keepalived
# 监控脚本
cp /root/mysql_cluster_ha_pack/keepalived/proxysql_check.sh /etc/keepalived/proxysql_check.sh
chmod u+x /etc/keepalived/proxysql_check.sh

4.2 配置

查看网卡名（proxysql01、02）

ifconfig

（proxysql01）

vim /etc/keepalived/keepalived.conf
# 仅需修改网卡名称和虚拟IP
! Configuration File for keepalivedvrrp_script chk_proxysql_port  {script "sh /etc/keepalived/proxysql_check.sh"interval 2weight 1
}vrrp_instance VI_1 {state MASTERinterface ens192 #节点网卡名virtual_router_id 51priority 100advert_int 2authentication {auth_type PASSauth_pass keep2023}virtual_ipaddress {192.168.0.99/24 #虚拟IP，和外网的IP要一个网段}track_script {chk_proxysql_port}
}

（proxysql02）

vim /etc/keepalived/keepalived.conf
# 仅需修改网卡名称和虚拟IP
! Configuration File for keepalivedvrrp_script chk_proxysql_port  {script "sh /etc/keepalived/proxysql_check.sh"interval 2weight 1
}vrrp_instance VI_1 {state BACKUPinterface ens192 #节点网卡名virtual_router_id 51priority 99advert_int 2authentication {auth_type PASSauth_pass keep2023}virtual_ipaddress {192.168.0.99/24 #虚拟IP，和外网的IP要一个网段}track_script {chk_proxysql_port}
}

4.3 启动（proxysql01、02）

systemctl start keepalived

日志位置：/var/log/messages

五、测试

create database testdb;
use testdb;
create table test (id int primary key,name varchar(20));
select * from test;
insert into test values (1,'zhangsan');

读写分离测试

# 登录proxysql回到6032，查看路由日志
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;

通过 stop mysql 或 stop proxysql 服务做故障迁移测试（MGR集群单点故障和proxysql集群单点故障）；