世界上只有一种英雄主义,就是看清生活的真相之后依然热爱生活!

使用 ProxySQL 代理 MariaDB 实现读写分离和负载均衡

2019-07-29
52次查阅
2019/7/31

在 ProxySQL V2.0.0 以上版本可以原生支持 galera 集群,不再需要 scheduler 调度程序中使用外部脚本。

本文实验环境拓扑图:

代理层主机组规划:

  • hostgroup 1(宕机组)
  • hostgroup 2(写组)
  • hostgroup 3(读组)
  • hostgroup 4(备份写组)

后端数据库规划:

  • 192.168.0.3(node-1):负责写
  • 192.168.0.4(node-2):备份写
  • 192.168.0.5(node-3):负责读
  • 192.168.0.6(node-4):负责读

主机组分配和权重规划(hg代表主机组):

  • node-1 / hg:2 / 权重:100
  • node-2 / hg:2 / 权重:10
  • node-3 / hg:3 / 权重:100
  • node-4 / hg:3 / 权重:100

准备工作

使用原生支持 galera 集群功能,必须将读取组内节点配置为只读实例,本文中为 node-3 和 node-4:

在后端节点/etc/my.cnf添加配置:

[mysqld]
read_only = on

保存重启数据库使其生效。

ProxySQL 配置

如果ProxySQL使用集群模式,需要先配置好集群,集群和keepalived配置:http://1987.name/1789.html,这里暂时使用单机模式。

创建 ProxySQL 监控用户

要在 ProxySQL 中启用对后端节点的监视,需要创建一个具有USAGE权限的用户,并在 ProxySQL 中配置该用户。

在所有后端的MySQL中创建用户:

CREATE USER 'proxysql'@'%' IDENTIFIED BY '123456';
GRANT USAGE ON *.* TO 'proxysql'@'%';
FLUSH PRIVILEGES;

在PorxySQL中配置此用户:

mysql -P6032 -uadmin -padmin -h 127.0.0.1
-- 用户名
UPDATE global_variables 
SET variable_value = 'proxysql' 
WHERE
    variable_name = 'mysql-monitor_username';

-- 密码
UPDATE global_variables 
SET variable_value = '123456' 
WHERE
    variable_name = 'mysql-monitor_password';

-- 加载配置和持久化:
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

创建 ProxySQL 客户端用户

ProxySQL 必须具有可以访问后端节点的用户。要添加用户需要在mysql_users表中插入,首先在后端服务器创建用户,主要根据实际业务需求创建用户,这里略过...

这里以 root 和 sk 两个用户为例:

-- 这两个用户默认指向主机组 2
INSERT INTO mysql_users ( username, PASSWORD, default_hostgroup )
VALUES
    ( 'root', '123456', 2 ),
    ( 'sk', '123456', 2 );

-- 加载配置和持久化:
load mysql users to runtime;
save mysql users to disk;

重要字段说明:

username                 # 前端应用连接ProxySQL,以及 ProxySQL 将 SQL 语句路由给后端 MySQL 所使用的用户名。
password                 # 对应的密码。可以是明文密码也可以是 hash 密码。如果使用hash密码,先在后端某个 MySQL 节点上执行 select password(PASSWORD),然后将加密结果复制到该字段。
default_hostgroup        # 该用户默认的路由目标。例如,指定 root 用户的该字段值为 1 时,则使用 root 用户发送的 SQL 语句默认将路由到 hostgroup_id=1 组中的某个节点上。
active: 1                # 1 代表用户生效,0 代表不生效
default_schema           # 登录后端默认连接的数据库,为 NULL 时则由全局变量 mysql-default_schema 决定
transaction_persistent   # 值为 1 时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,所有的语句都路由到同一个组中,避免语句分散到不同组

查看用户表:

mysql> select username,password,active,default_hostgroup from mysql_users; 
+----------+----------+--------+-------------------+
| username | password | active | default_hostgroup |
+----------+----------+--------+-------------------+
| root     | 123456   | 1      | 1                 |
| sk       | 123456   | 1      | 1                 |
+----------+----------+--------+-------------------+
2 rows in set (0.00 sec)

将集群节点添加到ProxySQL

ProxySQL 使用 hostgroups 配置后端节点的组群。就可以通过将不同类型的流量路由到不同的组来平衡群集中的负载。可以通过多种方式配置主机组(例如主从,读写组),每个后端节点可以配置在多个组中。

在 ProxySQL 中添加后端 MySQL 集群节点,需要在mysql_servers表中插入相应的记录,其中hostgroup_id2是写组、3是读组:

