0

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.

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Ravi Srivastava
  • 31
  • 5
  • What is your containers configurations? – Liutas Nov 15 '22 at 12:54
  • i have a similar setup, seems you have not added user `ravi` into proxy sql. proxysql and mysql dont share the same users account. you need to create the account in both to gain access. or perhaps im confused with the document. but thats how i got it working – janithcooray Feb 03 '23 at 03:25

2 Answers2

0

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.

izzy
  • 358
  • 4
  • 7
0

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

Severalnines
  • 106
  • 5