基于请求域名通过HAProxy实现MySQL连接负载均衡

Got it, let's break this down into two clear parts: adjusting your Spring Boot app's MySQL configuration to send SNI, and testing the HAProxy routing with a MySQL client. I've dealt with similar setups before, so here's what you need to know:

First, you need to ensure your MySQL JDBC driver supports sending SNI (which it does if you're using Connector/J 8.0.18 or newer—definitely use a recent version to avoid bugs). Here's how to tweak your config:

• JDBC URL Parameters: You must enable SSL (since SNI is a TLS extension) and explicitly set the target MySQL backend's hostname via the serverName parameter. This tells the JDBC driver to include that hostname in the SNI extension during the TLS handshake with HAProxy. A full example URL looks like:

  jdbc:mysql://your-haproxy-ip:3306/your-db-name?useSSL=true&serverSslMode=REQUIRED&serverName=mysql-backend-1.yourdomain.com&allowPublicKeyRetrieval=true
  

  • useSSL=true: Enables SSL/TLS for the connection (required for SNI)
  • serverSslMode=REQUIRED: Ensures the connection uses SSL (prevents fallback to unencrypted)
  • serverName: The exact hostname you configured in HAProxy's SNI matching rule (this is the critical part for routing)
  • allowPublicKeyRetrieval=true: Optional, but often needed if you're using self-signed certificates or haven't configured certificate trust properly

• Application Config File: Translate this into your application.yml or application.properties:
  For application.yml:

  spring:
    datasource:
      url: jdbc:mysql://your-haproxy-ip:3306/your-db-name?useSSL=true&serverSslMode=REQUIRED&serverName=mysql-backend-1.yourdomain.com&allowPublicKeyRetrieval=true
      username: your-db-user
      password: your-db-pass
      driver-class-name: com.mysql.cj.jdbc.Driver
  

  For application.properties:

  spring.datasource.url=jdbc:mysql://your-haproxy-ip:3306/your-db-name?useSSL=true&serverSslMode=REQUIRED&serverName=mysql-backend-1.yourdomain.com&allowPublicKeyRetrieval=true
  spring.datasource.username=your-db-user
  spring.datasource.password=your-db-pass
  spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  

• SSL Certificate Trust (If Needed): If your HAProxy uses a self-signed certificate or a certificate from a private CA, you'll need to either:

  • Disable certificate verification (not recommended for production) by adding verifyServerCertificate=false to the JDBC URL, or
  • Import the CA certificate into your app's truststore and configure it via spring.datasource.hikari.data-source-properties.ssl.trustCertificateKeyStoreUrl (for HikariCP, which is the default in Spring Boot)

To verify your HAProxy setup works as expected, use the official MySQL command-line client with flags that force SNI send:

1. Basic Test Command:
   Use the --ssl-mode=REQUIRED and --ssl-server-name flags to specify the target backend hostname (matching what you have in HAProxy). Replace placeholders with your values:

   mysql -h your-haproxy-ip -P 3306 -u your-db-user -p --ssl-mode=REQUIRED --ssl-server-name=mysql-backend-1.yourdomain.com
   

   When prompted, enter your database password. If the connection succeeds, you're routed to the correct backend.

2. Validate Routing to Different Backends:
   Repeat the above command with different --ssl-server-name values (matching other backends in your HAProxy config). To confirm you're connected to the right database, run a query that returns a unique identifier for each backend, like:

   SELECT @@hostname; -- Returns the backend MySQL server's hostname
   SELECT @@datadir; -- Or check the data directory, which is unique per server
   

   If each --ssl-server-name value returns the corresponding backend's details, your routing is working.

3. Troubleshooting Tip:
   If connections fail, check that:

   • HAProxy is listening on the correct port (3306 in these examples)
   • The --ssl-server-name value exactly matches the SNI rule in HAProxy
   • Your MySQL backends are accepting SSL connections (check require_secure_transport in MySQL config if needed)

内容的提问来源于stack exchange，提问作者lakshayk