How to Allow Remote Connections in MySQL

MySQL is one of the most popular and powerful open-source relational database management system. It is used to power from small blogs to extensive applications with complex data modelling requirements.

By default, MySQL is configured to only accept connections from the local host, which means that remote connections from other are rejected. This enhances security of the MySQL server by ensuring that no users from outside the local machine can connect to it.

However, in some cases, you may need to allow remote connections to your MySQL server, such as when you want to connect to your database from a different computer or server.

In this tutorial, we will guide you through the process of allowing remote connections to MySQL.

Prerequisites

This tutorial requires you to have the following:

• A MySQL server installed and running on your host machine.
• Access to the MySQL server with administrative privileges (typically the root user).
• A remote machine or server from which you want to connect to the MySQL server.

Configure MySQL to Allow Remote Connections

By default, the MySQL server is configured to bind to the local address or 127.0.0.1 which restricts all the connections to the local machine.

https://www.geekbits.io/what-is-localhost/

To allow remote connections on the MySQL server, we need to change the bind address.

We can do this by editing the MySQL configuration file. In most Linux systems, it is typically located in /etc/mysql/mysql.conf.d/mysqld.cnf.

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

In the configuration file, locate the bind-address directive. By default, this is set to 127.0.0.1. To allow remote connections, change the value to 0.0.0.0.

bind-address = 0.0.0.0

Once you have made the changes, save the file and close. Next, you need to restart the MySQL server by running the command:

sudo systemctl restart mysql

Create User For Remote Access

Once we have configured MySQL to accept remote connections, we need to create an account that is allowed remote access.

NOTE: Please avoid using the root user for remote connections. This poses a heavy security risk, especially in production environments.

Open the terminal and connect to the MySQL instance:

sudo mysql -uroot -p

Provide the password for the root user when prompted and run the command below to create a new user.

CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';

Ensure to replace the username and password with your desired username and password. An example is as shown:

CREATE USER 'geekbits'@'%' IDENTIFIED BY 'password';

The command above should create a user that can connect from remote host as specified by the % parameter.

Next, grant full access to a specific database to the created user as shown:

GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'%';

Example:

GRANT ALL PRIVILEGES ON public.* TO 'geekbits'@'%';

This should grant all permissions to the geekbits user on the public database.

Configuring the Firewall.

To allow remote connections to the MySQL server, we need to configure the server’s firewall to permit incoming MySQL traffic.This may vary depending on the Firewall you are using.

For our case, we will demonstrate how to allow MySQL access on UFW.

Run the command:

sudo ufw allow 3306/tcp

Reload the firewall with the command:

sudo ufw reload

Test MySQL Remote Connection

To ensure that remote connections are working correctly, you can test the connection from a remote machine or server using a MySQL client.

On the remote machine, open a terminal

Use the MySQL client to connect to the MySQL server on your host machine. Replace <host> with the IP address or hostname of your MySQL server, <username> with the MySQL username you created, and <password> with the corresponding password:

mysql -h <host> -u <username> -p

Example:

mysql -h 167.91.112.78 -u geekbits -p

Enter the password for the specified user to login.

If the connection is successful, you should now have access to the MySQL server from the remote machine.

Conclusion

In this tutorial, you learned how to configure MySQL to allow remote connections, create a MySQL user for remote access, grant remote access permissions, and test the remote connection