配置proxysql进行mysql的读写分离
proxysql简介
proxysql是一款高性能的mysql中间件，可以实现读写分离，查询重写，防火墙，数据库监控等功能。是目前percona主推的一款数据库中间件产品。
proxysql安装配置
实验环境：
两台服务器配置mysql GTID主从复制，一台为master，一台为slave。master负责写入数据，slave只负责读取数据。主从复制配置方法可以参考我之前的博客https://www.cnblogs.com/stacks/p/7171648.html
配置方法
- 安装proxysql
目前proxysql托管在github，也可以通过其官网登录到github进行下载http://www.proxysql.com/，下载下来为rpm包，直接安装即可。 - 配置proxysql
proxysql安装完成之后由一下几个配置文件组成：
- /etc/init.d/proxysql
- /etc/proxysql.cnf #proxysql主配置文件，可以直接在库中修改proxysql配置，也可以在此文件指定配置信息
- /usr/bin/proxysql
- /usr/share/proxysql/tools/proxysql_galera_checker.sh
- /usr/share/proxysql/tools/proxysql_galera_writer.pl
proxysql数据目录下有如下文件： - proxysql.db #proxysql是以sqllite方式存储数据的，此文件是sqllite的数据文件
- proxysql.log #日志文件
- proxysql.pid
- proxysql_stats.db
- proxysql支持不停机变更配置。其存在三个配置区：
- runtime：当前线上正在使用的配置；
- memory：当前编辑区，编辑完成可以加载到runtime，也可以保存到disk；
- disk：持久化存储在磁盘
流程图如下：
-
proxysql启动登录
proxysql监听在6032和6033端口，6032为管理端口，6033为数据库连接端口
/etc/init.d/proxysql start即可启动proxysql，通过mysql客户端连接管理端口即可登录到proxysql进行管理工作：mysql -uadmin -padmin -h127.0.0.1 -P6032
登录之后操作方法和mysql是一样的。 -
配置proxysql的监控用户和数据操作用户：
首先需要在master数据库添加monitor用户和数据库操作用户，slave端会自动同步到自己的库中。
grant usage on *.* to 'monitor'@'%' identified by 'monitor'; #创建monitor用户
grant all on *.* to 'sbuser'@'%' identified by 'sbuser' #添加数据操作用户
flush privileges #刷权限
proxysql执行如下语句进行服务端和用户的添加：
INSERT INTO MySQL_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.11.184',3306); #hostgroupid适用于区分读写分离服务器组的，可以把所有写库加入到一个组id，所有读库加入到另一个组id
INSERT INTO MySQL_servers(hostgroup_id, hostname, port) VALUES (1,'192.168.11.185',3306);
INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('sbuser','sbuser',0);
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='MySQL-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='MySQL-monitor_password';
load mysql servers to runtime; #加载memory刚才的配置到runtime生效
save mysql servers to disk; #持久化配置到sqlite存储中
mysql_servers表用于存储proxysql后端服务器，mysql_users表用于存储后端服务器数据库账号和密码
global_variables表存储各种全局配置信息；
执行mysql -usbuser -psbuser -h127.0.0.1 -P6033可以登录到后端服务器
配置读写分离
- master库执行
show slave hosts;查看是否已经有slave库的存在； - 配置读写分离rules，在proxysql执行如下语句：
insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',0,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
mysql_query_rules表适用于配置读写路由规则的表，所有的配置都是通过正则表达式的模式去进行匹配的
actime：表示此规则是否是激活状态，1表示激活
match_pattern：表示匹配的sql语句规则，以正则表达式方式进行匹配，select .* for update表示匹配所有数据修改操作语句
destination_hostgroup：分配到那个组；
apply：正则匹配上此规则之后是否还可以应用其他的rules，如果需要应用其他规则，则需要修改此配置参数；
- proxysql执行
SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;语句用于清空query统计信息； - 通过proxysql登录，然后执行select，update，insert，delete等语句，然后proxysql查询query统计信息：
mysql> select hostgroup hg,sum_time,count_star,digest_text from stats_mysql_query_digest order by digest_text; #从stats_mysql_query_digest表查询query统计信息
+----+----------+------------+------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+------------------------------------------+
| 0 | 2338 | 1 | desc sbtest.sbtest1 |
| 0 | 4469 | 1 | insert into sbtest1 values (?,?,?,?) |
| 1 | 10019135 | 4 | select count(*) from sbtest.sbtest1 |
| 0 | 3617 | 1 | update sbtest.sbtest1 set c=? where id=? |
+----+----------+------------+------------------------------------------+
4 rows in set (0.00 sec)
可以看到已经实现了读写分离了
proxysql监控状态查看
mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10; #查询后端库连接信息；
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.11.185 | 3306 | 1530675542539279 | 3844 | NULL |
| 192.168.11.184 | 3306 | 1530675542523231 | 867 | NULL |
| 192.168.11.185 | 3306 | 1530675482538252 | 3026 | NULL |
| 192.168.11.184 | 3306 | 1530675482522256 | 646 | NULL |
| 192.168.11.185 | 3306 | 1530675422538393 | 3043 | NULL |
| 192.168.11.184 | 3306 | 1530675422522091 | 2244 | NULL |
| 192.168.11.185 | 3306 | 1530675362535883 | 1984 | NULL |
| 192.168.11.184 | 3306 | 1530675362520011 | 828 | NULL |
| 192.168.11.185 | 3306 | 1530675302563722 | 4890 | NULL |
| 192.168.11.184 | 3306 | 1530675302519919 | 945 | NULL |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10; #查询后端库存活信息
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.11.185 | 3306 | 1530675592871849 | 695 | NULL |
| 192.168.11.184 | 3306 | 1530675592869012 | 176 | NULL |
| 192.168.11.185 | 3306 | 1530675582871431 | 683 | NULL |
| 192.168.11.184 | 3306 | 1530675582868776 | 178 | NULL |
| 192.168.11.185 | 3306 | 1530675572871008 | 854 | NULL |
| 192.168.11.184 | 3306 | 1530675572868341 | 175 | NULL |
| 192.168.11.185 | 3306 | 1530675562870802 | 732 | NULL |
| 192.168.11.184 | 3306 | 1530675562868067 | 183 | NULL |
| 192.168.11.185 | 3306 | 1530675552870456 | 784 | NULL |
| 192.168.11.184 | 3306 | 1530675552867687 | 187 | NULL |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
proxysql常用库和表
- mysql_servers：存储后端数据库服务器信息
- mysql_users：存储后端数据库权限
- mysql_query_rules：读写分离规则表
- stats_mysql_query_digest：query统计表
...
