注意:第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
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