Beginner’s Guide to Installing and Configuring MariaDB on Ubuntu

1. Introduction

Features and Benefits of MariaDB

• Open-source license: MariaDB is developed under the GPL (GNU General Public License) and is available for free.
• High compatibility: When migrating data and applications from MySQL to MariaDB, no major modifications are required.
• Enhanced security: MariaDB comes with strengthened security settings by default, allowing you to build a highly secure environment.
• Scalability: It is optimized to handle large datasets and high‑load systems.

Benefits of Using MariaDB on Ubuntu

1. Stability: Ubuntu’s LTS (Long‑Term Support) releases guarantee stability and long‑term security updates.
2. Ease of package management: Using the apt command makes installing and updating MariaDB straightforward.
3. Broad support: Because the Ubuntu‑MariaDB combination is popular among developers, you can easily find troubleshooting information in online documentation and forums.

Article Goal

2. Prerequisites

Recommended Environment

• Ubuntu version:
• We recommend using a supported LTS version (such as 20.04, 22.04, etc.).
• Hardware requirements:
• Memory: at least 512 MB (2 GB or more recommended)
• Disk space: at least 500 MB free (more may be needed depending on data size)
• CPU: processor of 1 GHz or higher (multiple cores recommended)

Dependency Packages and System Requirements

sudo apt update
sudo apt install -y software-properties-common gnupg

sudo apt install -y curl wget

Updating the Repository

sudo apt update
sudo apt upgrade -y

• apt update: Retrieves the latest package list from the current repository.
• apt upgrade: Updates installed packages on the system to their latest versions.

Verifying SSH Connection (for Remote Servers)

ssh username@server_ip_address

3. Installing MariaDB

Installing from the Default Repository

Steps

1. Update the repository.

   sudo apt update

2. Install MariaDB.

   sudo apt install -y mariadb-server mariadb-client

3. After installation, check the status of the MariaDB service.

   sudo systemctl status mariadb

4. If the MariaDB service doesn’t start automatically, start it manually with the following command.

   sudo systemctl start mariadb

5. Configure the service to start automatically.

   sudo systemctl enable mariadb

Advantages

• Using the default repository makes it easier to receive official Ubuntu support.
• Installation is straightforward with simple steps.

Installing Using the Official Repository

Steps

1. Add the official MariaDB repository.

   sudo apt install -y software-properties-common
   sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.aarnet.edu.au/pub/mariadb/repo/10.6/ubuntu focal main'

2. Add the repository’s GPG key.

   sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

3. Update the package list.

   sudo apt update

4. Install MariaDB.

   sudo apt install -y mariadb-server mariadb-client

5. Check the service status and start it.

   sudo systemctl status mariadb
   sudo systemctl start mariadb
   sudo systemctl enable mariadb

Cautions

• When using the official repository, it may conflict with Ubuntu’s default repository. If you encounter errors during installation, remove any conflicting packages.

Post-Installation Verification

Check Version

mysql --version

Connect to MariaDB

sudo mysql

4. Initial Setup and Security Hardening

Running mysql_secure_installation

Executing the Command

sudo mysql_secure_installation

Explanation of Each Prompt

1. Enter current root password

• If this is the first time, no password is set, so press Enter.

1. Set root password

• Choose a strong password. For example: at least 12 characters, mixing letters, numbers, and symbols.

1. Remove anonymous users

• Recommended: Y (delete anonymous users)

1. Disable remote root login

• Recommended: Y (disable remote login for root user)

1. Remove test database

• Recommended: Y (delete the unnecessary default database)

1. Reload privilege tables

• Recommended: Y (apply changes immediately)

Configuring Authentication Plugins

Check Current Authentication Method

sudo mysql -e "SELECT user, host, plugin FROM mysql.user;"

Changing Authentication Method (e.g., to mysql_native_password)

1. Connect to the MariaDB console.

   sudo mysql

2. Change the plugin.

   ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'new_password';
   FLUSH PRIVILEGES;

3. Verify the changes.

   SELECT user, host, plugin FROM mysql.user;

Character Set Configuration

How to Configure

1. Edit the configuration file.

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

2. Add or modify the following lines.

   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_general_ci

3. Restart MariaDB.

   sudo systemctl restart mariadb

4. Verify the configuration.

   SHOW VARIABLES LIKE 'character_set%';

Setting a Password Policy

Configuration Steps

1. Enable the validate_password plugin.

   sudo mysql -e "INSTALL PLUGIN validate_password SONAME 'validate_password.so';"

2. Check or modify the password policy.

   SHOW VARIABLES LIKE 'validate_password%';
   SET GLOBAL validate_password.length = 12;
   SET GLOBAL validate_password.mixed_case_count = 1;
   SET GLOBAL validate_password.number_count = 1;
   SET GLOBAL validate_password.special_char_count = 1;

5. Basic Operations

Managing the MariaDB Service

Starting the Service

sudo systemctl start mariadb

Stopping the Service

sudo systemctl stop mariadb

Restarting the Service

sudo systemctl restart mariadb

Checking the Service Status

sudo systemctl status mariadb

Creating a Database

Connecting to the Console

sudo mysql

Creating a Database

CREATE DATABASE sample_db;

Verifying the Created Database

SHOW DATABASES;

Creating Users and Setting Permissions

Creating a User

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

Granting Permissions

GRANT ALL PRIVILEGES ON sample_db.* TO 'new_user'@'localhost';

FLUSH PRIVILEGES;

Verifying Users and Permissions

SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'new_user'@'localhost';

