Installing MariaDB on Ubuntu

Table of Contents

• Installation and Setup
• Creating a Database User
• Enabling Remote Access
• Frequently Asked Questions

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 we should ensure the system is up-to-date by running:

apt update && apt full-upgrade -y

To install MariaDB run:

apt install mariadb-server -y

The mariadb service should automatically start in the background once the installation has completed, but you can check by running:

systemctl status mariadb

If all is well you should see the following at the beginning of the output:

● mariadb.service - MariaDB 10.3.34 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2022-04-22 15:17:04 UTC; 17s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 1664 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 1131)
      Memory: 63.7M
      CGroup: /system.slice/mariadb.service
      └─1664 /usr/sbin/mysqld

Note: "q" on the keyboard will exit that output.

Next to run the secure installation script, run:

mysql_secure_installation

It will ask you for the current root password, this will be empty by default so just hit "enter".

Enter current password for root (enter for none): 

Now enter "y" and then enter a strong password. Then enter it again to confirm when it asks.

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]

Enter "y" again to remove the anonymous users.

Remove anonymous users? [Y/n]

Enter "y" here too to prevent remote login as the root user (we'll set up a separate user for logging in remotely shortly).

Disallow root login remotely? [Y/n] 

Again enter "y" here as you won't need the test database.

Remove test database and access to it? [Y/n] 

And again enter "y" to reload the privilege tables.

Reload privilege tables now? [Y/n] 

Now you should be able to access MariaDB by running the following and entering the password from earlier when prompted:

mysql -uroot -p

Creating a Database User

Inside the mysql client, create a new user by running the following with a username (instead of "elliot", unless that's your name too) and password:

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

Now you can grant some permissions to the new user (replace the "elliot" username with the one you used in the previous step):

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

If you want to be able to access the database remotely you will need to comment-out the bind-address line in /etc/mysql/mariadb.conf.d/50-server.cnf:

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

Note: to save the file in the nano text editor it's "ctrl+o", then to exit it's "ctrl+x".

Find the following line:

# 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

and replace the bind-address line with:

#bind-address            = 127.0.0.1

Once you have saved the file, restart the mariadb service by running:

systemctl restart mariadb

Now you should be able to access your database remotely, for example:

mysql -u elliot -h 123.45.67.89 -p

Where the -h IP address is the IP address of the server you've created.

One more thing to note if you are connecting via GUI client or programming script, the port number is 3306.

Once you are in the MariaDB server, you'll probably want to create a database, so log in again:

mysql -u elliot -h 123.45.67.89 -p

and run:

CREATE DATABASE mydatabase;

If you are using ufw to manage firewall rules, ensure port 3306 is open by running the following in the Linux command line:

ufw allow mysql

Frequently Asked Questions

How do I secure remote connections with SSL/TLS?

To enable SSL/TLS encryption for MariaDB connections, you need to generate SSL certificates and configure MariaDB to use them. First, generate certificates using OpenSSL or a certificate authority. Then edit /etc/mysql/mariadb.conf.d/50-server.cnf and add SSL certificate paths under the [mysqld] section: ssl-ca, ssl-cert, and ssl-key. Restart MariaDB and require SSL for users with GRANT USAGE ON *.* TO 'user'@'%' REQUIRE SSL;. This encrypts all data transmitted between the client and server, protecting credentials and query data from interception.

What ports need to be opened for remote access?

MariaDB uses port 3306 by default for both local and remote connections. To allow remote access, you need to open this port in your firewall using ufw allow mysql (which opens port 3306) 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. If you're behind a router or cloud firewall, you may also need to configure port forwarding or security group rules to allow traffic on port 3306.

Is it safe to allow remote database access?

Remote database access introduces security risks and should only be enabled when necessary. The main risks include: exposure to brute-force password attacks, potential data interception without SSL/TLS, and increased attack surface for database vulnerabilities. To mitigate these risks: use strong passwords, enable SSL/TLS encryption, restrict access to specific IP addresses using firewall rules (not '%' in user hosts), use non-standard ports, implement fail2ban to block brute-force attempts, and regularly update MariaDB. For production environments, consider using SSH tunneling or a VPN instead of direct database exposure.

How do I restrict access to specific IP addresses?

There are two ways to restrict database access by IP: at the database user level and at the firewall level. For database users, replace '%' with specific IP addresses when creating users: CREATE USER 'user'@'192.168.1.100' instead of 'user'@'%'. For firewall restrictions, use ufw allow from 192.168.1.100 to any port 3306 to only allow connections from specific IPs. The most secure approach uses both methods together: database-level IP restrictions ensure users can only connect from approved addresses, while firewall rules provide an additional network-level security layer. You can also use CIDR notation for IP ranges: 'user'@'192.168.1.0/24'.

Conclusion

You now have a fully functional MariaDB installation on your Ubuntu server with secure remote access configured. By following the mysql_secure_installation script and creating a dedicated user account instead of using root remotely, you've implemented fundamental security best practices. The configuration changes to allow remote connections give you the flexibility to manage your databases from development tools or remote applications while maintaining security.

Remember to use strong passwords for all database users and consider implementing additional security measures for production environments, such as setting up SSL/TLS encryption for database connections or restricting access to specific IP addresses in your firewall rules. With your database server now operational, you can create databases and start building applications that require persistent data storage.