28 December, 2022 by Ashley

How to Allow Remote Connection to MySQL in Linux

MySQL comes with a default configuration that is perfect and enough for users who are hosting their website and MySQL server on a Linux system. What if you wish to connect other systems and users to your MySQL server? This article helps you learn How to Allow Remote Connection to MySQL in Linux. When you buy a Linux VPS, you are ready to install a MySQL server on it which accepts only incoming connections from itself since it listens for connections only from localhost.

But if you decide to not just read and write information from the database on the same server, you need to allow remote connections to your MySQL server. To do this, assign the MySQL service a public IP address on your server and configure your system firewall to accept inbound MySQL connections. Join us with this guide to review all the required steps to allow remote connection to MySQL server in Linux.

Table of Contents

Prerequisites to Allow Remote Connection to MySQL in Linux

To allow remote connection to the MySQL server, you need to do some extra configuration. But before that, consider the below options to let this tutorial work correctly.

A server running Linux.
A non-root user with sudo privileges.
MySQL software installed.

Tutorial Allow Remote Connection to MySQL

Nowadays, it is not surprising to hear about remote connections. The new version of connecting people, employees, and businesses together. After installing a MySQL server on a Linux system, you can provide a situation to connect to the remote MySQL server from your local system and get in touch with all the running apps on the different computers from the database server.

Once you purchase your desired VPS to host your website, it is important to manage your information in databases to protect them. Previously, you read what is Database Management System. Especially if you want to access the MySQL server from a remote location, using DBSM is essential to protect all your valuable data.

Let’s get started to learn what should be done to allow remote connection to a MySQL server to let all your considered users and computers access your data. There are only 3 steps required to complete the process to allow remote connection to MySQL.

Step 1: Configure MySQL server to make it accessible from remote machines

To set up the MySQL server, check the communication status of your MySQL. If the MySQL server and clients can communicate over a private network, it should listen only on the private IP. But you have to set the MySQL server to listen on all IP addresses on the machine if you prefer to connect to the server over a public network.

Your MySQL server must be set up to be accessible from remote systems. To do this, you need to configure a public bind address (Change its value options) in the MySQL configuration file.

So, use your favorite text editor to open the /etc/mysql/mysql.cnf file.

$ sudo nano /etc/mysql/mysql.cnf

The value is set to 127.0.0.1 by default. So, find the lines which says bind-address to change its value to the IP address on which the MySQL server should listen. Set your server’s public IP address instead of the default address.

[mysqld]
bind-address = Your IP

Also, using the wildcard of 0.0.0.0 as your bind address helps you troubleshoot while facing an issue, but it is not recommended.

[mysqld]
bind-address = 0.0.0.0

Save the changes you made when you are finished and exit the file. Run the following command to restart the MySQL service and let the changes take effect.

On Debian and Ubuntu:

$ sudo systemctl restart mysql

On RedHat-based distributions like CentOS:

$ sudo systemctl restart mysqld

Step 2: Configure Firewall to allow remote access through it

MySQL runs on port 3306 by default. In this step, you will review how to allow remote access through your system firewall to make you closer to allowing remote connection to MySQL. According to the distribution you are using, the command would be different. Find what adapts with your own systems’ firewall.

On Ubuntu:

$ sudo ufw allow mysql

Any other system that uses an uncomplicated firewall (ufw), can run the above command to allow remote access through the firewall.

On Red Hat, CentOS, and Fedora:

$ sudo firewall-cmd --zone=public --add-service=mysql --permanent
$ sudo firewall-cmd --reload

All derivative systems that use firewalld can run the above command to allow remote access through the firewall.

If you consider allowing access from a specific IP address on a specific port, create a new zone named ”mysqlzone” as you see below:

sudo firewall-cmd --new-zone=mysqlzone --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --permanent --zone=mysqlzone --add-source=Your IP
sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcp
sudo firewall-cmd --reload

iptables work on almost any system. To allow access from any IP address on the internet to the MySQL port, you can use the following command when you are using iptables as your firewall.

$ sudo iptables -A INPUT -p tcp --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

Finally, it is a good idea to check if the remote user can connect to the MySQL server or not. To verify, run:

mysql -u user_name -h mysql_server_ip -p

So, if you can log in to the remote MySQL server smoothly, you can make sure that all is set up completely.

Note: The command below means:

user_name: The name of the user you granted access to.

mysql_server_ip: The IP address of the host where the MySQL server runs.

Step 3: Configure your user to accept remote connections

finally, the 3rd step to allow remote connection to MySQL in Linux is granting access to a particular user from a remote machine. After enabling the MySQL service to accept incoming connections and then your firewall allowing them through, it is time to allow access to the database to the remote user.

Firstly, run the following command to log in to the MySQL server with your root account:

$ sudo mysql

On some configurations like the old and native MySQL authentication plugins, use the below command and enter your root password.

$ mysql -u root -p

Here, you need to configure the user you have created to make it accessible from the IP address. Again, adapt the command below with your own configuration.

Here, we use RENAME USER command:

mysql> RENAME USER 'OperaVPS'@'localhost' TO 'OperaVPS@'Your IP';

But if you have not created a user and this is the first time you are creating one, use the CREATE USER command and just keep in mind to replace your own username, IP address, and password.

mysql> CREATE USER 'OperaVPS'@'Yourr IP' IDENTIFIED BY 'password_here';

To let your user be accessible from ANY IP address, use % in your command instead of a specific IP address. You can see the way you can edit the existing user to be an accessible user from any IP in the below.

mysql> RENAME USER 'OperaVPS'@'localhost' TO 'OperaVPS'@'%';

Also, you can use the following command to create a new user that can be accessed from any IP address.

mysql> CREATE USER 'OperaVPS'@'%' IDENTIFIED BY 'password_here';

You’re all set. You granted your desired user access to one or more databases to use the account credentials to access the database remotely. Once you passed all the steps of this tutorial, allowing remote connection to MySQL must be correctly completed.

Conclusion

In this article, you learned How to Allow Remote Connection to MySQL in Linux. While MySQL as the most popular open-source database server listens for incoming connections only on localhost, you need to perform the explained steps to be able to allow remote connections to your MySQL server. As you reviewed, we configured the MySQL server to listen on all or a specific interface first. After opening the MySQL port in your firewall, you finished the process by granting access to the remote user. If you follow the above steps properly, you will be able to achieve the purpose of this guide easily.