INSERT INTO mysql_servers ( hostgroup_id, hostname, PORT, weight )
VALUES
    ( 2, '192.168.0.3', 3306, 100 ),
    ( 2, '192.168.0.4', 3306, 10 ),
    ( 3, '192.168.0.5', 3306, 100 ),
    ( 3, '192.168.0.6', 3306, 100 );

查看连接监控是否正常:

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

对心跳信息的监控(ping指标的监控):

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

配置后如果connect_error的结果为NULL则表示正常。

查看控制超时和检查间隔时间的全局变量:

select * from global_variables where variable_name like '%monitor_galera%';

配置 ProxySQL 上关于 Galera 集群的规则

用于定义 galera 集群的mysql_galera_hostgroups表的定义:

使用show create table mysql_galera_hostgroups\G命令查看表结构。

writer_hostgroup          # 定义写入主机组的ID
backup_writer_hostgroup   # 定义备份写入组的 ID,如果是多主模式运行,写入节点数量大于 max_writers,权重低的主机就被放入该值定义的组中。
reader_hostgroup          # 定义读取主机组ID。
offline_hostgroup         # 当监控主机处于脱机状态时,就放入 offline_hostgroup 定义的组中。
active                    # 启用配置(0 或 1)。
max_writers               # 限制写入主机数,大于此值就被放入 backup_writer_hostgroup 定义的组中。
writer_is_also_reader     # 启用后,写入组的节点也属于读取主机组。(0 或 1)
max_transactions_behind   # 防止读取的后端主机有延迟数据,延迟事务数超过此值就避开此节点。延迟事务数由 wsrep_local_recv_queue 查询。
comment                   # 备注信息。

官方文档:https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_galera_hostgroups

添加galera服务器配置:

INSERT INTO mysql_galera_hostgroups ( writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind )
VALUES
    ( 2, 4, 3, 1, 1, 1, 0, 100 );

-- 加载配置和持久化:
load mysql servers to runtime;
save mysql servers to disk;

查看配置

select * from mysql_servers;
select * from runtime_mysql_servers;
select * from mysql_galera_hostgroups;

统计MySQL连接池信息:

select * from stats.stats_mysql_connection_pool;

配置加载运行之后,写入组中权限低的 node-2 节点主机组被重新配置为 4,因为配置了max_writers=1,即最大写入节点为1个。只有 node-1 发生故障才会重置组机组为2

配置路由规则(读写分离)

路由规则官网文档:https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules

字段解释:

rule_id                 # 规则ID
active                  # 激活此条规则
match_digest            # SQL匹配正则
destination_hostgroup   # 匹配的规则路由到此主机组
apply                   # 配置为1表示规则不匹配后继续匹配其他规则。
INSERT INTO mysql_query_rules ( rule_id, active, match_digest, destination_hostgroup, apply )
VALUES
    ( 1, 1, '^SELECT.*', 3, 0 ),
    ( 2, 1, '^SELECT.* FOR UPDATE$', 2, 1 );

-- 加载配置和持久化
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

查看集群中每个节点的状态:

mysql> select * from mysql_server_galera_log order by time_start_us desc limit 4;
+-------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------+
| hostname    | port | time_start_us    | success_time_us | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_|
+-------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------+
| 192.168.0.6 | 3306 | 1564380949093460 | 5157            | YES               | YES       | 0                      | 4                 | NO           | NO                   | NO                      |
| 192.168.0.5 | 3306 | 1564380949092583 | 4994            | YES               | YES       | 0                      | 4                 | NO           | NO                   | NO                      |
| 192.168.0.4 | 3306 | 1564380949092168 | 2922            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                      |
| 192.168.0.3 | 3306 | 1564380949091740 | 5977            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                      |
+-------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+-------------------------+
4 rows in set (0.00 sec)

运维常用语句

-- 查看表
show tables from stats;
show tables from monitor;

-- 查看路由规则
select rule_id,active,match_digest,username,destination_hostgroup,apply from mysql_query_rules;

-- 查看最近查询的语句
select * from stats_mysql_query_digest limit 10;

-- 查看运行的 mysql servers 配置
select * from runtime_mysql_servers;

-- 查看规则命中
select * from stats_mysql_query_rules;

-- 查看后端数据库连接池信息
select * from stats_mysql_connection_pool;

-- 语句统计汇总
select * from stats_mysql_commands_counters;

-- 错误信息
select * from stats_mysql_errors;

-- 路由规则和命中合并查询
select a.rule_id,a.active,a.match_digest,a.username,a.destination_hostgroup,a.apply,b.hits
from mysql_query_rules a,stats_mysql_query_rules b
where a.rule_id = b.rule_id;

-- 删除配置
delete from mysql_servers;
load mysql servers to runtime;
save mysql servers to disk;

delete from mysql_query_rules;
load mysql query rules to runtime;
save mysql query rules to disk;

1条评论

想说点什么?