一、前言
ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎，其特性主要由：读写分离、负载均衡、规则路由器等等，相关介绍可参考下述链接：
http://seanlook.com/2017/04/10/mysql-proxysql-install-config/
ProxySQL的部署架构
本次实验按照上述拓扑部署实现ProxySQL的读写分离，MySQL主从复制这里不再介绍配置过程，本次实验只演示ProxySQL的配置过程。
配置完成ProxySQL后，使得读取Mysql数据的连接会话通过ProxySQL被调度到后端slave1和slave2上获取，而写入操作等则调度到Master上进行处理。
proxysql的下载链接：https://www.percona.com/downloads/proxysql/
二、构建ProxySQL
1、安装ProxySQL服务
首先安装ProxySQL服务，此次我安装的为最新版proxysql-1.4.8：
[root@proxysql ~]# yum install -y https://www.percona.com/downloads/proxysql/proxysql-1.4.8/binary/redhat/7/x86_64/proxysql-1.4.8-1.1.el7.x86_64.rpm
2、ProxySQL的初始化配置
proxysql有个配置文件 /etc/proxysql.cnf，只在第一次启动的时候有用，后续所有的配置修改都是对SQL数据库操作，并且不会更新到proxysql.cnf文件中。ProxySQL绝大部分配置都可以在线修改，配置存储在 /var/lib/proxysql/proxysql.db 中。
在编辑配置proxySQL的配置文件前，我们先要在Master上创建ProxySQL的授权账号，用于proxySQL连接监控后端服务器：
MariaDB [(none)]> grant all on *.* to 'proxysql'@'192.168.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
接着我们来配置proxysql的初始化配置：
[root@proxysql ~]# vim /etc/proxysql
admin_variables=
{
admin_credentials="admin:admin" #proxysql的管理接口登录账号和密码
mysql_ifaces="0.0.0.0:6032" #proxysql的管理接口监听地址及端口
}
mysql_variables=
{
.....
interfaces="0.0.0.0:6033" #proxysql代理mysql的Ip和端口，默认为6033，不过通常建议更改为3306
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="proxysql" #proxysql用于监控后端服务器的账号及密码
monitor_password="123456"
......
}
mysql_servers =
(
{
address = "192.168.0.82" #指定后端服务器的Ip
port = 3306
hostgroup = 0 #为后端服务器分配一个hostgroup id
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "192.168.0.89"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
{
address = "192.168.0.87"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
}
)
mysql_users:
(
{
username = "proxysql" # no default , required
password = "123456" # default: ''
default_hostgroup = 0 # default: 0
default_schema="hellodb"
active = 1 # default: 1
}
)
mysql_query_rules: #配置路由规则
(
{
rule_id=1
active=1
match_pattern="^INSERT"
destination_hostgroup=0
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=1
apply=1
}
)
配置完成后即可启动proxysql服务：
[root@proxysql ~]# systemctl start proxysql
启动完成后即可通过连接访问mysql的管理端口6032来管理proxysql服务，如：
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看后端服务器的状态
MySQL [(none)]> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 192.168.0.82 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.0.89 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.0.87 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
此时通过访问连接6033端口即可访问到后端的数据库服务器。
[root@localhost ~]# mysql -uproxysql -p123456 -h192.168.0.81 -P6033
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]> show databases; #正常查看到相应的数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
#通过proxysql插入数据
MySQL [(none)]> insert into mydb.account(name) values ('tony'),('stark');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [(none)]> select * from mydb.account;
+----+---------+
| id | name |
+----+---------+
| 1 | charlie |
| 3 | jack |
| 5 | alice |
| 6 | Ops |
| 8 | IT |
| 10 | Manager |
| 11 | Web |
| 12 | tony |
| 13 | stark |
+----+---------+
9 rows in set (0.00 sec)
通过连接到proxysql的6033端口，proxysql服务会将相关的SQL操作代理到后端的mysql服务器，此处我的设置为将insert操作代理到Master主机，select查询相关的操作代理到后端的两个slave主机上，这样子就能适当减轻下Master主机的负载压力。
3、ProxySQL的管理接口
此前提到过，proxysql的配置文件/etc/proxysql.cnf只有在第一次启动时才有用，后续的所有配置的修改均是通过对SQL数据库操作的，这里的操作指的就是通过连接到Proxysql的管理Ip和端口上来进行修改操作。
例如，上述我们在/etc/proxysql.cnf所配置的初始化配置，在管理接口上我们可以下述的命令来修改。
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 #默认的admin账号只允许在proxysql本地服务器上登录
#添加proxysql用于访问后端数据库的账号
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',0,1);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from mysql_users\G
*************************** 1. row ***************************
username: proxysql
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 0
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
1 row in set (0.00 sec)
#添加用于做健康检测的账号
MySQL [(none)]> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> set mysql-monitor_password='123456';
Query OK, 1 row affected (0.00 sec)
#添加后端数据库主机，hostgroup_id 0表示写组，1表示读组
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(0,'192.168.0.82',3306,1,'Write Group');
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.87',3306,1,'Read Group');
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.89',3306,1,'Read Group');
Query OK, 1 row affected (0.01 sec)
#将insert这样的修改语句路由到0组（写组）
MySQL [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^INSERT.*',0,0);
#将select语句全部路由至hostgroup_id=1的组(也就是读组)
MySQL [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',1,0);
MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^INSERT.* | 0 | 0 |
| 2 | 1 | ^SELECT | 1 | 0 |
+---------+--------+----------------------+-----------------------+-------+
#加载刚才添加的配置
MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.01 sec)
#保持配置至db文件中
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 95 rows affected (0.01 sec)
MySQL [(none)]> save admin variables to disk;
Query OK, 31 rows affected (0.01 sec)
按照上述的命令配置完成后，此时应该能通过proxysql的6033端口访问后端的数据库服务了。
此时可以通过下述命令查看相关路由规则的匹配情况：
MySQL [(none)]> select active,hits, mysql_query_rules.rule_id, schemaname, match_digest, match_pattern, replace_pattern,destination_hostgroup hostgroup,s.comment,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules JOIN mysql_servers s on destination_hostgroup=hostgroup_id ORDER BY mysql_query_rules.rule_id;
+--------+------+---------+------------+--------------+---------------+-----------------+-----------+-------------+--------+---------+
| active | hits | rule_id | schemaname | match_digest | match_pattern | replace_pattern | hostgroup | comment | flagIN | flagOUT |
+--------+------+---------+------------+--------------+---------------+-----------------+-----------+-------------+--------+---------+
| 1 | 2 | 1 | NULL | ^INSERT.* | NULL | NULL | 0 | Write Group | 0 | NULL |
| 1 | 1 | 2 | NULL | ^SELECT | NULL | NULL | 1 | Read Group | 0 | NULL |
| 1 | 1 | 2 | NULL | ^SELECT | NULL | NULL | 1 | Read Group | 0 | NULL |
+--------+------+---------+------------+--------------+---------------+-----------------+-----------+-------------+--------+---------+
3 rows in set (0.00 sec)
另外proxysql提供了一个类似于审计的功能，可以查看各类SQL的执行分离情况：
#此命令需要在proxySQL 的管理端口执行
MySQL [(none)]> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+---------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+---------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1 | information_schema | proxysql | 0xCAEE33E27FE8899C | select * from mydb.account | 3 | 1530186285 | 1530186734 | 8191 | 1273 | 3634 |
| 0 | information_schema | proxysql | 0x20A90DAB1FD06917 | insert into mydb.account(name) values (?),(?) | 3 | 1530185541 | 1530186569 | 19155 | 5161 | 8142 |
| 0 | information_schema | proxysql | 0x3375EBC9A89AA861 | insert into mysql.account(name) values (?),(?) | 2 | 1530185533 | 1530186564 | 1874 | 798 | 1076 |
| 0 | information_schema | proxysql | 0xF8BB084D36148187 | insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(?,?,?,?,?) | 1 | 1530185979 | 1530185979 | 926 | 926 | 926 |
| 0 | information_schema | proxysql | 0x9461F19B72760588 | create database testdb | 1 | 1530186704 | 1530186704 | 3408 | 3408 | 3408 |
相关的select语句成功被路由到1组进行处理，因此只要编写好相关的路由规则，ProxySQL便能很好地将Mysql读写分离。
相关链接：http://blog.51cto.com/l0vesql/2090721
http://seanlook.com/2017/04/17/mysql-proxysql-route-rw_split/
http://seanlook.com/2017/04/10/mysql-proxysql-install-config/
网友评论