Installing MariaDB on Ubuntu

By Elliot J. Reed • •

How to install MariaDB (MySQL) on Ubuntu 20.04+ and allow remote access

Table of Contents

Installation and Setup

The following assumes you are logged in as the root user on your system.

If you are logged in as another user then first run the following to get into an interactive sudo shell:

sudo -i

First ensure the system is up-to-date:

apt update && apt full-upgrade -y

To install MariaDB run:

apt install mariadb-server -y

The mariadb service should automatically start. Check with:

systemctl status mariadb

Next, run the secure installation script:

mysql_secure_installation

It will ask for the current root password — this will be empty by default, so just hit “enter”. Then enter “y” and set a strong password. Enter “y” again to remove anonymous users, disallow root login remotely, remove the test database, and reload privilege tables.

Now you should be able to access MariaDB:

mysql -uroot -p

Creating a Database User

Inside the mysql client, create a new user with a username and password:

CREATE USER 'elliot'@'%' IDENTIFIED BY 'averystrongpassword';

Grant permissions to the new user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'elliot'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now you should be able to access MariaDB using your new username and password:

mysql -uelliot -p

Enabling Remote Access

To allow remote access, comment out the bind-address line in /etc/mysql/mariadb.conf.d/50-server.cnf: 

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

Find and replace the bind-address line:

# bind-address            = 127.0.0.1

Then restart the mariadb service:

systemctl restart mariadb

Now you should be able to access your database remotely:

mysql -u elliot -h 123.45.67.89 -p

If you are using ufw to manage firewall rules, open port 3306:

ufw allow mysql

To create a database once connected:

CREATE DATABASE mydatabase;

Frequently Asked Questions

What ports need to be opened for remote access?

MariaDB uses port 3306 by default. To allow remote access, use ufw allow mysql or ufw allow 3306/tcp. For better security, restrict access to specific IP addresses using ufw allow from YOUR_IP_ADDRESS to any port 3306.

Is it safe to allow remote database access?

Remote database access introduces security risks. Use strong passwords, enable SSL/TLS encryption, restrict access to specific IP addresses, use non-standard ports, and implement fail2ban. For production environments, consider using SSH tunneling or a VPN instead of direct database exposure.

Conclusion

You now have a fully functional MariaDB installation on your Ubuntu server with secure remote access configured. Remember to use strong passwords for all database users and consider implementing additional security measures for production environments.