ProxySQL无法连接XtraDB：直连正常但代理连接权限拒绝

我的ProxySQL配置截图如下

直接连接XtraDB成功 #

ubuntu@proxysql:~$ mysql -u cerebra -pnew_password -h 51.12.210.119 -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 828
Server version: 8.0.36-28.1 Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases
→ ;
+-------------------+
| Database          |
+-------------------+
| cerebra           |
| information_schema|
| mysql             |
| performance_schema|
| sys               |
+-------------------+
5 rows in set (0.00 sec)

通过ProxySQL连接失败 #

ubuntu@proxysql:~$ mysql -u cerebra -pnew_password -h 127.0.0.1 -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.36-28.1 Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3 (ProxySQL)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases
→ ;
ERROR 1045 (28000): Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES)
mysql>

ProxySQL日志内容 #

ubuntu@proxysql:~$ sudo tail -f /var/lib/proxysql/proxysql.log
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.211.18:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 20.240.210.183:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 20.240.210.183:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.210.119:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.211.18:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 20.240.210.183:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.211.18:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 20.240.210.183:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.210.119:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).
2024-09-11 08:05:39 mysql_connection.cpp:999:handler(): [ERROR] Failed to mysql_real_connect() on 51.12.211.18:3306 , FD (Conn:43 , MyDS:43) , 1045: Access denied for user ‘cerebra’@‘135.225.106.78’ (using password: YES).

需求：希望能够通过ProxySQL连接XtraDB集群

核心问题是XtraDB集群拒绝了ProxySQL服务器公网IP（135.225.106.78）的cerebra用户连接请求，直接连接时使用的是ProxySQL服务器到XtraDB的本地/内网连接IP，因此可以成功。

1. 调整XtraDB用户权限 #

登录XtraDB集群任意节点，执行以下命令检查cerebra用户的授权范围：

SELECT user, host FROM mysql.user WHERE user = 'cerebra';

如果结果中没有cerebra@135.225.106.78或cerebra@%（允许任意IP连接），添加对应授权：

-- 仅允许ProxySQL公网IP连接（推荐）
GRANT ALL PRIVILEGES ON *.* TO 'cerebra'@'135.225.106.78' IDENTIFIED BY 'new_password';
-- 或允许所有IP连接（生产环境谨慎使用）
GRANT ALL PRIVILEGES ON *.* TO 'cerebra'@'%' IDENTIFIED BY 'new_password';
-- 刷新权限生效
FLUSH PRIVILEGES;

2. 验证ProxySQL用户映射配置 #

登录ProxySQL管理端口（默认6032）：

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

查询用户配置：

SELECT username, password, default_hostgroup FROM mysql_users;

确保：

• username为cerebra
• password为正确的加密值（可通过SELECT PASSWORD('new_password');生成，若使用明文需设置mysql-setting中的password_validation_policy=OFF）
• default_hostgroup指向已配置XtraDB节点的主机组

3. 检查ProxySQL后端主机状态 #

查看XtraDB节点配置：

SELECT hostgroup_id, hostname, port, status FROM mysql_servers;

若节点状态为OFFLINE，重新上线：

UPDATE mysql_servers SET status='ONLINE' WHERE hostname='XtraDB节点IP';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

4. 生效配置并测试 #

在ProxySQL管理端执行命令使配置生效：

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

重新测试连接：

mysql -u cerebra -pnew_password -h 127.0.0.1 -P 6033

内容的提问来源于stack exchange，提问作者Sarian