How to Grant Remote Access to MySQL Database on RHEL/CentOS

Introduction

Let’s say that you want to access the database of your server from a remote location. For example, you are running a game server and it needs to update statistics in the database. This tutorial will cover changes that need to be implemented in order enable remote MySQL access your RHEL/CentOS server.

What you’ll need

Before you begin this guide you’ll need the following:

• SSH access to the server

Step 1 — Changing MySQL configuration

By default, MySQL is not listening for external connections. You will change that by adding an additional option in the
configuration file. Please open your preferred SSH client and access the server. Once there, run this command to determine the
exact location of the configuration file used by MySQL:

mysql --help | grep "Default options" -A 1

Output should be something like this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Since in this case MySQL looks at /etc/my.cnf file for options, you need to open this file by running the command below:

nano /etc/my.cnf

Now just locate the line that contains label [mysqld] and add the following code below:

bind-address=YOUR-SERVER-IP

YOUR-SERVER-IP must be replaced by your server’s dedicated IP address.

Save the file with CTRL+X (or COMMAND+X shortcut if you are on MAC). You have just successfully specified the IP address on which MySQL will now listen for incoming connections.

In order for changes to take effect, please restart your MySQL daemon by running this command:

service mysql restart

Step 2 — Opening the required port

MySQL is set to use TCP 3306 port for connections by default, you need to open this port in the firewall.

Run the following command to open 3306 port for all incoming IPs:

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

Where 10.5.1.3 is the IP address from which you will access the database. Now just save iptables configuration by running:

service iptables save and you should receive this response:

iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]

All is set, from now on you should be able to access your database from a remote location.

Conclusion

Now you got the knowledge what changes should be made in MySQL and firewall configurations so your server could accept incoming external connections. Additionally, you also learned basic iptables commands and how to get the path of MySQL configuration file.