How to Install MariaDB on Ubuntu 24.04

Introduction

Setting up a secure database server is crucial for any application, and MariaDB is a popular choice for its reliability, performance, and active community that provides support and resources for developers. This guide will walk you through the steps to install and configure MariaDB on an Ubuntu 24.04 cloud server instance. By the end, you'll have a secure and ready-to-use database server for your applications.

Prerequisites

Before you begin, ensure you have the following:

• A Vultr Ubuntu 24.04 cloud server instance.
• A user account with sudo privileges. If you don’t have one, you can create it with:

sudo adduser your_user
sudo usermod -aG sudo your_user

• Basic knowledge of command-line operations.
• SSH access to your server.

Connecting to the Ubuntu Cloud Server

1. Before performing any actions, you must first connect to the server via SSH. If you don’t have an SSH key pair, you can generate one with:

ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

Then, use the following command to connect:

ssh -i ~/.ssh/id_rsa your_user@your_server_ip

For enhanced security, consider using a strong passphrase for your SSH key and enabling the SSH agent to avoid having to enter your passphrase every time you connect.

Keeping Your System Up-to-Date: Updating Packages

1. Update the Package Index

Start by updating your package index to ensure you have the latest package listings:

sudo apt update

2. Upgrade the System Packages

After updating the package index, upgrade the installed packages on your system to their latest versions. This step ensures that all existing software is up-to-date and reduces the risk of compatibility issues:

sudo apt upgrade

Install MariaDB

1. Install MariaDB

Install the MariaDB server package using the following command:

sudo apt install mariadb-server

2. Verify MariaDB Installation

Check the installed version of MariaDB to ensure it was installed:

mariadb --version

Output:

mariadb  Ver 15.1 Distrib 10.11.7-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

3. Start and Enable MariaDB

After the installation is complete, start the MariaDB service and enable it to start on boot:

sudo systemctl start mariadb
sudo systemctl enable mariadb

Manage the MariaDB System Service

1. Check MariaDB Status

Ensure MariaDB is running by checking its status:

sudo systemctl status mariadb

You should see a status indicating it is active (running).

● mariadb.service - MariaDB 10.11.7 database server
         Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled)
         Active: active (running) since Mon 2024-06-03 06:11:17 UTC; 8h ago
             Docs: man:mariadbd(8)
                         https://mariadb.com/kb/en/library/systemd/
        Process: 19853 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
        Process: 19863 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
        Process: 19892 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl>    Process: 20014 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
        Process: 20016 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
     Main PID: 19994 (mariadbd)
         Status: "Taking your SQL requests now..."
            Tasks: 10 (limit: 14978)
         Memory: 79.1M (peak: 81.5M)
                CPU: 6.694s
         CGroup: /system.slice/mariadb.service
                         └─19994 /usr/sbin/mariadbd

2. Restart MariaDB

If you need to restart the MariaDB service, use:

sudo systemctl restart mariadb

3. Stop MariaDB

If you need to stop the MariaDB service, use:

sudo systemctl stop mariadb

Secure the MariaDB Server

1. Run the Security Script

This script will strengthen the security of your MariaDB server by setting a strong root password, removing anonymous users, and disabling remote root logins, among other security measures. You can run this script with:

sudo mysql_secure_installation

Follow the prompts to set the root password, remove anonymous users, disallow remote root login, remove test databases, and reload privilege tables.

Access MariaDB

1. Log in to MariaDB

To access the MariaDB shell, use the following command and enter your root password when prompted:

sudo mariadb -u root -p

2. Create a Test Database

After logging in, create a test database called example_vultr:

CREATE DATABASE example_vultr;

Verify the database was created by listing all databases:

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| example_vultr      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3. Create a User and Grant Privileges

Create a new user and grant them full privileges on the example_vultr database:

CREATE USER 'vultr_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON example_vultr.* TO 'vultr_user'@'localhost';
FLUSH PRIVILEGES;

Configure Remote Access (if needed)

Now, let’s set up a new user with remote access to your MariaDB database. This will allow you to connect to the database from other computers.

1. Enable Remote Connections:

To allow connections to your MariaDB server from other computers, you need to modify the configuration file. Open the 50-server.cnf file with a text editor:

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

Locate the line bind-address = 127.0.0.1 and change it to:

bind-address = 0.0.0.0

This change allows MariaDB to accept connections from any IP address.

Save the file (Ctrl+O) and exit (Ctrl+X).

Restart the MariaDB service:

sudo systemctl restart mariadb

2. Create a new user:

mariadb -u root -p

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'your_strong_password';

• Replace remote_user with the desired username.
• Replace your_strong_password with a strong password.
• The % wildcard allows the user to connect from any IP address.

3. Grant privileges to the user:

GRANT ALL PRIVILEGES ON example_vultr.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

4. Connect remotely using the new user:

mariadb -u remote_user -p -h your_server_ip example_vultr

Replace your_server_ip with the IP address of your server.

Important Note: The remote_user can connect to MariaDB from any IP address, but not from the server itself. To connect from the server, you need to use the root user.

Conclusion

With MariaDB installed and secured, you’re ready to dive into the world of relational databases. Start building your applications and let MariaDB manage your data with ease and reliability. For more advanced configurations and optimizations, such as setting up replication or performance tuning, refer to the official MariaDB documentation.