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

1. Introduction

Our production environment is deployed on cloud servers (application servers, MySQL servers, etc.). Exposing MySQL directly to the public network is risky, so the MySQL port is kept closed. 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 resides in the same internal network as the MySQL server.

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

After enabling the module, we can use its directives to configure the proxy.

1) stream

This directive defines a stream server, placed at the same level as the http block, defined in the main block.

Scope: main

Syntax: stream { ... }

Example:

stream {
    server {
        ...
    }
}

2) server

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

Scope: stream

Syntax: server { ... }

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

3) listen

Specifies the address and port that the virtual host listens on.

Scope: server

Syntax: listen address:port;

Example:

listen 127.0.0.1:3306;
listen *:3306;
listen 3306;
listen localhost:3306;

4) Configuration Examples

MySQL server, port 3306 (single instance):

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

MySQL server, port 3306 (cluster):

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 the proxy.

3. Restrict Access by IP

After enabling the proxy, anyone can connect to MySQL via Nginx. To further limit access, we can restrict connections to IPs within the corporate network using the ngx_stream_access_module which provides simple allow and deny directives.

1) allow

Allows specified IPs; can be combined with deny.

Scope: stream, server

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

Example:

# Allow 192.168.110.1
allow 192.168.110.1;

# Allow 192.168.110.0/16
allow 192.168.110.0/16;

# Allow all
allow all;

2) deny

Denies specified IPs; can be combined with allow.

Scope: stream, server

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

Example:

# Deny 192.168.110.1
deny 192.168.110.1;

# Deny 192.168.110.0/16
deny 192.168.110.0/16;

# Deny all
deny all;

3) Configuration Example

Allow only 192.168.110.100 and deny all others:

allow 192.168.110.100;
deny all;

Tips: If you specify allow, you must also use deny; otherwise all IPs are allowed.

4. Comprehensive Example

Only 192.168.110.100 is permitted to connect to MySQL through Nginx:

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