proxysql 安装、负载均衡和读写分离

约定不等于承诺〃 2022-05-25 00:44 305阅读 0赞

# proxysql 安装、负载均衡、读写分离和分库 #

*  proxysql 安装、负载均衡、读写分离和分库
    
     *  相关地址
     *  yum/rpm安装
     *  服务管理
     *  查看启动文件以及配置文件位置
     *  修改配置文件
        
         *  配置文件层级
         *  mysql\_ifaces
         *  admin\_credentials
     *  库、表说明
     *  负载均衡
        
         *  在proxysql中添加数据库server信息
         *  在数据库server中添加账号
         *  在proxysql中添加程序账号信息
         *  使配置生效
         *  验证负载均衡
         *  负载均衡过程解析
     *  读写分离
        
         *  在proxysql中添加数据库server信息
         *  在数据库server中添加账号
         *  在proxysql中添加程序账号信息
         *  在proxysql中添加路由规则
         *  使配置生效
         *  验证读写分离
     *  proxysql分库(same MySQL Server different schemas)
        
         *  模拟分库
         *  配置查询规则
         *  测试
     *  JDBC连接proxysql
     *  开启WEB统计
     *  scheduler打印状态到日志
     *  集群

## 相关地址 ##

*  [github/proxysql][github_proxysql]
 *  [github/releases][github_releases]
 *  [github/wiki][github_wiki]
 *  [proxysql][]

## yum/rpm安装 ##

在`github`或官网上可以下载`rpm`包，`wiki`的`Getting start`章节有详细介绍。

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/7
    gpgcheck=1
    gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    EOF
    
    yum clean all
    yum makecache
    yum install proxysql

同样可以借助`repotrack(yum install yum-tools)`和`createrepo(yum install createrepo)`来实现离线安装的。

## 服务管理 ##

service proxysql start service proxysql stop service proxysql status

## 查看启动文件以及配置文件位置 ##

rpm -ql proxysql
    
    /etc/init.d/proxysql    #proxysql的启动控制文件
    /etc/proxysql.cnf   #proxysql配置文件
    /usr/bin/proxysql
    /usr/share/proxysql/tools/proxysql_galera_checker.sh
    /usr/share/proxysql/tools/proxysql_galera_writer.pl

## 修改配置文件 ##

### 配置文件层级 ###

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

简单说就是配置`proxysql`分为三个级别，`RUNTIME`是即时生效的，`MEMORY`是保存在内存中但并不立即生效的，`DISK|CONFIG FILE`是持久化或写在配置文件中的。

这三个级别的配置文件互不干扰，在某个层级修改了配置文件，想要加载或保存到另一个层级，需要额外的`LOAD`或`SAVE`操作：`LOAD xx_config FROM xx_level | LOAD xx_config TO xx_level | SAVE xx_config TO xx_level | SAVE xx_config FROM xx_level`，达到加载配置或者持久化配置的目的。

*  `RUNTIME`层级的配置时在`proxysql`管理库(`sqlite`)的`main`库中以`runtime_`开头的表，这些表的数据库无法直接修改，只能从其他层级加载；
 *  `MEMORY`层级的配置在`main`库中以`mysql_`开头的表以及`global_variables`表，这些表的数据可以直接修改；
 *  `DISK|CONFIG FILR`层级的配置在磁盘上的`sqlite`库或配置文件里。

配置文件的修改流程一般是：

*  启动时：先修改必要的`CONFIG FILE`配置，比如管理端口，然后启动；
 *  其他配置：修改`MEMORY`中的表，然后加载到`RUNTIME`并持久化。

更多信息：[Configuring ProxySQL][]

### mysql\_ifaces ###

> First of all, bear in mind that the best way to configure ProxySQL is through its admin interface. This lends itself to online configuration (without having to restart the proxy) via SQL queries to its admin database. It’s an effective way to configure it both manually and in an automated fashion.  
> As a secondary way to configure it, we have the configuration file.

