Using Nginx Stream Proxy to Securely Access MySQL

1. Introduction

Our production environment runs on cloud servers (application servers, MySQL servers, etc.). Exposing MySQL directly to the public internet is risky, so the MySQL port should not be open.

When a bug occurs and developers need remote access, we can use an Nginx proxy (jump server) to connect.

2. Nginx Proxy Connection

To forward connections we need a server with Nginx installed in the same internal network as MySQL.

The ngx_stream_core_module must be compiled with --with-stream during Nginx configuration.

2.1 stream

Defines a stream block, similar to the http block, placed in the main context.

stream {
    server {
        ...
    }
}

2.2 server

Defines a virtual host inside the stream block; multiple server blocks can be defined.

stream {
    server {
        ...
    }
    server {
        ...
    }
}

2.3 listen

Specifies the address and port the virtual host listens on.

listen 127.0.0.1:3306;
listen *:3306;
# same effect as above
listen 3306;
listen localhost:3306;

3. Restrict Access IP

To limit access to only internal IPs, enable the ngx_stream_access_module and use allow and deny directives.

3.1 allow

Permits specified IPs.

# allow a single IP
allow 192.168.110.1;
# allow a CIDR range
allow 192.168.110.0/16;
allow 192.168.110.0/24;
allow all;

3.2 deny

Denies specified IPs.

# deny a single IP
deny 192.168.110.1;
# deny a CIDR range
deny 192.168.110.0/16;
deny 192.168.110.0/24;
deny all;

Tip: allow should be paired with deny to avoid unintentionally allowing all IPs.

4. Comprehensive Example

Only IP 192.168.110.100 is allowed to connect to MySQL via Nginx.

stream {
    allow 192.168.110.100;
    deny all;
    server {
        listen 3306;
        proxy_pass 192.168.110.101:3306;
    }
}