How to Allow Remote Connections to MySQL Database on RHEL/CentOS
access_time
hourglass_empty
person_outline

How to Allow Remote Connections to MySQL Database on RHEL/CentOS

In this article, you will learn how to allow remote connections to MySQL on an RHEL/CentOS server or VPS. This way, you can manage your MySQL database from a distant location, such as your home. However, you need to complete the following steps when you are connected to a local network.

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:

  1. 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:The command to find MySQL configuration file

  2. Now that you know MySQL looks at /etc/my.cnf file for options. You need to open it using the nano editor:
    nano /etc/my.cnf
  3. Locate the line that contains [mysqld] label and add the following code below:
    bind-address=YOUR.SERVER.IP

    Note that you have to replace YOUR.SERVER.IP with your actual dedicated IP address.

  4. 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

  1. 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.

  2. Save the iptables configuration by entering:
    service iptables save

    You should receive this response:iptables command that allow remote connection to MySQL

All is set. From now on, your server will allow remote connections to your MySQL database.

Conclusion

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:

  1. Change MySQL configuration so it will listen to external connections.
  2. Open 3306 port in the firewall to allow remote connection.

Good luck, and be sure to comment below if you have any questions!

The Author

Author

Edgaras G. / @edgaras

Edgaras is a veteran server administrator at Hostinger. He makes sure that every server runs at full throttle and has all the latest technological advancements. When he's not working, Edgaras enjoys skiing and exploring the world.

Related tutorials

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Become a part of Hostinger now!

More in MySQL
Difference Between MySQL and SQL Server
SQL Server vs MySQL: Overview, Similarities, Differences

Close