也就是说`proxysql`有一个`admin`接口专门来做配置，相当于一个`mysql shell`可以通过`sql`来让配置实时生效。

`mysql_ifaces`配置了允许连接`proxysql`的`ip`和`port`：

vi /etc/proxysql.cnf
    
    # 将admin_variables中的mysql_ifaces修改成允许远程访问
    # mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
           mysql_ifaces="0.0.0.0:6032"

如果`ip`配置为`0.0.0.0`表示不限制`ip`，但是出于安全考虑，`admin`用户无论怎么设置都只能在本机登录，要想远程登录请看下一小节。

### admin\_credentials ###

这个`key`保存所有可以操作`proxysql`的用户名和密码，格式为：`user:pass;user1:pass1`，这里可以修改密码或定义一个非`admin`的用户用于远程登录。  
首先保证想要管理`proxysql`的机器安装有`mysql client`：

-- 先在本机登录
    -- 本机IP为192.168.20.6
    mysql -uadmin -padmin -P6032
    (u@h:p) [d]> update global_variables set variable_value = 'admin:admin;radmin:radmin' where variable_name = 'admin-admin_credentials';
    Query OK, 1 row affected (0.00 sec)
    
    (u@h:p) [d]> LOAD ADMIN VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    (u@h:p) [d]> SAVE ADMIN VARIABLES TO DISK;
    Query OK, 31 rows affected (0.00 sec)
    
    (u@h:p) [d]>

这样就可以使用下面的命令在其他机器上使用`radmin`用户登录（其他机器上也需要有mysql client）：

mysql -uradmin -pradmin -P6032 -h192.168.20.6
    mysql>

## 库、表说明 ##

*  `main` 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以`runtime_`开头的表示`proxysql`当前运行的配置内容，不能通过`dml`语句修改，只能修改对应的不以 `runtime_`开头的（在内存）里的表，然后`LOAD` 使其生效， `SAVE` 使其存到硬盘以供下次重启加载。
 *  `disk` 是持久化到硬盘的配置，sqlite数据文件。
 *  `stats` 是`proxysql`运行抓取的统计信息，包括到后端各命令的执行次数、流量、`processlist`、查询种类汇总/执行时间等等。
 *  `monitor` 库存储 `monitor`模块收集的信息，主要是对后端`db`的健康/延迟检查。

更多表介绍、配置介绍：[MySQL ProxySQL读写分离使用初探][MySQL ProxySQL]

## 负载均衡 ##

### 在proxysql中添加数据库server信息 ###

-- 在galera上测试，集群中有两个节点
    192.168.11.118:3333
    192.168.11.119:3333
    
    -- proxysql安装在
    192.168.20.6:6032

-- 登录到192.168.20.7，使用远程连接用户radmin登入
    # mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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> show tables;
    +--------------------------------------------+
    | tables                                     |
    +--------------------------------------------+
    | global_variables                           |
    | mysql_collations                           |
    | 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_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                                  |
    +--------------------------------------------+
    20 rows in set (0.00 sec) -- 这里用到mysql_servers mysql> INSERT INTO mysql_servers ( hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment ) VALUES ( 100, '192.168.11.118', 3333, 1, 1000, 10, 'test proxysql' ),( 100, '192.168.11.119', 3333, 1, 1000, 10, 'test proxysql' );
    -- 特别注意这里两条数据的hostgroup_id是一样的，权重weight也是一样的，前者保证同一个group以便负载均衡，后者权重可以影响负载均衡结果
    mysql> select hostgroup_id,hostname,weight from mysql_servers;
    +--------------+----------------+--------+
    | hostgroup_id | hostname       | weight |
    +--------------+----------------+--------+
    | 100          | 192.168.11.118 | 1      |
    | 100          | 192.168.11.119 | 1      |
    +--------------+----------------+--------+
    2 rows in set (0.00 sec)

### 在数据库server中添加账号 ###

