Tutorial: How to Allow MySQL Port and Restrict Access to Specific IPs

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 particularly useful when you need remote access to your MySQL database but want to minimize security risks.

Prerequisites

• VPS or Server: You need a server with MySQL installed.

• UFW (Uncomplicated Firewall): Ensure UFW is installed and active on your server.

• Root or Sudo Access: You need administrative privileges to modify firewall settings.

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

• Log in to Your Server: Use SSH to connect to your server.

ssh user@your_vps_ip
   

• Allow Port 3306: Use the following command to open port 3306 for MySQL.

sudo ufw allow 3306/tcp
   

• Enable UFW: If UFW is not already enabled, activate it.

sudo ufw enable
   

• Check UFW Status: Verify that the rule has been added.

sudo ufw status
   

You should see a rule allowing traffic on port 3306.

Step 2: Allow MySQL Port for a Specific IP Address

For better security, it is advisable to allow MySQL connections only from trusted IP addresses. Here’s how to do it:

• Remove the Previous Rule (if applicable): If you have already allowed port 3306 for all IPs, remove that rule.

sudo ufw delete allow 3306/tcp
   

• Allow Port 3306 for a Specific IP: Replace <your_ip> with the IP address you want to allow access.

sudo ufw allow from <your_ip> to any port 3306
   

• Verify the Rule: Check the UFW status to ensure the rule is correctly set.

sudo ufw status
   

You should see something like:

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

Step 3: Secure MySQL Configuration

For additional security, ensure that your MySQL server is configured securely:

• Bind MySQL to a Specific IP: Edit the MySQL configuration file to bind the server to a specific IP or all interfaces.

- Open the MySQL configuration file, usually located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf.

- Find the line with bind-address and set it to 0.0.0.0 to allow connections from any IP, or specify your server's IP address.

bind-address = 0.0.0.0
     

• Restart MySQL: Apply the changes by restarting the MySQL service.

sudo systemctl restart mysql
   

Step 4: Test the Connection

From the allowed IP, try connecting to the MySQL server to ensure that everything is working as expected.

• Install MySQL Client (if not already installed):

sudo apt-get install mysql-client
   

• Connect to MySQL:

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

Enter your password when prompted. If the connection is successful, you have correctly configured access.

Conclusion

By following this tutorial, you have successfully configured your server to allow MySQL connections through port 3306 and restricted access to specific IP addresses. This setup enhances the security of your MySQL server while still allowing remote management capabilities.

For further security, consider additional measures such as enabling SSL/TLS for MySQL connections and regularly updating your server and MySQL to patch security vulnerabilities.