How to Allow Remote Connections to MySQL Database Server

Introduction

If we have MySQL database running on a server, while the application we are developing is on another host/server, then we have to make a connection among them. So we have to prepare database to be accessible by an applications. Unfortunately, by default, the MySQL server listens for connections only from localhost, which means it can be accessed only by applications running on the same host. On this article we will discuss how to enable remote connection to MySQL Server database from remote host.

For our scenario, we will have the MySQL server database and one application host. MySQL server is hosted on CentOS 8 with IP Address is 192.168.19.128 and Application host is using Ubuntu 20.04 LTS with IP Address is 192.168.19.131. We also have created user database for this tutorial purpose. There are several steps for enabling remote connection toMySQL server database as described below :

• MySQL Server Configuration
  1. Set MySQL Server listen on all interface
  2. Grant access for remote user
• Firewall Configuration
• Access Testing From Remote Host

MySQL Server Configuration

The MySQL server configuration file on CentOS 8 is located on mysqld.cnf file. On my system the complete location is located on /etc/mysql/mysql.conf.d/mysqld.cnf. On this file we could add bind-address parameter to notify, if the server is reachable from the remote. If the address is set to be 0.0.0.0, the MySQL server can accept connections from all host IPv4 interfaces and using 0::0::0::0 for IPv6

1. Set MySQL Server listen on all interface

bind-address=0.0.0.0

After parameter was added, then we restart MySQL daemon, with command line below :

[root@otodiginet ~]# systemctl restart mysqld

2. Grant Access for Remote User

This step is intended to grant an access for remote user who will access to MySQL Server. On this scenario, we will create a new database user called as ‘ramans’ then we will grant him to access from remote. We will login to MySQL with root account, by using command line mysql -u root -p.

[root@otodiginet my.cnf.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE USER 'ramans' @'%' IDENTIFIED BY 'NoMercy20#';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON zabbix.* TO 'ramans'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'ramans'@'%';
+--------------------------------------------+
| Grants for ramans@% |
+--------------------------------------------+
| GRANT USAGE ON . TO ramans@% |
| GRANT SELECT ON zabbix.* TO ramans@% |
+--------------------------------------------+
2 rows in set (0.00 sec)
mysql> ALTER USER 'ramans'@'%' IDENTIFIED WITH mysql_native_password BY 'NoMercy20#';
Query OK, 0 rows affected (0.00 sec)

Firewall Configuration

After configuration inside MySQL Server was done, the next step is configuring Firewall to allow traffic on port who is used by MySQL server. Default port MySQL server is 3306. On our scenario we will us iptables to add the port which is goint to use.

[root@otodiginet etc]# iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
[root@otodiginet etc]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ] 

It will be more secure to use the spesific client IP Address as shown below (we are using IP Address 192.168.19.131):

[root@otodiginet etc]# iptables -A INPUT -i eth0 -s 192.168.19.131 -p tcp --destination-port 3306 -j ACCEPT
[root@otodiginet etc]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]

The output will be as follow :

[root@otodiginet etc]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT udp -- anywhere anywhere udp dpt:domain
ACCEPT tcp -- anywhere anywhere tcp dpt:domain
ACCEPT udp -- anywhere anywhere udp dpt:bootps
ACCEPT tcp -- anywhere anywhere tcp dpt:bootps
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT tcp -- 192.168.19.131 anywhere tcp dpt:mysql

Until here, we have configured MySQL Server to enable it accessed from remote. Now we will test it from remote host.

Access Testing From Remote Host

As mentioned earlier on this article, if we will use remote host for accessing the MySQL server. We will do several task for testing.

1. Testing port 3306 is open and accessible. We will use telnet command line, as below:

rapik@diginetapp01:~/Desktop$ telnet 192.168.19.128 3306
Trying 192.168.19.128…
Connected to 192.168.19.128.
Escape character is '^]'.
^CConnection closed by foreign host.

Result : the port is available and opened.

2. Testing login to MySQL server. We will use ‘ramans’ database user as we have created previously on this article.

rapik@diginetapp01:~/Desktop$ mysql -h 192.168.19.128 -u ramans -p
Enter password:
The ouptu will be :
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3. Testing Using MySQL Workbench.

Conclusion

Finally, the configuration of enable remote connection for MySQL database has been completed done.