First time setting up ProxySQL. I can connect to the proxy, but when I try to issue a command like ‘select @@hostname’ I get this…
ERROR 2013 (HY000): Lost connection to MySQL server at ‘handshake: reading initial communication packet’, system error: 111
…or…
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 6221 after 10000ms
Google says this is cause by a firewall, but it isn’t in this case. All servers are plugged into the same switch, same VLAN, and the mysql client works fine when logging in from the proxy server to the back-end server. The errors only appear when I connect to the proxy and then issue a command.
1 Like
Do you have ProxySQL properly set up? (hostgroups, routing rules, users, etc)
1 Like
As far as I can tell, yes. I followed the guides here…
…and here…
How To Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04 | DigitalOcean>
I went through them multiple times and I can’t see where I’ve done anything wrong. None of them mentioned creating rules as a requirement, though. The ProxySQL documentation seems to say that it can work without rules, based only on host groups.
1 Like
The digital ocean blog you posted uses MySQL Group Replication in Multi-Primary mode. This means you can write to any node and they all stay in sync, thus the need for any rules is not really needed.
What version of MySQL is your backend? You may need to configure SSL on ProxySQL (using ProxySQL v2 right?).
1 Like
We’re using ProxySQL2.
Understood, but the Initial Configuration guide on the ProxySQL site does not mention rules as part of the basic setup either. I followed their steps carefully and I still get the connection error. I can’t guarantee I’m not being blind to something, but I sure don’t see what I’m missing. Is there a better guide somewhere? Both of them are vague in places.
I don’t really want to use SSL as it would eliminate WireShark packet capture as a diagnostic tool. Plus, I don’t see how SSL addresses the problem. If it can’t connect on port 3306, I don’t know why it would have better luck on 443.
1 Like
Internet 101: SSL is not limited to port 443.
You are thinking https (443) which is not what we are talking about here. MySQL started using SSL as default in MySQL 5.7. Port 3306 is MySQL’s port which supports both non-SSL and SSL connections.
Back to the issue, the ProxySQL guide you listed above absolutely uses query rules to handle routing. It is a major section of that documentation. They assign a hostgroup to their test user, which is the default for this user, containing only 1 server. That user will never use any of the other servers unless the primary goes offline and one of the other replicas turns off its read_only flag (something proxysql does not manage). Look for ‘MySQL Query Rules’ section. If you don’t use rules, then you can only do basic routing of user → hostgroup which could be fine for most situations.
Have you checked that ProxySQL is able to monitor any of the backend hosts? This is also in the ProxySQL docs you pasted above:
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
1 Like
Sorry I got in a hurry. I’m aware that SSL operates on other ports besides 443, and MySQL uses the same port when SSL is enabled. We have about 500 instances of MySQL and we’ve enabled SSL before. What I meant to say is that if the server cannot connect on port 3306 (lost connection) then I don’t see how enabling SSL would help. Also, if the issue was SSL-related, there would at least be related errors in the MySQL error log, but there are none. In any case, encryption spoils the packet capture option for application diagnostics.
I’ll look.
ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
±---------±-----±-----------------±------------------------±--------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
±---------±-----±-----------------±------------------------±--------------+
| ha50a | 5622 | 1643233656272451 | 2616 | NULL |
| ha50a | 5622 | 1643233654245689 | 2677 | NULL |
| ha50a | 5622 | 1643233652261249 | 4179 | NULL |
±---------±-----±-----------------±------------------------±--------------+
The above only shows one server because I removed the other one. Before I removed it, I was getting…
ERROR 2013 (HY000): Lost connection to MySQL server at ‘handshake: reading initial communication packet’, system error: 111
After I removed it, I started getting…
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 6221 after 10000ms
1 Like
So it appears that ‘ha50a’ is working fine with ProxySQL. The mysql_server_connect_log table that you showed above shows no errors for ‘ha50a’. You said that another server was giving you errors, and so you removed it from proxysql. This tells me that you have some configuration difference between ‘ha50a’ and server X with regards to proxysql.
You said above that you can successfully connect from proxysql server using command-line mysql client. When you do this, enter \s and confirm that SSL is not in use. If it is, exit and try connecting again without SSL. If that fails, then SSL is the issue.
Please confirm ProxySQL and MySQL versions please.
1 Like
Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. It might be because the MySQL server is bound to the loop-back IP (127.0.0.1 / localhost) which effectively cuts you off from connecting from “outside”. If this is the case, you need to upload the script to the webserver (which is probably also running the MySQL server) and keep your server host as ‘localhost’ Another common cause of connect timeouts is the reverse-DNS lookup that is necessary when authenticating clients. It is recommended to run MySQL with the config variable in my.cnf:
Open mysql configuration file named my.cnf and try to find “bind-address”, here replace the setting (127.0.0.1 OR localhost) with your live server ip (the ip you are using in mysql_connect function).
Restart service by command : service httpd restart
GRANT ALL PRIVILEGES ON yourDB.* TO 'username'@'YOUR_APPLICATION_IP' IDENTIFIED BY 'YPUR_PASSWORD' WITH GRANT OPTION;
1 Like