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
- Set MySQL Server listen on all interface
- 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 . TOramans
@%
| | GRANT SELECT ONzabbix
.* TOramans
@%
| +--------------------------------------------+ 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.