Practical MySQL & MariaDB

MariaDB on Linux: What It Is, How to Install (Ubuntu/CentOS/Debian), and Secure Setup

Basic Operations
目次

1. What Is MariaDB?

Overview and Key Features of MariaDB

MariaDB is an open-source relational database management system (RDBMS). It is developed based on MySQL and is widely adopted in many systems and applications due to its high compatibility.

Originally, after MySQL was acquired by Oracle, some of its developers branched off and launched the MariaDB project. As a result, MariaDB retains a command set and data structures similar to MySQL while emphasizing openness and transparency in its design.

MariaDB is free to use and supports a wide range of purposes for both commercial and non-commercial use. In particular, it has a strong track record in Linux server environments and is highly regarded for stability, performance, and security.

Differences from MySQL

MariaDB and MySQL are very similar, and many commands and configuration files can be used as-is. However, there are several important differences.

  • Licensing differences
    Because MySQL is developed and managed by Oracle, licensing restrictions may apply in some commercial-use scenarios. MariaDB, on the other hand, is based on the GPL (GNU General Public License), allowing more flexible use.
  • Development speed and community-driven approach
    MariaDB is actively developed in a community-driven manner and tends to introduce new features more quickly. It is especially strong in enterprise-oriented features (such as storage engine options and performance optimizations).
  • Extensibility and compatibility
    MariaDB maintains high compatibility with MySQL while also providing its own storage engines (such as Aria and ColumnStore), enabling flexible operations depending on your use case. However, as versions evolve, compatibility differences can appear, so caution is required when migrating.

Benefits of Choosing MariaDB

When deploying a database in a Linux environment, MariaDB offers the following benefits:

  • Easy installation with a simple configuration
  • Lightweight and fast performance
  • Scalability that can handle large-scale data processing
  • Stable operation over the long term
  • Extensive knowledge and support resources from developers worldwide

For these reasons, MariaDB is chosen for a wide range of environments, from personal development setups to enterprise production systems.

2. Preparation Before Installation

Before installing MariaDB in a Linux environment, there are several points to confirm and preparation steps to complete. Skipping this step can lead to problems later, so advance preparation is very important.

Check System Requirements

MariaDB is a lightweight and fast database, but it still has minimum system requirements. Below are general guideline requirements (they vary depending on the version and how you use it):

  • OS: Linux distributions such as Ubuntu, Debian, CentOS, and RHEL
  • CPU: 1GHz or higher (multi-core recommended)
  • Memory: 512MB or more (1GB or more recommended)
  • Storage: 1GB or more of free space (adjust as needed depending on data volume)
  • Network: Internet connection required to fetch packages and apply updates

Note that on older Linux distributions, the latest MariaDB versions may not be supported. Check your Linux version, and if necessary, add repositories or take other steps accordingly.

Verify and Update Required Packages

To install MariaDB, you typically use the OS’s standard package manager (such as APT or YUM). Updating your system beforehand helps avoid dependency issues.

Here is an example for Ubuntu/Debian:

sudo apt update
sudo apt upgrade -y

On CentOS/RHEL, run the following:

sudo yum update -y

Also, verify that the following packages are installed (install them if they are missing):

  • curl: Used when adding the MariaDB repository
  • gnupg: Used to verify signing keys
  • software-properties-common (mainly for Ubuntu): Required for managing PPAs. Not always necessary on Debian.

Consider Using the Official MariaDB Repository

In many Linux distributions, MariaDB is included in the default package repositories, but the provided version may be older.

If you want to always use the latest stable release, you can add the official MariaDB repository. The next section introduces OS-specific steps, but deciding in advance which version you will use will make the installation smoother.

3. Installation Steps by Major Distribution

MariaDB is available on many Linux distributions, but installation methods differ slightly. In this section, we’ll walk through detailed installation steps for three representative distributions: Ubuntu, CentOS, and Debian.

Installing MariaDB on Ubuntu

On Ubuntu, you install MariaDB using the APT package manager. This section targets Ubuntu 20.04 / 22.04 LTS.

1. Update the package list

sudo apt update

