在 ProxySQL V2.0.0 以上版本可以原生支持 galera 集群，不再需要 scheduler 调度程序中使用外部脚本。 本文实验环境拓扑图：
![图片[1]-使用 ProxySQL 代理 MariaDB 实现读写分离和负载均衡-岸边IBIAN](https://www.ibian.online/wp-content/uploads/2023/07/20200727173316-1595871196.jpg)
代理层主机组规划：
- 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
准备工作
- MariaDB Galera Cluster配置：https://www.ibian.online/1390.html
- ProxySQL安装配置：https://www.ibian.online/215.html
使用原生支持 galera 集群功能，必须将读取组内节点配置为只读实例，本文中为 node-3 和 node-4：
在后端节点/etc/my.cnf添加配置：
[mysqld] read_only = on
保存重启数据库使其生效。
ProxySQL 配置
如果ProxySQL使用集群模式，需要先配置好集群，集群和keepalived配置（https://www.ibian.online/1389.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_id为2是写组、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_hostgroupsG命令查看表结构。
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
查看集群中每个节点的状态：
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;
加载配置和持久化 LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; 查看集群中每个节点的状态：
THE END
暂无评论内容