Enabling Remote MySQL Connections and Opening Port 3306 on CentOS 7

When working with servers, it is sometimes necessary to make databases accessible from remote devices or servers. This is particularly important for database management or transferring data between servers. However, due to security and configuration considerations, MySQL is, by default, configured to accept only local connections. In this article, we will explain the steps to allow remote MySQL connections and how to configure CentOS 7 to allow remote access through the necessary security settings.

1. Enabling Remote MySQL Connections

By default, MySQL only allows connections from localhost, meaning that MySQL can only be accessed from the server itself, and remote devices are not able to connect. To enable remote access, we need to modify some restrictions in the MySQL configuration file.

Steps:

Connect to Your Server via SSH:

To modify the MySQL configuration, you need to log in to your server using SSH. Once connected, open the MySQL configuration file with the following command:

nano /etc/my.cnf
Modify or Remove bind-address and skip-networking:

In the configuration file, locate the line that defines bind-address. This line typically looks like this:

bind-address = 127.0.0.1

This line tells MySQL to only accept connections from localhost (127.0.0.1). To allow remote connections, either remove this line entirely or set the address to 0.0.0.0, which allows connections from any IP address:

bind-address = 0.0.0.0

Additionally, if the line skip-networking is present, it should be removed. This line disables all network access to MySQL, which would prevent remote connections.
Restart the MySQL Service:

After making changes to the configuration file, restart the MySQL service for the changes to take effect. Use the following command:

service mysqld restart

Alternatively, if your system uses the mysql service:

service mysql restart

2. Opening Port 3306 on CentOS 7

On CentOS 7, you must ensure that the necessary port (3306) for MySQL connections is open in the firewall. If this port is closed, remote devices will be unable to connect to your MySQL server.

Steps:

Check if Firewalld is Running:

First, verify that the firewalld service is running by using the following command:

systemctl status firewalld

If firewalld is not running, start it with these commands:

systemctl start firewalld systemctl enable firewalld
Open Port 3306:

Once firewalld is running, open port 3306 (the default MySQL port) by running the following command:

firewall-cmd --permanent --zone=public --add-port=3306/tcp

This command will open port 3306 for TCP connections and make the change permanent (so it persists after a reboot).
Reload the Firewall:

For the changes to take effect, reload firewalld:

firewall-cmd --reload

3. Security Considerations

Allowing remote access to your MySQL server introduces some security risks. Therefore, it is important to take additional precautions:

Use Strong Passwords: Ensure that MySQL users with remote access permissions have strong, complex passwords to prevent unauthorized access.
Limit IP Addresses: If possible, restrict access to specific IP addresses instead of allowing all remote connections. For example, you can allow a specific user to connect from a specific IP address with this SQL command:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'192.168.1.100' IDENTIFIED BY 'password';
Use SSL/TLS: For added security, consider using SSL/TLS to encrypt MySQL connections, ensuring that sensitive data is not exposed over the network.

Conclusion

In this article, we explained the steps to enable remote MySQL connections and configure the necessary firewall settings on CentOS 7. These steps are crucial for database administrators and those needing to access MySQL databases from remote servers. However, always consider security when enabling remote access and take the necessary precautions to protect your data.