How to Allow Remote Connections to MySQL

Introduction

In the traditional deployment, people often install the MySQL server and use it from the same host. 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.

To make the above method possible, we have to configure the MySQL server to allow remote connection from its client.

Configure the MySQL server

Firstly, let’s 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 following the below configuration:

Open the configuration file:

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

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

Grant access to a user from remote hosts

In this part, we will show you the way to allow access to a database to remote users.

Log in to the MySQL server:

$ sudo mysql

Then,

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

For example:

mysql> GRANT ALL ON linuxways.* TO [email protected] IDENTIFIED BY ‘mysecuredpass’;

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:

iptables

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

ufw

If you use ufw to open port 3306, you can simply run the following command:

$ sudo ufw allow 3306/tcp

Conclusion

You’ve already go through the details of how to allow remote connections to a MySQL server.

If you have any concerns, please let me know. Thank you for reading.