Mysql中间件——ProxySQL

ProxySQL

ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL 中间件产品.

ProxySQL特点

• 支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
• 将所有配置保存写入到SQLit表中
• 实现读写分离

ProxySQL运行机制

• runtime：运行中使用的配置文件
• memory：提供用户动态修改配置文件
• disk：将修改的配置保存到磁盘SQLit表中（即：proxysql.db）

• RUNTIME层

代表的是ProxySQL当前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。

• MEMORY层

是平时在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像。我们通过Set修改配置也是先修改此层的内容。

• DISK层

持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口等，还是需要在管理命令行里修改，再save到硬盘。

ProxySQL安装

## 配置proxysql源
[root@localhost yum.repos.d]# vim ProxySQL.repo

[proxysql_repo]
name=ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/8
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

[root@localhost ~]# rpm -ivh proxysql-2.0.15-1-centos8.x86_64.rpm 
warning: proxysql-2.0.15-1-centos8.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:proxysql-2.0.15-1                warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
################################# [100%]
Created symlink /etc/systemd/system/multi-user.target.wants/proxysql.service → /etc/systemd/system/proxysql.service.

## 启动proxysql服务
[root@localhost ~]# systemctl enable --now proxysql
[root@localhost yum.repos.d]# ss -antl
State          Recv-Q         Send-Q                   Local Address:Port                   Peer Address:Port         
LISTEN         0              128                            0.0.0.0:111                         0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:6032                        0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:6033                        0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:6033                        0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:6033                        0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:6033                        0.0.0.0:*            
LISTEN         0              128                            0.0.0.0:22                          0.0.0.0:*            
LISTEN         0              128                               [::]:111                            [::]:*            
LISTEN         0              128                               [::]:22                             [::]:* 

ProxySQL的Admin管理接口

当Proxysql启动后，将监听两个端口：

• admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL
• 接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口

本地使用admin管理ProxySQL(admin是默认管理用户，只允许本地登录)

[root@localhost ~]# 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, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> 



## 查看sqlite库中数据库
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.001 sec)

由于 ProxySQL 的配置全部保存在几个自带的库中，所以通过管理接口，可以非常方便地通过发送一些SQL命令去修改 ProxySQL 的配置。 ProxySQL 会解析通过该接口发送的某些对ProxySQL 有效的特定命令，并将其合理转换后发送给内嵌的 SQLite3 数据库引擎去运行

ProxySQL 的配置几乎都是通过管理接口来操作的，通过 Admin 管理接口，可以在线修改几乎所有的配置并使其生效。只有两个变量的配置是必须重启 ProxySQL 才能生效的，它们是：
mysql-threads 和 mysql-stacksize

admin相关变量详解

admin-admin_credentials

admin-admin_credentials 变量控制的是admin管理接口的管理员账户。默认的管理员账户和密码为admin:admin，但是这个默认的用户只能在本地使用。如果想要远程连接到ProxySQL，例如用windows上的navicat连接Linux上的ProxySQL管理接口，必须自定义一个管理员账户.

• 添加新管理员账户，并在异地登录ProxySQL库

MySQL [(none)]> select @@admin-admin_credentials
    -> ;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin               |
+---------------------------+
1 row in set (0.002 sec)


## 添加新用户
MySQL [(none)]> set admin-admin_credentials='admin:admin;testuser:123456';
Query OK, 1 row affected (0.001 sec)

MySQL [(none)]> select @@admin-admin_credentials;
+-----------------------------+
| @@admin-admin_credentials   |
+-----------------------------+
| admin:admin;testuser:123456 |
+-----------------------------+
1 row in set (0.002 sec)


## 载入到runtime层，立即生效；保存到disk层，下午重启服务也能生效。
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.001 sec)

MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.004 sec)


## 远程登录proxysql实现admin接口管理
[root@localhost ~]# mysql -utestuser -p123456 -P6032 -h192.168.197.141  ## 指定数据库存在的ip地址
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.5.30 (ProxySQL Admin Module)777

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

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.002 sec)

• windows上利用navicat工具查看disk库数据
  

admin-stats_credentials

admin-stats_credentials 变量控制admin管理接口的普通用户，这个变量中的用户没有超级管理员权限，只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限

