How to Allow MySQL Port and Restrict Access to Specific IPs

Introduction

In this tutorial, we will guide you through the process of allowing the MySQL port (3306) on your server using the UFW firewall, and how to restrict access to specific IP addresses for enhanced security. This setup is ideal for allowing remote access to your MySQL database while minimizing security risks.

Prerequisites

• VPS or Server: MySQL must be installed.
• UFW (Uncomplicated Firewall): Ensure UFW is installed and active.
• Root or Sudo Access: Administrative privileges are required.

Step 1: Open MySQL Port 3306 for All IPs (Not Recommended for Production)

1. Log in to Your Server

ssh user@your_vps_ip

2. Allow Port 3306

sudo ufw allow 3306/tcp

3. Enable UFW (if not already enabled)

sudo ufw enable

4. Check UFW Status

sudo ufw status

You should see a rule allowing traffic on port 3306.

Step 2: Allow MySQL Port for a Specific IP Address

> Recommended for production environments to limit access to trusted IPs only.

1. Remove the Previous Rule (if added)

sudo ufw delete allow 3306/tcp

2. Allow Port 3306 for a Specific IP

Replace <your_ip> with the IP address you want to allow:

sudo ufw allow from <your_ip> to any port 3306

3. Verify the Rule

sudo ufw status

Example output:

To                         Action      From
--                         ------      ----
3306                       ALLOW       <your_ip>

Step 3: Secure MySQL Configuration

1. Bind MySQL to a Specific IP

• Open the MySQL configuration file (usually located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf).
• Find the bind-address line and set it to 0.0.0.0 (to allow all IPs) or to your server's specific IP address.

bind-address = 0.0.0.0

2. Restart MySQL to Apply Changes

sudo systemctl restart mysql

Step 4: Test the Connection

1. Install MySQL Client (if not installed)

sudo apt-get install mysql-client

2. Connect to MySQL from the Allowed IP

mysql -u your_username -p -h your_vps_ip -P 3306

Enter your MySQL password when prompted. If successful, your configuration is correct.

Conclusion

By following this tutorial, you have:

• Opened MySQL port 3306 securely.
• Restricted access to specific IP addresses.
• Configured MySQL for remote access securely.

> Tip: For even stronger security, consider:

> - Enabling SSL/TLS for MySQL connections.

> - Regularly updating your server and MySQL installation.

> - Monitoring access logs for unusual activity.

For more advanced security options, refer to the MySQL documentation and server hardening guides.