How to Allow Remote Access to MySQL Database Server

The software ecosystem is not new to the notion of distributed systems. You do not need to be physically present in order to interact with your remotely hosted software.

Therefore your MySQL does not need to reside on a local machine for you to fully benefit from its functional features. You can now have the MySQL running on a remote dedicated server and still be guaranteed the same database security and performance as the case with a MySQL running on a local/desktop machine.

This article guide will walk us through setting up and enabling remote access to a MySQL server in Linux. On the remote server/machine hosting your MySQL database software, we need to perform a few configuration steps for it to allow authenticated remote users access.

Create a New MySQL User

It is advisable to work with a remote user who is neither a root MySQL user nor tied to the remote server hosting the MySQL database software.

Access your remote MySQL server and database via SSH.

$ ssh dnyce@192.168.50.130
$ mysql -u root -p
Connect to MySQL Database
Connect to MySQL Database

To create a new MySQL user, we will adhere to the command syntax:

CREATE USER 'username'@'%' IDENTIFIED BY 'your_user_password';

As you have noted, we are using the notation ‘username’@’%’ and not ‘username’@’localhost’ since we want this user to be dynamic enough to access the MySQL server from any other host machine with a different IP address.

mysql> CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password1122';

Let us grant this user some database privileges. For instance, this user can have the same database privileges as the root user only that the root user is bound to the host machine.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%';
mysql> FLUSH PRIVILEGES;
mysql> exit;
Create New MySQL User
Create New MySQL User

[ You might also like: How to Create a New MySQL User via phpMyAdmin ]

Enable Remote MySQL Access

Now that we have the remote user created, it’s time to perform some additional configuration changes. By default, the MySQL bind-address is 127.0.0.1 which implies that only localhost users can interact with the MySQL database.

To give access to other users’ IP addresses to your database server, navigate and open the file MySQL configuration file.

 
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
OR
$ sudo vi /etc/my.cnf

To allow remote access to your MySQL database server, the fields bind-address and mysqlx-bind-address need to be commented out. These configuration changes will prevent the server hosting MySQL database software from only relying on localhost (127.0.0.1) connections. Afterward, any IP address will be allowed to access it.

Enable MySQL Remote Access
Enable MySQL Remote Access

Save and close the file.

Restart Your MySQL server and then identify the IP address of the MySQL server.

$ sudo systemctl restart mysql
$ ifconfig
Find Linux Server IP Address
Find Linux Server IP Address

Attempting Remote MySQL Database Access

To directly connect to the MySQL database server remotely, adhere to the following MySQL syntax:

$ mysql -u username -h mysql_server_ip_adress -p

In our case, the command will look like the following:

$ mysql -u remote_user -h 192.168.50.130 -p
Access Remote MySQL Database
Access Remote MySQL Database

If you run into the above error as depicted by the screen capture, you will need to configure your MySQL user to use a native password. Sign-in back to your MySQL server via SSH and implement the command:

mysql> ALTER USER 'remote_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password1122'; 
mysql> exit;

Afterward, exit the MySQL database shell and restart the MySQL daemon.

$ sudo systemctl restart mysql
Configure MySQL Native Password
Configure MySQL Native Password

Re-attempt remote access to your MySQL server.

$ mysql -u remote_user -h 192.168.50.130 -p
Connect to Remote MySQL Database
Connect to Remote MySQL Database

And just like that, you can directly and remotely gain access to a MySQL database server from a remote client computer configured to an IP address of your choice.

Enable Remote MySQL Access in Firewall

If you have a firewall enabled on your remote server, you might need to grant access to MySQL’s default port 3306 and grant access to remote_ip_address as shown.

UFW Firewall

$ sudo ufw allow from remote_ip_address to any port 3306

FirewallD

$ sudo firewall-cmd --new-zone=mysqlrule --permanent
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --permanent --zone=mysqlrule --add-source=remote_ip_address
$ sudo firewall-cmd --permanent --zone=mysqlrule --add-port=3306/tcp
$ sudo firewall-cmd --reload

Iptables

$ sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp -s remote_ip_address --dport 3306 -j ACCEPT

That’s all! In this article, you have learned helpful insight into the common ideas of remote MySQL access in Linux.

3 thoughts on “How to Allow Remote Access to MySQL Database Server”

Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.