ProxySQL Error: Access denied for user

[arun chakrapani rao]

Hi Gurus,

Need help in a basic test set up.

i am testing proxsql on a 3 node galera cluster. i did follow one of the youtube video 
Installation and Configuration of MariaDB Galera Cluster(High Availability) & ProxySQL Configuration
proxs. the settipup of the user/cluster into the proxsql went fine. i dont see any errors in the mysql_server_active_log tableProxySQL> select * from mysql_server_connect_log;
+----------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us    | connect_success_time_us | connect_error |
+----------+------+------------------+-------------------------+---------------+
| rk9vm4   | 3306 | 1748771097755527 | 5009                    | NULL          |
| rk9vm3   | 3306 | 1748771098220070 | 5007                    | NULL          |
| rk9vm2   | 3306 | 1748771098683684 | 5256                    | NULL          |



i am able to connect to the cluster db from the proxservre using sql on port 3306
[bbnladmin@rk9sqlproxy1 ~]$ mysql -uproxyadmin -pOracle-321 -hrk9vm2 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 437787
Server version: 10.11.11-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show processlist;
+--------+-------------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+----------+
| Id     | User        | Host                | db   | Command     | Time   | State                                                         | Info             | Progress |
+--------+-------------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+----------+
|      1 | system user |                     | NULL | Sleep       |  73648 |                                                               | NULL             |    0.000 |
|      2 | system user |                     | NULL | Sleep       | 616988 | wsrep aborter idle                                            | NULL             |    0.000 |
| 417418 | repl        | rk9maxscl1:40974    | NULL | Binlog Dump |  20291 | Master has sent all binlog to slave; waiting for more updates | NULL             |    0.000 |
| 432561 | monitor     | 10.168.254.51:35554 | NULL | Sleep       |      0 |                                                               | NULL             |    0.000 |
| 433059 | root        | localhost           | NULL | Sleep       |   4500 |                                                               | NULL             |    0.000 |
| 437787 | proxyadmin  | 10.168.254.51:46376 | NULL | Query       |      0 | starting                                                      | show processlist |    0.000 |
+--------+-------------+---------------------+------+-------------+--------+---------------------------------------------------------------+------------------+----------+

but when i try to connect through proxysql[bbnladmin@rk9sqlproxy1 ~]$ mysql -uproxyadmin -p321 -P6033 --prompt='ProxySQLClient'
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxyadmin'@'127.0.0.1' (using password: YES)

please do let me know where i am going wrong.thanks in advance
arun

[Dave Rix]

Hi Arun,

I'm not from the ProxySQL team, but I think I can help here.

Have you added the user `proxyadmin` to the ProxySQL table `mysql_users` with the correct password hash, and then loaded that to runtime with "LOAD MYSQL USERS TO RUNTIME;"

If the MySQL users have not been added to ProxySQL, then ProxySQL cannot authenticate those users. The user's connection to the database is not 'pass-through', but 'man-in-the-middle', so you have to authenticate the users with ProxySQL, which then authenticates to the MySQL server using the same user credentials. This allows ProxySQL to process, alter, and route incoming connections as it knows which MySQL user they are coming from, and therefore where they are intended to be routed.

See this section in the docs - https://proxysql.com/documentation/proxysql-configuration/#:~:text=affected%20(0.00%20sec)-,MySQL%20Users,-After%20configuring%20the

Regards, 

Dave Rix

Senior Cloud Architect (AWS) / DevSecOps / MySQL DBA