MySQL [(none)]> select @@admin-stats_credentials;
+---------------------------+
| @@admin-stats_credentials |
+---------------------------+
| stats:stats               |
+---------------------------+

## 添加新的普通用户
MySQL [(none)]> set admin-stats_credentials='selectuser:123456';
Query OK, 1 row affected (0.001 sec)

## 刷新配置文件
MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.000 sec)

MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.004 sec)


## 远程访问，查看数据库
[root@localhost ~]# mysql -uselectuser -p123456 -P6032 -h192.168.197.141;
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | monitor       |                                     |
| 3   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
3 rows in set (0.002 sec)

admin-mysql_ifaces

admin-mysql_ifaces 变量指定admin接口的监听地址，格式为冒号分隔的hostname:port列表。默认监听在 0.0.0.0:6032

MySQL [(none)]> select @@admin-mysql_ifaces;
+----------------------+
| @@admin-mysql_ifaces |
+----------------------+
| 0.0.0.0:6032         |
+----------------------+
1 row in set (0.002 sec)

## 修改默认管理端口
MySQL [(none)]> set admin-mysql_ifaces='0.0.0.0:1995';
Query OK, 1 row affected (0.001 sec)

MySQL [(none)]> load admin variables to runtime;
Query OK, 0 rows affected (0.002 sec)

MySQL [(none)]> save admin variables to disk;
Query OK, 35 rows affected (0.004 sec)

## 再次查看变量及服务端口号
MySQL [(none)]> select @@admin-mysql_ifaces;
+----------------------+
| @@admin-mysql_ifaces |
+----------------------+
| 0.0.0.0:1995         |
+----------------------+
1 row in set (0.003 sec)

[root@localhost proxysql]# ss -antl
State  Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0      128          0.0.0.0:111       0.0.0.0:*   
LISTEN 0      128          0.0.0.0:6033      0.0.0.0:*   
LISTEN 0      128          0.0.0.0:6033      0.0.0.0:*   
LISTEN 0      128          0.0.0.0:6033      0.0.0.0:*   
LISTEN 0      128          0.0.0.0:6033      0.0.0.0:*   
LISTEN 0      128          0.0.0.0:22        0.0.0.0:*   
LISTEN 0      128          0.0.0.0:1995      0.0.0.0:*      ## 6032端口变为1995
LISTEN 0      128             [::]:111          [::]:*   
LISTEN 0      128             [::]:22           [::]:* 

ProxySQL实现读写分离

环境信息

主机IP	角色	主机服务
192.168.197.141	数据库代理主机	proxysql
192.168.197.150	主数据库master	mysql
192.168.197.154	从数据库slave1	mysql
192.168.197.155	从数据库slave2	mysql

部署步骤

• 配置数据库主从

## 全部安装mysql或者mairadb服务

[root@localhost ~]# yum install -y mariadb*
......

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q   Local Address:Port   Peer Address:Port 
LISTEN 0      80             0.0.0.0:3306        0.0.0.0:*    
LISTEN 0      128            0.0.0.0:22          0.0.0.0:*    
LISTEN 0      128               [::]:22             [::]:* 


## master端创建slave主从用户
MariaDB [mysql]> grant replication slave ON *.* TO 'repli'@'192.168.197.154' identified by '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> grant replication slave ON *.* TO 'repli'@'192.168.197.155' identified by '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)


## 两个slave测试登录数据库
[root@slave1 ~]# mysql -urepli -p123456 -h192.168.197.150
......
MariaDB [(none)]>

[root@slave2 ~]# mysql -urepli -p123456 -h192.168.197.150
......
MariaDB [(none)]>


## 开启master端二进制日志
[root@master ~]#vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=10
#
......


## 查看master节点位置
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)



## 开启slave中继日志
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id=20
relay-log=mysql-relay-bin
......

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server-id=30                           
relay-log=mysql-relay-bin


## 两个slave重启服务
[root@slave1 ~]# systemctl restart mariadb
[root@slave2 ~]# systemctl restart mariadb

注：无论主从，server-id必须唯一，且slave服务器上的id必须比master服务器上的id大

## 配置并启动主从复制
[root@slave1 ~]# mysql
......
MariaDB [(none)]> change master to
    -> master_host='192.168.197.150',
    -> master_user='repli',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=996;

MariaDB [(none)]> change master to master_host='192.168.197.150', master_user='repli', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=996;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.197.150
                   Master_User: repli
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 996
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
......




