ProxySQL MySQL MGR8配置

上一篇博文记录了如何使用MySQL8.0.12部署mgr集群（单主模式或者多主模式都可以），那么在实际环境中应用如何去连接mgr集群呢？再比如单主模式如何实现读写分离呢？用代理软件需要配合各种脚本太麻烦，修改程序对开发是个考验，维护方面不是很方便。正好听说ProxySQL新版本1.4在最新的功能性增强，包含了对MGR的原生支持，不在需要使用第三方脚本进行配合使用。那么这一点对运维人员也是最大的福音。网上查了很多文档，包括官方文档等等，看的云里雾里的。说还得用什么sql存储过程。感觉都是坑。个人觉得没有一篇文档能有完全的参考价值。这里意思是说不能完全照着一篇文章做下来，综合了多个文档一天半才搞定。说多了全是眼泪~~~希望看到我这篇博客的小伙伴你们不会有此想法。
前期准备工作：
1）mysql8.0.12 mgr集群（一主两从）配置完成，172.17.0.5是主节点。不会配置的请参考上一篇文章。

2）再准备一台服务器，需要能通互联网。另外和mgr集群一个网段。
3）关闭所有服务器的防火墙。
开始工作：
1、proxysql的安装（使用最新稳定版本1.4.11）
既然mgr集群是基于docker环境上部署，那么再用docker创建一个proxysql容器
docker run -d -it --privileged --name=proxysql centos /usr/sbin/init
docker exec -it proxysql bash
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum install perl perl-DBD-mysql mysql -y
wget https://github.com/sysown/proxysql/releases/download/v1.4.11/proxysql-1.4.11-1-centos7.x86_64.rpm
rpm -ivh proxysql-1.4.11-1-centos7.x86_64.rpm
systemctl start proxysql
2、proxysql需要和mgr集群通信，那么肯定是通过用户，这里在mgr主节点上创建两个用户，一个监控用户用于取数据来判断mgr组里面mysql的读写状态，另外一个业务用户用于最后测试读写分离效果。
mysql> create user monitor@'%' identified by 'Monitor@123';
mysql> grant select on sys.* to monitor@'%';
mysql> create user test@'%' identified by 'Test@123';
mysql> grant all on *.* to test@'%';
mysql> FLUSH PRIVILEGES;
本来应该到这可以了。但是proxysql内置的mysql是8之前的版本，而mgr集群中的mysql是8.0.12版本。采用的是默认认证方式是caching_sha2_password，两者的身份认证不同。所以为了让mysql8能兼容新老版本的认证方式需要为上述创建的两个用户执行以下命令。这个问题卡了我很长时间，至少我看过的文档里面没有一个人提到这个问题。不然后续proxysql连接mgr会报错：
error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
mysql> ALTER USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123' PASSWORD EXPIRE NEVER;
mysql> alter user 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'Monitor@123';
mysql> ALTER USER 'test'@'%' IDENTIFIED BY 'Test@123' PASSWORD EXPIRE NEVER;
mysql> alter user 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Test@123';
mysql> FLUSH PRIVILEGES;
到此mgr集群就不需要再做什么配置了。
3、配置proxysql，其实有两种方法，第一种是动态配置，也就是sql语句的这种。第二种是通过proxysql.cnf文件配置（个人觉得配置文件简单，不用敲那么多命令，但是不尝试第一种动态配置，或许你用配置文件可能不是那么很好理解proxysql的实现过程）。所以还是先看第一种吧！proxysql有两个端口，一个是管理端口6032，一个是业务连接端口6033。
mysql -u admin -padmin -h 127.0.0.1 -P6032
MySQL > \R Admin> 进入管理员模式
插入mgr集群的三个节点信息，组id都设置一样。
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.5',3306);
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.6',3306);
Admin>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.7',3306);
Admin>select * from mysql_servers;

更新proxysql的监控用户和密码及各种时间
Admin>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Admin>UPDATE global_variables SET variable_value='Monitor@123' WHERE variable_name='mysql-monitor_password';
Admin>UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
进行验证
Admin>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

将配置写入运行中，然后再保存到磁盘
Admin>LOAD MYSQL VARIABLES TO RUNTIME;
Admin>SAVE MYSQL VARIABLES TO DISK;
验证是否可以检测到后端mgr集群并获取数据
Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