首先在`proxysql`中确认监控用户名和密码

然后在所有`server`中添加账号：监控账号和程序账号，其中监控账号只需要`USAGE`权限；程序账号是开放给用户使用的，需要业务要求的权限。两者的用户名和密码都可以任意修改，但是配置的地方不一样，前者在`global_variables`表，后者在`mysql_users`表。

在任意一个节点操作即可，因为是`galera`集群，其他节点会自动同步：

# /mysql/app/proxysql_galera/program/bin/mysql -uroot -p1234 -S /mysql/app/proxysql_galera/program/proxysql_galera.sock
    
    -- 监控账户
    mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT USAGE ON *.* TO 'monitor'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    -- 程序账户
    mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT ALL ON *.* TO 'proxysql'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT user,host FROM mysql.user;
    +---------------+-----------+
    | user          | host      |
    +---------------+-----------+
    | monitor       | %         |
    | proxysql      | %         |
    | root          | %         |
    | mysql.session | localhost |
    | mysql.sys     | localhost |
    +---------------+-----------+
    5 rows in set (0.00 sec)

### 在proxysql中添加程序账号信息 ###

# mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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
    
    -- 这里需要注意，default_hostgroup需要和上面的mysql_servers.hostgroup_id对应
    mysql> INSERT INTO mysql_users ( username, password, active, default_hostgroup, transaction_persistent ) VALUES ('proxysql', 'proxysql', 1, 100, 1);

### 使配置生效 ###

因为修改了`mysql_users`和`mysql_servers`(还可能修改了`global_variables`)，这三个表在上面的配置小节中可以找到是属于`Memory`级别的，如果需要立即生效，需要加载到`Runtime`；同时应该持久化到`Disk`。

# mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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
    
    -- Memory -> Runtime
    mysql> LOAD MYSQL SERVERS TO RUNTIME;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> LOAD MYSQL USERS TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> LOAD MYSQL VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    -- Memory -> Disk
    mysql> SAVE MYSQL SERVERS TO DISK;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SAVE MYSQL USERS TO DISK;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SAVE MYSQL VARIABLES TO DISK;
    Query OK, 94 rows affected (0.00 sec)

### 验证负载均衡 ###

`proxysql`的`6032`端口是管理入口，`6033`端口就是客户端入口。

先写一个`sql`脚本查询一个可以区分到底调用到哪个主机的参数：

vi test_proxysql_lb.sql
    
    select @@relay_log_basename;
    
    ESC
    :wq

再写一个`shell`脚本循环`200`次调用这个`sql`语句并把文件输出到`/tmp/test_proxysql_lb.txt`:

#!/bin/bash 
    i=0
    while(($i<200))
    do
            /mysql/app/test/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql -pproxysql < test_proxysql_lb.sql >> /tmp/test_proxy_sql_lb.txt
            let "i++"
            echo "$i"
            sleep 0.1
    done

执行后检查结果：

# grep -nc '118' /tmp/test_proxy_sql_lb.txt 
    103
    
    # grep -nc '119' /tmp/test_proxy_sql_lb.txt 
    97

结果符合预期。

### 负载均衡过程解析 ###

*  首先，`proxysql`接收到来自`6033`端口`proxysql`用户的请求：`mysql -h192.168.20.6 -P6033 -uproxysql -pproxysql < test_proxysql_lb.sql`
 *  因为`mysql_user`表`username + frontend`是唯一键，`proxysql`查到这个用户绑定的`hostgroup_id`为`100`，因此会被应用到`192.168.11.118`或`192.168.11.119`上
 *  根据`server`的权重来做负载均衡，分配到对应的主机。
 *  这里没有配置`mysql_query_rules`，默认使用`mysql_users`中用户的`default_hostgroup`，可以在`mysql_query_rules`中添加路由规则，让匹配到规则的用户、`sql`、代理端口等等路由到特定的组。

## 读写分离 ##

