ProxySQL配置读写分离

╰+攻爆jí腚メ 2022-04-22 07:54 288阅读 0赞

# 简介 #

ProxySQL是具有GPL许可证的高性能MySQL代理。作为监视进程监视的守护进程运行。该进程监视守护进程并在发生崩溃时重新启动它以最大限度地减少停机时间。  
守护进程接受来自MySQL客户端的传入流量，并将其转发到后端MySQL服务器。  
代理设计为连续运行而无需重新启动。大多数配置可以在运行时使用类似于SQL语句的查询来完成。其中包括运行时参数，服务器分组和与流量相关的设置。

官方文档：[https://github.com/sysown/proxysql/wiki][https_github.com_sysown_proxysql_wiki]  
Percona的官方文档：[https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html][https_www.percona.com_doc_percona-xtradb-cluster_LATEST_howtos_proxysql.html]

# ProxySQL配置系统 #

通过一个多级配置系统，在MEMORY中配置好参数，加载到RUNTIME（运行环境）中生效，并在有需求时持久化到磁盘上，从而实现能够简单地动态更新配置文件，兼容MySQL的管理接口可以实现这个目标，能够动态修改尽可能多的配置项，无需重启ProxySQL。能够轻松地回滚无效的配置项

+-------------------------+
    |         RUNTIME         |
    +-------------------------+
           /|\          |
            |           |
        [1] |       [2] |
            |          \|/
    +-------------------------+
    |         MEMORY          |
    +-------------------------+ _
           /|\          |      |\
            |           |        \
        [3] |       [4] |         \ [5]
            |          \|/         \
    +-------------------------+  +-------------------------+
    |          DISK           |  |       CONFIG FILE       |
    +-------------------------+  +-------------------------+

这里可以看到有4个部分

*  RUNTIME  
    表示的是ProxySQL线程处理请求时使用的数据结构
 *  MEMORY  
    MEMORY (有时也称为 main) 表示的是通过MySQL兼容接口公开的内存数据库。用户可以用MySQL的客户端连接到该接口，并查询ProxySQL的各种配置表或库。  
    通过该接口可获得的配置表包括：（配置ProxySQL主要也是配置下面这几个表）
    
     *  mysql\_servers – ProxySQL需要连接的后端服务器节点列表。
     *  mysql\_users – 连接到ProxySQL的用户及对应密码的列表。注意， ProxySQL也会按照该列表中的用户配置去连接后端服务器节点。
     *  mysql\_query\_rules – ProxySQL根据这些规则将请求路由到后端服务器。这些规则有时会重写查询语句或者将已执行的结果进行缓存。
     *  global\_variables – ProxySQL使用的全局变量列表，以及允许谁可以在runtime环境下进行调整。
 *  DISK  
    DISK 表示的是一个在磁盘上的SQLite3数据库，默认位置为$(DATADIR)/proxysql.db。重启时，那些没有持久化到磁盘的内存配置会丢失，因此有必要将内存中的配置持久化到 DISK
 *  CONFIG FILE  
    CONFIG 文件是传统的配置文件

理解了上面的多层次配置系统，我们就可以很好的使用ProxySQL了。  
主要配置四个部分

1.  添加后端主机
2.  添加监控
3.  添加连接到ProxySQL的用户及密码
4.  设置语句路由规则

# 管理接口中的数据库 #

下面这个是disk库中的表

下面这个是main中的表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]

从这里可以看出DISK，MEMORY，RUNTIME这三个层次的表都是相同的，不同的是它们的作用DISK这个层次的表已经更新到磁盘上了，MEMORY里的表配置存在内存中，但是未生效。RUNTIME里表的也是存在于内存中并且已经生效了！

# 安装 #

### 第一步：添加仓库 ###

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
    [proxysql_repo]
    name= ProxySQL YUM repository
    baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
    gpgcheck=1
    gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    EOF

### 第二步：安装 ###

yum install proxysql -y

# 配置方式 #

*  通过管理接口进行配置(推荐)
 *  通过配置文件配置

# 通过管理接口配置读写分离 #

拓扑图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]

