MySQL架构之 主从+ProxySQL实现读写分离

时间：2022-01-19 09:30:34

准备服务器：

docker run -d --privileged -v `pwd`/mysql_data:/data -p 3001:3306 --name mysql5-master --hostname mysql5-master --net staticnet --ip 192.168.0.101 eiki/mysql:5.7.23 /usr/sbin/init

docker run -d --privileged -v `pwd`/mysql_data:/data -p 3002:3306 --name mysql5-slave --hostname mysql5-slave --net staticnet --ip 192.168.0.102 eiki/mysql:5.7.23 /usr/sbin/init

docker run -d --privileged -v `pwd`/mysql_data:/data -p 3003:3306 --name mysql5-s2 --hostname mysql5-s2 --net staticnet --ip 192.168.0.103 eiki/mysql:5.7.23 /usr/sbin/init

docker run -d --privileged -v `pwd`/mysql_data:/data -p 6032:6032 --name proxysql --hostname proxysql --net staticnet --ip 192.168.0.201 eiki/mysql:5.7.23 /usr/sbin/init

docker run -d --privileged -v `pwd`/mysql_data:/data -p 7032:6032 -p 6080:6080 --name proxysql2 --hostname proxysql2 --net staticnet --ip 192.168.0.202 eiki/proxysql:latest /usr/sbin/init
其中6032是管理端口，6080是http端口

主从搭建

安装过程略

主库my.cnf

[mysql]

prompt = [\\u@\\h][\\d]>\\_

port = 3306

socket = /usr/local/mysql/mysql.sock

[mysqld]

user = mysql

port = 3306

server-id = 1

pid-file = /usr/local/mysql/mysqld.pid

socket = /usr/local/mysql/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

log-bin = master-bin

log-bin-index = master-bin.index

从库my.cnf

[mysql]

prompt = [\\u@\\h][\\d]>\\_

port = 3306

socket = /usr/local/mysql/mysql.sock

[mysqld]

user = mysql

port = 3306

server-id = 101

pid-file = /usr/local/mysql/mysqld.pid

socket = /usr/local/mysql/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

relay-log-index = slave-relay-bin.index

relay-log = slave-relay-bin

创建复制用户

主库上执行：

create user repl@'%' identified by 'repl';

grant replication slave on *.* to repl@'%';

flush privileges;

复制配置

从库上执行：

CHANGE MASTER TO

MASTER_HOST='192.168.0.101',

MASTER_USER='repl',

MASTER_PASSWORD='repl',

MASTER_LOG_FILE='master-bin.000007',

MASTER_LOG_POS=739

--for channel 's1'

;

ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.
需要在从库配置下面两个参数
master_info_repository =table
relay_log_info_repository =table

创建管理账号

grant replication slave,reload,create user,super on *.* to mats@'%' identified by 'mats' with grant option;

主从架构图

MySQL架构之 主从+ProxySQL实现读写分离

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

服务管理

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

修改配置文件

配置文件层级

MySQL架构之 主从+ProxySQL实现读写分离

简单说就是配置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.0.201

mysql -h 192.168.0.201 -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.0.201

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信息

-- 集群中有三个节点
192.168.0.101:3306
192.168.0.102:3306
192.168.0.103:3306

-- proxysql安装在
192.168.0.201:6032

-- 登录到192.168.0.201，使用远程连接用户radmin登入

# mysql -h192.168.0.201 -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
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.101','3306',1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.102','3306',1,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.0.103','3306',1,'Read Group');

-- 特别注意这里两条数据的hostgroup_id是一样的，权重weight也是一样的，前者保证同一个group以便负载均衡，后者权重可以影响负载均衡结果
mysql> select hostgroup_id,hostname,weight from mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname | weight |
+--------------+---------------+--------+
| 1 | 192.168.0.101 | 1 |
| 1 | 192.168.0.102 | 1 |
| 1 | 192.168.0.103 | 1 |
+--------------+---------------+--------+
3 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.0.201 -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对应
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1);
使配置生效

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

# mysql -h192.168.0.201 -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 @@server_id;

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.0.201 -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.0.201 -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.0.101:3306

-- slaves
192.168.0.102:3306
192.168.0.103:3306

在proxysql中添加数据库server信息

-- 登录到192.168.0.201，使用远程连接用户radmin登入
# mysql -h192.168.0.201 -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,comment) values(1001,'192.168.0.101','3306',1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1002,'192.168.0.101','3306',1,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1002,'192.168.0.102','3306',2,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1002,'192.168.0.103','3306',2,'Read Group');

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.0.101 | 1 |
| 1002 | 192.168.0.101 | 1 |
| 1002 | 192.168.0.102 | 2 |
| 1002 | 192.168.0.103 | 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.0.201 -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账户
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql_msRW', 'proxysql_msRW', 1001);

在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.0.201 -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 -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e 'drop schema if exists test';
mysql -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e "create schema if not exists test_rw";
mysql -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e "create table if not exists test_rw.test_rw(id int(11) not null,primary key(id)) engine=innodb charset=utf8 comment '测试读写分离'";
mysql -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (1)";
mysql -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (2)";

...

/mysql/app/xftest/program/bin/mysql -h192.168.0.201 -P6033 -uproxysql_msRW -pproxysql_msRW -e "insert into test_rw.test_rw(id) values (6)";

/mysql/app/xftest/program/bin/mysql -h192.168.0.201 -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.0.201 -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.0.201 -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.0.201 -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;
1
查询规则首先匹配/* 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.0.201 -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.0.201 -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.0.201: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!");
}
}

开启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.0.201:6080并使用stats:stats登录即可查看一些统计信息。

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。

暂不研究。

相关文章