Expose MySQL and MariaDB Database Server to the Internet

MariaDB/MySQL database server only runs on the IP 127.0.0.1 or the hostname localhost by default. So, it is not accessible from other computers on your local network or the internet. In order to make MariaDB/MySQL accessible from other computers on your local network or the internet, you have to do a little bit of configuration.

In this article, I will show you how to expose MySQL and MariaDB database server to the internet. I will be using Ubuntu 18.04 LTS to demonstrate the processes. But, it should work in other Linux distributions as well. The configuration files may be in a different location. That’s the only difference. So, let’s get started.

Do I Need a Public IP Address?

If you want to expose MySQL or MariaDB to the internet, then you will need a public IP address. Otherwise, your server won’t able accessible from the internet.

If you want to access MySQL or MariaDB from only your local network (LAN), then the procedures shown here should work. In that case, you don’t need a public IP address.

Installing MariaDB/MySQL Ubuntu:

MariaDB/MySQL is available in the official package repository of Ubuntu. So, it is easy to install.

First, update the APT package repository cache with the following command:

$ sudo apt update

Now, you can install MySQL or MariaDB database on Ubuntu. They are both technically the same. The only difference is; MySQL is developed by Oracle and MariaDB is community developed. The license of MariaDB is more open than MySQL. MariaDB is a fork of MySQL.

You can install MySQL on Ubuntu with the following command:

$ sudo apt install mysql–server mysql–client

If you want to use MariaDB instead of MySQL, you can install it on Ubuntu with the following command:

$ sudo apt install mariadb–server mariadb–client

Once you’ve run the required command to install your desired database package, press y and then press <Enter>. I will go with MariaDB.

MySQL/MariaDB should be installed.

Changing Bind Address of MySQL/MariaDB:

Now, you have to change the bind address of MySQL/MariaDB.

If you’ve picked MySQL, then the configuration file to edit is /etc/mysql/mysql.conf.d/mysqld.cnf

If you’ve picked MariaBD, then the configuration file to edit is /etc/mysql/mariadb.conf.d/50-server.cnf

Now, edit the required configuration file (in my case the MariaDB configuration file /etc/mysql/mariadb.conf.d/50-server.cnf) with the following command:

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

Now, scroll down a little bit and find the line as marked in the screenshot below.

Once you do find the line, comment out the line (by putting a # at the beginning of the line).

Now, save the configuration file by pressing <Ctrl> + x followed by y and then press <Enter>.

Now, you have to restart MySQL/MariaDB service.

If you’ve installed MySQL, then run the following command:

$ sudo systemctl restart mysql

If you’ve installed MariaDB, then run the following command:

$ sudo systemctl restart mariadb

Creating New Users:

Now, in order to access MySQL/MariaDB remotely, you have to create at least one MySQL/MariaDB database user with remote access privileges.

To do that, login to the MySQL/MariaDB database console as root with the following command:

$ sudo mysql –u root

NOTE: By default, MySQL/MariaDB server has no root password set. If you’re using an existing MySQL/MariaDB server, then it may have root password set. In that case, you can login to the MySQL/MariaDB console as follows:

$ sudo mysql –u root –p

You should be logged in to the MySQL/MariaDB console.

Now, create a database user with the following SQL command:

CREATE USER ‘your_username’@‘host_ip_addr’ IDENTIFIED BY ‘your_password’;

NOTE: Replace your_username and your_password depending on what you want the username and password to be. Here, host_ip_addr is the hostname or IP address of the computer from where you want to connect to the MySQL/MariaDB server. You can also use % as host_ip_addr if you want to connect from any computer. It can also be something like 192.168.2.% if you want to connect from computers from the IP range 192.168.2.1 – 192.168.2.254.

Now, grant privileges to the necessary databases to the user you just created. I will just let the user use all the databases.

GRANT ALL PRIVILEGES ON *.* TO ‘shovon’@‘%‘;

NOTE: *.* means all databases. You can also use db_name.* to only let the user use the database db_name.

Now, apply the changes with the following SQL command:

FLUSH PRIVILEGES;

Now, exit out of the MariaDB/MySQL console with the following command:

quit

Connecting to the MySQL/MariaDB Server Remotely:

In order to access the MySQL/MariaDB server remotely, you need to know the IP address or hostname of the MySQL/MariaDB server.

To find the IP address of the MySQL/MariaDB server, run the following command on the server:

$ ip a

As you can see, the IP address is in my case 192.168.21.128. It will be different for you. So, make sure you replace it with yours from now on.

You can access the MySQL/MariaDB server from any MySQL/MariaDB client programs including the traditional terminal based mysql client program. There are many graphical MySQL/MariaDB IDEs such as DataGrip, MySQL Workbench etc. In this section, I am going to connect to the MySQL/MariaDB server from the terminal based mysql client program. The MySQL/MariaDB client program is not installed by default. But, you can install them very easily.

To install the MySQL client tools, run the following command:

$ sudo apt install mysql-client -y

To install the MariaDB client tools, run the following command:

$ sudo apt install mariadb-client -y

NOTE: You can install either the mysql-client or the mariadb-client and you will be able to connect to the MySQL/MariaDB server from any of these clients.

Now, from your client machine, connect to the MySQL/MariaDB server remotely with the following command:

$ mysql –u your_username –h host_ip_addr –p

Note: Repalce your_username with your MySQL/MariaDB username and host_ip_addr with the hostname or IP address of your MySQL/MariaDB server.