Admin>SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

Admin>LOAD MYSQL SERVERS TO RUNTIME;
再查看一下第一步插入的后端mgr集群数据，似乎没什么变化
Admin>SELECT * FROM mysql_servers;

现在对后端的mgr集群进行分组，1为写，2为读，名称为cluster1
Admin>INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');
Admin>LOAD MYSQL SERVERS TO RUNTIME;
从proxysql验证读写状态，已经区分很明显了
Admin>SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;

再查一下状态，貌似发生了改变，proxysql 已经将172.17.0.5划到了写组，172.17.0.6和172.17.0.7划到了读组
Admin>SELECT * FROM mysql_servers;

Admin>SAVE MYSQL SERVERS TO DISK;
Admin>SAVE MYSQL VARIABLES TO DISK;
这样就基本完成了proxysql配置。接下来我们去验证读写分离效果
插入用户名、密码、组
Admin>insert into mysql_users(username,password,default_hostgroup) values('test','Test@123',1);
插入读写分离规则，1是写，2是读，千万别弄混了。以select开头的请求到2组，但是还有一种请求是查询后更新的语句，这种要配置请求到1组
Admin>insert into mysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(1,1,'test','^SELECT.*FRO UPDATE$',1,1),(2,1,'test','^SELECT',2,1);
Admin>load mysql users to runtime;
Admin>save mysql users to disk;
Admin>load mysql query rules to runtime;
Admin>save mysql query rules to disk;
退出管理员模式，在proxysql上测试读写分离
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'create database test;'
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'show databases;'
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'select user,host from mysql.user;'
进入proxysql查看读写分离效果，不需要进管理模式即可
mysql -u admin -padmin -h 127.0.0.1 -P6032
mysql> SELECT * from stats_mysql_query_digest;

从上图看到create和show语句请求的是写，select请求的从，这样就完成了读写分离测试。
还有另外一个问题我们需要进行测试，当mgr主节点故障，新选举的主节点会不会被proxysql识别到？
手动停止mgr当前的主库
systemctl stop mysqld
随便进入剩下两个节点中的一个查看当前的主
mysql> select * from performance_schema.replication_group_members;

从图中我们没法看到IP，只能看到主机名，肯定是发生了切换，当前主是172.17.0.6
再进入proxysql查看读到的mgr状态
mysql> select hostgroup_id,hostname,port,status from runtime_mysql_servers;

已经看到172.1.0.5状态是SHUNNED，当前hostgroup_id为1的主是172.17.0.6
再次写入数据测试
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'create database test1;'
在proxysql上进行查看是否创建成功
mysql -utest -pTest@123 -h 127.0.0.1 -P6033 -e 'show databases;'

test1库正常创建。到此第一种动态配置方法完成。
下面开始通过配置文件读取，然后也方便做到docker容器里面。配置文件内容如下：
cat /etc/proxysql.cnf

datadir="/var/lib/proxysql"
admin_variables=
{

admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032"

}

mysql_variables=
{

threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033;/tmp/proxysql.sock" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="Monitor@123" monitor_history=600000 monitor_connect_interval=2000 monitor_ping_interval=2000 monitor_read_only_interval=2000 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10

}

mysql_servers =
(

{ address="172.17.0.5" , port=3306 , hostgroup=1, max_connections=1000 }, { address="172.17.0.6" , port=3306 , hostgroup=1, max_connections=1000 }, { address="172.17.0.7" , port=3306 , hostgroup=1, max_connections=1000 }

)

mysql_users=
(

{ username = "test" , password = "Test@123" , default_hostgroup = 1 , active = 1 }

)

mysql_query_rules=
(

{ rule_id=1 active=1 match_pattern="^SELECT .* FOR UPDATE$" destination_hostgroup=1 apply=1 }, { rule_id=2 active=1 match_pattern="^SELECT" destination_hostgroup=2 apply=1 }

)

mysql_replication_hostgroups=
(

 { writer_hostgroup=1 reader_hostgroup=2 comment="cluster1" }

)`
其实就是把第一种方法里面配置的内容都写到proxysql.cnf文件里面。第二种静态配置文件测试方法很简单，删掉/var/lib/proxysql目录下db结尾的文件，然后重启proxysql服务。最后进入proxysql查看数据是否都存在即可！这里就不再进行测试！