前提：  
192.168.253.128与192.168.253.158的数据库已经完成，并且Slave库设置了read\_only=1

### 第一步：启动ProxySQL ###

systemctl start proxysql  #启动

### 第二步：添加后端主机 ###

在192.168.253.133主机上，进入管理接口

mysql -uadmin -padmin  -h127.0.0.1 -P 6032

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]

添加后端主机  
这里设置192.168.253.128在1主机组，192.168.253.158在0主机组，后面会根据主机组进行分配读写请求

MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.253.128','3306');
    MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'192.168.253.158','3306');

查看一下

MySQL [(none)]> SELECT * FROM mysql_servers;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]

### 第三步：配置监控用户 ###

需要后端节点已经创建好监控用户，用户名/密码为monitor/monitor

然后在管理界面添加

MySQL [(none)]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor'; #配置监控用户
    MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME; #加载到RUNTIME中
    MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;  #保存到DISK中

用下面两个命令可以查看后端节点的健康状态

MySQL [(none)]> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
    MySQL [(none)]> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]  
这里可以看到ping err和connect err 为NULL说明没错误

### 第四步：启用后端节点 ###

现在我们已经知道了所有的节点都被合理监控且它们都正常，把后段主机的配置装载到运行环境并保存到磁盘

MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;

### 第五步：添加连接到ProxySQL的用户及密码 ###

这里的用户密码需要先在后端MySQL节点配置好的，提供给客户端访问的  
用户名/密码 root/mysql

MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','mysql',1);

加载到RUNTIME并保存到DISK中

MySQL [(none)]>  LOAD MYSQL USERS TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL USERS TO DISK;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 5]  
说明添加用户成功，可以正常连接

### 第六步：设置读写分离规则 ###

读请求发送到192.168.253.158主机  
写请求发送到192.168.253.128主机

MySQL [(none)]>  UPDATE mysql_users SET default_hostgroup=1; # 所有的查询将默认路由到1主机组
    MySQL [(none)]>  LOAD MYSQL USERS TO RUNTIME;  # 加载到RUNTIME中
    MySQL [(none)]>  SAVE MYSQL USERS TO DISK;     #保存到DISK中
    
    MySQL [(none)]>  INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
    VALUES
    (1,1,'^SELECT.*FOR UPDATE$',1,1),
    (2,1,'^SELECT',0,1);  #设置匹配到SELECT开头的请求发送到0主机组，也就是192.168.253.158中
    LOAD MYSQL QUERY RULES TO RUNTIME; # 加载到RUNTIME中
    SAVE MYSQL QUERY RULES TO DISK;    #保存到DISK中

### 第七步：测试 ###

为了测试方便所以后端两个数据库没做主从  
并且创建了t1这个表里面的值也不同  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 6]  
下面来测试读请求发送到哪里  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 7]  
可以看出它一直发送到192.168.253.158这台主机，说明读请求分配给了192.168.253.158中，

接下来测试一下写请求  
![在这里插入图片描述][20181105193739348.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 8]  
说明写请求全都分配给了192.168.253.128中

到这里可以看出读写分离已经成功了。

[https_github.com_sysown_proxysql_wiki]: https://github.com/sysown/proxysql/wiki
[https_www.percona.com_doc_percona-xtradb-cluster_LATEST_howtos_proxysql.html]: https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]: /images/20220422/698f6b7745354bbbb59fc0fe0d6af77d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]: /images/20220422/0134a34893064b29a95402808279e3d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]: /images/20220422/4c36411e02e046e28fab9a368ed3dfab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]: /images/20220422/cc9ffae4c0e143f39983360847eb1182.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]: /images/20220422/ebfb042d0df54c68abb1ae4e85ac523f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 5]: /images/20220422/d046d75498bc46fb981f7a964297c08e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 6]: /images/20220422/355387f5d8d14777ab9849be79375931.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 7]: /images/20220422/7536d8311f9d40028c04506a52bf0049.png
[20181105193739348.png]: /images/20220422/05e2ff9a8d1740cbb22755f280ae8972.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 8]: /images/20220422/2572063b8f184dc0ad40fce597efcaae.png