背景说明

应用程序使用 MySQL 作为后端数据库，同时使用了 MySQL ProxySQL 读写分离中间件将读写负载分散到不同的数据库节点。在MySQL数据库从 8.0.36 版本升级到 8.0.44 版本后，需要验证数据库读写分离是否正常。本文给出了验证方案，供参考。

验证步骤

1. 环境状态检查

连接到ProxySQL管理界面：

mysql -h 127.0.0.1 -P 6032 -uadmin -padmin

1.1 ProxySQL后端服务器状态

SELECT * FROM mysql_servers;
SELECT hostgroup_id, hostname, port, status, weight FROM runtime_mysql_servers;

确保所有后端MySQL 8.0.44实例状态均为ONLINE。

1.2 查询规则状态检查

-- 检查读写分离规则
SELECT rule_id, active, match_digest, match_pattern, destination_hostgroup, apply 
FROM mysql_query_rules 
WHERE active = 1 
ORDER BY rule_id;

-- 检查运行时规则状态
SELECT * FROM runtime_mysql_query_rules;

2. 读写分离功能验证

2.1 写操作验证

-- 通过ProxySQL执行写操作测试，连接到ProxySQL的6033端口（默认读写端口）
mysql -h 127.0.0.1 -P 6033 -uadmin -padmin

CREATE DATABASE IF NOT EXISTS proxy_test;
USE proxy_test;

CREATE TABLE IF NOT EXISTS rw_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    source_host VARCHAR(100)
);

-- 插入测试数据
INSERT INTO rw_test (data, source_host) VALUES ('write_test1', @@hostname);
INSERT INTO rw_test (data, source_host) VALUES ('write_test2', @@hostname);
UPDATE rw_test SET data = 'updated' WHERE id = 1;
DELETE FROM rw_test WHERE id = 2;

2.2 读操作验证

-- 执行读操作，验证是否路由到从库
SELECT * FROM rw_test;
SELECT @@hostname as current_host, @@port as current_port;
SELECT COUNT(*) FROM rw_test;

-- 多次执行以下查询，观察是否在不同从库间负载均衡
SELECT @@server_id, @@hostname, @@port, 'read_query' as query_type;

2.3 事务内读写验证

-- 测试事务中的读写行为
START TRANSACTION;
INSERT INTO rw_test (data, source_host) VALUES ('transaction_test', @@hostname);
SELECT * FROM rw_test WHERE data = 'transaction_test';
COMMIT;

3. 路由规则精确验证

3.1 基于正则表达式的路由测试

根据具体规则，针对性测试：

-- 测试SELECT语句路由（应该到读组）
SELECT * FROM rw_test WHERE id = 1;
SELECT COUNT(*) as cnt FROM rw_test;

-- 测试INSERT/UPDATE/DELETE路由（应该到写组）
INSERT INTO rw_test (data) VALUES ('pattern_test');
UPDATE rw_test SET data = 'updated_pattern' WHERE id = 1;
DELETE FROM rw_test WHERE id > 100;

-- 测试特定表名模式的路由
SELECT * FROM large_table WHERE condition = 'value';
INSERT INTO log_table (message) VALUES ('test message');

3.2 存储过程/函数调用测试

-- 测试存储过程执行路由
CALL test_procedure();
SELECT test_function();

4. 监控和统计信息分析

4.1 ProxySQL统计信息查询

-- 查看查询路由统计
SELECT hostgroup, count_star, sum_time, sum_rows_affected, sum_rows_sent
FROM stats_mysql_query_digest 
ORDER BY count_star DESC 
LIMIT 20;

-- 查看各主机组的查询分布
SELECT hostgroup, 
       SUM(count_star) as total_queries,
       SUM(sum_time) as total_time
FROM stats_mysql_query_digest
GROUP BY hostgroup;

-- 重置统计以便后续观察
SELECT * FROM stats_mysql_query_digest_reset;

4.2 连接池状态监控

-- 检查各后端连接状态
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR
FROM stats_mysql_connection_pool;

5. 性能和数据一致性验证

5.1 复制延迟检查

-- 在每个从库上检查复制状态
SHOW SLAVE STATUS\G
-- 关注 Seconds_Behind_Master 参数

-- 或者在主库检查
SELECT * FROM performance_schema.replication_group_members;

5.2 数据一致性验证

-- 在主库和从库分别验证数据一致性
-- 在主库执行
SELECT @@hostname, COUNT(*)  FROM rw_test;

-- 在从库执行
SELECT @@hostname, COUNT(*)  FROM rw_test;

验证关键点

1. 路由准确性：各类型查询是否正确路由到预期主机组
2. 规则有效性：所有正则表达式规则是否按预期工作
3. 数据一致性：主从数据同步是否正常

通过以上系统性的验证，可以全面确保MySQL 8.0.44升级后ProxySQL读写分离功能完全正常。

关于作者

网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~