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

最新推荐文章于 2024-09-09 17:52:59 发布
最新推荐文章于 2024-09-09 17:52:59 发布
阅读量3.4k 收藏 4
点赞数
分类专栏： mysql 文章标签： mysql proxysql 读写分离 负载均衡
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CL_YD/article/details/80198766
版权

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>

库、表说明

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数据文件。
  • stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
  • monitor 库存储 monitor模块收集的信息，主要是对后端db的健康/延迟检查。

更多表介绍、配置介绍：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中确认监控用户名和密码

mysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor        |
| mysql-monitor_username | monitor        |
+------------------------+----------------+
2 rows in set (0.01 sec)

然后在所有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中确认监控用户名和密码

mysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor        |
| mysql-monitor_username | monitor        |
+------------------------+----------------+
2 rows in set (0.01 sec)

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

在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)

使配置生效

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

-- 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上：

mysql> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest where hostgroup>1000;
+-----------+---------------+-----------------------------+------------+
| hostgroup | username      | digest_text                 | count_star |
+-----------+---------------+-----------------------------+------------+
| 1002      | proxysql_msRW | select @@relay_log_basename | 1212       |
+-----------+---------------+-----------------------------+------------+
1 row in set (0.00 sec)

可以看到所有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账户一致：

mysql>  select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+-----------------------------------+----------------+
| variable_name                     | variable_value |
+-----------------------------------+----------------+
| admin-stats_credentials           | stats:stats    |<--账户密码
| admin-stats_mysql_connections     | 60             |
| admin-stats_mysql_connection_pool | 60             |
| admin-stats_mysql_query_cache     | 60             |
| admin-stats_system_cpu            | 60             |
| admin-stats_system_memory         | 60             |
| admin-web_enabled                 | true           |
| admin-web_port                    | 6080           |<--端口
+-----------------------------------+----------------+
8 rows in set (0.00 sec)

访问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。

暂不研究。

