How to Allow Remote Access to MySQL Database on Ubuntu Linux

Install MySQL Database Server

To get remote MySQL access on Ubuntu, you first need to install the MySQL database server software using simple commands in your terminal. This process sets up the main program that will handle your database operations on your computer. Just type these commands into your terminal: sudo apt update and then sudo apt install mysql-server mysql-client.

sudo apt update
sudo apt install mysql-server mysql-client

After installing MySQL, the commands below can be used to stop, start, and enable MySQL service to always start up when the server boots…

Run these on Ubuntu 18.04 LTS

sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl enable mysql.service

Next, run the commands below to secure the database server with a root password if you were not prompted to do so during the installation…

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

We are securing the MySQL server deployment.

I am connecting to MySQL using a blank password.

The VALIDATE PASSWORD PLUGIN tests passwords and improves security by checking their strength. This plugin allows you to set only secure passwords. You can set up the VALIDATE PASSWORD PLUGIN to enforce strong password policies.

• Press y|Y for Yes, any other key for No: N
• New password: Create New Password
• Re-enter new password: Repeat New Password
• Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
• Disallow root login remotely? (Press y|Y for Yes, any other key for No): Y
• Remove test database and access to it? (Press y|Y for Yes, any other key for No):  Y
• Reload privilege tables now? (Press y|Y for Yes, any other key for No):  Y

MySQL database installation success on Ubuntu Linux requires verification. To test if the MySQL database server installed correctly, run the following commands, which will confirm the server is running and ready for remote access configuration.

sudo mysql -u root -p

type the root password when prompted…

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, 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> 

If you see a similar line, as shown above, the server was successfully installed…

Configure MySQL Remote Access

Configuring MySQL remote access on Ubuntu involves changing a setting called the ‘bind address’ in the MySQL configuration file. This tells the MySQL server which network addresses it should listen on for incoming connections. By changing this to 0.0.0.0, you allow the server to accept connections from any IP address, which is key for remote access.

The `bind-address` setting in the MySQL configuration file controls which network addresses the MySQL server listens on. To allow all IPv4 addresses, change `bind-address` to `0.0.0.0`. This action makes the MySQL server accept connections from any IPv4 network interface on your Ubuntu Linux system. If you use IPv6, adjust the setting accordingly.

On Ubuntu systems with MySQL database server installed, its default configuration file is located at/etc/mysql/mysql.conf.d/mysqld.cnf

Simply run the commands below to open the MySQL configuration file.

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

Depending on your systems, you may find that same configuration file may be at the location below:

sudo nano /etc/my.cnf

When the file is opened, search for a line that begins with bind-address, as shown below. Its default value should be 127.0.0.1.

# this is read by the standalone daemon and embedded servers
# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning

# this is read by the standalone daemon and embedded servers

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

#
# * Fine Tuning

In the same file, you’ll want to comment out the line that begins with skip-networking by putting the # before it. Or delete it together. Then save your changes.

Please add the changes above under the [mysqld] section.

After making the change above, save the file and run the commands below to restart the server.

sudo systemctl restart mysql.service

To verify that the change happens, run the commands below.

sudo apt install net-tools
sudo netstat -anp | grep 3306

, and you should find the result that looks like the one below

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

Now, the server is set up to listen to all IP addresses, but individual IP needs to be explicitly configured to connect to a database.

You must grant access to the remote server to enable a client to connect to a database.

Access from Remote Clients

After you’ve set up your MySQL server to accept remote connections on Ubuntu, you need to create specific user accounts to control access. You grant permission by defining exactly which IP addresses these users can connect from, ensuring only authorized clients can reach your database. For example, you might create a user like ‘da’ allowed to connect from ‘192.168.1.2’ to ‘database_name’.

For example, if you wish for a client computer with IP address 192.168.1.2 to connect to a database called database_name as user database_user, run the commands below after logging onto the database server.

GRANT ALL ON database_name.* TO 'database_user@192.168.1.2' IDENTIFIED BY 'database_user_password';

• database_name is the name of the database that the user will connect to.
• database_user is the name of the database user.
• 192.168.1.2 is the IP from which the client is connecting.
• database_user_password is the password of the database_user account

After running the commands above, you can access the server from the client computer with that assigned IP.

To connect to the server from the approved IP address, run the commands below

mysql -u database_user -p database_user_password -h database_server

That’s it! You’ve successfully configured remote access to the MySQL database server.

Ubuntu Firewall

If your Ubuntu server has a firewall enabled, you will want to open a connection to the database server. Simply run the commands below to open the firewall to the client from the IP address to the port only.

For example, to open the Ubuntu Firewall, allow the IP address 192.168.1.2 to connect to port 3306.

sudo ufw allow from 192.168.1.2 to any port 3306

sudo ufw allow 3306/tcp

That’s it!

Congratulations! You have successfully installed and configured MySQL to allow remote access.