Mysql Group Replication 主从（单主）中间件ProxySQL安装配置

程序员文章站 2022-07-13 08:59:30

...

注意：第4、第5两步的操作可以在mysql group replication主节点上先行执行。

1、下载安装ProxySQL

wget https://github.com/sysown/proxysql/releases/download/v1.3.3/proxysql-1.3.3-1-centos67.x86_64.rpm
rpm –ivh proxysql-1.3.3-1-centos67.x86_64.rpm

2、启动ProxySQL

service proxysql start

3、登录ProxySQL

mysql -h127.0.0.1 -P6032 -uadmin -padmin

4、MGR集群创建相关用户并授权

#在集群的主节点执行如下脚本
#添加监控用户并授权
grant all privileges on *.* to 'proxysql'@'172.16.90.%' identified by 'proxysql';
#添加代理业务用户并授权
CREATE USER 'proxyuser'@'%' IDENTIFIED BY 'proxypass'; 
GRANT INSERT,UPDATE,DELETE,SELECT ON * . * TO 'proxyuser'@'%';
flush privileges;

5、创建检查MGR节点状态的函数和视图

参照前面的博客，在MGR主节点上执行下面链接中的SQL

https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql

source /root/addition_to_sys.sql

6、配置proxysql

添加MGR成员节点到proxysql mysql_servers表

insert into mysql_servers (hostgroup_id, hostname, port) values(1, '172.16.90.39', 3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '172.16.90.39', 3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '172.16.90.40', 3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(2, '172.16.90.41', 3306);

hostgroup_id = 1代表write group，针对我们提出的限制，这个地方只配置了一个节点；hostgroup_id = 2代表read group，包含了MGR的所有节点。

proxysql还可以配置读写分离，本文不考虑这个特性的配置。对于上面的hostgroup配置，所有的读写操作，默认会发送到hostgroup_id为1的hostgroup，也就是发送到写节点上。

修改proxysql监控的用户及密码

UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_password';

添加应用通过proxyuser访问后端MGR节点的用户

INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxyuser','proxypass',1);

将global_variables，mysql_servers、mysql_users表的信息加载到RUNTIME，更进一步加载到DISK

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

7、配置scheduler

在Github地址https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker下载gr_sw_mode_checker.sh

将脚本gr_sw_mode_cheker.sh放到目录/var/lib/proxysql/下

在proxysql的scheduler表里面加载如下记录，然后加载到RUNTIME使其生效，同时还可以持久化到磁盘

insert into scheduler(id, active, interval_ms, filename, arg1, arg2, arg3, arg4)
  values(1, 1, 3000, '/var/lib/proxysql/gr_sw_mode_checker.sh', 1, 2, 1, '/var/lib/proxysql/checker.log');
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;

8、java连接proxysql配置

jdbc.url=jdbc:mysql://172.16.90.39:6033/ifaes_test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
jdbc.username=proxyuser
jdbc.password=proxypass