万字详述 MySQL ProxySQL

2020-08-17

加入收藏

ProxySQL是用C++语言开发的，一个轻量级开源软件，性能和功能满足读写中间件所需的绝大多数功能，其配置数据基于SQLite存储，目前已到v2.0.13版本。

功能方面如下：

最基本的读/写分离，且方式有多种。
可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding。
不支持分表，可以分库，但利用规则配置实现分表。
可缓存查询结果。基本ProxySQL的缓存策略实现了基本的缓存功能，绝大多数时候够用。支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。支持query cache。支持对query的路由，可以针对某个语句进行分配执行实例。
监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性 (replication lag)。

特性方面：

连接池，而且是 multiplexing；
主机和用户的最大连接数限制；
自动下线后端DB；延迟超过阀值ping 延迟超过阀值网络不通或宕机
强大的规则路由引擎；实现读写分离查询重写sql流量镜像
支持prepared statement；
支持Query Cache；
支持负载均衡，与gelera结合自动failover；
将所有配置保存写入到SQLit表中。
支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
支持query cache。
支持对query的路由。
不支持分表，可以分库，但是利用规则配置实现分表。

ProxySQL：

官方站点

官方github

percona ProxySQL手册

下面基于2.0.10版本所有操作。

安装部署

安装地址

官方说明

proxysql服务器上安装MySQL客户端，用于在本机连接到ProxySQL的管理接口

一、tar.gz安装

[root@ss30 proxysql-2.0.10]# tar -xvf proxysql-2.0.10.tar.gz.gz 
[root@ss30 proxysql-2.0.10]# cd proxysql-2.0.10/
[root@ss30 proxysql-2.0.10]# make -j 4
[root@ss30 proxysql-2.0.10]# make install DESTDIR=/opt/idc/proxysql-2.0.10
make[1]: Leaving directory `/opt/idc/softwares/proxysql-2.0.10/src'
[root@ss30 proxysql-2.0.10]# make install DESTDIR=/opt/idc/proxysql-2.0.10
install -m 0755 src/proxysql /usr/bin
install -m 0600 etc/proxysql.cnf /etc
if [ ! -d /var/lib/proxysql ]; then mkdir /var/lib/proxysql ; fi
Creating proxysql user and group
useradd -r -U -s /bin/false proxysql
install -m 0644 systemd/system/proxysql.service /usr/lib/systemd/system/
systemctl enable proxysql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /usr/lib/systemd/system/proxysql.service.

卸载：make uninstall

二、rpm包安装

rpm解压即可，会在/usr/local/proxysql 生成文件

[root@ss30 softwares]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:proxysql-2.0.10-1                warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.

三、启动服务

[root@ss30 softwares]# systemctl start proxysql
[root@ss30 softwares]# ps -ef | grep proxy
proxysql   3964      1  0 21:01 ?        00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf
proxysql   3965   3964  7 21:01 ?        00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf
root       3989  77420  0 21:01 pts/2    00:00:00 grep --color=auto proxy

服务命令：systemctl start | stop | restart | statu proxysql

四、配置信息

ProxySQL很少停止或重启，因为绝大多数配置都可以在线修改。

服务启动配置文件

[root@ss30 proxysql-2.0.10]# vim /etc/systemd/system/proxysql.service 
[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target

[Service]
Type=forking
RuntimeDirectory=proxysql
#PermissionsStartOnly=true
#ExecStartPre=/usr/bin/mkdir -p /var/run/proxysql /var/run/proxysql
#ExecStartPre=/usr/bin/chown -R proxysql: /var/run/proxysql/
ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf
#PIDFile=/var/lib/proxysql/proxysql.pid
#StandardError=null  # all output is in stderr
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes

[Install]
WantedBy=multi-user.target

五、参数配置文件

[root@ss30 softwares]# vim /etc/proxysql.cnf 
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
        admin_credentials="admin:admin"
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}
......

ProxySQL结构

Qurey Processor 用于匹配查询规则并根据规则决定是否缓存查询或者将查询加入黑名单或者重新路由、重写查询或者镜像查询到其他hostgroup。
User Auth 为底层后端数据库认证提供了用户凭证。
Hostgroup manager – 负责管理发送SQL请求都后端数据库并跟踪SQL请求状态。
Connection pool – 负责管理后端数据库连接，连接池中建立的连接被所有的前端应用程序共享。
Monitoring – 负责监控后端数据库健康状态主从复制延时并临时下线不正常的数据库实例。

一、启动过程

RUNTIME层代表的是ProxySQL当前生效的配置，包括 globalvariables, mysqlservers, mysqlusers, mysqlquery_rules。无法直接修改这里的配置，必须要从下一层load进来
MEMORY层是平时在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像。该层级的配置在main库中以mysql开头的表以及globalvariables表，这些表的数据可以直接修改；
DISK|CONFIG FILR层持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。

注意：

如果找到数据库文件（proxysql.db），ProxySQL 将从 proxysql.db 初始化其内存中配置。因此，磁盘被加载到 MEMORY 中，然后加载到 RUNTIME 中。

如果找不到数据库文件（proxysql.db）且存在配置文件（proxysql.cfg），则解析配置文件并将其内容加载到内存数据库中，然后将其保存在 proxysql.db 中并在加载到 RUNTIME。

请务必注意，如果找到 proxysql.db，则不会解析配置文件。也就是说，在正常启动期间，ProxySQL 仅从持久存储的磁盘数据库初始化其内存配置。

二、数据库结构

ProxySQL自身共有5个库，分别为3个保存在内存中的库，和三个保存在磁盘的SQLite库。

通过6032管理端口登入后，默认就是main库，所有的配置更改都必须在这个库中进行，disk存档库不会直接受到影响。接下来看下

[root@ss30 proxysql-2.0.10]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
mysql> 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)

main：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。SQLite3 数据库，默认位置为 $（DATADIR）/proxysql.db,在重新启动时，未保留的内存中配置将丢失。因此，将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)
stats：proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
stats_history:统计信息历史库

三、核心配置表

五、小结

这些数据库的功能实现了实用化内容：

允许轻松动态更新配置，便于运维管理，与MySQL兼容的管理界面可用于此目的。
允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程
可以毫不费力地回滚无效配置
通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘

ProxySQL读写分离配置

一、MySQL里创建账号

数据库段创建访问用户，监控用户

[root@ss30 ~]# mysql -uroot -p123456 -h127.0.0.1 -P3410
mysql>  GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'%' identified by '123456'  WITH GRANT OPTION;

mysql> create user monitor@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication client on *.* to monitor@'%';
Query OK, 0 rows affected (0.01 sec)

二、添加MySQL节点

...

✨ 接下来内容请访问原文（https://www.modb.pro/db/28841?YYF）进行查看~

更多数据库相关内容，可访问墨天轮（https://www.modb.pro/?YYF）进行浏览。