MySQL数据库：ProxySQL 读写分离方案

1. 环境准备

基于MySQL数据库：主从库搭建复制配置环境和配置

2. 安装 ProxySQL

添加 ProxySQL 软件源 ProxySQL 不在默认的 Debian 软件源中，因此需要添加官方 ProxySQL 软件源。请按照以下步骤操作：

# 安装前置依赖
sudo apt-get update
sudo apt-get install -y lsb-release wget apt-transport-https ca-certificates

# 下载并添加 ProxySQL GPG 密钥
wget -nv -O /usr/share/keyrings/proxysql-3.0.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key.gpg'

# 添加 ProxySQL 软件源到 sources 列表
echo "deb [signed-by=/usr/share/keyrings/proxysql-3.0.x-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

再次更新软件包列表 添加软件源后，更新软件包列表以包含 ProxySQL 软件源：

sudo apt update

安装 ProxySQL 和 MySQL 客户端 现在可以安装 proxysql 和 mysql-client：

sudo apt install -y proxysql mysql-client

如果需要安装特定版本的 ProxySQL（例如 2.6.6），可以指定版本：

sudo apt install -y proxysql=2.6.6 mysql-client

验证安装 安装完成后，检查 ProxySQL 版本以确认安装成功：

proxysql --version

管理proxysql服务

# 启动 ProxySQL
sudo systemctl start proxysql

# 设置开机自启
sudo systemctl enable proxysql

# 检查服务状态
sudo systemctl status proxysql

3. 配置读写分离规则

登录ProxySQL管理端

ProxySQL默认管理账户信息

依照默认用户名密码进行登录

mysql -u admin -padmin -h 127.0.0.1 -P 6032

添加主从服务器

在ProxySQL执行：

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES 
(10,'127.0.0.1',3306), -- 主库hostgroup_id=10 (写组)
(20,'127.0.0.1',3307);       -- 从库hostgroup_id=20 (读组)

配置读写分离规则

• 写操作路由到主库
• 读操作路由到从库

在ProxySQL执行：

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

加载配置

在ProxySQL执行：

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

4. 配置监控用户

在主库创建监控账号（ProxySQL用）

监控账号的作用

• 检测后端 MySQL 服务器的存活状态（connect 检查）
• 监控主从复制延迟（replication lag 检查）
• 检查只读状态（read_only 检查）

没有监控账号，ProxySQL 无法自动检测主从库的健康状态，可能导致：

• 故障转移失败
• 读写分离失效（如把查询路由到已宕机的从库）

在主库执行：

CREATE USER 'monitor'@'%' IDENTIFIED BY '12345678';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';

在ProxySQL配置监控

在ProxySQL执行：

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='12345678' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

5. 验证路由规则

查看路由命中情况

在ProxySQL执行：

SELECT * FROM stats_mysql_query_rules;

6. 性能测试 (mysqlslap)

在 ProxySQL 上配置普通用户 proxyuser

你需要为 mysqlslap 测试添加一个用户（建议使用 proxyuser，也可以用 monitor 如果你想赋予它普通查询权限）。

• 添加 proxyuser 到 mysql_users： 在 ProxySQL 管理接口中运行：

  在ProxySQL执行：

  INSERT INTO mysql_users (username, password, default_hostgroup, active) VALUES ('proxyuser', '12345678', 1, 1);
  LOAD MYSQL USERS TO RUNTIME;
  SAVE MYSQL USERS TO DISK;
  

  • username='proxyuser'：用户名为 proxyuser。
  • password='12345678'：与 mysqlslap 命令中的密码一致。
  • default_hostgroup=1：默认路由到主库（hostgroup_id=1）。
  • active=1：启用用户。

• （可选，不必要执行）添加 monitor 到 mysql_users： 如果你想用 monitor用户运行 mysqlslap，也需要添加： 在ProxySQL执行：

  INSERT INTO mysql_users (username, password, default_hostgroup, active) VALUES ('monitor', '12345678', 1, 1);
  LOAD MYSQL USERS TO RUNTIME;
  SAVE MYSQL USERS TO DISK;
  

• 验证用户配置： 检查 mysql_users表： 在ProxySQL执行：

  SELECT * FROM mysql_users;
  

  确认 proxyuser 和/或 monitor 已添加。

  在 MySQL 主库和从库中检查 proxyuser 用户是否添加且有效

  确保 proxyuser 在主库和从库中存在，并且支持 'localhost' 和 '127.0.0.1' 两种主机名。

• 登录主库：

  mysql -u root -p -h 127.0.0.1 -P 3306
  

  检查proxyuser用户

  在主库执行：

  SELECT user, host, plugin FROM mysql.user WHERE user='proxyuser';
  

  如果没有 'proxyuser'@'localhost'，创建并授权：

  CREATE USER 'proxyuser'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';
  GRANT ALL ON test.* TO 'proxyuser'@'localhost';
  GRANT ALL ON test.* TO 'proxyuser'@'127.0.0.1'; -- 确保也支持 127.0.0.1
  FLUSH PRIVILEGES;
  

• 登录从库：

  mysql -u root -p -h 127.0.0.1 -P 3307 --socket=/var/run/mysqld/mysqld_slave.sock
  

  检查proxyuser用户

  在从库执行：

  SELECT user, host, plugin FROM mysql.user WHERE user='proxyuser';
  

  如果没有 'proxyuser'@'localhost'，创建并授权：

  CREATE USER 'proxyuser'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';
  GRANT SELECT ON test.* TO 'proxyuser'@'localhost';
  GRANT SELECT ON test.* TO 'proxyuser'@'127.0.0.1'; -- 确保也支持 127.0.0.1
  FLUSH PRIVILEGES;
  

