How to Configure MySQL for Remote Connection on Ubuntu 18.04

Simon Bennett · Mar 22, 2020

If you are a system administrator and responsible for managing database servers, you may need to allow remote access to the MySQL database server. For example, if your application is hosted on one server and the database backend is hosted on other servers then you can separate these functions by setting up a remote database. When configured correctly, you can able to connect to the database servers from anywhere around the world where Internet access is available.

In this tutorial, we will show you how to allow remote access to the MySQL server on Ubuntu 18.04.

Prerequisites

A server running Ubuntu 18.04.
MySQL server installed in your system.
A static IP address 192.168.0.100 is configured on MySQL server and 192.168.0.101 is configured on remote MySQL client system.
A root password is configured in your system.

Verify MySQL Server

Before starting, make sure the MySQL server is installed and running on your server. You can verify it by running the following command:

netstat -tnlp | grep mysql

If everything is fine you should see the following output:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 6607/mysqld

In the above output, you should see that MySQL server is running and listening on port 3306.

Configure MySQL Server for Remote Access

By default, MySQL server is configured to listen on localhost only and blocks all remote connections. This will prevent you from accessing the database server from the outside.

In this section, we will learn how to configure MySQL server for remote access in MySQL server version 5.7 and version 8.0.

Configure Remote Access for MySQL Server Version 5.7

To allow MySQL remote connections, you will need to edit the MySQL main configuration file mysqld.cnf.

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

Change the bind-address value from 127.0.0.1 to 0.0.0.0 as shown below:

bind-address = 0.0.0.0

Save and close the file when you are finished. Then, restart the MySQL service to apply the changes.

systemctl restart mysql

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

netstat -tnlp | grep mysql

You should see that MySQL server is now listening on all IP addresses:

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6607/mysqld

Configure Remote Access for MySQL Server Version 8.0 and Later

To allow MySQL remote connections, you will need to edit the MySQL main configuration file mysqld.cnf.

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

Add the following line inside [mysqld] section:

bind-address = 0.0.0.0

Save and close the file when you are finished. Then, restart the MySQL service to apply the changes.

systemctl restart mysql

Grant Access from Remote Clients

At this point, MySQL server is configured to listen on all IP addresses, now you will need to allow remote clients to access the MySQL database. In this section, we will learn how to grant access to a specific database, all databases, specific IP address and all IP addresses.

Grant Access to Specific IP and Specific Database

Here, we will create a database named mydb and grant access to the remote system with IP address 192.168.0.101 to connect to a database mydb.

For MySQL Server Version 5.7

First, log into MySQL server with the following command:

mysql -u root -p

Provide your root password when prompt then create a database named mydb and a user with the following command:

mysql> CREATE DATABASE mydb; mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';

Next, grant permissions to the remote system with IP address 192.168.0.101 to connect to a database mydb with the following command:

mysql> GRANT ALL ON mydb.* to 'myuser'@'192.168.0.101' IDENTIFIED BY 'password' WITH GRANT OPTION;

Next, flush the privileges and exit from the MariaDB shell with the following command:

mysql> FLUSH PRIVILEGES; mysql> EXIT;

Where:

mydb : Name of the MySQL database.

myuser : Name of the MySQL database user.

192.168.0.101 : IP address of the remote system.

password : Password of the MySQL database user.

For MySQL Server Version 8.0 or Later

First, log in to MySQL server with the following command:

mysql -u root -p

Provide your root password when prompt then create a database named mydb and a user with the following command:

mysql> CREATE DATABASE mydb; mysql> CREATE USER 'user1'@'192.168.0.101' IDENTIFIED BY 'password';

Next, grant permissions to the remote system with IP address 192.168.0.101 to connect to a database mydb with the following command:

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'192.168.0.101'; Query OK, 0 rows affected (0.02 sec)

Next, flush the privileges and exit from the MariaDB shell with the following command:

mysql> FLUSH PRIVILEGES; mysql> EXIT;

Grant Access to Specific IP and All Databases

Here, we will grant access to the remote system with an IP address 192.168.0.101 to connect to all MySQL databases.

First, log in to MySQL server with the following command:

mysql -u root -p

Provide your root password when prompt then grant permissions to the remote system with an IP address 192.168.0.101 to connect to all MySQL databases with the following command:

For MySQL Server Version 5.7:

mysql> GRANT ALL ON *.* to 'myuser'@'192.168.0.101' IDENTIFIED BY 'password' WITH GRANT OPTION;

For MySQL Server Version 8.0 or later:

mysql> GRANT ALL ON *.* to 'user1'@'192.168.0.101';

Next, flush the privileges and exit from the MariaDB shell with the following command:

mysql> FLUSH PRIVILEGES; mysql> EXIT;

Grant Access to All IPs and All Databases

Here, we will grant access to all remote systems to connect to all MySQL databases.

First, log into MySQL server with the following command:

mysql -u root -p

Provide your root password when prompt then grant permissions to all remote systems to connect to all MySQL databases with the following command:

For MySQL Server Version 5.7:

mysql> GRANT ALL ON *.* to 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

For MySQL Server Version 8.0 or later:

mysql> GRANT ALL ON *.* to 'user1'@'%';

Next, flush the privileges and exit from the MariaDB shell with the following command:

mysql> FLUSH PRIVILEGES; mysql> EXIT;

Configure Firewall

If your server is configured with the UFW firewall, then you will need to allow MySQL port 3306 through the UFW firewall.

To allow the specific IP address to connect to MySQL port 3306, run the following command:

ufw allow from 192.168.0.101 to any port 3306

To allow all IP addresses to connect to MySQL port 3306, run the following command:

ufw allow 3306/tcp

Next, reload the UFW firewall to apply the changes:

ufw reload

Verify Remote MySQL Access

At this point, the MySQL server is configured to allow the remote system to connect to the MySQL database server. It's time to verify it from the remote system.

First, log into the remote system, open your terminal and connect to your MYSQL server with the following command:

mysql -h 192.168.0.100 -u myuser -p

You will need to provide the password of the myuser as shown below:

Enter password:

Once the connection has been made, you should see the following output:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1682 Server version: 5.7.29-0ubuntu0.18.04.1 (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.

You can now list the databases with the following command:

mysql> SHOW DATABASES;

You should see the following output:

Where:

192.168.0.100 : IP addres of your MYSQL server.

myuser : Username of your MySQL server.

Conclusion

Congratulations! you have successfully configured MySQL server for remote access. I hope this will helps you to host your application and database on their own machines.

a