Allow Remote Connections to MySQL - Best Method ?

Installing MySQL server and using it from the same host is a very common practice by Server Administrators. However, it's highly recommended that we should separate the MySQL server and client, the MySQL server should be accessed from a remote host. This method helps the system become more secure and increases performance.

Here at LinuxAPT, as part of our Server Management Services, we regularly help our Customers to perform related MySQL queries.

In this context, we shall look into how to configure the MySQL server to allow remote connection from its client.

Steps to Connect to a MySQL database remotely

In order to perform these steps, ensure that you log in as the root MySQL user and do the following.

1. Configure the MySQL server

To begin, we will configure the MySQL server to listen on a pre-defined IP or all IP addresses on your server.

If the MySQL server is in the same network as the clients, you should configure the listen port on the private IP.

On another hand, if you need to connect to the MySQL server through a public network, let's configure the server.

You should Open the configuration file by running the below command:

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Then, Go to the line that begins with bind-address.

By default, the value of bind-address is 127.0.0.1.

If you want to set the MySQL server to listen on all of the IP addresses, configure the value of bind-address = 0.0.0.0

To make the change effect, let’s restart the MySQL service:

$ sudo systemctl restart mysql

2. Grant access to a user from remote hosts

Here, you will see how to allow access to a database to remote users.

To do this, start by Logging in to the MySQL server with the command:

$ sudo mysql

Then run the below query:

mysql> GRANT ALL ON DATABASE-NAME.* TO username@IP-ADDRESS IDENTIFIED BY 'USER-PASSWORD';

For example:

mysql> GRANT ALL ON linuxapt.* TO user@10.0.0.5 IDENTIFIED BY 'mysecuredpass';

3. Configure Firewall

The MySQL server listens on port 3306. You have to open this port to allow traffic from the remote hosts. You can use one of these below methods:

When using iptables, run the below command:

$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT

When using ufw, open port 3306 by simply running the below command:

$ sudo ufw allow 3306/tcp

[Need assistance in fixing MySQL database issues? We can help you. ]