Not the answer you need?
Register and ask your own question!

ProxySQL: Can't connect after install

jdeboerjdeboer ContributorCurrent User Role Contributor
Greetings everybody,
I'm new here. Trying to set up an XtraDB Cluster with ProxySQL as a load balancer as is advised in the documentation. Now i have the XtraDB cluster up and running. However after i've installed ProxySQL and started the service i try to run the proxysql-admin tool but i keep getting this error:

[[email protected] ~]# 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.


Now i've tried setting up a different user in the proxysql-admin.cnf as that's also advised. But i get the same results. I even created this user on the cluster.

[[email protected] ~]# 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 'proxy_admin'@'' (using password: YES)
Please check the ProxySQL connection parameters! Terminating.
[[email protected] ~]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --adduser
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxy_admin'@'' (using password: YES)
Please check the ProxySQL connection parameters! Terminating.

I haven't changed anything else in the proxysql settings so i'm at a loss as to where i'm going wrong. Does anybody have any ideas?
I get the feeling that there's an issue determining the host or something? But i've tried changing the parameter of the host in the config files and that did not help either.

Also tried this on StackOverflow but no answers yet.

Kind regards,
Jeffrey de Boer

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi Jeremy, welcome to the Percona community forum.
    Yes, one of the team replied on Stackoverflow, but that platform's not too good at notifying us when there are comments - so unless you log in and watch for new updates, you can miss them. I'll let them know that you still need assistance.
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    Hi Jeffrey,

    Could you please share proxysql admin credentials from proxysql database? Also, share

    Commands to get credentials;

    from shell :

    or

    from mysql client :
  • jdeboerjdeboer Contributor Current User Role Contributor
    Greetings Ramesh,

    Here's my proxysql-admin.cnf:

    # 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


    And here's the output of grep admin_credentials /etc/proxysql.cnf:
    [[email protected] ~]# 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
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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! :)
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    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.
  • jdeboerjdeboer Contributor Current User Role Contributor
    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
    
  • jdeboerjdeboer Contributor Current User Role Contributor
    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.
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    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)
  • jdeboerjdeboer Contributor Current User Role Contributor
    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
  • jdeboerjdeboer Contributor Current User Role Contributor
    Also i don't see any .sock files in /tmp?
  • jdeboerjdeboer Contributor Current User Role Contributor
    Is it weird that when i connect like this:
    [[email protected] 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.
    [[email protected] 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
  • jdeboerjdeboer Contributor Current User Role Contributor
    Guess i'm running into a dead end here.
    Is it worth switching to HAProxy instead?
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    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:
    [[email protected] 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.
    [[email protected] 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.
  • jdeboerjdeboer Contributor Current User Role Contributor
    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.

    [[email protected] ~]# 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:
    [[email protected] ~]# 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
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    Hi Jeffery,

    Could you please share the output of below command.

    mysql -u admin -p -h localhost -P 6032 -e"show global variables"
  • jdeboerjdeboer Contributor Current User Role Contributor
    Greetings Ramesh,

    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)

    https://www.dropbox.com/s/uf2deo6cw41c4xa/mysql-output.xlsx?dl=0

    Kind regards,
    Jeffrey de Boer
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    Hi Jeffery,

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

    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
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    Hi Jeffery,

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

    Please remove proxysql database directory and restart proxysql (it will create the fresh database directory). I hope it will resolve the issue.
  • jdeboerjdeboer Contributor Current User Role Contributor
    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 :)

    Kind regards,
    Jeffrey
  • ramesh.sivaramanramesh.sivaraman Percona Percona Staff Role
    I'm glad to hear that you were able to fix the issue. :)
  • victorvictor Entrant Current User Role Contributor
    I had similar issues connecting to ProxySQL 1.4.12-9. There were three problems:
    1. The default 'admin' user can only connect locally (see documentation)
    2. The admin user cannot be called 'proxysql' (I cannot find this documented anywhere)
    3. After configuration file changes the ProxySQL service must be stopped ()
  • steven Linsteven Lin Contributor Current User Role Contributor
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.