Expose MySQL and MariaDB Database Server to the Internet

29/12/2020
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 mysqlserver mysqlclient

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

$ sudo apt install mariadbserver mariadbclient

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/50server.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.

Now, type in the password for your MySQL/MariaDB user and press <Enter>.

You should be connected to the MySQL/MariaDB server remotely as you can see in the screenshot below. I am connected to my MariaDB server.

I can also run MySQL/MariaDB database queries.

So, that’s how you expose MySQL and MariaDB database servers to the internet. Thanks for reading this article.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

Install latest phpMyAdmin on Linux

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. Currently it can create...
28/12/2020

Installing MySQL/MariaDB on Debian 10

In this article, I am going to show you how to install MySQL/MariaDB on Debian 10. So, let’s get started. Installing...
29/12/2020

MySQL GUI Tools for Ubuntu 18.04

In this lesson, we will look at some most popular GUI tools to access MySQL database on Ubuntu 18.04. These vary in application...
29/12/2020
Bài Viết

Bài Viết Mới Cập Nhật

Tìm Hiểu Về Thuê Proxy US – Lợi Ích và Cách Sử Dụng Hiệu Quả
11/12/2024

Mua Proxy V6 Nuôi Facebook Spam Hiệu Quả Tại Onetcomvn
03/06/2024

Hướng dẫn cách sử dụng ProxyDroid để duyệt web ẩn danh
03/06/2024

Mua proxy Onet uy tín tại Onet.com.vn
03/06/2024

Thuê mua IPv4 giá rẻ, tốc độ nhanh, uy tín #1
28/05/2024