How To Allow Remote Access to MySQL

If your web application and database are hosted on the same machine then you don't need to configure MySQL remote access. However, if your web application and database are hosted on different servers then you will need to enable a remote MySQL connection. with time, a distributed environment has become an essential requirement of many projects. A separate database server can improve security and allows you to quickly scale resources.

In this post, we will explain how to enable remote connections to a MySQL database.

Requirements
Install MySQL Server
Change MySQL Bind IP Address
Configure Firewall for MySQL Connection
Grant Remote Access to MySQL Database
Verify MySQL Remote Connection
Grant MySQL Users Read and Write Access
Conclusion

Requirements

A server running Ubuntu operating system.
A root password is set up on your server.

Install MySQL Server

Before starting, you will need to install the MySQL server package to your server. You can install it using the following command:

apt-get install mysql-server -y

Once the MySQL server is installed, start the MySQL service with the following command:

systemctl start mysql

Next, verify the MySQL version with the following command:

mysql -V

Sample output:

mysql Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Change MySQL Bind IP Address

By default, MySQL is configured to listen on localhost. So you will need to configured MySQL to listen to the IP address of the server.

Edit the MySQL server configuration file with the following command:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the bind-address from 127.0.0.1 with 0.0.0.0:

bind-address = 0.0.0.0

Save and close the file then restart the MySQL service to apply the changes:

systemctl restart mysql

Now, verify the MySQL listening connection with the following command:

ss -antpl | grep 3306

Sample output:

LISTEN 0 151 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=15228,fd=24))

Configure Firewall for MySQL Connection

If the UFW firewall is installed and configured on your server then you will need to allow port 3306 to a remote IP address from where the user wants to connect to MySQL.

You can do it with the following command:

ufw allow from remote_ip_address to any port 3306

Where: remote_ip_address is the IP address of the remote machine.

Grant Remote Access to MySQL Database

Before starting, you will need to create a new database if you have not any database.

First, log in to MySQL with the following command:

mysql -u root -p

Once you are login, create a new database called testdb with the following command:

mysql> CREATE DATABASE testdb;

Next, create a new user called testuser with the following command:

mysql> CREATE USER 'testuser'@'remote_ip_address' IDENTIFIED BY 'password';

Next, grant remote user access to a specific database using the following command:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'remote_ip_address' WITH GRANT OPTION;

Next, flush the privileges using the following command:

mysql> FLUSH PRIVILEGES;

Next, verify your granted privileges using the following command:

mysql> SHOW GRANTS FOR 'testuser'@'remote_ip_address';

You should get the following output:

+-----------------------------------------------------------------------------------+ | Grants for testuser@remote_ip_address | +-----------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`remote_ip_address` WITH GRANT OPTION | +-----------------------------------------------------------------------------------+

Finally, exit from the MySQL using the following command:

mysql> EXIT;

Verify MySQL Remote Connection

At this point, MySQL is configured to allow remote connection. Now, it's time to verify the MySQL connection from the remote server.

On the remote server, run the following command to connect the MySQL server:

mysql -u testuser -p -h mysql_server_ip

You will be asked to provide a MySQL user password as shown below:

Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Grant MySQL Users Read and Write Access

If you want to grant remote MySQL users read and write access, run the following command:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON testdb.* TO 'testuser'@'remote_machine_ip' WITH GRANT OPTION;

If you want to grant users append-only access, run the following command:

mysql> GRANT SELECT,INSERT ON testdb.* TO 'testuser'@'remote_machine_ip' WITH GRANT OPTION;

If you want to revoke all privileges for testuser, run the following command:

mysql> REVOKE ALL PRIVILEGES on testdb.* FROM 'testuser'@'remote_machine_ip';

Conclusion

In the above guide, we explained how to grant remote access to MySQL. I hope this will help you if you want to host the database on a separate server.