04-MySQL读写分离

MySQL读写分离

一、读写分离架构的搭建

目录

步骤一：安装部署 ProxySQL

  1. 下载并安装 ProxySQL

    官网地址：https://www.proxysql.com/documentation/installing-proxysql/

    下载链接：

    Centos7：https://github.com/sysown/proxysql/releases/download/v2.4.6/proxysql-2.4.6-1-centos7.x86_64.rpm

    Centos8/kylinV10sp3：https://github.com/sysown/proxysql/releases/download/v2.4.6/proxysql-2.4.6-1-centos8.x86_64.rpm

    • 使用 yum 命令安装 ProxySQL：
      wget https://github.com/sysown/proxysql/releases/download/v2.4.6/proxysql-2.4.6-1-centos7.x86_64.rpm
yum localinstall -y proxysql-2.4.6-1-centos7.x86_64.rpm

  2. 启动 ProxySQL 服务

    systemctl start proxysql

  3. 检查端口监听

    netstat -lntup | grep proxysql
    • 默认监听端口：
      • 6032：管理端口，用于登录管理 ProxySQL。
      • 6033：业务访问端口，用于前端业务连接。

  4. 登录管理端口

    mysql -uadmin -padmin -h127.0.0.1 -P6032

步骤二：ProxySQL 功能设置

  1. 配置表作用

    • runtime：加载配置到内存中，立即生效但重启后失效。（runtime_mysql_users）
    • memory：配置功能信息存储在内存中。
    • disk：保存配置到磁盘，重启后仍然生效。

  2. 配置表信息

    (1) mysql_replication_hostgroups

    • 用于定义主从复制的读写组。

    • 主库：set global read_only=0;允许执行写操作
      从库：set global read_only=1;禁止执行写操作，仅允许读操作

    • 表结构详细说明：

      CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup >= 0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup <> writer_hostgroup AND reader_hostgroup >= 0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only', 'innodb_read_only', 'super_read_only', 'read_only|innodb_read_only', 'read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT ''
);

      # writer_hostgroup
	类型：INT
	约束：>= 0，主键
	说明：定义写操作的主机组编号。通常用于存储主库（read_only=0）的组。
# reader_hostgroup
	类型：INT
	约束：>= 0，且不能与 writer_hostgroup 相同
	说明：定义读操作的主机组编号。通常用于存储从库（read_only=1）的组。
# check_type
	类型：VARCHAR
	默认值：read_only
	约束：支持的值包括 read_only、innodb_read_only、super_read_only、read_only|innodb_read_only、read_only&innodb_read_only
	说明：指定用于检测 MySQL 实例是否为只读模式的检查类型。
# comment
	类型：VARCHAR
	默认值：空字符串
	说明：用于存储用户自定义的注释信息，例如组的用途或配置说明。

    • 插入数据：

      INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'proxy');

    • 加载并保存数据：

      load mysql servers to runtime;
save mysql servers to disk;

    (2) mysql_servers

    • 用于定义后端 MySQL 服务器信息。

    • 表结构详细说明：

      CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id >= 0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE', 'SHUNNED', 'OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <= 10000000) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >= 0) NOT NULL DEFAULT 1000,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port)
);

      # hostgroup_id
	类型：INT
	约束：>= 0，默认值为 0
	说明：后端 MySQL 节点所属的主机组编号。主机组是实现读写分离和负载均衡的关键。
# hostname
	类型：VARCHAR
	说明：后端 MySQL 节点的主机名或 IP 地址。
# port
	类型：INT
	约束：0 <= port <= 65535，默认值为 3306
	说明：后端 MySQL 节点的监听端口。
# gtid_port
	类型：INT
	约束：0 <= gtid_port <= 65535，且不能与 port 相同，除非为 0
	说明：用于 GTID 复制的端口，通常保持默认值。