2. Install MariaDB

sudo apt install mariadb-server -y

This command automatically installs MariaDB and its required dependency packages.

3. Start and enable the service

sudo systemctl start mariadb
sudo systemctl enable mariadb

4. Verify it’s running

sudo systemctl status mariadb

If you see “active (running)”, it has started successfully.

Installing MariaDB on CentOS

On CentOS 7 and CentOS Stream 8, you use YUM or dnf. Since the default repositories often provide older versions, it’s common to add the official repository.

1. Add the official repository (example: MariaDB 10.6)

sudo vi /etc/yum.repos.d/MariaDB.repo

Paste the following and save:

[mariadb]
name = MariaDB
baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.6/yum/centos7-amd64
gpgkey=https://downloads.mariadb.com/MariaDB/MariaDB-Server-GPG-KEY
gpgcheck=1

*Adjust the version and OS settings as needed for your environment.

2. Install packages

sudo yum install mariadb-server -y

Or on CentOS Stream 8 and later:

sudo dnf install mariadb-server -y

3. Start and enable the service

sudo systemctl start mariadb
sudo systemctl enable mariadb

Installing MariaDB on Debian

Debian also uses APT, but by using the official MariaDB repository instead of the default repositories, you can install the latest stable release.

1. Install dependency packages

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

2. Add the MariaDB repository (example: Debian 11 “Bullseye”)

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

3. Install

sudo apt update
sudo apt install mariadb-server -y

4. Start and enable the service

sudo systemctl start mariadb
sudo systemctl enable mariadb

 

4. Character Set Configuration

One of the most important points when using MariaDB in a Japanese environment is configuring the character set (encoding). Because Japanese includes multibyte characters, starting operation with incorrect settings can lead to issues such as garbled text or data corruption.

Recommended Character Set for Japanese Environments: UTF-8

For MariaDB, the recommended character set is utf8mb4. It is more capable than the legacy utf8 and supports 4-byte characters such as emoji, making it suitable not only for Japanese but also for multilingual environments.

In MySQL-family default settings, you may see latin1 or utf8, and leaving those as-is can cause issues. Therefore, change the setting to utf8mb4 right after installation.

How to Edit the Configuration File (my.cnf)

MariaDB’s configuration file is typically located at /etc/mysql/my.cnf or /etc/my.cnf. Add or edit the [client] and [mysqld] sections as shown below.

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

After changing the settings, restart MariaDB to apply them:

sudo systemctl restart mariadb

Verify the Settings Were Applied

Connect to MariaDB and confirm that the settings have been applied.

sudo mariadb

After connecting, run the following command:

SHOW VARIABLES LIKE 'character\_set%';

Expected example output:

character_set_client    utf8mb4
character_set_connection utf8mb4
character_set_database   utf8mb4
character_set_results    utf8mb4
character_set_server     utf8mb4

If it looks like this, the character set configuration has been applied correctly.

Note: Character Sets for Existing Data

If you already have existing databases or tables, their character sets will not change automatically. If needed, you must change them individually with an ALTER statement like the following:

ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

Similarly, you can change settings at the table or column level.

5. Security Configuration

Right after installing MariaDB, security is not fully hardened. By applying minimum essential security settings before production use, you can reduce the risk of unauthorized access and data leaks. This section introduces basic MariaDB security configuration steps.

Run mysql_secure_installation

MariaDB provides a security hardening script that you should run immediately after installation: mysql_secure_installation.

Run it with the following command:

sudo mysql_secure_installation

When you run it, several questions will be shown in sequence. Here’s what each option means.

  1. Set (or update) the root password
    → Set a password for the MariaDB administrator user “root”. Use a strong password.
  2. Remove anonymous users
    → By default, an “anonymous” user may exist. This is unnecessary and risky, so removal is recommended.
  3. Disallow remote root login
    → Allowing root access directly from external networks is extremely dangerous. In most cases, set this to disallow.
  4. Remove the test database
    → The “test” database created for testing is also unnecessary in many cases. Removal is recommended.
  5. Reload privilege tables
    → This applies the above changes immediately. Confirm with “Yes”.

