How to Allow Remote Connections to MySQL Database on RHEL/CentOS
How to Allow Remote Connection to MySQL Database
Before we begin this guide, you have to know how to establish an SSH connection using PuTTY (Windows) or built-in terminal shell (Linux, macOS). Also, since you will change your MySQL configuration, you need to have root access to the server. If you are a Hostinger VPS user, you can find the login details on hPanel’s Server tab.
Step 1. Changing MySQL Configuration
By default, MySQL is not listening for external connections. You need to change that by adding an extra option in the configuration file. Here are the steps:
- Log in to your server and run this command to determine the location of the MySQL configuration file:
mysql --help | grep "Default options" -A 1
The output should look like this:
- Now that you know MySQL looks at /etc/my.cnf file for options. You need to open it using the nano editor:
- Locate the line that contains [mysqld] label and add the following code below:
Note that you have to replace YOUR.SERVER.IP with your actual dedicated IP address.
- Save the file by pressing CTRL+X (or COMMAND+X if you are on MAC). For changes to take effect, restart your MySQL daemon by running this command:
systemctl restart mysqld
Step 2. Opening The Required Port
- By default, MySQL is set to use TCP 3306 port for external connections. Thus, you need to open this port in the firewall by executing the command below:
iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
Alternatively, you can grant access to just one IP:
iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT
Don’t forget to change 10.5.1.3 in this example to your real IP address.
- Save the iptables configuration by entering:
service iptables save
You should receive this response:
All is set. From now on, your server will allow remote connections to your MySQL database.
In this tutorial, you have learned how to allow remote connections to MySQL. By doing this, you can now access your server’s database even when you are not connected to a local network. To summarize, let’s overview the steps once again:
- Change MySQL configuration so it will listen to external connections.
- Open 3306 port in the firewall to allow remote connection.
Good luck, and be sure to comment below if you have any questions!