Guide to Get MySQL Remote Connections on CentOS, hPanel and RHEL
MySQL databases and web servers are widely managed with the help of the same local machine. Still, this can cause many inconveniences that might grow into bigger issues.
With this in mind, corporations and teams often go for remote MySQL servers just to allow the web server and the database to develop at a comfortable speed and grow to the necessary extent. As a result, the server and the database available remotely have shown excellence in improving hardware performance and dealing with management and security issues.
The goal of MySQL server is to be sensitive to incoming connections that come locally. So that’s its default configuration that implies no access from any remote system can occur.
Still, achieving remote access to the chosen database is possible, namely with a CentOS server and RHEL. Keep reading this guide to see how exactly to do it.
Allowing Remote Connections to a MySQL Database Server on CentOS or RHEL
To actually see this guide work, you need to have the following tools at your disposal:
- A remote MySQL server.
- Command line / terminal window.
- Local and remote machines with root privileges.
If the server doesn’t belong to your data center, you will have to establish an SSH connection first. If you do it on Windows, use PuTTY. While using Linux and MacOS, it’s best to utilize the built-in terminal shell.
As a user of a VPS server, you can find the login data if you look on the hPanel’s tab named ‘Servers’.
How to Alter the MySQL Server Configuration File
Now, it is time to give the task to your remote server to ‘catch’ external connections. Here is how to add a new option to a configuration file.
Log to the server as its root user and launch this command to control the location of the MySQL configuration file:
mysql --help | grep "Default options" -A 1
The output is going to look like this:
Next, get down to editing the MySQL configuration file with the help of the nano editor:
nano /etc/my.cnf
When you get to open the file, place the line with the mark [mysqld] and complete it with the following piece of code:
bind-address=YOUR.SERVER.IP
Substitute YOUR.SERVER.IP with a definite IP address of your remote system that is the target for the MySQL server to listen to.
If you see the skip-networking phrase among the lines of code, make it a comment (by placing a #) or just remove it. Otherwise, this piece will not allow the server to ‘catch’ TCP/IP connections.
Once the editing is over, click ‘CTRL+X’ or ‘COMMAND+X’ (for Mac) to save the file. The changes you anticipate are going to work after restarting MySQL. This is performed with the following command:
systemctl restart mysqld
How to Open the Necessary MySQL Port
Next, one needs to deal with opening the right port because the default port in MySQL for external connections is always 3306. If the firewall service is already configured on the MySQL server, then the next logical step is letting traffic go through the port.
This works if your default firewall tool is iptables, issue the following command:
iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
Now, this command opened port #3306 for an unlimited amount of traffic and made remote access available for all IP addresses.
If you don’t want to allow this kind of access, just grant access to the server for just one specific IP address with the help of this command:
iptables -A INPUT -i eth0 -s remote_IP_address -p tcp
--destination-port 3306 -j ACCEPT
Don’t also forget to change the remote_IP_adress to the necessary IP address of the remote host.
Finally, save the renewed iptables configuration by entering:
service iptables save
The response is going to look like this:
From now on, the server will grant remote connections to the MySQL service.
Check the results by running the MySQL server to make the connection from the remote system with the following command:
mysql -u username -h mysql_server_ip -p
The ‘username’ should be replaced with your MySQL username and mysql_server_IP with the actual IP address (or the server's name). By looking at the -p parameter, you will be given a hint to log into your MySQL user account with the help of the password.
Here is what the output is going to look like:
Connection to <em>mysql_server_ip</em> 3306 port [tcp/mysql] succeeded!
Allowing Remote Connections to MySQL Database Server on hPanel
The tool can establish a remote connection to the MySQL server. This is done by allowing the IP from one’s account.
First, log in to your hPanel and go directly to Databases – Remote MySQL.
The Remote MySQL page is going to let you enter the IP address of the remote server by going to the IP field or the AnyHost box. This will enable you to connect from literally any IP address.
Next, choose the target database for remote access and select ‘Create’.
But that is not all. Remote connections like this need a MySQL server hostname from a MySQL user. If you’re curious about how to get it, scroll to the top of this page.
Final Word
This short guide has shown how to connect to the MySQL server using remote host access and create access from a desired IP address for a remote MySQL user.
To sum it all up about the stages of accepting the remote connection from hosts on a CentOS or RHEL server:
- Changing MySQL configuration file. This is done using the command line to add another option and a new remote user IP address to the MySQL config file.
- Opening the necessary MySQL port. This is achieved by adding an entry to the rules of your firewall, specifically for port 3306.
Best of luck to you! Leave comments below this guide if you still have questions and ‘white spots’ in establishing remote MySQL connections.