ProxySQL 详细安装与配置指南（MySQL主从集群）

一、环境准备

• 主库(Master): 192.168.1.160
• 从库(Slave1): 192.168.1.20
• 从库(Slave2): 192.168.1.29
• ProxySQL节点: 建议独立服务器（可与应用同机）

二、ProxySQL安装

1. 安装ProxySQL

# Ubuntu/Debian
wget https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -cs)/proxysql_2.4.2-ubuntu20_amd64.deb
sudo dpkg -i proxysql_*.deb
sudo systemctl start proxysql
sudo systemctl enable proxysql

# CentOS/RHEL
cat <<EOF | sudo tee /etc/yum.repos.d/proxysql.repo
[proxysql]
name=ProxySQL
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF
sudo yum install proxysql
sudo systemctl start proxysql
sudo systemctl enable proxysql

2. 验证安装

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt="ProxySQL Admin> "
# 密码默认是admin:admin

三、基础配置

1. 添加MySQL服务器

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES 
(10,'192.168.1.160',3306),  -- 主库hostgroup 10
(20,'192.168.1.20',3306),    -- 从库hostgroup 20
(20,'192.168.1.29',3306);    -- 从库hostgroup 20

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

2. 配置监控用户

在主库创建监控账号：

CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

ProxySQL配置：

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

3. 配置读写分离规则

-- 设置主库组
INSERT INTO mysql_replication_hostgroups VALUES (10,20,'read_only','ProxySQL集群');
LOAD MYSQL SERVERS TO RUNTIME;

-- 验证自动检测
SELECT hostgroup_id,hostname,status FROM runtime_mysql_servers;

四、高级路由配置

1. 创建应用访问用户

在主库执行：

CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppUser@123';
GRANT ALL ON *.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

ProxySQL配置：

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES 
('appuser','AppUser@123',10);  -- 默认路由到主库

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

2. 读写分离规则

-- 写操作路由到主库(hostgroup 10)
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
(1,1,'^INSERT',10,1),
(2,1,'^UPDATE',10,1),
(3,1,'^DELETE',10,1),
(4,1,'^ALTER',10,1);

-- 读操作路由到从库(hostgroup 20)
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
(5,1,'^SELECT.*FOR UPDATE',10,1),  -- SELECT FOR UPDATE特殊处理
(6,1,'^SELECT',20,1);

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

3. 验证路由

-- 查看路由统计
SELECT hostgroup,sum_time,count_star FROM stats_mysql_query_digest;

五、健康检查与故障转移

1. 配置健康检查

UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_timeout';
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_read_only_timeout';
UPDATE global_variables SET variable_value='3000' WHERE variable_name='mysql-monitor_replication_lag_interval';
UPDATE global_variables SET variable_value='10' WHERE variable_name='mysql-monitor_replication_lag_count';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

2. 自动故障转移

-- 设置主库故障时提升从库
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections) VALUES
(10,'192.168.1.20',3306,1000,1000);  -- 从库1作为备用主库

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

六、生产环境优化

1. 连接池配置

UPDATE global_variables SET variable_value='300' WHERE variable_name='mysql-max_connections';
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-use_tcp_keepalive';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

2. 启用统计功能

UPDATE global_variables SET variable_value='true' WHERE variable_name='admin-stats_credentials';
UPDATE global_variables SET variable_value='statsuser:Stats@123' WHERE variable_name='admin-stats_credentials';

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

七、监控与管理

1. 常用监控命令

-- 查看后端状态
SELECT hostgroup_id,hostname,status,ConnUsed,MaxConnUsed FROM runtime_mysql_servers;

-- 查询统计
SELECT hostgroup,digest,count_star,sum_time FROM stats_mysql_query_digest;

-- 连接池状态
SELECT * FROM stats_mysql_connection_pool;

2. 集成Prometheus

# 启用Prometheus exporter
UPDATE global_variables SET variable_value='0.0.0.0:42000' WHERE variable_name='admin-web_enabled';
LOAD ADMIN VARIABLES TO RUNTIME;

八、故障排查

1. 日志检查

tail -f /var/lib/proxysql/proxysql.log

2. 常见问题解决

-- 重置所有配置
LOAD MYSQL SERVERS FROM DISK;
LOAD MYSQL USERS FROM DISK;
LOAD MYSQL QUERY RULES FROM DISK;

九、配置备份

-- 导出配置
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL VARIABLES TO DISK;
SAVE ADMIN VARIABLES TO DISK;

-- 物理备份
cp /var/lib/proxysql/proxysql.db /backup/

通过以上配置，您将获得：
✅ 自动读写分离
✅ 主库故障自动切换
✅ 细粒度SQL路由控制
✅ 连接池和性能优化
✅ 完善的监控体系