Backup and Restoration

Creating a Backup

mysqldump -u root -p sample_db > sample_db_backup.sql

Restoring a Backup

mysql -u root -p sample_db < sample_db_backup.sql

6. Troubleshooting

When MariaDB Won’t Start

Symptoms

Solution

1. Check Service Status

   sudo systemctl status mariadb

2. Check Error Log MariaDB’s error log is stored at the following location.

   sudo cat /var/log/mysql/error.log

3. Check Disk Space MariaDB uses disk space for log files and data storage. Insufficient free space can prevent the service from starting.

   df -h

4. Modify Configuration File If there is a configuration error, check MariaDB’s config file (/etc/mysql/mariadb.conf.d/50-server.cnf).
5. Restart Service After fixing the issue, restart MariaDB.

   sudo systemctl restart mariadb

When Permission Errors Occur

Symptoms

Solution

1. Check User Privileges Connect to MariaDB and verify the user’s privileges.

   SHOW GRANTS FOR 'username'@'hostname';

2. Grant Appropriate Privileges Grant the necessary privileges.

   GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';
   FLUSH PRIVILEGES;

3. Verify Hostname Configuration Ensure the host the user is connecting from (e.g., localhost or %) is correct.

Database Connection Errors

Symptoms

Solution

1. Check If MariaDB Allows Remote Connections By default, MariaDB only allows connections from localhost. To permit remote connections, modify the bind-address.

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

   bind-address = 0.0.0.0

2. Firewall Settings Ensure the port MariaDB uses (default: 3306) is open.

   sudo ufw allow 3306
   sudo ufw reload

3. Configure User for Remote Connections Create or configure a user that is allowed to connect from remote hosts.

   CREATE USER 'username'@'%' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';
   FLUSH PRIVILEGES;

Performance Issues

Symptoms

Solution

1. Query Optimization

• Enable the slow query log to identify slow queries. ini slow_query_log = 1 long_query_time = 2
• Check the log: bash sudo cat /var/log/mysql/slow.log

1. Add Indexes

• Adding appropriate indexes to frequently used queries improves search speed. sql CREATE INDEX idx_column ON table_name(column_name);

1. Use Caching Enable query cache in MariaDB’s configuration file.

   query_cache_size = 16M
   query_cache_type = 1

4. Monitor Server Resources Use top or htop commands to check CPU and memory usage, and increase server resources as needed.

7. FAQ (Frequently Asked Questions)

Q1: What is the difference between MariaDB and MySQL?

• License: MariaDB uses the GPL (GNU General Public License) and adheres more strictly to open-source principles than MySQL.
• Features: MariaDB introduces new features faster than MySQL, with enhanced query optimization and plugins.
• Development model: MySQL is led by Oracle, whereas MariaDB is developed by an independent community.

Q2: How do I install a specific version of MariaDB?

1. Add the official MariaDB repository:

   sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.aarnet.edu.au/pub/mariadb/repo/10.6/ubuntu focal main'

2. Update the package list and install:

   sudo apt update
   sudo apt install -y mariadb-server

Q3: I don’t understand the settings of mysql_secure_installation.

• Setting the root password: It is not set by default, so be sure to assign a strong password.
• Removing anonymous users: Deleting anonymous users is recommended to improve security.
• Removing the test database: Delete the test database that comes with MariaDB.
• Reloading privilege tables: Apply configuration changes immediately.

Q4: Why set the character set to utf8mb4?

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Q5: How can I improve MariaDB performance?

• Query optimization: Enable the slow query log to identify poorly performing queries.
• Utilizing indexes: Create indexes on columns that are frequently used.
• Cache configuration: Properly set up the query cache in MariaDB’s configuration file to store reusable results.
• Resource monitoring: Monitor CPU and memory usage to reduce server load.

Q6: Is there a way to automate MariaDB backups?

1. Create a backup script:

   nano /usr/local/bin/backup_mariadb.sh

   #!/bin/bash
   mysqldump -u root -p'password' --all-databases > /backup/mariadb_backup_$(date +%F).sql

2. Make the script executable:

   chmod +x /usr/local/bin/backup_mariadb.sh

3. Set up the cron job:

   crontab -e

   0 3 * * * /usr/local/bin/backup_mariadb.sh

8. Summary and Next Steps

Key Points of the Article

1. Introduction

• We explained the features of MariaDB and the benefits of using it on Ubuntu.

1. Prerequisites

• We reviewed the recommended environment, dependencies, and repository update procedures before installation.

1. Installing MariaDB

• We learned how to install using the default repository and the official MariaDB repository.

1. Initial Configuration and Security Hardening

• We covered security configuration with mysql_secure_installation, setting authentication plugins, and changing character sets.

1. Basic Operations

• We covered managing the service, creating databases, configuring users and privileges, and performing backups and restores.

1. Troubleshooting

• We examined solutions for startup errors, permission issues, connection problems, and performance concerns.

1. FAQ (Frequently Asked Questions)

• We provided detailed explanations of basic MariaDB concepts, useful configuration tips, and automating backups.

Next Steps

Learn Replication

Performance Tuning

Further Strengthening Security

• Configure secure connections using SSL/TLS
• Implement log monitoring and data encryption

Advanced MariaDB Features

• High‑availability clustering with Galera Cluster
• Leverage modern features such as the JSON data type and full‑text search

Reference Links

• MariaDB Official Documentation: https://mariadb.org/documentation/
• Ubuntu Official Guide: https://ubuntu.com/tutorials/