CL有毒
关注
专栏目录
ProxySQL+MHA搭建MySQL读写分离高可用集群
悦分享
01-09 706
ProxySQL是一个高性能的、高可用性MySQL中间件。ProxySQL是基于 MySQL 的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。允许轻松动态更新配置，便于运维管理，与MySQL兼容的管理界面可用于此目的；几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启proxysql；
Linux安装ProxySql（图文解说详细版）
编码人生
09-11 1063
ProxySQL是一个高性能的开源数据库代理，专门用于在数据库系统和客户端之间进行流量路由和负载均衡。它能够管理和优化数据库连接，提高应用程序的性能和可用性。ProxySQL支持多种数据库系统，包括MySQL、MariaDB、Percona等，使其成为了许多企业和开发者在构建大规模、高性能数据库架构时的首选工具。ProxySQL能够将客户端请求均匀地分配到多个后端数据库服务器上，从而实现负载均衡，提高数据库的吞吐量和响应能力。
ProxySQL离线安装包CENTOS6.5
12-07
ProxySQL离线安装包CENTOS6.5，包括所有依赖的包，
MySQL ProxySQL读写分离使用初探
weixin_34067102的博客
05-10 128
目的       在美团点评DBProxy读写分离使用说明文章中已经说明了使用目的，本文介绍ProxySQL的使用方法以及和DBProxy的性能差异。具体的介绍可以看官网的相关说明，并且这个中间件也是percona推的一款中间件。其特性和其他读写分离的中间件差距不大，具体的会在文中介绍。本文大致简单的介绍在使用过程中的一些说明，也可以看官方的wiki获得使用帮助。 环境：  Distri...
Docker方式部署ProxySQL和Keepalived组合实现MGR的高可用访问
最新发布
gzt19881123的专栏
09-09 1233
ProxySQL 代理访问MGR，可以做到故障自动切换主节点，可以自定义访问规则实现读写分离。
通过 ProxySQL 均衡负载 StarRocks， doris
DZH的博客
04-21 580
mysql -udev_user -pdev_pswd99 -h172.00.01.03 -P6032 // 通用用户登录。#(StarRocks 启动命令异常) ./usr/bin/proxysql -c ./etc/proxysql.cnf --no-monitor。mysql -u admin -padmin -h 127.0.0.1 -P6032 -- 这里是管理方式的登录。备注：这里登录的是ProxySQL(如果使用 StarRocks 账户是无法登录的)
6）、ProxySQL 方式负载均衡
Rainbow的博客
06-29 788
ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。Doris 的 FE 进程负责接收用户连接和查询请求，其本身是可以横向扩展且高可用的，但是需要用户在多个 FE 上架设一层proxy，来实现自动的连接负载均衡。
ProxySQL 安装配置详解及读写分离、负载均衡
藏经阁
07-13 3332
前言 在MySQL的高可用集群环境中，中间件是不可缺少的一部分，它提供了读写分离、负载均衡等各种功能，满足集群的横向、纵向的可扩展。由于官方并没有在这方面推出好的产品，更多的是第三方的产品。如： ProxySQL #Percona MaxScale #MariaDB Atlas #360开源 OneProxy #平民软件楼方鑫 MyCat #社区推广 KingShard #原Atlas作者离职后使用go开发 TDDL #阿里巴巴开源 Cobar #阿里巴巴开源 DBProxy #美团在3
mysql负载均衡-读写分离-ProxySQL
root9527
11-24 476
由于把所有的读和写请求都放在一台数据库上，数据库压力太大，而且写请求并不是频繁，同时写请求也不太好做分离，那么我们可以把读和写分开，在对读做一个负载集群，那么性能将会大大提高。
ProxySQL安装与读写分离教程：从下载到配置详解
ProxySQL是一款开源的MySQL代理服务器，它允许对数据库集群进行智能负载均衡和监控，适用于提高数据库性能并实现高可用性。 1. **环境准备** - ProxySQL服务器地址: 192.168.65.2 - 主库(Master): 192.168.65.3 ...
mysql-proxy完成负载均衡与读写分离
qq_43382364的博客
11-25 245
下载：https://downloads.mysql.com/archives/proxy/ 先加入点新知识 修改root的登录时的地址由localhost改为了192.168.%.% 下载完解压。 启动mysql_proxy 连接mysql_proxy 负载均衡 在连接上之后,做sql查询,却总是往某1台mysql server来发送------负载均衡没体现出来? 答: 不是没体现...
如何利用MHA+ProxySQL实现读写分离和负载均衡
华为云官方博客
04-06 4533
本文分享一下“MHA+中间件ProxySQL”如何来实现读写分离+负载均衡的相关知识。
mysql proxysql
数据库笔记
09-12 3859
proxy最大的作用是实现mysql的读写分离。其他可用作负载均衡，故障分析，查询分析，查询过滤和修改等。 位于客户端和mysql服务端之间。 mysql proxy作为一个中间代理，一个连接池，负责将前台应用的连接请求转发给后台的数据库。 并通过lua脚本，实现复杂的连接控制和过滤，从而实现读写分离和负载均衡。 对应用程序是透明的，只需要把应用连接到proxy的监听端口即可。 安装： 配...
mysql proxy负载均衡_（5.20）mysql中的ProxySQL实现读写分离与读负载均衡【转】
weixin_36303462的博客
02-07 346
1、ProxySQL 介绍ProxySQL 是基于 MySQL 的一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载(无需重启 ProxySQL 服务)，故障切换和一些 SQL 的过滤功能。介绍：相关 ProxySQL 的网站2、环境准备全新初始化的实例IPserver-id数据库版本备注10.0...
proxysqlCentos7离线安装包汇总
03-22
proxysqlCentos7离线安装包汇总
负载均衡proxy
HolyAngemon的博客
05-13 374
1
基于ProxySQL读写分离实现
qiuxinbo的博客
08-21 991
基于ProxySQL-SQL语句的读写分离实现 前段时间与一个同事聊天，过程中却被吐槽。 因为自己的博客中空空如也，一直是技术类文章忠实的读者和使用者，自己却不曾有什么分享 有点惭愧… 最近刚好完成了一些项目，特此做些总结和分享 （附上Larry Ellison的照片，吐槽下MySQL Proxy就别自己写了，这么多年还没有GA版本，还是买吧，毕竟有钱~） 1、读写分离概述 读写分离是数据库架构...
MySQL配置proxy读写分离负载均衡
thedman的专栏
01-16 452
前文讲了配置master/slave模式的mysql，现在在此基础上配置proxy，使得master和slave读写分离和负载均衡 环境再次介绍 192.168.153.129  master 192.168.153.130  slave 192.168.153.128  proxy 一、安装mysql-proxy 我是直接从官网上下载的二进制包，免去了make build的过
高级运维工程师教你centos7离线安装proxysql方法最详细
nasen512的博客
03-22 670
MySQL数据库代理技术proxysql安装部署
大型网站的负载均衡器、db proxy和db
热门推荐
longxibendi的专栏
08-06 1万+
大型网站的负载均衡器、db proxy和db 本文主要分析网站后台架构中的负载均衡器，企业常用的硬件负载均衡器软件负载均衡器、数据库代理服务器和数据库。 1.1 负载均衡 在大型网站部署中，负载均衡至少有三层部署。第一层为web server或者缓存代理之上的负载均衡，第二
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值