{"id":2620,"date":"2022-10-11T10:53:12","date_gmt":"2022-10-11T10:53:12","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2620"},"modified":"2024-01-27T17:27:34","modified_gmt":"2024-01-27T17:27:34","slug":"can-not-connect-to-proxysql-reasons-and-fixtures","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/can-not-connect-to-proxysql-reasons-and-fixtures","title":{"rendered":"Can not connect to ProxySQL: reasons and fixtures"},"content":{"rendered":"\n

This ProxySQL post is sourced from an error I faced recently<\/p>\n\n\n\n

handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user<\/code><\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
ProxySQL has become one of the popular choice as the “proxy” for MySQL databases. This post is tipping you off for debugging connectivity issue and fixtures around it for a specific use-case.
<\/p>\n\n\n\n
ProxySQL Connectivity<\/h2>\n\n\n\n
Ofcourse while to connect ProxySQL we have two choices for what we want to do: Admin Interface and MySQL.<\/p>\n\n\n\n
  • Admin interface allows us to configure and manage the ProxySQL.<\/li>
  • MySQL interface is basically the “proxy” connectivity to MySQL databases configured in ProxySQL.<\/li><\/ul>\n\n\n\n\n\n\n\n
    Connecting ProxySQL Admin Interface<\/h2>\n\n\n\n
    mysql -uADMIN_USER -pADMIN_PASSWORD -h127.0.0.1 -P6032<\/code><\/pre>\n\n\n\n
    The ADMIN_USER and ADMIN_PASSWORD is taken from admin-admin_credentials configuration. Port 6032 is defined in mysql_ifaces configuration option and it is configurable with multiple ports as well.<\/p>\n\n\n\n
    Connecting backend MySQL Servers from ProxySQL<\/h2>\n\n\n\n
    mysql -uUSER -pPASSWORD -h127.0.0.1 -P6033<\/code><\/pre>\n\n\n\n
    Here, USER and PASSWORD are defined in mysql_users table (and ofcourse loaded to runtime). The Port 6033 is defined in interfaces configuration option and it is configurable.<\/p>\n\n\n\n
    Now that we have established very basic understanding of connectivity, let’s produce the connectivity issue that we’re going to discuss further along with the resolution.<\/p>\n\n\n\n
    Start with creating an “admin” user for our application to connect to the database. This user is ofcourse present on MySQL server. Note that even though the password here is kept as plaintext, we can also use the encrypted password from MySQL’s user table.<\/p>\n\n\n\n
    [root@kedar ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
    \u2026
    mysql> insert into mysql_users (username, password,default_hostgroup) values ('admin','admin',0);
    Query OK, 1 row affected (0.00 sec)<\/code><\/pre>\n\n\n\n
    mysql> load mysql users to runtime; save mysql users to disk;\nQuery OK, 0 rows affected (0.00 sec)\nQuery OK, 0 rows affected (0.01 sec)<\/code><\/pre>\n\n\n\n
    Let’s try re-connecting to the admin interface again<\/p>\n\n\n\n
    [root@kedar ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1\nmysql: [Warning] Using a password on the command line interface can be insecure.\nERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)\n[root@kedar ~]#\n# note that you can still connect to MySQL port though (6033)<\/code><\/pre>\n\n\n\n
    Looking at error log<\/p>\n\n\n\n
    [root@kedar ~]# tail -1 \/var\/lib\/proxysql\/proxysql.log
    2022-10-08 10:41:16 MySQL_Session.cpp:5439:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)
    [root@kedar ~]#<\/code><\/pre>\n\n\n\n
    There is nothing we can see in the error log that hints us anything about the error. But we know that the access is a mess since we created that user! Let’s get rid of that, can we? Without actually connecting to ProxySQL?
    Technically we can. ProxySQL uses SQLite as default datastore to save the data on disk and we can manipulate it with CAUTION.<\/p>\n\n\n\n
    Edit ProxySQL Database<\/h2>\n\n\n\n
    # Stop ProxySQL\n[root@kedar ~]# systemctl stop proxysql\n\n# Backup the db file\n[root@kedar ~]# cd \/var\/lib\/proxysql\/\n[root@kedar ~]# cp proxysql.db proxysql.db.1\n\n# Open db file with sqlite3\n[root@kedar proxysql]# sqlite3 proxysql.db\nSQLite version 3.7.17 2013-05-20 00:56:22\nEnter \".help\" for instructions\nEnter SQL statements terminated with a \";\"\nsqlite> .tables\nglobal_variables\nmysql_aws_aurora_hostgroups\nmysql_collations\nmysql_firewall_whitelist_rules\nmysql_firewall_whitelist_sqli_fingerprints\nmysql_firewall_whitelist_users\nmysql_galera_hostgroups\nmysql_group_replication_hostgroups\nmysql_query_rules\nmysql_query_rules_fast_routing\nmysql_replication_hostgroups\nmysql_servers\nmysql_users\nproxysql_servers\nrestapi_routes\nscheduler\n\nsqlite> .headers on\nsqlite> .schema mysql_users\nCREATE TABLE mysql_users (username VARCHAR NOT NULL , password VARCHAR , active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1 , use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0 , default_hostgroup INT NOT NULL DEFAULT 0 , default_schema VARCHAR , schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0 , transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1 , fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0 , backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1 , frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000 , attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '' , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (username, backend) , UNIQUE (username, frontend));\nsqlite> select * from mysql_users;\nusername|password|active|use_ssl|default_hostgroup|default_schema|schema_locked|transaction_persistent|fast_forward|backend|frontend|max_connections|attributes|comment\nkedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|0|1|10000||\nkedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|1|0|10000||\nadmin|*4ACFE3202A5FF5CF467898FC58AAB1D615029441|1|0|10|test|0|1|0|1|1|10000||<\/strong>\n\n# Delete the user we created and verify it is gone\nsqlite> delete from mysql_users where username='admin';\nsqlite> select * from mysql_users;\nusername|password|active|use_ssl|default_hostgroup|default_schema|schema_locked|transaction_persistent|fast_forward|backend|frontend|max_connections|attributes|comment\nkedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|0|1|10000||\nkedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|1|0|10000||\nsqlite> .quit\n\n# Start ProxySQL\n[root@kedar proxysql]# systemctl start proxysql\n\n# Attempt connectivity (Voila!)\n[root@kedar proxysql]# mysql -uadmin -padmin -P6032 -h127.0.0.1\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 1\nServer version: 5.5.30 (ProxySQL Admin Module)\n\nCopyright (c) 2009-2021 Percona LLC and\/or its affiliates\nCopyright (c) 2000, 2021, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> \\s\n--------------\nmysql  Ver 14.14 Distrib 5.7.36-39, for Linux (x86_64) using  6.2\n\nConnection id:\t\t1\nCurrent database:\tadmin\nCurrent user:\t\tpercona\nSSL:\t\t\tNot in use\nCurrent pager:\t\tstdout\nUsing outfile:\t\t''\nUsing delimiter:\t;\nServer version:\t\t5.5.30 (ProxySQL Admin Module)\nProtocol version:\t10\nConnection:\t\t127.0.0.1 via TCP\/IP\nServer characterset:\tutf8\nDb     characterset:\tutf8\nClient characterset:\tutf8\nConn.  characterset:\tutf8\nTCP port:\t\t6032\nUptime:\t\t\t2 sec\n\nThreads: 0  Questions: 0  Slow queries: 0\n--------------\n<\/code><\/pre>\n\n\n\n
    Again, ensure that you have the backups in place before playing with the ProxySQL’s backend database. <\/p>\n\n\n\n
    But why were we blocked? We faced this issue as the documentation clearly mentions the limitations for usernames. <\/p>\n\n\n\n
    The users defined in admin-admin_credentials or admin-stats_credentials cannot be used also in mysql_users table.<\/p><\/blockquote>\n\n\n\n
    Thus this is not only limited to “admin” user but also to any other usernames chosen for those two roles. And if you do so, ProxySQL will not warn you or let you know via error log but just block you from accessing it!<\/p>\n\n\n\n
    Reset ProxySQL<\/h2>\n\n\n\n
    There’s one more thing we can do here but it is like a hard-reset. We can also reset the ProxySQL setup to initialize the backend database from the configuration.<\/p>\n\n\n\n
    [root@kedar ~]# proxysql --initial
    \u2026 [INFO] Using config file \/etc\/proxysql.cnf
    Renaming database file \/var\/lib\/proxysql\/proxysql.db
    \u2026<\/code><\/pre>\n\n\n\n
    The initial flag here will reset the SQLite database file to its original state loading it from configuration and rename the existing SQLite database file in case a rollback is required.<\/p>\n\n\n\n
    That’s what you see in ProxySQL data dir:<\/p>\n\n\n\n
    [root@kedar ~]# ls proxysql.db*
    proxysql.db proxysql.db.bak<\/code><\/pre>\n\n\n\n
    Quick Tip<\/strong>: To prepare mysql_users insert statements you may use following insert query on the database. Here I’m assuming default hostgroup is 10.<\/p>\n\n\n\n
    select concat('INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (',user,',',authentication_string,',10);') from mysql.user group by user;<\/code><\/pre>\n\n\n\n
    Hope this helps! Happy ProxySQLing.<\/p>\n","protected":false},"excerpt":{"rendered":"This ProxySQL post is sourced from an error I faced recently ProxySQL has become one of the popular choice as the “proxy” for MySQL databases. This post is tipping you…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377,866,6],"tags":[508,517,427,479,509,507],"class_list":{"0":"post-2620","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"category-proxysql","9":"category-technical","10":"tag-access-denied","11":"tag-access-denied-for-user-admin127-0-0-1","12":"tag-mysql","13":"tag-proxysql","14":"tag-proxysql-error","15":"tag-sqlite"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2620","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2620"}],"version-history":[{"count":11,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2620\/revisions"}],"predecessor-version":[{"id":2714,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2620\/revisions\/2714"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}