[root@slave2 ~]# mysql
......
MariaDB [(none)]> change master to
    -> master_host='192.168.197.150',
    -> master_user='repli',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=996;

MariaDB [(none)]> change master to master_host='192.168.197.150', master_user='repli', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=996;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.197.150
                   Master_User: repli
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 996
                Relay_Log_File: mysql-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
......

• 测试主从

## master创建新数据库
MariaDB [(none)]> create database test_db;
Query OK, 1 row affected (0.001 sec)


## slave1库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+

## slave2库查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.001 sec)

• master上为proxysql主机创建读写账号

MariaDB [(none)]> grant all on *.* to 'proxysql'@'192.168.197.141' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

• proxysql登录6032管理端

[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 3
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> 

• 向mysql_servers表中插入数据

## 首先查看mysql_servers表结构
MySQL [main]> pragma table_info("mysql_servers");
+-----+---------------------+--------------+---------+------------+----+
| cid | name                | type         | notnull | dflt_value | pk |
+-----+---------------------+--------------+---------+------------+----+
| 0   | hostgroup_id        | INT          | 1       | 0          | 1  |
| 1   | hostname            | VARCHAR      | 1       | NULL       | 2  |
| 2   | port                | INT          | 1       | 3306       | 3  |
| 3   | gtid_port           | INT          | 1       | 0          | 0  |
| 4   | status              | VARCHAR      | 1       | 'ONLINE'   | 0  |
| 5   | weight              | INT          | 1       | 1          | 0  |
| 6   | compression         | INT          | 1       | 0          | 0  |
| 7   | max_connections     | INT          | 1       | 1000       | 0  |
| 8   | max_replication_lag | INT          | 1       | 0          | 0  |
| 9   | use_ssl             | INT          | 1       | 0          | 0  |
| 10  | max_latency_ms      | INT UNSIGNED | 1       | 0          | 0  |
| 11  | comment             | VARCHAR      | 1       | ''         | 0  |
+-----+---------------------+--------------+---------+------------+----+
12 rows in set (0.000 sec)

## 插入数据
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.197.150',3306,1,'Write Group Master');
Query OK, 1 row affected (0.000 sec)

MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.197.154',3306,1,'Read Group Slave1');
Query OK, 1 row affected (0.000 sec)

MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(30,'192.168.197.155',3306,1,'Read Group Slave2');
Query OK, 1 row affected (0.000 sec)

## 查看表内数据
MySQL [main]> select * from mysql_serversG
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.197.150
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: Write Group Master
*************************** 2. row ***************************
       hostgroup_id: 20
           hostname: 192.168.197.154
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: Read Group Slave1
*************************** 3. row ***************************
       hostgroup_id: 30
           hostname: 192.168.197.155
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: Read Group Slave2
3 rows in set (0.000 sec)

## 加载到runtime层，并持久保存到disk中
MySQL [main]> load mysql servers to run;
Query OK, 0 rows affected (0.004 sec)

MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.121 sec)

• 在mysql_users表中添加此前在master上创建的管理员用户

## 查看mysql_users表结构
MySQL [main]>  pragma table_info("mysql_users");
+-----+------------------------+---------+---------+------------+----+
| cid | name                   | type    | notnull | dflt_value | pk |
+-----+------------------------+---------+---------+------------+----+
| 0   | username               | VARCHAR | 1       | NULL       | 1  |
| 1   | password               | VARCHAR | 0       | NULL       | 0  |
| 2   | active                 | INT     | 1       | 1          | 0  |
| 3   | use_ssl                | INT     | 1       | 0          | 0  |
| 4   | default_hostgroup      | INT     | 1       | 0          | 0  |
| 5   | default_schema         | VARCHAR | 0       | NULL       | 0  |
| 6   | schema_locked          | INT     | 1       | 0          | 0  |
| 7   | transaction_persistent | INT     | 1       | 1          | 0  |
| 8   | fast_forward           | INT     | 1       | 0          | 0  |
| 9   | backend                | INT     | 1       | 1          | 2  |
| 10  | frontend               | INT     | 1       | 1          | 0  |
| 11  | max_connections        | INT     | 1       | 10000      | 0  |
| 12  | comment                | VARCHAR | 1       | ''         | 0  |
+-----+------------------------+---------+---------+------------+----+

