Configure remote access to MySQL or MariaDB database servers on Ubuntu

This brief tutorial shows students and new users how to configure remote access to MySQL or MariaDB database servers on Ubuntu 17.04 / 17.10 systems. When configured correctly, you will be able to connect to the database servers from a remote system on the same network.

If the server is connected directory to the Internet, you may able able to access it from anywhere around the world where Internet access is available.. however, opening up your database servers directly to the internet is not recommended.

In our next post, we’ll update this tutorial to show you how to enable secure the connection to the database server via SSL so that no one can intercept the communications and analyze the data between the server and the client computers.

When you’re ready to setup remote database access, please continue below.

By default, MySQL or MariaDB only listens for connections from the localhost. All remote access to the server is denied by default. To enable remote access, run the commands below to open MySQL/MariaDB configuration file.

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

on MariaDB server, the file may live below

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Then make the below change below from:

bind-address                              = 127.0.0.1

To

bind-address                               = 0.0.0.0

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

sudo systemctl restart mysql.servicesudo systemctl restart mariadb.service

To verify that the change happens, run the commands below

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 setup to listen to all IP addresses but individual IP needs to be explicitly configure to connect to a database.

To enable a client to connect to a database, you must grant access to the remote server.

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

GRANT ALL ON wpdatabase.* TO 'wpuser@192.168.1.5' IDENTIFIED BY 'new password here';

After running the commands above, you should be able to access the server from the client computer with that assigned IP.

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

sudo mysql -uroot -pdatabaseuser_password -h server hostname or IP address

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

You may want to open Ubuntu Firewall to allow IP address 192.168.1.5 to connect on port 3306.

sudo ufw allow from 192.168.1.5 to any port 3306

Summary:

This post shows students and new users how to connect remotely to MySQL or MariaDB database server. When configured correctly, systems that are granted access to defined databases should be allowed.