ProxySQL user is not authenticated by MySQL

Question

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.

Answer 1

In regards to the default_authentication_plugin, please run the following command:

SQL> show global variables like '%authentication%';

If you were using caching_sha2_password, try using mysql_native_password instead of caching_sha2_password as ProxySQL does not fully support the caching_sha2_password mechanism internally.

There is a trick to make it work, but it's not highly recommended because the password will be in plain-text and insecure on the ProxySQL side. You can disable the hashing password in ProxySQL by executing the following command:

SQL> update global_variables set Variable_Value='false' where Variable_name='admin-hash_passwords';

At this point, you will be able to connect to MySQL 8 using the caching_sha2_password, but the password is visible in ProxySQL.

For this, you can refer to the documentation here: ProxySQL Support for MySQL caching_sha2_password - Percona Database Performance Blog

Answer 2

Please verify that you have added the "ravi" user to the mysql_users table on proxysql e.g.:

INSERT INTO mysql_users VALUES (username, password) ('ravi', 'super_secret_password') 

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:

LOAD mysql users TO RUNTIME;
SAVE mysql users TO DISK;

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.