Secure MySQL Access via Nginx Stream Proxy: Step‑by‑Step Guide

1. Introduction

Our production environment runs on cloud servers (application servers, MySQL servers, etc.). Exposing MySQL directly to the public network is risky, so the MySQL port is kept closed to protect data.

When a bug occurs and developers need remote MySQL 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 that shares the same internal network with the MySQL server.

We also need the ngx_stream_core_module which is not built by default; it must be enabled with --with-stream during configure.

Refer to “Nginx basic commands & zero‑downtime upgrade” for the build process.

1) stream

The stream directive defines a stream server, placed at the main level, parallel to the http block.

Scope: main

Syntax: stream { … }

Example:

stream {
    server {
        …
    }
}

2) server

The server directive defines a virtual host inside the stream block, similar to http’s server.

Scope: stream

Syntax: server { … }

stream {
    server {
        …
    }
    server {
        …
    }
}

3) listen

The listen directive sets the address and port the virtual host listens on.

Scope: server

Syntax: listen address:port;

Examples:

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

4) Configuration Examples

Single MySQL instance (port 3306):

stream {
    server {
        listen 3306;
        proxy_pass 192.168.110.101:3306;
    }
}

MySQL cluster (port 3306):

stream {
    upstream mysql_socket {
        server 192.168.110.101:3306;
    }
    server {
        listen 3306;
        proxy_pass mysql_socket;
    }
}

Clients such as Navicat can now connect through Nginx.

3. Restrict Access by IP

After proxying, anyone can reach MySQL via Nginx. To limit access to internal IPs, use the ngx_stream_access_module with allow and deny directives.

1) allow

Permits specified IPs; can be combined with deny.

Scope: stream, server

Syntax: allow address | CIDR | unix: | all;

Examples:

# allow 192.168.110.1
allow 192.168.110.1;

# allow 192.168.110.0/16
allow 192.168.110.0/16;

# allow 192.168.110.0/24
allow 192.168.110.0/24;

# allow all
allow all;

2) deny

Denies specified IPs; can be combined with allow.

Scope: stream, server

Syntax: deny address | CIDR | unix: | all;

# deny 192.168.110.1
deny 192.168.110.1;

# deny 192.168.110.0/16
deny 192.168.110.0/16;

# deny 192.168.110.0/24
deny 192.168.110.0/24;

# deny all
deny all;

3) Example

Allow only 192.168.110.100 and deny the rest:

allow 192.168.110.100;
deny all;

Tip: If you use allow , you must also use deny ; otherwise all IPs are permitted.

4. Comprehensive Example

Only 192.168.110.100 can connect to MySQL through Nginx:

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