# status
	类型：VARCHAR
	默认值：ONLINE
	约束：可选值为 ONLINE、SHUNNED、OFFLINE_SOFT、OFFLINE_HARD
	说明：
		ONLINE：节点正常工作，可接收请求。
		SHUNNED：节点被隔离，不会接收新请求。
		OFFLINE_SOFT：节点离线，但会等待当前连接的事务完成。
		OFFLINE_HARD：节点离线，立即断开所有连接。
# weight
	类型：INT
	约束：0 <= weight <= 10000000，默认值为 1
	说明：负载均衡权重，权重越高，分发的请求越多。
# compression
	类型：INT
	约束：0 或 1，默认值为 0
	说明：是否启用压缩连接。
# max_connections
	类型：INT
	约束：>= 0，默认值为 1000
	说明：ProxySQL 到后端节点的最大连接数。
# max_replication_lag
	类型：INT
	约束：0 <= max_replication_lag <= 126144000，默认值为 0
	说明：主从复制的最大延迟阈值，超过此值后节点会被设置为 SHUNNED。
# use_ssl
	类型：INT
	约束：0 或 1，默认值为 0
	说明：是否启用 SSL 连接。
# max_latency_ms
	类型：INT UNSIGNED
	约束：>= 0，默认值为 0
	说明：最大延迟阈值（毫秒），超过此值后节点会被从连接池中剔除。
# comment
	类型：VARCHAR
	默认值：空字符串
	说明：用户自定义注释

    • 插入数据：

      INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '10.0.0.50', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.0.0.52', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '10.0.0.53', 3306);

    • 加载并保存数据：

      save mysql servers to disk;
load mysql servers to runtime;

    (3) global_variables

    • 用于设置监控用户信息。

    • 方式一：

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

    • 方式二：

      create user monitor@'10.0.0.%' identified with mysql_native_password by '123';
grant all on *.* to monitor@'10.0.0.%';

select * from mysql_server_connect_log;
-- 确认监控用户是否可以连接到后端节点做监控
select * from mysql_server_ping_log;
-- 检查确认所有节点的网络连通情况  
select * from mysql_server_read_only_log limit 3;
-- 可以获取主从数据库状态信息

    (4) mysql_users

    • 用于定义业务用户信息。

    • 插入数据：

      CREATE USER xiaoQ@'%' IDENTIFIED BY '123';
GRANT ALL ON *.* TO xiaoQ@'%';
或者直接在表中插入
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('xiaoQ', '123', 10);

    • 加载并保存数据：

      load mysql users to runtime;
save mysql users to disk;

    (5) mysql_query_rules

    • 用于定义读写分离规则。（定义语句信息 识别读或写 实现读写分离 读负载均衡）

    • 插入数据：

      INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^select.*for update$', 10, 1);
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^select', 20, 1);

    • 加载并保存数据：

      load mysql query rules to runtime;
save mysql query rules to disk;

步骤三：读写分离测试

  1. 测试写操作

    mysql -uxiaoQ -p123 -P6033 -h10.0.0.53 -e "BEGIN; SELECT @@server_id; COMMIT;"

  2. 测试读操作

    mysql -uxiaoQ -p123 -P6033 -h10.0.0.53 -e "SELECT @@server_id;"

二、读写分离扩展应用

1. 多业务读写分离配置

假设我们有多个业务（如 www、bbs、blog），每个业务都有独立的主从数据库架构，需要通过 ProxySQL 实现读写分离。

业务架构

业务名称 主库 IP 从库 IP（多个） MHA IP
www 10.0.0.51 10.0.0.52, 10.0.0.53 10.0.0.50
bbs 10.0.0.61 10.0.0.62, 10.0.0.63 10.0.0.60
blog 10.0.0.71 10.0.0.72, 10.0.0.73 10.0.0.70

ProxySQL 配置

1.1 mysql_replication_hostgroups 表
此表定义每个业务的读写分组。

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES
(10, 20, 'www'),
(30, 40, 'bbs'),
(50, 60, 'blog');

