I have a container for ProxySQL 2.2.4 and another for MySQL 8.0.31. Both containers are using same network name. So from ProxySQL container I can access MySQL running in another container.
Even, ProxySQL Admin can show the MySQL service as ONLINE and ping/connect in Admin has no errors.
ProxySQL Admin>
select hostgroup,srv_port,status from stats_mysql_connection_pool;
hostgroup | srv_port | status |
---|---|---|
1 | 3306 | ONLINE |
1 | 3306 | ONLINE |
Now, when from outside the ProxySQL container, I try to run following command to access MySQL services thru proxySQL, it fails.
Note: The user "ravi" exists on both mysql and proxysql side, with same password.
$ sudo mysql -u ravi -pravi123 -h 0.0.0.0 -P16033 -e "SELECT @@port"
Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000) at line 1: Access denied for user 'ravi'@'172.18.0.2' (using password: YES)
The ProxySQL log in container shows:
2022-11-12 08:31:24 mysql_connection.cpp:1063:handler(): [ERROR] Failed to mysql_real_connect() on 1:mysql:3306 , FD (Conn:42 , MyDS:42) , 1045: Access denied for user 'ravi'@'172.18.0.2' (using password: YES).
The same user gets connected with same password from the ProxySQL container shell.
$ sudo docker exec -it proxysql bash
root@34c06973094d:/# mysql -u ravi -p -h mysql Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4352 Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
what is the error behind it? why is not ProxySQL able to authenticate the user, while the same user is able to login to MySQL from shell.
Expected : The user should be able to run the SQL queries using ProxySQL which is already connected and being monitored without connection and monitoring errors.
Please verify that you have added the "ravi" user to the mysql_users table on proxysql e.g.:
You can use the hashed password in the mysql_users password column, but you have to use the
mysql_native_password
authentication plugin in MySQL. Otherwise, you will need to enter the password in plaintext. (related GitHub issue)Afterwards, you will need to run:
This is important, because changes to the mysql_users table will not take place until you load them to runtime, and will not persist after a crash unless you save them to disk.
Please note, you can configure quite a lot in the mysql_users table, please check the proxysql user configuration documentation for more details.