How to proxy MySQL connection with Nginx and limit accessible IPs?

1. Preface

Our production environments are basically deployed on cloud servers, such as application servers, MySQL servers and so on. If the MySQL server is directly exposed to the public network, there is a great risk, and the ports of the MySQL server are not open to the public in order to ensure data security.

Coincidentally, the online business encountered a bug, the development partners need to remotely connect to MySQL to view the data, so what should be done?

We can connect via the Nginx proxy (“springboard machine”).

2. Nginx proxy connection

To implement proxy forwarding of connections, we need a server with Nginx installed that is on an intranet with a MySQL server that is accessible from the intranet.

Second, we need to use thengx_stream_core_modulemodule, which is not built by default, we need to add the configure--with-streamto do the build.

You can refer to the article [Nginx basic commands & non-stop version upgrade] for the process of adding, we will not repeat it here.

Since it’s going to be usedngx_stream_core_modulemodule, first and foremost, is to look at the instructions it provides so we know how to configure it.

1）stream

This directive defines the stream server. Leveled with the http block, it is defined in the main block.

• Scope: main

• Syntax: stream {…}

Example:

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

2）server

This directive defines a virtual host, similar to server in an http block. We can define multiple server blocks in the stream block.

• Scope: stream

• Syntax: server {…}

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

3）listen

This directive defines the address and port of the socket on which the virtual host server will listen.

• Scope: server

• Syntax: listen address:port;

Example:

listen 127.0.0.1:3306;
 listen *:3306;
 # The effect is the same as listen *:3306.
 listen 3306;
 listen localhost:3306;

4) Configuration Example

MySQL server, port 3306 (standalone environment)

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

MySQL server, port 3306 (clustered environment)

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

At this point, we can connect via a client such as Navicat, for example.

3. Restriction of access to IP

Implemented a proxy for the connection, everyone can connect to the MySQL server by accessing Nginx, which solves the problem of not being able to connect to the extranet.

To narrow down access even further and ensure data security, we can restrict only IP addresses from the company network to connect through Nginx.

Nginx provides thengx_stream_access_modulemodule, which is very simple and contains only the allow and deny directives.

1）allow

This directive sets the specified IP to allow access. It can be used in conjunction with the deny directive

• Scope: stream, server

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

Example:

# Allow access to 192.168.110.1
 allow 192.168.110.1;
 
 # Allow 192.168.110.1 to 192.168.255.254
 allow 192.168.110.0/16;
 
 # Allow 192.168.110.1 to 192.168.110.254
 allow 192.168.110.0/24;
 
 # Allow all IP access
 allow all;

2）deny

This directive sets the specified IP to disable access. It can be used in conjunction with the allow directive.

• Scope: stream, server

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

# Block access to 192.168.110.1
 deny 192.168.110.1;
 
 # Disable 192.168.110.1 to 192.168.255.254
 deny 192.168.110.0/16;
 
 # Disable 192.168.110.1 to 192.168.110.254
 deny 192.168.110.0/24;
 
 # Block all IP access
 deny all;

3) Configuration Example

Block access from all IPs except 192.168.110.100.

allow 192.168.110.100;
 deny all;

Tips: If allow is specified, it needs to be used in conjunction with deny, otherwise it is to allow access from all IP addresses.

4. Comprehensive cases

Allow only 192.168.110.100 to connect to the MySQL server via Nginx.

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