Can’t auth ProxySQL with admin to 6032 when created user ‘admin’ under mysql_users table

This issue is the similar issue which is reported here https://github.com/sysown/proxysql/issues/1538.

I found some workaround to fix this issue, without initialising or recreate/reconfigure your ProxySQL node.

Problem:

We have default ProxySQL user “admin” to connect the ProxySQL over the 6032 port.

Once we connected and created the “admin” user account inside the mysql_users table and then loaded into the memory and persisted to disk, we are unable to connect the ProxySQL over port 6032 anymore.

ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)

Solution:

To fix this issue, we can login to backend via sqlite3 and remove the “admin” user from mysql_users tables.

cd /var/lib/proxysql/

sqlite3 proxysql.db 

sqlite> SELECT * FROM mysql_users;

sqlite> DELETE from mysql_users where username='admin';

Then restart the ProxySQL service.

service proxysql restart

Tip : Never create admin user account (admin_credentials) inside the mysql_users table which is mentioned in the ProxySQL documentation as well. https://github.com/sysown/proxysql/wiki/Global-variables#admin-admin_credentials [users in admin-admin_credentials cannot be used also in mysql_users table.]