基于上面的负载均衡，一起配置负载均衡和读写分离，分配以下主机：

-- 一主多从master
    192.168.20.7:3344
    
    -- slaves
    192.168.11.119:3344
    192.168.20.8:3344

### 在proxysql中添加数据库server信息 ###

-- 登录到192.168.20.8，使用远程连接用户radmin登入
    # mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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
    
    -- 插入server信息，其中master和slave的hostgroup_id要不一样
    INSERT INTO mysql_servers ( hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment ) VALUES ( 1001, '192.168.20.7', 3344, 1, 1000, 10, 'master:test proxysql rw split' ),( 1002, '192.168.11.119', 3344, 2, 1000, 10, 'slave:test proxysql rw split' ),( 1002, '192.168.20.8', 3344, 2, 1000, 10, 'slave:test proxysql rw split' ),( 1002, '192.168.20.7', 3344, 1, 1000, 10, 'master:test proxysql rw split' );
    Query OK, 3 rows affected (0.00 sec)
    
    mysql> select hostgroup_id,hostname,weight from mysql_servers where hostgroup_id >= 1001;
    -- 这里准备把主库也配置为可读，配置较低的权重
    +--------------+----------------+--------+
    | hostgroup_id | hostname       | weight |
    +--------------+----------------+--------+
    | 1001         | 192.168.20.7   | 1      |
    | 1002         | 192.168.20.7   | 2      |
    | 1002         | 192.168.11.119 | 1      |
    | 1002         | 192.168.20.8   | 2      |
    +--------------+----------------+--------+
    4 rows in set (0.00 sec)

### 在数据库server中添加账号 ###

首先在`proxysql`中确认监控用户名和密码

在`master`节点操作即可，因为是`mgr`单主集群，其他节点会自动同步：

# /mysql/app/proxysql_ms_test/program/bin/mysql -uroot -p1234 -S /mysql/app/proxysql_ms_test/program/proxysql_ms_test.sock
    
    -- 监控账户
    mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT USAGE ON *.* TO 'monitor'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    -- 程序账户，拥有所有业务权限（这里给ALL）
    mysql> CREATE USER 'proxysql_msRW'@'%' IDENTIFIED BY 'proxysql_msRW';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT ALL ON *.* TO 'proxysql_msRW'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT user,host FROM mysql.user;
    +---------------+-----------+
    | user          | host      |
    +---------------+-----------+
    | monitor       | %         |
    | proxysql_msRW | %         |
    | root          | %         |
    | rpuser        | %         |
    | mysql.session | localhost |
    | mysql.sys     | localhost |
    +---------------+-----------+
    7 rows in set (0.01 sec)

### 在proxysql中添加程序账号信息 ###

# mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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
    
    -- 这里需要注意，default_hostgroup需要和上面的mysql_servers.hostgroup_id对应，master使用RW账户，slave使用R账户
    mysql> INSERT INTO mysql_users ( username, password, active, default_hostgroup, transaction_persistent ) VALUES ('proxysql_msRW', 'proxysql_msRW', 1, 1001, 1);
    Query OK, 1 rows affected (0.00 sec)

### 在proxysql中添加路由规则 ###

读写规则：以`select`开头的且不以`for update`结尾的语句一律路由到`slave`节点，其他的路由到`master`节点。

INSERT INTO mysql_query_rules(active,username,match_pattern,destination_hostgroup,apply) VALUES(1,'proxysql_msRW','^SELECT.*FOR UPDATE$',1001,1),(1,'proxysql_msRW','^SELECT',1002,1);
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> select destination_hostgroup hostgroup,username,match_patternfrom mysql_query_rules where destination_hostgroup >= 1001;
    +-----------+---------------+----------------------+
    | hostgroup | username      | match_pattern        |
    +-----------+---------------+----------------------+
    | 1001      | proxysql_msRW | ^SELECT.*FOR UPDATE$ | | 1002 | proxysql_msRW | ^SELECT | +-----------+---------------+----------------------+ 2 rows in set (0.00 sec)

