Guide to Get MySQL Remote Connections on CentOS, hPanel and RHEL

MySQL databases and web servers are often managed on the same local machine. However, this setup can lead to various issues.

To avoid these problems, businesses and development teams often opt for remote MySQL servers. This allows both the web server and the database to scale independently and efficiently. Remote MySQL servers significantly improve hardware performance and simplify management and security.

By default, MySQL server is configured to prioritize local connections, effectively blocking remote access.

This guide explains how to enable remote access to your MySQL database using CentOS, hPanel, and RHEL.

Allowing Remote Connections to a MySQL Database Server on CentOS or RHEL

Before you begin, ensure you have the following:

1. A remote MySQL server.
2. Command line / terminal access.
3. Root privileges on both local and remote machines.

If your server isn’t in your local data center, you’ll need to establish an SSH connection. Use PuTTY for Windows or the built-in terminal shell for Linux and MacOS.

If you’re using a VPS server, you can find your login credentials in the ‘Servers’ section of your hPanel.

How to Alter the MySQL Server Configuration File

Now, configure your remote server to accept external connections by modifying the configuration file.

Log in to the server as root and use the following command to locate the MySQL configuration file:

mysql –help | grep “Default options” -A 1

The output will look similar to this:

Next, edit the MySQL configuration file using the nano editor:

nano /etc/my.cnf

Locate the [mysqld] section and add the following line:

bind-address=YOUR.SERVER.IP

Replace YOUR.SERVER.IP with the specific IP address of the remote system that you want the MySQL server to listen on.

If you find the line skip-networking, comment it out by adding a # at the beginning, or remove it completely. This option prevents the server from accepting TCP/IP connections.

Save the file by pressing CTRL+X or COMMAND+X (on Mac). Restart MySQL for the changes to take effect:

systemctl restart mysqld

How to Open the Necessary MySQL Port

The next step is to open port 3306, the default port for MySQL external connections. If your server uses a firewall, you need to allow traffic through this port.

If you are using iptables, use the following command:

iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT

This command opens port 3306 for all traffic and allows remote access from any IP address.

To restrict access to a specific IP address, use the following command:

iptables -A INPUT -i eth0 -s remote_IP_address -p tcp

–destination-port 3306 -j ACCEPT

Remember to replace remote_IP_address with the IP address of your remote host.

Finally, save the updated iptables configuration:

service iptables save

The response should look like this:

Your server is now configured to accept remote connections to the MySQL service.

Verify the connection from your remote system using the following command:

mysql -u username -h mysql_server_ip -p

Replace username with your MySQL username and mysql_server_ip with the actual IP address (or server name). The -p option will prompt you for your MySQL user password.

The output should resemble this:

Connection to mysql_server_ip 3306 port [tcp/mysql] succeeded!

Allowing Remote Connections to MySQL Database Server on hPanel

hPanel provides a tool to easily establish remote connections to your MySQL server by whitelisting the IP addresses from specific accounts.

Log in to your hPanel and navigate to Databases – Remote MySQL.

On the Remote MySQL page, enter the IP address of the remote server in the IP field, or check the AnyHost box to allow connections from any IP address.

Select the database you want to enable remote access for and click ‘Create’.

You’ll also need the MySQL server hostname from a MySQL user. Check the beginning of this guide for instructions on how to retrieve it.

Final Word

This guide has demonstrated how to connect to a MySQL server using remote host access and how to create access from a specific IP address for a remote MySQL user.

In summary, the steps to enable remote connections on a CentOS or RHEL server are:

1. Modifying the MySQL configuration file using the command line to add the bind-address option and the remote user IP address.
2. Opening the necessary MySQL port by adding a rule to your firewall, specifically for port 3306.

Good luck! If you have any questions, leave a comment below.

Blog