1.2 mysql_servers 表
将每个业务的主从库加入对应的主机组。

-- www 业务
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status) VALUES
(10, '10.0.0.51', 3306, 'ONLINE'),  -- 主库
(20, '10.0.0.52', 3306, 'ONLINE'),  -- 从库
(20, '10.0.0.53', 3306, 'ONLINE');  -- 从库

-- bbs 业务
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status) VALUES
(30, '10.0.0.61', 3306, 'ONLINE'),
(40, '10.0.0.62', 3306, 'ONLINE'),
(40, '10.0.0.63', 3306, 'ONLINE');

-- blog 业务
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status) VALUES
(50, '10.0.0.71', 3306, 'ONLINE'),
(60, '10.0.0.72', 3306, 'ONLINE'),
(60, '10.0.0.73', 3306, 'ONLINE');

1.3 mysql_users 表
为每个业务创建独立的用户，并指定默认的主机组。

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
('www_user', '123', 10),  -- www 业务用户
('bbs_user', '123', 30),  -- bbs 业务用户
('blog_user', '123', 50); -- blog 业务用户

1.4 mysql_query_rules 表
根据业务需求，定义读写分离规则。

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT', 20, 1),  -- 读操作路由到从库
(2, 1, '^INSERT|^UPDATE|^DELETE', 10, 1);  -- 写操作路由到主库

2. 写的负载均衡配置

如果某个业务（如 www）有大量并发写请求，可以通过以下方式实现写负载均衡：

2.1 业务架构

业务名称 主库 IP 从库 IP（多个） MHA IP
www 10.0.0.49 10.0.0.51, 10.0.0.52, 10.0.0.53, 10.0.0.54 10.0.0.50

2.2 mysql_replication_hostgroups 表

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES
(10, 20, 'www');

2.3 mysql_servers 表
将主库和从库加入对应的主机组。

-- 主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status) VALUES
(10, '10.0.0.49', 3306, 'ONLINE');

-- 从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status) VALUES
(20, '10.0.0.51', 3306, 'ONLINE'),
(20, '10.0.0.52', 3306, 'ONLINE'),
(20, '10.0.0.53', 3306, 'ONLINE'),
(20, '10.0.0.54', 3306, 'ONLINE');

2.4 mysql_users 表

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
('www_user', '123', 10);

2.5 mysql_query_rules 表

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT', 20, 1),  -- 读操作路由到从库
(2, 1, '^INSERT|^UPDATE|^DELETE', 10, 1);  -- 写操作路由到主库

3. 简化策略配置

3.1 基于端口的策略调度

通过监听不同的端口，为不同业务分配独立的读写端口。

3.1.1 修改 ProxySQL 监听端口

SET mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';

3.1.2 保存并重启 ProxySQL

SAVE MYSQL VARIABLES TO DISK;
systemctl restart proxysql

3.1.3 配置读写分离规则

DELETE FROM mysql_query_rules;  -- 清空现有规则

INSERT INTO mysql_query_rules (rule_id, active, proxy_port, destination_hostgroup, apply) VALUES
(1, 1, 6033, 10, 1),  -- 写操作（端口 6033）
(2, 1, 6034, 20, 1);  -- 读操作（端口 6034）

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
3.2 基于用户的策略调度

通过为不同用户分配默认的主机组，实现读写分离。

3.2.1 配置用户

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
('write', '123', 10),  -- 写用户
('reader', '123', 20); -- 读用户

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

3.2.2 配置读写分离规则

DELETE FROM mysql_query_rules;  -- 清空现有规则

INSERT INTO mysql_query_rules (rule_id, active, username, destination_hostgroup, apply) VALUES
(1, 1, 'write', 10, 1),  -- 写操作
(2, 1, 'reader', 20, 1); -- 读操作

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
posted @ 2025-03-21 16:57  丁志岩  阅读(86)  评论(0)    收藏  举报