## 添加用户
insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','123456',10,1);

## 查看
MySQL [main]> select * from mysql_usersG
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
1 row in set (0.000 sec)

• master上添加健康检查的只读权限用户

MariaDB [(none)]> grant select on *.* to 'monitor'@'192.168.197.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.001 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

• proxysql上修改此用户变量值

MySQL [main]> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.001 sec)

MySQL [main]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.001 sec)

## 修改monitor密码
MySQL [main]> set mysql-monitor_password='123456';
Query OK, 1 row affected (0.000 sec)

MySQL [main]> load mysql variables to run;
Query OK, 0 rows affected (0.001 sec)

## 加载，保存并查看
MySQL [main]> save mysql variables to disk;
Query OK, 137 rows affected (0.004 sec)

MySQL [main]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| 123456                   |
+--------------------------+
1 row in set (0.001 sec)

• proxysql添加读写分离的路由规则

MySQL [main]> pragma table_info("mysql_query_rules");
+-----+-----------------------+--------------+---------+------------+----+
| cid | name                  | type         | notnull | dflt_value | pk |
+-----+-----------------------+--------------+---------+------------+----+
| 0   | rule_id               | INTEGER      | 1       | NULL       | 1  |
| 1   | active                | INT          | 1       | 0          | 0  |
| 2   | username              | VARCHAR      | 0       | NULL       | 0  |
| 3   | schemaname            | VARCHAR      | 0       | NULL       | 0  |
| 4   | flagIN                | INT          | 1       | 0          | 0  |
| 5   | client_addr           | VARCHAR      | 0       | NULL       | 0  |
| 6   | proxy_addr            | VARCHAR      | 0       | NULL       | 0  |
| 7   | proxy_port            | INT          | 0       | NULL       | 0  |
| 8   | digest                | VARCHAR      | 0       | NULL       | 0  |
| 9   | match_digest          | VARCHAR      | 0       | NULL       | 0  |
| 10  | match_pattern         | VARCHAR      | 0       | NULL       | 0  |
| 11  | negate_match_pattern  | INT          | 1       | 0          | 0  |
| 12  | re_modifiers          | VARCHAR      | 0       | 'CASELESS' | 0  |
| 13  | flagOUT               | INT          | 0       | NULL       | 0  |
| 14  | replace_pattern       | VARCHAR      | 0       | NULL       | 0  |
| 15  | destination_hostgroup | INT          | 0       | NULL       | 0  |
| 16  | cache_ttl             | INT          | 0       | NULL       | 0  |
| 17  | cache_empty_result    | INT          | 0       | NULL       | 0  |
| 18  | cache_timeout         | INT          | 0       | NULL       | 0  |
| 19  | reconnect             | INT          | 0       | NULL       | 0  |
| 20  | timeout               | INT UNSIGNED | 0       | NULL       | 0  |
| 21  | retries               | INT          | 0       | NULL       | 0  |
| 22  | delay                 | INT UNSIGNED | 0       | NULL       | 0  |
| 23  | next_query_flagIN     | INT UNSIGNED | 0       | NULL       | 0  |
| 24  | mirror_flagOUT        | INT UNSIGNED | 0       | NULL       | 0  |
| 25  | mirror_hostgroup      | INT UNSIGNED | 0       | NULL       | 0  |
| 26  | error_msg             | VARCHAR      | 0       | NULL       | 0  |
| 27  | OK_msg                | VARCHAR      | 0       | NULL       | 0  |
| 28  | sticky_conn           | INT          | 0       | NULL       | 0  |
| 29  | multiplex             | INT          | 0       | NULL       | 0  |
| 30  | gtid_from_hostgroup   | INT UNSIGNED | 0       | NULL       | 0  |
| 31  | log                   | INT          | 0       | NULL       | 0  |
| 32  | apply                 | INT          | 1       | 0          | 0  |
| 33  | comment               | VARCHAR      | 0       | NULL       | 0  |
+-----+-----------------------+--------------+---------+------------+----+
34 rows in set (0.000 sec)

• 添加路由规则(基于SQL语句)

MySQL [main]>  insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(10,1,'^(update|insert|delete)',10,1);
Query OK, 1 row affected (0.000 sec)

MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(15,1,'^select.*from.*test_table',20,1);
Query OK, 1 row affected (0.000 sec)

MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(20,1,'^(select|show|desc)',30,1);
Query OK, 1 row affected (0.000 sec)



## 加载并保存
MySQL [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.001 sec)

MySQL [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.357 sec)

## 查看表数据
MySQL [main]> select * from mysql_query_rulesG
*************************** 1. row ***************************
              rule_id: 10
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^(update|insert|delete)
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
 ......
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 15
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^select.*from.*test_table
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
......
                apply: 1
              comment: NULL
*************************** 3. row ***************************
              rule_id: 20
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^(select|show|desc)
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 30
......
                apply: 1
              comment: NULL
3 rows in set (0.000 sec)

• proxysql本地登录

[root@proxysql ~]# mysql -uproxysql -p123456 -h127.0.0.1 -P3306;
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 232
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> 

• 查询test_table这张表

MySQL [(none)]> select * from test_db.test_table;
Empty set (0.010 sec)

• proxysql主机进入6032端口管理

[root@proxysql ~]# mysql -padmin -uadmin -h127.0.0.1 -P6032;
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> select * from stats_mysql_query_digestG      ## 此表用来统计路由信息

MySQL [(none)]> select * from stats_mysql_query_digestG
*************************** 1. row ***************************
        hostgroup: 20                      ## hostgroup组为20，也就是slave1主机，正好对应了select规则
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x23E7E13566F1C0BF
      digest_text: select * from test_db.test_table
       count_star: 1
       first_seen: 1609244618
        last_seen: 1609244618
         sum_time: 8958
         min_time: 8958
         max_time: 8958
sum_rows_affected: 0
    sum_rows_sent: 0

*************************** 3. row ***************************
        hostgroup: 30                        ## show 命令对应id为30的group，也就是slave2主机
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x02033E45904D3DF0
      digest_text: show databases          
       count_star: 1
       first_seen: 1609244445
        last_seen: 1609244445
         sum_time: 7690
         min_time: 7690
         max_time: 7690
sum_rows_affected: 0
    sum_rows_sent: 4

由于设定了查询test_table这张表必须由slave1来执行，所以第一个查询由hostgroup=20的主机运行。

show，select，desc这3个查阅命令统一交给slave2执行，所以这里hostgroup=30

• 创建一张新表，添加数据，并删除一条数据

[root@proxysql ~]# mysql -uproxysql -p123456 -h127.0.0.1 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [(none)]> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test_db]> create table student (id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.020 sec)


## 添加一条数据
MySQL [test_db]> insert into student (id,name,age) value(1,'tom',20);
Query OK, 1 row affected (0.007 sec)


## 删除这条数据
## MySQL [test_db]> delete from student where id=1;
Query OK, 1 row affected (0.005 sec)

• 进入admin控制端口，查看记录

MySQL [(none)]> select * from stats_mysql_query_digestG;
*************************** 9. row ***************************
        hostgroup: 10
       schemaname: test_db
         username: proxysql
   client_address: 
           digest: 0x25502B06F2A2F4A4
      digest_text: create table student (id int not null,name varchar(?) not null,age tinyint)
       count_star: 1
       first_seen: 1609245630
        last_seen: 1609245630
         sum_time: 18922
         min_time: 18922
         max_time: 18922
sum_rows_affected: 0
    sum_rows_sent: 0

*************************** 1. row ***************************
        hostgroup: 10
       schemaname: test_db
         username: proxysql
   client_address: 
           digest: 0x728E984EDA5CFAE6
      digest_text: insert into student (id,name,age) value(?,?,?)
       count_star: 1
       first_seen: 1609245834
        last_seen: 1609245834
         sum_time: 5892
         min_time: 5892
         max_time: 5892
sum_rows_affected: 1
    sum_rows_sent: 0

        hostgroup: 10
       schemaname: test_db
         username: proxysql
   client_address: 
           digest: 0xED5AABA3AB8F6C15
      digest_text: delete from student where id=?
       count_star: 1
       first_seen: 1609245917
        last_seen: 1609245917
         sum_time: 4328
         min_time: 4328
         max_time: 4328
sum_rows_affected: 1
    sum_rows_sent: 0

这里由于create,insert,delete都是写操作，且规则所属hostgroup=10，所以都是在master主机上运行，从而达到读写分离的效果