Configuring Nginx Stream Module to Proxy MySQL Connections and Restrict Access by IP

1. Introduction

In many production environments MySQL servers are deployed on cloud instances and are not exposed to the public network for security reasons. When a bug occurs and developers need remote access to MySQL, a proxy (jump server) can be used to forward connections safely.

2. Nginx Proxy Connection

To forward MySQL connections you need a server with Nginx installed that resides in the same internal network as the MySQL server. The ngx_stream_core_module must be compiled into Nginx using the --with-stream configure option.

2.1 stream

The stream directive defines a stream block at the main level, similar to the http block.

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

2.2 server

The server directive defines a virtual host inside the stream block. Multiple server blocks can be defined.

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

2.3 listen

The listen directive specifies the address and port the virtual host should listen on.

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

2.4 Configuration Examples

Single‑instance MySQL (port 3306):

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

Clustered MySQL using an upstream block:

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

3. Restricting Access by IP

After enabling proxying, anyone can connect through Nginx, which may be undesirable. The ngx_stream_access_module provides simple allow and deny directives to limit access to specific IP ranges.

3.1 allow

Permits the specified IP or CIDR range.

# 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;

3.2 deny

Denies the specified IP or CIDR range.

# 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.3 Example: Allow only one IP

Only the IP 192.168.110.100 is permitted, all others are blocked.

allow 192.168.110.100;
deny all;

4. Comprehensive Example

Putting everything together, the following configuration allows only the specified internal IP to connect to MySQL via Nginx.

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

After applying this configuration, tools such as Navicat can connect to MySQL through the Nginx proxy while the access is limited to trusted IP addresses.

Note: The original article also contains promotional messages for a community, giveaways, and related resources, which are not part of the technical tutorial.