MySQL-ProxySQL中间件

ProxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离，支持
Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启 ProxySQL 服务），
支持故障切换和SQL的过滤功能。
相关 ProxySQL 的网站：
https://www.proxysql.com/
https://github.com/sysown/proxysql/wiki

安装ProxySQL，并启动运行。

# yum install proxysql-2.0.10-1-centos7.x86_64.rpm -y
# systemctl start proxysql      
# netstat -nltp | grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      1534/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      1534/proxysql       

端口 6032 是admin管理接口，用于查看和配置ProxySQL

端口 6033 是接收SQL语句的接口，类似于MySQL的3306端口

登录方式和MySQL类似，默认用户密码是admin

# mysql -uadmin -padmin -h127.0.0.1 -P6032

分为三层：

• RUNTIME：代表 ProxySQL 当前正在使用的配置，无法直接修改此配置，必须要从 MEM层 load 进来。

• MEMORY：上面连接 RUNTIME 层，下面disk持久层。这层可以在线操作 ProxySQL 配置，随便修改，不会影响生产环境。确认正常之后在加载达到RUNTIME和持久化的磁盘上。修改方法： insert、update、delete、select。

• DISK 和 CONFIG FILE：持久化配置信息。重启时，可以从磁盘快速加载回来。

配置读写分离，主从复制环境基于《MySQL-MHA搭建》中的一主两从环境。

1、在mysql_replication_hostgroup表中，配置读写组编号

mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');

mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+

mysql> load mysql servers to runtime;

mysql> save mysql servers to disk;

ProxySQL 会根据server 的read_only 的取值将服务器进行分组。 read_only=0 的server，
master被分到编号为10的写组，read_only=1 的server，slave则被分到编号20的读组。
所以需要将从库设置：
set global read_only=1;
set global super_read_only=1;

2、添加主机到ProxySQL中

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.3.112',3306);
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.3.113',3306);
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.3.114',3306);
mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;

3、创建监控用户，开启监控

master 上创建监控用户，自动同步 slave 库

db01 [(none)]>create user monitor@'%' identified with mysql_native_password by '123';
db01 [(none)]>grant replication client on *.* to monitor@'%';

ProxySQL添加监控用户

mysql> set mysql-monitor_username='monitor';
mysql> set mysql-monitor_password='123';
mysql> load mysql variables to runtime;
mysql> save mysql variables to disk;

查看监控日志

mysql> select * from mysql_server_connect_log;
mysql> select * from mysql_server_ping_log;
mysql> select * from mysql_server_read_only_log;
mysql> select * from mysql_server_replication_lag_log;

4、配置应用用户

master 上配置添加用户

db01 [(none)]>create user root@'%' identified with mysql_native_password by '123';
db01 [(none)]>grant all on *.* to root@'%';

ProxySQL 添加

mysql> insert into mysql_users(username,password,default_hostgroup)
values('root','123',10);
mysql> load mysql users to runtime;
mysql> save mysql users to disk;

5、配置读写分离规则

mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select.*for update$',10,1);
mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',20,1);
mysql> load mysql query rules to runtime;
mysql> save mysql query rules to disk;

6、测试读写分离

# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "begin;select @@server_id;commit;"
# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@server_id;"

测试出现的问题，按照前面的定义的规则是只有select 开头的去从库执行，测试时，select 语句在主库也执行了。

检查导致该情况原因：

mysql> select * from mysql_servers;

上面语句结果显示，在写分组 10 和读分组 20 中都含有这三台服务器信息。从服务器设置read_only后，写分组 10 中只有主服务器信息；使用 delete 语句删除读分组 20 中的主服务器记录即可。修改完成记得load 到 runtime中，否则不生效。

查询统计

select * from stats_mysql_query_digest\G

ProxySQL还有其他路由规则，可以基于端口、用户等设置规则。