### 使配置生效 ###

-- Memory -> Runtime
    mysql> LOAD MYSQL SERVERS TO RUNTIME;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> LOAD MYSQL USERS TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> LOAD MYSQL VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    -- Memory -> Disk
    mysql> SAVE MYSQL SERVERS TO DISK;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SAVE MYSQL USERS TO DISK;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SAVE MYSQL VARIABLES TO DISK;
    Query OK, 94 rows affected (0.00 sec)

### 验证读写分离 ###

先验证负载均衡是否是正常的：

#!/bin/bash 
    i=0
    while(($i<500))
    do
            /mysql/app/test/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e 'select @@relay_log_basename' >> /tmp/test_proxy_sql_lb.txt
            let "i++"
            echo "$i"
            sleep 0.1
    done

和负载均衡一样执行后，统计行数：

#11.119
    grep -nc 'mysql119' /tmp/test_proxy_sql_lb.txt 
    176
    
    #20.8
    grep -nc 'qwer1' /tmp/test_proxy_sql_lb.txt 
    223
    
    #20.7
    grep -nc 's011' /tmp/test_proxy_sql_lb.txt
    101

大致是`2:2:1`，主节点的查询是从节点的`1/2`。  
上面的查询可以用来验证`select`是否被正确地路由，在`proxysql`上：

可以看到所有`select`都被路由到了`1002(slave+master)`

然后验证读写分离

/mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e 'drop schema test';
    
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "create schema test_rw";
    
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "create table test_rw.test_rw(id int(11) not null,primary key(id)) engine=innodb charset=utf8 comment '测试读写分离'";
    
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (1)";
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (2)";
    
    ...
    
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (6)";
    
    /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "delete from test_rw.test_rw";
    
    -- 添加500条数据
    #!/bin/bash 
    i=0
    while(($i<500))
    do
            /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values ($i)" >> /tmp/test_proxy_sql_lb.txt
            let "i++"
            echo "$i"
            sleep 0.1
    done
    
    -- 查询500次
    #!/bin/bash 
    i=0
    while(($i<500))
    do
            /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "select * from test_rw.test_rw" >> /tmp/test_proxy_sql_lb.txt
            let "i++"
            echo "$i"
            sleep 0.1
    done
    
    -- select for update 500次
    #!/bin/bash 
    i=0
    while(($i<500))
    do
            /mysql/app/xftest/program/bin/mysql -h192.168.20.6 -P6033 -uproxysql_msRW -pproxysql_msRW -e "select * from test_rw.test_rw limit 1 for update;commit" >> /tmp/test_proxy_sql_lb.txt
            let "i++"
            echo "$i"
            sleep 0.1
    done

在`proxysql`查询统计和命中次数：

