Unable to connect to TiDB after HAProxy

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: haproxy 之后连不上tidb

| username: Wander

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
Error Screenshot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 0
haproxy configuration
global # Global configuration.
log 127.0.0.1 local2 # Define global syslog server, up to two can be defined.
chroot /var/lib/haproxy # Change the current directory and set superuser permissions for the startup process to enhance security.
pidfile /var/run/haproxy.pid # Write the PID of the HAProxy process to the pidfile.
maxconn 4096 # Maximum concurrent connections a single HAProxy process can accept, equivalent to the command line parameter “-n”.
nbthread 48 # Maximum number of threads. The upper limit of threads is the same as the number of CPUs.
user haproxy # Same as the UID parameter.
group haproxy # Same as the GID parameter, it is recommended to use a dedicated user group.
daemon # Let HAProxy work in the background as a daemon process, equivalent to the command line parameter “-D”. Of course, it can also be disabled with the “-db” parameter on the command line.
stats socket /var/lib/haproxy/stats # Location to save statistics.

defaults # Default configuration.
log global # Logs inherit the settings from the global configuration section.
retries 2 # Maximum number of attempts to connect to the upstream server, beyond which the backend server is considered unavailable.
timeout connect 60s # Timeout for HAProxy to connect to the backend server. If within the same LAN, a shorter time can be set.
timeout client 30000s # Timeout for inactive connections between the client and HAProxy after data transfer is complete.
timeout server 30000s # Timeout for inactive connections on the server side.

listen admin_stats # Combination of frontend and backend, the name of this monitoring group can be customized as needed.
bind 0.0.0.0:8080 # Listening port.
mode http # Mode in which monitoring runs, here it is http mode.
option httplog # Enable logging of HTTP requests.
maxconn 10 # Maximum concurrent connections.
stats refresh 30s # Automatically refresh the monitoring page every 30 seconds.
stats uri /haproxy # URL of the monitoring page.
stats realm HAProxy # Prompt information on the monitoring page.
stats auth admin:pingcap123 # User and password for the monitoring page, multiple usernames can be set.
stats hide-version # Hide the HAProxy version information on the monitoring page.
stats admin if TRUE # Manually enable or disable backend servers (supported from HAProxy 1.4.9 onwards).

listen tidb-cluster # Configure database load balancing.
bind 0.0.0.0:3306 # Floating IP and listening port.
mode tcp # HAProxy uses the 4th layer transport layer.
balance leastconn # The server with the least connections receives the connection first. leastconn is recommended for long session services such as LDAP, SQL, TSE, etc., rather than short session protocols like HTTP. This algorithm is dynamic, and the server weight will be adjusted during operation for slow-start servers.
server tidb-1 10.0.1.4:4000 send-proxy check inter 2000 rise 2 fall 3 # Check port 4000, check frequency is once every 2000 milliseconds. If 2 checks are successful, the server is considered available; if 3 checks fail, the server is considered unavailable.
server tidb-2 10.0.1.5:4000 send-proxy check inter 2000 rise 2 fall 3
server tidb-3 10.0.1.6:4000 send-proxy check inter 2000 rise 2 fall 3
tidb
“Server.onConn handshake”] [conn=4391968410825657851] [error=“write tcp 10.0.1.4:4000->10.0.1.101:59936: write: connection reset by peer”] [“remote addr”=10.0.1.101:59936]

10.0.0.101 is the IP address of the haproxy host

The haproxy host has two IP addresses, one is 10.0.1.101 and the other is vip (keepalived ensures haproxy high availability) 10.0.1.102

| username: db_user | Original post link

It looks like you have configured send-proxy, which should be for IP transparency. Have you configured this? Note that the address written here is not the TiDB address, but the HAProxy address.

| username: WalterWj | Original post link

Is the database user on the whitelist?

| username: Wander | Original post link

Configured
image
The mosaic IP address is the VIP address.

| username: Wander | Original post link

There is no firewall.

| username: WalterWj | Original post link

What I mean is, did you specify a whitelist when you created the user?

| username: Wander | Original post link

Using the system’s built-in root@%

| username: db_user | Original post link

Try changing this address to the IP of haproxy. Who is your VIP assigned to?

| username: zhanggame1 | Original post link

Looking at the configuration, there doesn’t seem to be any issue. Try connecting the client to the actual IP.

| username: Wander | Original post link

There is no problem connecting to the actual IP address.

| username: Wander | Original post link

The architecture I use is like this: HAProxy and Keepalived are deployed on two machines (VIP is drifted through Keepalived), and the HAProxy configuration is as I posted. When using tiup cluster edit cluster-name, the IP address written in the networks parameter is the VIP.

| username: zhanggame1 | Original post link

I don’t quite understand the relationship between VIP and TiDB.

| username: Wander | Original post link

The VIP address is the same as the HAProxy address.

| username: db_user | Original post link

There shouldn’t be any problem then. The 3306 port on the HAProxy machine is open, right? The mode is also set to TCP. I don’t see any issues. You can check if HAProxy has any prompts.

| username: zhanggame1 | Original post link

proxy-protocol-networks=“10.0.10.0/24” Take a look, and also try setting proxy-protocol-networks to empty.

| username: Billmay表妹 | Original post link

You can try the following steps:

  1. Check the network connection between the client and the TiDB server. You can use the ping command to check if the server is accessible from the client.
  2. Verify the authentication credentials. Ensure you are using the correct username and password to connect to the TiDB server.
  3. Check the server configuration. Ensure the TiDB server is running and listening on the correct port. You can use the netstat command to check if the server is listening on the correct port.
  4. Check the TiDB server logs for any error messages. The logs can provide more information about the cause of the issue.