How to properly use Nginx as reverse proxy with ssl for MySQL?

Question

Nginx as reverse proxy without ssl works fine Nginx reverse proxy for mysql without ssl, however when I enable ssl, no more connections can be established. Ssl certificate is issued to lets say my.domain.

My configuration:

stream {
    log_format main '$remote_addr - [$time_local] $status';

    upstream demo-db {
        server 127.0.0.1:3300;
    }

    server {
        listen 6030 ssl so_keepalive=on;
        server_name my.domain; # no change when this line is removed
        access_log /var/log/nginx/demo.db.log main;

        ssl_certificate <hidden>;
        ssl_certificate_key <hidden>;

        ssl_protocols TLSv1.3;

        proxy_socket_keepalive on;

        proxy_pass demo-db;
    }
}

I get 500 status in access log:

<ip> - [14/Aug/2024:11:57:16 +0200] 500

Nothing in error log.

When I do https request in Postman https://my.domain:6030, I get this error: Parse Error: The server returned a malformed response - Error: Parse Error: Expected HTTP/

My mysql client DBeaver shows this error (with and without using ssl in DBeaver connection configuration):

Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

details:

Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Is there more configuration to be done in order to make ssl work?

Answer 1

Nginx listen ssl parameter allows specifying that all connections accepted on this port should work in SSL mode to handle HTTPS requests. That's not what you actually need. https://nginx.org/en/docs/http/ngx_http_core_module.html#listen

While we just need secure connections to MySQL proxied with Nginx.

Here is a simple Nginx configuration for your case.

stream {
  upstream demo_db {
    server 127.0.0.1:3306;
  }
  server {
    listen 6030;
    proxy_pass demo_db;
    zone tcp_mem 64k;
  }
}

Also, you need to configure MySQL server to accept encrypted connections. https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html

Let's assume you already have your certificate.

mysqld.conf:

[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
require_secure_transport=ON # require ONLY secure connections