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.
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
sudoprivileges. - 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.cnfThe 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 IPAlso, 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.0Save 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.
$ sudo systemctl restart mysqlOn RedHat-based distributions like CentOS:
$ sudo systemctl restart mysqldStep 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 mysqlAny 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 --reloadAll 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 --reloadiptables 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 ACCEPTFinally, 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 -pSo, 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 mysqlOn some configurations like the old and native MySQL authentication plugins, use the below command and enter your root password.
$ mysql -u root -pHere, 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.