And here’s the output of grep admin_credentials /etc/proxysql.cnf: [root@node1 ~]# grep admin_credentials /etc/proxysql.cnf
admin_credentials=“admin:admin”
I couldn’t get them from the mysql client because when i try to connect to the mysql instance of proxysql on port 6032 i keep getting the error as described earlier.
@Lorraine: Sorry i didn’t mean to note that as a means of saying the percona team/community did not reply. Simply wanted to state that i tried asking elsewhere and did not receive any correct answers yet, so i was thinking it wasn’t a standard issue.
Hey no, not at all. I was almost talking to myself there about Stackoverflow - I get an email every time this forum is updated but realized that I didn’t get the same from SO doh!
[root@node1 ~]# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)
Active: active (exited) since ma 2018-05-14 10:19:38 CDT; 16h ago
Docs: man:systemd-sysv-generator(8)
Process: 17833 ExecStop=/etc/rc.d/init.d/proxysql stop (code=exited, status=0/SUCCESS)
Process: 17835 ExecStart=/etc/rc.d/init.d/proxysql start (code=exited, status=0/SUCCESS)
mei 14 10:19:38 node1.ccloud.com.au systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL...
mei 14 10:19:38 node1.ccloud.com.au su[17837]: (to proxysql) root on none
mei 14 10:19:38 node1.ccloud.com.au proxysql[17835]: Starting ProxySQL: DONE!
mei 14 10:19:38 node1.ccloud.com.au systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL.
I can also see that the port is open and listening:
I was wondering is this could be an issue with name resolving?
I could try to spin the cluster back up with the --skip-name-resolve to see if that makes a difference. But i don’t know if that impacts the cluster itself.
The issue is with proxysql connection not with the cluster. Login credentials look good but could not connect to proxysql. Could you please restart proxysql and try to reconnect with port(6032) or socket (/tmp/proxysql_admin.sock ).
Is it weird that when i connect like this:
[root@node1 proxysql]# mysql -u admin -p -h localhost -P 6032
i connect to the XtraDB Cluster and not the proxysql interface?
But when i try it on the local ip adress i get another result.
[root@node1 proxysql]# mysql -u admin -p -h 127.0.0.1 -P 6032
Enter password:
ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘admin’@‘’ (using password: YES)
What strikes me as od that the dump of the mysql_server seems to return empty?
Could that be the issue here?
No, that is expected.
Is it weird that when i connect like this:
[root@node1 proxysql]# mysql -u admin -p -h localhost -P 6032
i connect to the XtraDB Cluster and not the proxysql interface?
But when i try it on the local ip adress i get another result.
[root@node1 proxysql]# mysql -u admin -p -h 127.0.0.1 -P 6032
Enter password:
ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘admin’@‘’ (using password: YES)
I think you have started PXC in 6032 port, please start PXC in default port 3306.
Is it worth switching to HAProxy instead?
You can resolve the issue by running PXC on a different port.
Sorry i did not reply yet, had some other critical things i had to manage first yesterday.
So i specifically gave up port 3306 in my /etc/my.cnf on all nodes and restarted mysql. That should put PXC on port 3306 i think?
Then i restarted proxysql to ensure it could bind on port 6032. Then i tried to run the command to enable proxysql and i get the same error again.
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘admin’@‘’ (using password: YES)
Please check the ProxySQL connection parameters! Terminating.
Also tried connecting on the interface manually:
[root@node1 ~]# mysql -u admin -p -h 127.0.0.1 -P 6032
Enter password:
ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘admin’@‘’ (using password: YES)
Thanks for the reply, glad to see you’re still with me Hope you had a good weekend.
Below is a dropbox link to a excel file because the output is too large to post here and i cannot upload it in a excel or csv format. Hope you can see anything out of the ordinary (for the record, i’m certain i configured something wrong somewhere that’s now causing this behavior)
Great news, the last post was indeed the issue!
I removed the file /var/lib/proxysql/proxysql.db and restarted proxysql and then i was able to enable proxySQL.
[root@node1 ~]# proxysql-admin --proxysql-username=admin --proxysql-hostname=localhost --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
Configuring ProxySQL monitoring user..
ProxySQL monitor username as per command line/config-file is monitor
User 'monitor'@'10.%' has been added with USAGE privilege
Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
Percona XtraDB Cluster application username as per command line/config-file is proxysql_user
Percona XtraDB Cluster application user 'proxysql_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges
Adding the Percona XtraDB Cluster server nodes to ProxySQL
Configuring singlewrite mode with the following nodes designated as priority order:
Write node info
+----------------+--------------+------+---------+---------+
| hostname | hostgroup_id | port | weight | comment |
+----------------+--------------+------+---------+---------+
| 10.138.134.144 | 10 | 3306 | 1000000 | WRITE |
+----------------+--------------+------+---------+---------+
ProxySQL configuration completed!
ProxySQL has been successfully configured to use with Percona XtraDB Cluster
You can use the following login credentials to connect your application through ProxySQL
mysql --user=proxysql_user -p --host=localhost --port=6033 --protocol=tcp
Still have some configuration to do but i’ll follow the documentation on that. Thanks for your help!
I’ll also add the answer on StackOverflow (where you also commented in the beginning i see) so that people can learn from my mistake
I had similar issues connecting to ProxySQL 1.4.12-9. There were three problems: [LIST=1]
[]The default ‘admin’ user can only connect locally (see documentation)
[]The admin user cannot be called ‘proxysql’ (I cannot find this documented anywhere)
[*]After configuration file changes the ProxySQL service must be stopped ()
[/LIST]
hello , I think proxysql-admin is not a good tool for seting up proxysql.
The easy way is you config /etc/proxysql.conf and then “systemctl start proxysql”
Then you will see new files generated in /var/lib/proxysql, your proxysql will be initialed
The following is what i use to initial my proxysql:
cat > /etc/proxysql.cnf<<EOF
In order to FORCE a re-initialise of the on-disk database from the configuration file
the ProxySQL service should be started with “service proxysql initial”.