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

[joseletk]

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

Step 1 — Changing MySQL configuration
Step 2 — Opening the required port

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:

Code: [Select]

# mysql --help | grep "Default options" -A 1
Output should be something like this:

Code: [Select]

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:

Code: [Select]

# nano /etc/my.cnf
Now just locate the line that contains label [mysqld] and add the following code below:

Code: [Select]

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:

Code: [Select]

# iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
Alternatively, you can grant access to just one IP:

Code: [Select]

# 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:

Code: [Select]

# service iptables save  and you should receive this response:

Code: [Select]

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