• 验证用户认证： 直接测试 proxyuser 是否能登录：

  mysql -u proxyuser -p12345678 -h 127.0.0.1 -P 3306
  mysql -u proxyuser -p12345678 -h 127.0.0.1 -P 3307 --socket=/var/run/mysqld/mysqld_slave.sock
  

  创建测试数据库和表

  创建test数据库和test表

  PS：主库创建的表插入的数据正常情况下一般会自动同步到从库，所以不需要在从库再执行一次！！！

  在主库执行：

  CREATE DATABASE IF NOT EXISTS test;
  USE test;
  CREATE TABLE IF NOT EXISTS t1 (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
  

  验证：

  在主库执行：

  SELECT * FROM test.t1;
  

  写一个储存过程

  在主库执行：

  DELIMITER //
  DROP PROCEDURE IF EXISTS generate_posts//
  
  CREATE PROCEDURE generate_posts(IN num_posts INT)
  BEGIN
      DECLARE i INT DEFAULT 0;
      DECLARE next_id INT;
      DECLARE start_time BIGINT;
      DECLARE batch_size INT DEFAULT 5000; -- 每批插入5000条
  
      SET start_time = UNIX_TIMESTAMP();
      SELECT IFNULL(MAX(id), 0) INTO next_id FROM test.t1;
  
      -- 优化设置
      SET SESSION unique_checks = 0;
      SET SESSION foreign_key_checks = 0;
      SET SESSION autocommit = 0;
  
      -- 禁用索引（加速插入）
      ALTER TABLE test.t1 DISABLE KEYS;
  
      WHILE i < num_posts DO
          SET @batch_values = '';
          SET @batch_count = 0;
  
          -- 构建当前批次数据
          WHILE i < num_posts AND @batch_count < batch_size DO
              SET next_id = next_id + 1;
  
              -- 适配 test.t1 表的字段 (id, data)
              SET @batch_values = CONCAT(@batch_values, 
                  IF(@batch_count > 0, ',', ''),
                  '(',
                  next_id, ',',
                  '''测试内容-', i, REPEAT(' 内容填充', 5), ''')'
              );
  
              SET i = i + 1;
              SET @batch_count = @batch_count + 1;
          END WHILE;
  
          -- 执行当前批次插入
          SET @sql = CONCAT('INSERT INTO test.t1 (id, data) VALUES ', @batch_values);
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
  
          -- 每批提交一次
          COMMIT;
  
          -- 进度报告
          IF i % 10000 = 0 THEN
              SELECT CONCAT('已插入 ', i, '/', num_posts, ' 条 (', 
                           ROUND(i/num_posts*100,1), '%)') AS progress;
          END IF;
      END WHILE;
  
      -- 恢复设置
      ALTER TABLE test.t1 ENABLE KEYS;
      COMMIT;
      SET SESSION unique_checks = 1;
      SET SESSION foreign_key_checks = 1;
      SET SESSION autocommit = 1;
  
      -- 性能报告
      SELECT CONCAT('插入完成! 共 ', num_posts, ' 条，耗时: ', 
                   ROUND((UNIX_TIMESTAMP()-start_time)/60,2), ' 分钟') AS result;
  END//
  DELIMITER ;
  

  插入50万条数据

  在主库执行：

  CALL generate_posts(500000);
  

  使用LIMIT子句查询测试前100条，看看有没有数据

  在主库执行：

  SELECT * FROM test.t1 LIMIT 100;
  

测试性能

# 读测试（应路由到从库）
mysqlslap -h 127.0.0.1 -P 6033 -u proxyuser -p12345678 \
  --create-schema=test \
  --query="SELECT * FROM test.t1" \
  --concurrency=50 --iterations=1000

# 写测试（应路由到主库）
mysqlslap -h 127.0.0.1 -P 6033 -u proxyuser -p12345678 \
  --create-schema=test \
  --query="INSERT INTO test.t1 VALUES (NULL,'test')" \
  --concurrency=30 --iterations=500

分析读写分离规则命中

在ProxySQL执行：

mysql> SELECT * FROM stats_mysql_query_rules;

+---------+-------+
| rule_id | hits  |
+---------+-------+
| 1       | 0     |
| 2       | 59000 |
| 3       | 15060 |
| 4       | 0     |
| 5       | 0     |
+---------+-------+

读写分离规则命中分析

结论：ProxySQL 已成功实现：

• 读请求（rule_id=2）→ 从库
• 写请求（rule_id=3）→ 主库

7. 预期性能提升

方案对比

关键配置示例

1. ProxySQL 路由统计

    +---------+-------+
    | rule_id | hits  |
    +---------+-------+
    | 1       | 0     |
    | 2       | 59000 |
    | 3       | 15060 |
    | 4       | 0     |
    | 5       | 0     |
    +---------+-------+
   

   

2. mysqlslap 压测结果

   # 读测试
   Benchmark
           Average number of seconds to run all queries: 2.558 seconds
           Minimum number of seconds to run all queries: 2.119 seconds
           Maximum number of seconds to run all queries: 7.921 seconds
           Number of clients running queries: 50
           Average number of queries per client: 1
   

   

   # 写测试
   Benchmark
           Average number of seconds to run all queries: 1.409 seconds
           Minimum number of seconds to run all queries: 0.326 seconds
           Maximum number of seconds to run all queries: 2.991 seconds
           Number of clients running queries: 30
           Average number of queries per client: 1
   

   

压力测试 QPS 结果

读写分离方案优化效果

故障排查

1. 检查路由是否生效

   SELECT hostgroup, digest, count_star FROM stats_mysql_query_digest;
   

2. 监控主从延迟

   SELECT * FROM mysql_server_replication_lag;
   

3. 查看连接池状态

   SELECT * FROM stats_mysql_connection_pool;