mysql> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest where hostgroup > 1000;
    
    +-----------+--------------------------------------------------+------------+
    | hostgroup | digest_text                                      | count_star |
    +-----------+--------------------------------------------------+------------+
    | 1001      | insert into test_rw.test_rw(id) values (?) | 506 | | 1001 | create table test_rw.test_rw(id int(?) ... | 1 | | 1001 | create schema test_rw | 1 | | 1001 | delete from test_rw.test_rw | 1 | | 1001 | select * from test_rw.test_rw limit ? for update | 502 | | 1001 | drop schema test | 1 | | 1001 | commit | 502 | | 1002 | select * from test_rw.test_rw | 500 | +-----------+--------------------------------------------------+------------+

都正确分配到了对应的主机上。

## proxysql分库(same MySQL Server different schemas) ##

分库和读写分离基本类似，都是配置规则，路由到不同的机器。

分库可以分为同实例不同库、不同实例相同库、不同实例不同库，这里暂时只研究同实例不同库，其它种情况可以参考这里：[MySQL Sharding with ProxySQL][]

### 模拟分库 ###

场景：假设某一个业务流程需要记录流水记录，每天的流水非常多，如果放在同一张表，很快就会爆炸，所以按照月份分库，同一个月的数据放在一张表里。

依然使用负载均衡小节的`galera`：

mysql> create schema user_201804;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create schema user_201803;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create schema user_201802;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> create schema user_201801;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use user_201801;
    Database changed
    mysql> CREATE TABLE `user_op_history` ( -> `id` INT (20) NOT NULL AUTO_INCREMENT, -> `user_id` INT (20) NOT NULL COMMENT "用户ID", -> `operate_type` VARCHAR (64) NOT NULL COMMENT '操作类型', -> `operate_time` datetime NOT NULL COMMENT '操作时间', -> PRIMARY KEY (`id`) -> ) ENGINE = INNODB CHARSET = UTF8 COMMENT '用户操作流水表';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> INSERT INTO user_op_history(user_id,operate_type,operate_time) VALUES (65535,"login","2018-01-01 00:00:01"),(65535,"pay","2018-01-01 00:00:10"),(65535,"logout","2018-01-01 00:01:01");
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    -- 其他三个库做类似操作

### 配置查询规则 ###

查询时在`sql`里指定年月(`YYYYMM`)，`proxysql`根据年月映射到对应的库上执行`sql`，比如查询`201801`的数据规定这样查询：

select /* month=201801 */ * from user.user_op_history;

查询规则首先匹配`/* month=201801 */`，遇到这样注释，表明查询时需要分库，进入规则链；然后一级一级替换或者循环替换`user.`为`user_201801.`。

`proxy_sql`规则是可以循环调用自己做替换的，关键是`mysql_query_rules`表的三个字段：`apply,flagIN,flagOUT`，原理如下：  
1. 一个`sql`查询通过`proxysql`时，`proxysql`首先找`flagIN=0`的规则进行匹配，如果没有找到，就直接使用原`sql`到目标库查询；  
2. 匹配到后，做相应规则替换，如果规则的`apply=1`，那么本次替换后的`sql`将被放到目标库执行；  
3. 如果`apply=0`且`flagOUT!=0`，表示替换后进入下一个`flagIN=X`的规则，如果匹配到多条，则选择第一个找到的规则；  
4. 继续3中的步骤，直到`apply=1`或`flagOUT=0`或者规则链迭代次数超过`mysql-query_processor_iterations`定义的最大次数；  
5. 应用最终的`sql`到目标库。

首先配置`mysql-query_processor_iterations=10`：

# mysql -h192.168.20.6 -P6032 -uradmin -pradmin
    
    mysql> use main;
    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> update global_variables set variable_value=10 where variable_name='mysql-query_processor_iterations';
    
    mysql> LOAD MYSQL VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SAVE MYSQL VARIABLES TO DISK;
    Query OK, 94 rows affected (0.00 sec)

然后插入查询规则：

mysql> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply,FlagOUT,FlagIN) VALUES (101,1,'proxysql',"\S*\s*\/\*\s*month=(\d+)\s*\*.*",null,0,1001,0),(102,1,'proxysql','(\S*\s*\/\*\s*month=(\d+)\s*\*.*)user\.(.*)','\1user_\2.\3',0,1001,1001);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SAVE MYSQL QUERY RULES TO DISK;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select match_pattern,replace_pattern,apply,flagIN,flagOUT from mysql_query_rules;
    +---------------------------------------------+-----------------+-------+--------+---------+
    | match_pattern                               | replace_pattern | apply | flagIN | flagOUT |
    +---------------------------------------------+-----------------+-------+--------+---------+
    | \S*\s*\/\*\s*month=(\d+)\s*\*.*             | NULL            | 0     | 0      | 1001    |
    | (\S*\s*\/\*\s*month=(\d+)\s*\*.*)user\.(.*) | \1user_\2.\3    | 0     | 1001   | 1001    |
    +---------------------------------------------+-----------------+-------+--------+---------+

