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

1. Introduction

Our production environment runs on cloud servers such as application servers and MySQL servers. Exposing a MySQL server directly to the public Internet poses serious security risks, so the MySQL port is kept closed to external access.

When a production bug occurs, developers may need to connect remotely to MySQL to inspect data. This article shows how to use an Nginx proxy (a jump host) for that purpose.

2. Nginx Proxy Connection

To forward connections, you need a server with Nginx installed that resides in the same internal network as the MySQL server.

You must enable the ngx_stream_core_module module, which is not built by default. Add --with-stream to the configure command when building Nginx.

After enabling the module, review its directives to configure the proxy.

1) stream

This directive defines a stream server and is placed at the top level (main block), parallel to the http block.

Scope: main

Syntax:

stream { ... }

stream {
    server {
        ...
    }
}

2) server

The server directive defines a virtual host, similar to the http block's server. Multiple server blocks can be defined inside a stream block.

Scope: stream

Syntax:

server { ... }

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

3) listen

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

Scope: server

Syntax:

listen address:port;

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

4) Configuration Examples

Single‑instance MySQL server (port 3306):

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

Clustered MySQL servers (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 the Nginx proxy.

3. Restricting Access by IP

While the proxy allows anyone to reach MySQL, you can further limit access to specific corporate network IPs for enhanced security.

The ngx_stream_access_module provides simple allow and deny directives.

1) allow

Specifies IPs that are permitted to connect. Often used together with deny.

Scope: stream, server

Syntax:

allow address | CIDR | unix: | all;

# Allow a single IP
allow 192.168.110.1;

# Allow a range using CIDR
allow 192.168.110.0/16;
allow 192.168.110.0/24;

# Allow all IPs
allow all;

2) deny

Specifies IPs that are blocked. Typically paired with allow.

Scope: stream, server

Syntax:

deny address | CIDR | unix: | all;

# Deny a single IP
deny 192.168.110.1;

# Deny a range using CIDR
deny 192.168.110.0/16;
deny 192.168.110.0/24;

# Deny all IPs
deny all;

3) Example Configuration

Block all IPs except 192.168.110.100:

allow 192.168.110.100;
deny all;

Tip: If you use allow , you should also configure deny ; otherwise all IPs will be allowed.

4. Comprehensive Example

Only the IP 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;
    }
}