ProxySQL: Can't connect after install

Greetings Ramesh,

Here’s my proxysql-admin.cnf:

[I]# proxysql admin interface credentials.
export PROXYSQL_DATADIR=‘/var/lib/proxysql’
export PROXYSQL_USERNAME=‘admin’
export PROXYSQL_PASSWORD=‘admin’
export PROXYSQL_HOSTNAME=‘localhost’
export PROXYSQL_PORT=‘6032’

PXC admin credentials for connecting to pxc-cluster-node.

export CLUSTER_USERNAME=‘admin’
export CLUSTER_PASSWORD=‘admin’
export CLUSTER_HOSTNAME=‘localhost’
export CLUSTER_PORT=‘3306’

proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.

export MONITOR_USERNAME=‘monitor’
export MONITOR_PASSWORD=‘monit0r’

Application user to connect to pxc-node through proxysql

export CLUSTER_APP_USERNAME=‘proxysql_user’
export CLUSTER_APP_PASSWORD=‘passw0rd’

ProxySQL read/write hostgroup

export WRITE_HOSTGROUP_ID=‘10’
export READ_HOSTGROUP_ID=‘11’

ProxySQL read/write configuration mode.

export MODE=“singlewrite”

ProxySQL Cluster Node Priority File

export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf[/I]

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.

Thanks for the replies!

Regards,
Jeffrey

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! :slight_smile:

Hi Jeffery,

This looks strange. Is proxysql running on port 6032 ?

Could you please check the port in proxysql.cnf

Also, check proxysql is up and running.

Greetings Ramesh,

Sorry for the late reply.
Here’s the port of proxysql:

[root@node1 ~]# grep mysql_ifaces /etc/proxysql.cnf 
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"

And here’s proxysql’s status:

[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:

[root@node1 ~]# netstat -a | grep 6032
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 

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.

Hi Jeffery,

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 ).

Please share proxysql.log (/var/lib/proxysql/proxysql.log)

Greetings Ramesh,

So i tried restarting and then accesing proxysql on port 6032 but i get the same error.

[root@node1 proxysql]# systemctl restart proxysql
[root@node1 proxysql]# vim proxysql.log
[root@node1 proxysql]# mysql -h 127.0.0.1 -P 6032 -u admin -p 
Enter password: 
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)

Here’s the output of the error log (i moved the original one and made a new one for after the reboot to make it more readable)

2018-05-16 02:37:51 [INFO] Shutdown angel process
2018-05-16 02:37:51 [INFO] ProxySQL version 1.4.7-1.1
2018-05-16 02:37:51 [INFO] Detected OS: Linux node1.ccloud.com.au 3.10.0-693.21.1.el7.x86_64 #1 SMP Wed Mar 7 19:03:37 UTC 2018 x86_64
2018-05-16 02:37:51 [INFO] Starting ProxySQL
2018-05-16 02:37:51 [INFO] Sucessfully started
2018-05-16 02:37:51 [INFO] Angel process started ProxySQL process 12936
Standard ProxySQL Cluster rev. 0.1.0702 -- ProxySQL_Cluster.cpp -- Fri Apr 13 02:17:53 2018
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Fri Apr 13 02:17:53 2018
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Fri Apr 13 02:17:53 2018
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Fri Apr 13 02:17:53 2018
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Fri Apr 13 02:17:53 2018
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Fri Apr 13 02:17:53 2018
2018-05-16 02:37:51 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2018-05-16 02:37:51 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
2018-05-16 02:37:51 [INFO] Dumping mysql_servers_incoming
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2018-05-16 02:37:51 [INFO] New mysql_replication_hostgroups table
2018-05-16 02:37:51 [INFO] New mysql_group_replication_hostgroups table
2018-05-16 02:37:51 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2018-05-16 02:37:51 [INFO] Dumping mysql_servers
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
| hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+
Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Fri Apr 13 02:17:53 2018
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Fri Apr 13 02:17:53 2018
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Fri Apr 13 02:17:53 2018

What strikes me as od that the dump of the mysql_server seems to return empty?
Could that be the issue here?

Kind regards,
Jeffrey

Also i don’t see any .sock files in /tmp?

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)

Regards,
Jeffrey de Boer

Guess i’m running into a dead end here.
Is it worth switching to HAProxy instead?

Hi Jeffery,

Replies inline

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.

Greetings Ramesh,

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.

[root@node1 ~]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --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
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)

I’m truely at a loss here.

Regards,
Jeffrey

Hi Jeffery,

Could you please share the output of below command.

mysql -u admin -p -h localhost -P 6032 -e"show global variables"

Greetings Ramesh,

Thanks for the reply, glad to see you’re still with me :slight_smile: 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)

[url]Dropbox - mysql-output.xlsx - Simplify your life

Kind regards,
Jeffrey de Boer

Hi Jeffery,

Yes, I had a great weekend, thank you. :slight_smile:

You are running PXC on 3306 port. By default, mysql uses Unix socket to establish the connection that is why the server is ignoring port 6032.

You will be able to connect to proxysql using the following command.

After connecting to the proxysql database please give the output of

Hi Jeffery,

I think you have added 'table is for storing application user.
[URL]https://github.com/sysown/proxysql/issues/709[/URL]

Please remove proxysql database directory and restart proxysql (it will create the fresh database directory). I hope it will resolve the issue.

Greetings Ramesh,

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 :slight_smile:

Kind regards,
Jeffrey

I’m glad to hear that you were able to fix the issue. :slight_smile:

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”.

ex:proxysql --initial -f -c /etc/proxysql.cnf

datadir=“/var/lib/proxysql”
errorlog=“/var/lib/proxysql/proxysql.log”

admin_variables=
{
admin_credentials=“admin:xxxxx;proxysql:xxxxxx”
mysql_ifaces=“0.0.0.0:6032”
}

mysql_variables=
{
threads=8
max_connections=1500
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces=“0.0.0.0:3306”
default_schema=“information_schema”
stacksize=1048576
server_version=“5.7.26”
connect_timeout_server=3000
monitor_username=“proxysql_monitor”
monitor_password=“xxxxxxxxxxx”
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}

defines all the MySQL servers

mysql_servers =
(

)

defines all the MySQL users

mysql_users:
(
{
username = “proxysql_user”
password = “xxxxxxxxx”
default_hostgroup = 10
active = 1
}
)

EOF

After initialing proxysql,then add mysql servers ,mysql users, mysql rules etc…to your proxysql
it 's easy.