*  插入的两条规则，上面一条`flagIN=0`，匹配到`/* month=(\d+) */`将进入规则链，不做任何替换`replace_pattern=NULL`；
 *  然后进入`flagIN=1001`的规则，替换`user.`为`user_${month}.`；
 *  接着继续进入`flagIN=1001`的规则，循环替换，直到匹配失败，或者超过迭代次数限制；

### 测试 ###

-- 这里特别注意，在command line执行sql一定要带上--comments参数，否则会skip comments
    # /mysql/app/test/program/bin/mysql --comments -h192.168.20.6 -P6033 -uproxysql -pproxysql
    
    mysql> select /* month=201801 */ * from user.user_op_history;
    +----+---------+--------------+---------------------+
    | id | user_id | operate_type | operate_time        |
    +----+---------+--------------+---------------------+
    |  1 |   65535 | login        | 2018-01-01 00:00:01 |
    |  3 |   65535 | pay          | 2018-01-01 00:00:10 |
    |  5 |   65535 | logout       | 2018-01-01 00:01:01 |
    +----+---------+--------------+---------------------+
    3 rows in set (0.00 sec) mysql> select /* month=201802 */ a.operate_time,b.operate_time from user.user_op_history a left join user.user_op_history b ON 1=1;
    +---------------------+---------------------+
    | operate_time        | operate_time        |
    +---------------------+---------------------+
    | 2018-02-05 11:10:01 | 2018-02-05 11:10:01 |
    | 2018-02-10 00:00:10 | 2018-02-05 11:10:01 |
    | 2018-02-11 12:01:01 | 2018-02-05 11:10:01 |
    | 2018-02-05 11:10:01 | 2018-02-10 00:00:10 |
    | 2018-02-10 00:00:10 | 2018-02-10 00:00:10 |
    | 2018-02-11 12:01:01 | 2018-02-10 00:00:10 |
    | 2018-02-05 11:10:01 | 2018-02-11 12:01:01 |
    | 2018-02-10 00:00:10 | 2018-02-11 12:01:01 |
    | 2018-02-11 12:01:01 | 2018-02-11 12:01:01 |
    +---------------------+---------------------+
    9 rows in set (0.00 sec) mysql> insert into /* month=201802 */ user.user_op_history(user_id,operate_type,operate_time) values (95553,"post","2018-02-28 11:11:11");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * /* month=201802 */ from user.user_op_history;
    +----+---------+--------------+---------------------+
    | id | user_id | operate_type | operate_time        |
    +----+---------+--------------+---------------------+
    |  1 |   95553 | login        | 2018-02-05 11:10:01 |
    |  3 |   65535 | pay          | 2018-02-10 00:00:10 |
    |  5 |   95553 | comment      | 2018-02-11 12:01:01 |
    |  7 |   95553 | post         | 2018-02-28 11:11:11 |
    +----+---------+--------------+---------------------+
    4 rows in set (0.00 sec) mysql> update /* month=201802 */ user.user_op_history set operate_time="2018-02-28 12:12:12" where id=7;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * /* month=201802 */ from user.user_op_history;
    +----+---------+--------------+---------------------+
    | id | user_id | operate_type | operate_time        |
    +----+---------+--------------+---------------------+
    |  1 |   95553 | login        | 2018-02-05 11:10:01 |
    |  3 |   65535 | pay          | 2018-02-10 00:00:10 |
    |  5 |   95553 | comment      | 2018-02-11 12:01:01 |
    |  7 |   95553 | post         | 2018-02-28 12:12:12 |
    +----+---------+--------------+---------------------+
    4 rows in set (0.00 sec)

## JDBC连接proxysql ##

