ProxySQL集成MHA的单机部署和集群部署方式

说明：MHA为主从复制的MySQL集群提供了主节点故障转移的功能，但是如果此时你需要实现读写分离的话，就需要引入其他技术组件了，ProxySQL就是个不错的选择，它可以通过 read_only 参数自动识别MySQL集群中的读节点和写节点，从而实现读与写的分离操作。如果担心 ProxySQL 会发生单点故障，下面文章同样提供了集群版的 ProxySQL 实现方案（通过 Keepalived 实现）。

安装 ProxySQL

备注：此次安装测试机内核版本为CentOS-7.4.1708

将安装包 mysql_cluster_ha_pack.zip 上传至集群所有服务器，并解压。
上传位置 /root/
点击获取安装包

一、单机版

1 安装Proxysql

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

2 安装mysql客户端

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

备注：如果失败尝试命令后面加 --nodeps --force 并重试。

3 配置

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

4 MySQL创建用户

（mysql主节点）

-- 连接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;
-- 查看所有MySQL的read_only配置，确保主节点为OFF，从节点为ON
show variables like "read_only";

5 启动

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

登录proxysql管理界面

# 管理员身份登录 proxysql
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 修改写节点不提供读服务
set mysql-monitor_writer_is_also_reader='false';
# 添加角色
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_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type) VALUES (10,20,'read_only');
# 设置读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,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

6 查看集群状态

# 管理员身份登录 proxysql
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

# 状态校验
select hostgroup_id,hostname,port,status from runtime_mysql_servers;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vbo93NKh-1679397678055)(C:\Users\dragon\AppData\Roaming\Typora\typora-user-images\image-20230315093506069.png)]

除了 hostname 其余应保持一致。

# 接着测试一下能否正常登入数据库 （测试命令执行多次，则会登录到不同的mysql节点上）
[root@ProxySQL-node1 ~]# mysql -uproxysql -pproxysql@2023 -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1  |
+------------+

# 最后在proxysql管理端查看读写分离情况
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
..........
MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;

二、集群版

1 安装Proxysql

（proxysql01、02）

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

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

备注：如果失败尝试命令后面加 --nodeps --force 并重试。

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=1000
        cluster_check_status_frequency=10
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}

# 追加部分(注意修改IP)
proxysql_servers =                                    
(
        {
                hostname="192.168.x.104"
                port=6032
                weight=1
                comment="proxysql01"
        },
        {
                hostname="192.168.x.105"
                port=6032
                weight=1
                comment="proxysql02"
        }
)

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

4 MySQL创建用户

（mysql主节点）

-- 连接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;
-- 查看所有MySQL的read_only配置，确保主节点为OFF，从节点为ON
show variables like "read_only";

5 启动

（proxysql01、02）

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

登录proxysql管理界面（proxysql01）

# 管理员身份登录 proxysql
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 修改写节点不提供读服务
set mysql-monitor_writer_is_also_reader='false';
# 添加角色
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_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type) VALUES (10,20,'read_only');
# 设置读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,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

6 查看集群状态

（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 其余应保持一致。

# 接着测试一下能否正常登入数据库 （测试命令执行多次，则会登录到不同的mysql节点上）
[root@ProxySQL-node1 ~]# mysql -uproxysql -pproxysql@2023 -h 127.0.0.1 -P6033 -e"select @@hostname"
+------------+
| @@hostname |
+------------+
| MGR-node1  |
+------------+

# 最后在proxysql管理端查看读写分离情况
[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
..........
MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;

7 Keepalived安装

1) 安装Keepalivedexit

（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

2) 配置

查看网卡名（proxysql01、02）

ifconfig

（proxysql01）

vim /etc/keepalived/keepalived.conf
# 仅需修改网卡名称和虚拟IP
! Configuration File for keepalived

vrrp_script chk_proxysql_port  {
	script "/usr/bin/sh /etc/keepalived/proxysql_check.sh"
	interval 2
	weight 1
}

vrrp_instance VI_1 {
	state MASTER
	interface ens192 #节点网卡名
	virtual_router_id 51
	priority 100
	advert_int 2
	authentication {
		auth_type PASS
		auth_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 keepalived

vrrp_script chk_proxysql_port  {
	script "sh /etc/keepalived/proxysql_check.sh"
	interval 2
	weight 1
}

vrrp_instance VI_1 {
	state BACKUP
	interface ens192 #节点网卡名
	virtual_router_id 51
	priority 99
	advert_int 2
	authentication {
		auth_type PASS
		auth_pass keep2023
	}
	virtual_ipaddress {
		192.168.0.99/24 #虚拟IP，和外网的IP要一个网段
	}
	track_script {
		chk_proxysql_port
	}
}

3) 启动

（proxysql01、02）

systemctl start keepalived

日志位置：/var/log/messages