MySQL中间件之ProxySQL总结
MySQL中间件之ProxySQL总结
1、ProxySQL简介
ProxySQL为MySQL的中间件，其有两个版本，官方版和percona版，percona版是基于官方版基础上修改而来。ProxySQL是用C++语言开发的，虽然也是一个轻量级产品，但性能很好(据测试，能处理千亿级的数据)，功能也足够，能满足中间件所需的绝大多数功能，包括：
1)最基本的读/写分离，且方式有多种。
2)可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。
3)可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略。
4)监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)。
2、proxysql的配置系统体系结构
整个配置系统分为三层，如下所示:
proxysql的每一个配置项在三层中都存在，但是这三层是独立的，也就是说，proxysql可以同时拥有三份配置，每层都是独立的，可能三份都不一样，可能三份都一样。
RUNTIME这个顶级层，就是proxysql运行过程中实际采用的那一份配置，这一份配置就是要影响实际生产的，所以将你的配置加进RUNTIME层时需要三思而行。
MEMORY这个中间层，上面接着生产配置项RUNTIME，下面接着持久化层DISK,CONFIG FILE。MEMORY也是我们修改proxysql的唯一正常入口。一般的，我们修改一个配置，先改Memory，确认无误后再接入生产(RUNTIME)，和持久化到磁盘(DISK).也就是说memeory里面的配置随便改，不影响生产，也不影响磁盘中保存的数据。
DISK和CONFIG FILE这一层是持久化层，我们做的任何配置更改，如果不持久化下来，重启后，配置都将丢失。持久化层主要将数据存储在sqlite(`$(DATADIR)/proxysql.db`.)数据库中和文件中(/etc/proxysql.cnf)。
当proxysql启动时，首先读取配置文件CONFIG FILE(/etc/proxysql.cnf)，然后从该配置文件中获取datadir，datadir中存储的是sqlite的数据目录。如果该目录存在，且sqlite数据文件存在，那么正常启动，将sqlite中的配置项读进内存，并且加载进RUNTIME，用于初始化proxysql的运行。如果datadir目录下没有找到sqlite的数据文件，proxysql就会使用config file中的配置来初始化proxysql，并且将这些配置保存至数据库。
3、安装部署
3.1、安装包下载
安装包有两个地方可以下载，
· percona站点:
https://www.percona.com/downloads/proxysql/
· github：
https://github.com/sysown/ProxySQL
我们选择proxysql-2.0.1-1-centos7.x86_64.rpm,rpm包直接装。
3.2、安装
直接使用rpm安装即可:
[[email protected]proxysql ~]# rpm -ivh proxysql-2.0.1-1-centos7.x86_64.rpm
3.3、启动ProxySQL
[[email protected]proxysql ~]#service ProxySQL start
3.4、ProxySQL相关文件
[[email protected]]# pwd
/var/lib/proxysql
[[email protected]]# ll
total 300
-rw-rw-r--. 1 proxysql proxysql 1050 Dec 12 13:34 proxysql-ca.pem
-rw-rw-r--. 1 proxysql proxysql 1058 Dec 12 13:34 proxysql-cert.pem
-rw-------. 1 proxysql proxysql 151552 Dec 12 13:34 proxysql.db
-rw-rw-r--. 1 proxysql proxysql 1679 Dec 12 13:34 proxysql-key.pem
-rw-------. 1 proxysql proxysql 3732 Dec 12 13:34 proxysql.log
-rw-r--r--. 1 proxysql proxysql 6 Dec 12 13:34 proxysql.pid
-rw-------. 1 proxysql proxysql 135168 Dec 12 15:00 proxysql_stats.db
[[email protected]]# ls -al /etc/proxysql.cnf
-rw-r-----. 1 root proxysql 6282 Jan 25 2019 /etc/proxysql.cnf
ProxySQL.db是SQLITE的数据文件，Proxysql很多东西是存储在这个数据库里面的。ProxySQL.log是日志文件，排查问题好地方。ProxySQL.pid这个pid文件不多说了。ProxySQL.cnf是ProxySQL的一些静态配置项，比如一些启动选项，sqlite的数据目录等等。
3.5、ProxySQL的相关进程
[[email protected]]# ps -ef| grep proxysql
proxysql 23958 1 0 13:34 ? 00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql 23959 23958 0 13:34 ? 00:00:05 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
和MySQL的很相似，我们启动一个进程，然后fork出一个子进程，父进程负责监控子进程运行状况如果挂了则拉起来，子进程负责执行真正的任务。
3.6、登录ProxySQL
[[email protected]]# mysql -u admin -padmin -h 127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
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)]> show dtabases;
ERROR 1045 (28000): near "show": syntax error
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
22 rows in set (0.00 sec)
从show tables可以看出，主要分mysql 开头的和runtime开头的表。
Mysql开头的主要是memory层的配置，通过sql修改配置时，主要就是改变这一层的数据。Runtime是实时生效的配置信息。
3.7、如何修改配置
通过sql 语句修改mysql开头的表。
| global_variables |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users
将配置加载到runtime和保存到disk上。
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS RULES TO DISK;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS RULES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES RULES TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
4、ProxySQL 主从读写分离配置
4.1、环境说明：
maste (10.100.251.221:3306) , mysql master
slave01 (10.100.251.222:3306) , mysql slave
slave02+ proxysql (10.100.251.223:3306) , mysql slave
vip : 10.100.251.238
4.2、配置
4.2.1、添加数据库信息
把一主两从的mysql数据库信息添加到proxysql中。我们将主库master也就是做写入的节点放到group 10中，salve节点做读放到group20。
[[email protected] ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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)]> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10,'10.100.251.221',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20,'10.100.251.222',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20,'10.100.251.223',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+----------------+------+--------+
| 10 | 10.100.251.221 | 3306 | ONLINE |
| 20 | 10.100.251.222 | 3306 | ONLINE |
| 20 | 10.100.251.223 | 3306 | ONLINE |
+--------------+----------------+------+--------+
LOAD MYSQL SERVERS TO RUNTIME;
/*将配置应用于proxysql运行环境*/
SAVE MYSQL SERVERS TO DISK;
/*将配置存储到sqlite数据库中*/
4.2.2、添加数据库监控账号和业务账号
在数据库master中需要配置监控账号(ProxySQL)和应用账号(pstest)。以下SQL在master节点执行。
CREATE USER 'ProxySQL'@'%' IDENTIFIED BY 'ProxySQL';
GRANT USAGE ON *.* TO 'ProxySQL'@'%';
CREATE USER 'pstest'@'%' IDENTIFIED BY 'pstest';
GRANT ALL ON * . * TO 'pstest'@'%';
FLUSH PRIVILEGES;
确认账号创建成功：
mysql [(none)]> select host,user from mysql.user;
+-----------------------+----------+
| host | user |
+-----------------------+----------+
| % | ProxySQL |
| % | proxysql |
| % | pstest |
4.2.3、在proxysql中添加账号
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('pstest','pstest',10);
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';
设置读写分组(配置MHA才用到，能自动检测主从切换，如果发现组从切换，底下表会自动更改。)
INSERT INTO mysql_replication_hostgroups VALUES(10,20,'read_only','test');
SELECT * FROM mysql_replication_hostgroups;
MySQL [(none)]> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | test |
+------------------+------------------+------------+---------+
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
/*将配置应用于proxysql运行环境*/
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL USERS TO DISK;
/*将配置存储到sqlite数据库中*/
查看proxysql监控数据库的一些指标
MySQL [(none)]> 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 |
+----------------+------+------------------+-------------------------+---------------+
| 10.100.251.223 | 3306 | 1576137235801049 | 781 | NULL |
| 10.100.251.221 | 3306 | 1576137235142148 | 1261 | NULL |
| 10.100.251.222 | 3306 | 1576137234483025 | 1167 | NULL |
| 10.100.251.222 | 3306 | 1576137175889798 | 1482 | NULL |
| 10.100.251.221 | 3306 | 1576137175186269 | 1506 | NULL |
| 10.100.251.223 | 3306 | 1576137174482801 | 809 | NULL |
| 10.100.251.222 | 3306 | 1576137115351565 | 1375 | NULL |
| 10.100.251.223 | 3306 | 1576137114917005 | 705 | NULL |
| 10.100.251.221 | 3306 | 1576137114482624 | 1518 | NULL |
| 10.100.251.223 | 3306 | 1576137082353092 | 859 | NULL |
+----------------+------+------------------+-------------------------+---------------+
MySQL [(none)]> 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 |
+----------------+------+------------------+----------------------+------------+
| 10.100.251.223 | 3306 | 1576137324343870 | 200 | NULL |
| 10.100.251.221 | 3306 | 1576137324257100 | 575 | NULL |
| 10.100.251.222 | 3306 | 1576137324170319 | 531 | NULL |
| 10.100.251.223 | 3306 | 1576137314311303 | 167 | NULL |
| 10.100.251.221 | 3306 | 1576137314240587 | 373 | NULL |
| 10.100.251.222 | 3306 | 1576137314170297 | 519 | NULL |
| 10.100.251.222 | 3306 | 1576137304408942 | 403 | NULL |
| 10.100.251.221 | 3306 | 1576137304289561 | 312 | NULL |
| 10.100.251.223 | 3306 | 1576137304170287 | 235 | NULL |
| 10.100.251.223 | 3306 | 1576137294348098 | 174 | NULL |
+----------------+------+------------------+----------------------+------------+
4.2.4、测试分发情况
使用业务用户通过proxysql登录mysql,默认没有配置规则所有都只会往master节点转发。
[[email protected] ~]# mysql -upstest -ppstest -h10.100.251.223 -P6033 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
查看统计统计信息，所有的分发都到group 10。
MySQL [(none)]> select * from stats_mysql_query_digest order by sum_time desc;
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 10 | information_schema | pstest | | 0xDA65260DF35B8D13 | select @@server_id | 42 | 1576137416 | 1576137785 | 17728 | 303 | 822 |
| 10 | information_schema | pstest | | 0xAE77E0F7B80AFC0B | select @@version | 1 | 1576137405 | 1576137405 | 1828 | 1828 | 1828 |
| 10 | information_schema | pstest | | 0xE6E1D6C08ABD4EC8 | select @@server-id | 1 | 1576137412 | 1576137412 | 567 | 567 | 567 |
| 10 | information_schema | pstest | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 44 | 1576137405 | 1576137785 | 0 | 0 | 0 |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
查看命令执行情况
select * from stats_mysql_commands_counters;
4.2.5、读写分离配置
登录proxysql配置路由项。
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
active表示是否启用这个sql路由项，
match_pattern就是我们正则匹配项，
destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去，这里我们将select转发到group 1，也就是两个slave上。
apply为1表示该正则匹配后，将不再接受其他匹配，直接转发。
添加了sql路由，我们来看看是否实现了读写分离。
首先记得清空proxysql的query统计
MySQL [(none)]> select * from stats_mysql_query_digest order by sum_time desc;
Empty set (0.01 sec)
4.2.6、测试读写分离
1）执行select测试脚本
for ((i=0;i<30;i++)); do mysql -upstest -ppstest -h10.100.251.223 -P6033 -e "select @@server_id"; sleep 0.1; done
MySQL [(none)]> select * from stats_mysql_query_digest order by sum_time desc;
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 20 | information_schema | pstest | | 0xDA65260DF35B8D13 | select @@server_id | 30 | 1576138545 | 1576138549 | 12956 | 254 | 696 |
| 10 | information_schema | pstest | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 30 | 1576138545 | 1576138549 | 0 | 0 | 0 |
+-----------+--------------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
2 rows in set (0.01 sec)
可以看到查询分发到了group 20上
2）执行insert测试脚本
for ((i=0;i<30;i++)); do mysql -upstest -ppstest -h10.100.251.223 -P6033 test -e "insert into t1 values(1)"; sleep 0.1; done
MySQL [(none)]> select * from stats_mysql_query_digest order by sum_time desc ;
+-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 10 | test | pstest | | 0x3C44D988579DAFFA | insert into t1 values(?) | 30 | 1576138894 | 1576138897 | 95755 | 2285 | 12862 |
| 10 | test | pstest | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 30 | 1576138894 | 1576138897 | 0 | 0 | 0 |
+-----------+------------+----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
可以看到所有insert分发到了group 10上。