package com.enmo.dbaas;
    
    import java.sql.*;
    import java.util.Scanner;
    
    /** * Hello world! */
    public class App { 
    
        private final static String JDBC_URL = "jdbc:mysql://192.168.20.6:6033";
        private final static String USERNAME = "proxysql";
        private final static String PASSWORD = "proxysql";
    
        static {
            try {
                Class.forName("com.mysql.jdbc.Driver");
    
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    
    
        public static void main(String[] args) throws SQLException {
            Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            Statement st = conn.createStatement();
    
            String sql = null;
            do {
                sql = new Scanner(System.in).nextLine();
                if (sql != null && !sql.trim().isEmpty()) {
                    sql = sql.trim();
                    System.out.println(sql);
    
                    if (sql.startsWith("select")) {
                        ResultSet resultSet = st.executeQuery(sql);
                        System.out.println("id\tuser_id\toperate_type\toperate_time");
                        while (resultSet.next()) {
                            System.out.println(
                                            resultSet.getString("id") +
                                            "\t" +
                                            resultSet.getString("user_id") +
                                            "\t" +
                                            resultSet.getString("operate_type") +
                                            "\t" +
                                            resultSet.getString("operate_time")
                            );
                        }
                    } else if (sql.startsWith("update")) {
                        Integer rows = st.executeUpdate(sql);
                        System.out.println("Query OK, " + rows + " row affected");
                    } else {
                        System.out.println("Query " + (st.execute(sql) ? "OK" : "FAILED"));
                    }
    
                }
            } while (sql != null && !sql.trim().isEmpty());
    
    
            System.out.println("Bye!");
        }
    }

![sql][]

## 开启WEB统计 ##

首先打开`web`功能

mysql> update global_variables set variable_value='true' where variable_name='admin-web_enabled';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> LOAD ADMIN VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SAVE ADMIN VARIABLES TO DISK;
    Query OK, 31 rows affected (0.00 sec)

然后查看端口和登录`web`界面的用户名和密码，用户名和密码与`stat`账户一致：

访问`192.168.20.6:6080`并使用`stats:stats`登录即可查看一些统计信息。  
![prxysql][]

## scheduler打印状态到日志 ##

先定义一个脚本，往日志里面写数据：

vi /log/app/dbaas/proxysql/status.sh
    
    
    #!/bin/bash
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    echo "{\"dateTime\":\"$DATE\",\"status\":\"running\"}">> /log/app/dbaas/proxysql/status.log
    
    ESC
    :wq
    
    chmod 777 /log/app/dbaas/proxysql/status.sh

然后在`proxysql`插入一条`scheduler`：

mysql> insert into scheduler(active,interval_ms,filename) values (1,60000,'/log/app/dbaas/proxysql/status.sh');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> LOAD SCHEDULER TO RUNTIME;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SAVE SCHEDULER TO DISK;
    Query OK, 0 rows affected (0.01 sec)

查看日志就可以看到结果了：

{
       "dateTime":"2018-04-20 16:12:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:13:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:14:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:15:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:16:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:17:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:18:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:19:32","status":"running"}
    {
       "dateTime":"2018-04-20 16:20:32","status":"running"}

## 集群 ##

`proxysql`可以配置集群实现高可用[ProxySQL Cluster][]。

暂不研究。

[github_proxysql]: https://github.com/sysown/proxysql
[github_releases]: https://github.com/sysown/proxysql/releases
[github_wiki]: https://github.com/sysown/proxysql/wiki
[proxysql]: http://www.proxysql.com/
[Configuring ProxySQL]: https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
[MySQL ProxySQL]: https://www.cnblogs.com/zhoujinyi/p/6829983.html
[MySQL Sharding with ProxySQL]: https://www.percona.com/blog/2016/08/30/mysql-sharding-with-proxysql/
[sql]: /images/20220525/6d326c695dd649aea5b2bcf9b724fa68.png
[prxysql]: /images/20220525/e339bd4675db45ab8a97100ae0c28497.png
[ProxySQL Cluster]: https://github.com/sysown/proxysql/wiki/ProxySQL-Cluster