Proxysql|mysql读写分离 – Wakamizu's Blog

实验环境

主机名/IP	角色
server01/192.168.1.201	主
server02/192.168.1.202	从
server03/192.168.1.203	proxysql

主从同步部署请查阅：https://www.wakamizu.cn/mysql%e4%b8%bb%e4%bb%8e%e5%90%8c%e6%ad%a5/

服务基础配置

server01 && server02

创建测试用户

mysql -u root -p
create user testuser@'%' identified by 'wakamizu';
grant all privileges on world.* to testuser@'%';

创建监控用户

mysql -u root -p
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

server03

安装

wget https://github.com/sysown/proxysql/releases/download/v2.7.0/proxysql_2.7.0-ubuntu22_amd64.deb
apt install -y ./proxysql_2.7.0-ubuntu22_amd64.deb
apt-get update -y
apt-get install proxysql mysql-client -y

启动服务

systemctl start proxysql

进入管理端

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '

添加mysql服务器

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.201',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.202',3306);
SELECT * FROM mysql_servers;

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.201 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.1.202 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

添加mysql连接凭据

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

配置临时生效

LOAD MYSQL VARIABLES TO RUNTIME;

配置持久化

SAVE MYSQL VARIABLES TO DISK;

查看mysql服务状态

SELECT * FROM mysql_servers;

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.201 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.1.202 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 2;

+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.1.201 | 3306 | 1727018890913524 | 2427                    | NULL          |
| 192.168.1.202 | 3306 | 1727018890872968 | 2309                    | NULL          |
+---------------+------+------------------+-------------------------+---------------+

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 2;

+---------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 192.168.1.201 | 3306 | 1727018906115823 | 528 | NULL |
| 192.168.1.202 | 3306 | 1727018906115822 | 319 | NULL |
+---------------+------+------------------+----------------------+------------+

创建主机组

如果mysql服务器的read_only=0，则转移到写入分组1；如果mysql服务器的read_only=1，则转移到仅读分组2；

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'testcluster');
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

可以前往mysql从服务器节点设置read_only为1，然后执行命令查看，可以看到运行时的服务器状态表已经显示配置生效：

SELECT * FROM runtime_mysql_servers;

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.201 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.1.202 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

配置mysql用户

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','wakamizu',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
SELECT * FROM mysql_users;

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| testuser | wakamizu | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+

测试连接

mysql -u testuser -p -h 192.168.1.203 -P 6033

读写分离配置

删除规则

DELETE FROM mysql_query_rules;

使用不同端口进行读写分离

配置端口

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;

添加路由

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,1,1), (2,1,6402,2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

经过6401端口的流量全部访问写入分组1，经过6402端口的流量全部访问仅读分组2

使用正则表达式进行读写分离

UPDATE mysql_users SET default_hostgroup=1;
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

先设置mysql用户默认分组为写入分组1，然后根据正则表达式将所有select开头的操作都分到仅读分组2

分析语句执行成本后用摘要和正则表达式进行读写分离

先将所有流量传输到一个服务器，然后分析执行成本大的语句进行路由
stats_mysql_query_digest表提供每个后端的查询计数、每个查询的响应时间以及实际查询文本以及查询摘要，查询摘要是每个查询类型的唯一标识符

根据总执行时间查找前五个查询：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

根据计数查找前五个查询：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

根据最大执行时间查询前五个查询：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

查找按总执行时间排序且最短执行时间至少为 1 毫秒的前 5五个查询：

SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;

按总执行时间排序的前五个查询，平均执行时间至少为 1 秒，还显示总执行时间的百分比：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;

按总执行时间排序的前五个查询，平均执行时间至少为 15 毫秒，并显示占总执行时间的百分比：

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

接下来就可以将查询成本最大且重复多次的语句摘要转到仅读节点：

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

也可以评估所有SELECT COUNT(*)语句

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, 
       ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT COUNT%' ORDER BY sum_time DESC;

然后将所有以SELECT COUNT(*)开头的查询转移到仅读分组

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT COUNT\(\*\)',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

其他常用表

stats_mysql_connection_pool表显示与 MySQL 后端以及连接和总体流量相关的信息

SELECT * FROM stats.stats_mysql_connection_pool;

stats_mysql_commands_counters表返回有关执行的语句类型以及执行时间分布的详细信息

SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;