Answering all of these questions significantly strengthens MariaDB’s security in its default initial state.

Firewall Configuration (If Needed)

MariaDB typically communicates over port 3306. Whether you should open this port depends on how you plan to use the system.

  • When using it locally or on a standalone server
    → You can keep port 3306 closed.
  • When allowing external access (e.g., connecting from an app on another server)
    → Open access only for required IP addresses.

Example: Limiting access with UFW on Ubuntu

sudo ufw allow from 192.168.0.10 to any port 3306

Create a User Other Than root (Recommended)

In production, it’s best practice to create and use a dedicated user account rather than the administrator user “root”. Here is an example:

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

Using separate users per application helps minimize impact in case credentials are compromised.

6. Verification

After installing MariaDB, completing initial configuration, and applying security measures, the final step is to verify that everything is working correctly. This section explains how to confirm connectivity and run basic SQL commands.

Connect to MariaDB

After installation, verify that you can connect to MariaDB with the following command:

sudo mariadb

Or, if you want to specify the user and provide a password:

mariadb -u root -p

When prompted, enter the password you set with mysql_secure_installation.

If login succeeds, you will see a prompt like the following:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.6.16-MariaDB MariaDB Server

If you see this, MariaDB is running normally and you can connect successfully.

Create a Database

Next, as a simple test, create a database.

CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

If it executes successfully, the database has been created without issues.

Check the list of databases you have:

SHOW DATABASES;

If testdb appears, it worked.

Create a Table and Insert Data

Create a test table and insert some data.

USE testdb;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO users (name, email) VALUES ('田中太郎', 'taro-tanaka@example.com');

※This example verifies that multibyte (Japanese) text is stored correctly using utf8mb4.

Retrieve Data

To confirm the inserted data, run the following query.

SELECT * FROM users;

If the output shows the information for Taro Tanaka, you can conclude that the database is working correctly.

Check Server Information

If you want to check basic information such as the MariaDB version, run the following command:

STATUS;

This displays details such as the connection status, server version, and character set configuration.

7. FAQ (Frequently Asked Questions)

When installing and operating MariaDB in a Linux environment, many people encounter common questions or issues. Knowing how to handle them in advance can be reassuring. Here, we’ve summarized frequently searched points in an FAQ format for beginners through intermediate users.

Q1. How can I check the MariaDB version?

After connecting to MariaDB, you can check it by running the following command.

SELECT VERSION();

Or, you can check directly from the command line like this:

mariadb --version

Example output:

mariadb  Ver 15.1 Distrib 10.6.16-MariaDB, for Linux (x86_64)

Q2. What should I do if I see “Package not found” during installation?

Check the following:

  • The package list is not up to date
    → Run sudo apt update or sudo yum update, then try again
  • The official MariaDB repository was not added correctly
    → Check repository configuration files (such as /etc/apt/sources.list.d/ or /etc/yum.repos.d/)
  • Your distribution and MariaDB version are not compatible
    → Re-check supported versions on the MariaDB official Download page

Q3. How do I uninstall MariaDB?

For Ubuntu/Debian:

sudo apt remove --purge mariadb-server mariadb-client -y
sudo apt autoremove -y

For CentOS/RHEL:

sudo yum remove mariadb-server -y

After that, if you also want to remove data and configuration files, run the following:

sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/my.cnf /etc/mysql

*If you have important data, be sure to back it up before deleting anything.

Q4. How do I back up and restore a database?

Backup (create a dump file):

mysqldump -u root -p your_database > backup.sql

Restore:

mysql -u root -p your_database < backup.sql

This method is simple and common, and it’s very useful for MariaDB migrations and disaster recovery.

Q5. Why can’t I connect to MariaDB from outside the server?

Possible causes include:

  • bind-address is set to 127.0.0.1
    → In /etc/mysql/my.cnf or /etc/my.cnf, change it to bind-address = 0.0.0.0 (be careful with security)
  • Port 3306 is blocked by the firewall
    → Open the port with something like sudo ufw allow 3306
  • The user is not permitted for remote connections
    → You need to create a user in the form user@'%'