The World of MySQL & MariaDB

Complete MariaDB Installation Guide for Linux: Ubuntu, CentOS, Debian

Basic Operations
目次

1. What is MariaDB?

Overview and Features of MariaDB

MariaDB is an open-source relational database management system (RDBMS). It is developed based on MySQL and is adopted in many systems and applications due to its high compatibility. Originally, it started when some developers independently launched the MariaDB project in response to MySQL being acquired by Oracle Corporation. Therefore, MariaDB features a design that emphasizes openness and transparency while having the same command system and data structure as MySQL. MariaDB is free to use and supports a wide range of applications, both commercial and non-commercial. It has a wealth of implementation experience especially in Linux server environments and is highly regarded for its 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 some important differences.
  • Differences in Licensing Since MySQL is developed and managed by Oracle Corporation, there may be licensing restrictions for commercial use. On the other hand, MariaDB is based on the GPL (GNU General Public License) and can be used more freely.
  • Development Speed and Community-Driven MariaDB is actively developed by the community, with a tendency to introduce new features quickly. In particular, enterprise-oriented features (such as storage engine options and performance optimizations) are well-equipped.
  • Extensibility and Compatibility MariaDB maintains high compatibility with MySQL while providing its own storage engines (such as Aria and ColumnStore), allowing flexible operation according to the use case. However, as versions progress, there may be differences in compatibility, so caution is needed during migration.

Benefits of Choosing MariaDB

When introducing a database in a Linux environment, MariaDB offers the following benefits:
  • Easy installation with a simple configuration
  • Lightweight and high-speed operation
  • Scalability that can handle large-scale data processing
  • Stable operation over the long term
  • Abundant knowledge and support information from developers worldwide
For these reasons, MariaDB is widely chosen from personal development environments to corporate production environments.

2. Preparation Before Installation

Before installing MariaDB in a Linux environment, there are several points to check and preparation tasks. Skipping this step can sometimes cause troubles later on, so pre-installation preparation is extremely important.

Checking System Requirements

MariaDB is a lightweight and fast database, but it has minimum system requirements to operate. The following are general guidelines for requirements (these may vary depending on the version and usage):
  • OS: Linux distributions such as Ubuntu, Debian, CentOS, RHEL
  • CPU: 1GHz or higher (multi-core recommended)
  • Memory: 512MB or more (1GB or more recommended)
  • Storage: 1GB or more free space (adjust as needed based on data volume)
  • Network: Internet connection required for package retrieval and updates
Note that older Linux distributions may not support the latest MariaDB versions. Check the version of your Linux distribution and take measures such as adding repositories as needed.

Checking and Updating Required Packages

MariaDB installation uses the OS’s standard package manager (such as APT or YUM). Keeping the system up to date beforehand can help avoid dependency issues. The following is an example for Ubuntu/Debian systems:
sudo apt update
sudo apt upgrade -y
For CentOS/RHEL systems, execute as follows:
sudo yum update -y
Also, check if the following packages are installed (add them if not):
  • curl: Used when adding the MariaDB repository
  • gnupg: Used for signature key verification
  • software-properties-common (for Ubuntu): Required for PPA management

Considering the Use of MariaDB’s Official Repository

Many Linux distributions include MariaDB in their standard package repositories, but the provided version may be outdated. If you want to always use the latest stable version, you can also add MariaDB’s official repository. The next section introduces procedures for each OS, but deciding in advance which version to use during installation will make the process smoother.

3. Installation Procedures by Major Distributions

MariaDB is available on various Linux distributions, but the installation methods differ slightly. In this section, we will explain in detail the installation procedures for MariaDB for each of the three representative distributions (Ubuntu, CentOS, Debian).

MariaDB Installation on Ubuntu

On Ubuntu, MariaDB is installed using the APT package manager. This section targets Ubuntu 20.04 / 22.04 LTS. 1. Update Package List
sudo apt update
2. Install MariaDB
sudo apt install mariadb-server -y
This command automatically installs the MariaDB server and related dependency packages. 3. Start and Enable the Service
sudo systemctl start mariadb
sudo systemctl enable mariadb
4. Check Status
sudo systemctl status mariadb
If it displays “active (running)”, it is running normally.

MariaDB Installation on CentOS

On CentOS 7 and CentOS Stream 8, use YUM or dnf. Since the standard repositories provide older versions, it is common to add the official repository. 1. Add Official Repository (Example: MariaDB 10.6)
sudo vi /etc/yum.repos.d/MariaDB.repo
Copy and 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
※ Please adjust the version and OS according to your environment. 2. Install Packages
sudo yum install mariadb-server -y
Or for 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

MariaDB Installation on Debian

Debian also uses APT, but by using MariaDB’s official repository instead of the official one, you can install the latest stable version. 1. Install Dependency Packages
sudo apt install curl software-properties-common gnupg -y
2. Add 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 Encoding Settings

One important point to note when using MariaDB in a Japanese environment is the setting of character encoding. Since Japanese includes multi-byte characters, starting operations with incorrect settings can lead to issues such as garbled text or data corruption.

Recommended Character Encoding in Japanese Environments: UTF-8

In MariaDB, the character encoding utf8mb4 is recommended. It has higher extensibility than the conventional utf8 and supports 4-byte characters such as emojis, making it robust not only for Japanese but also for multilingual environments. The default settings in MySQL-based systems may be latin1 or utf8, and using them as is may cause issues. Therefore, change the settings to utf8mb4 immediately after installation.

How to Edit the Configuration File (my.cnf)

The MariaDB configuration file is usually located at /etc/mysql/my.cnf or /etc/my.cnf. Add or edit the following in the [client] and [mysqld] sections.
[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

Confirming the Settings Have Been Applied

Connect to MariaDB and check if the settings have been applied.
sudo mariadb
After connecting, execute the following command:
SHOW VARIABLES LIKE 'character_set%';
Expected output example:
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 encoding settings have been correctly applied.

Note: Character Encoding of Existing Data

If there are already created databases or tables, their character encodings will not change automatically. If necessary, change them individually using an ALTER statement as follows:
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
Similarly, changes can be made at the table or column level.

5. Security Settings

Right after installing MariaDB, the security is not sufficient. By performing minimum security settings before operation, you can reduce the risks of unauthorized access and information leakage. This section introduces the basic security settings for MariaDB.

mysql_secure_installation Execution

MariaDB comes with a script for initial security hardening that should be run right after installation. That’s mysql_secure_installation. You can run it with the following command:
sudo mysql_secure_installation
When you run it, several questions will be displayed in sequence. I’ll explain each option.
  1. Setting (or updating) the root password → Sets a password for MariaDB’s administrator user “root”. Use a strong password.
  2. Removing anonymous users → By default, “anonymous” users exist. They are unnecessary and dangerous, so deletion is recommended.
  3. Disabling remote root login → Allowing direct access to root from external networks is extremely dangerous. Basically, set it to prohibit.
  4. Removing the test database → The “test” database created for testing can be removed if unnecessary. Deletion is recommended.
  5. Reloading the privilege tables → This applies the above changes immediately. Confirm with “yes”.
By answering all these questions, you can significantly strengthen MariaDB’s security from its initial state.

Firewall Settings (As Needed)

MariaDB typically communicates on port 3306. Whether to open this port depends on the system’s usage.
  • When using in a local environment or on a single server → Keeping port 3306 closed is fine.
  • When allowing access from external sources (e.g., connecting from apps on other servers) → Configure it to open only to the necessary IP addresses.
Example: An example of port restriction using UFW on Ubuntu
sudo ufw allow from 192.168.0.10 to any port 3306

Creating Users Other Than Root (Recommended)

In production, it’s desirable to create and use dedicated user accounts instead of the administrator user “root”. Here’s an example:
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON your_database.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
By separating users per application, you can minimize the impact in case information is leaked.

6. Verifying Operation

Once you’ve completed the MariaDB installation, initial setup, and security measures, let’s finally check if everything is working correctly. This section explains how to verify your connection to MariaDB and execute basic SQL commands.

Connecting to MariaDB

After installation, use the following command to check if you can connect to MariaDB.
sudo mariadb
Or, when logging in by specifying a user and password:
mariadb -u root -p
If prompted for a password, enter the one you set with mysql_secure_installation. If login is successful, a prompt like the following will be displayed:
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 this is displayed, you can confirm that MariaDB is running normally and the connection is successful.

Creating a Database

Next, as a simple test, let’s create a database.
CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
If executed successfully, the database has been created without issues. Check the list of created databases:
SHOW DATABASES;
If testdb is displayed, it’s a success.

Creating a Table and Inserting Data

Let’s 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 ('Tanaka Tarō', 'taro@example.com');

Retrieving Data

To confirm the inserted data, execute the following query.
SELECT * FROM users;
If the information for Tanaka Tarō is displayed in the output, you can determine that the database is working correctly.

Verifying Server Information

To check basic information such as the MariaDB version, execute the following command:
STATUS;
This will display details such as the connection status, server version, and character encoding settings.

7. Frequently Asked Questions (FAQ)

Installing and operating MariaDB in a Linux environment can raise various questions and issues for many users. Knowing solutions in advance provides peace of mind. Here, we’ve compiled in FAQ format the key points that beginners to intermediate users particularly often search for.

Q1. How can I check the version of MariaDB?

After connecting to MariaDB, you can check by executing the following command.
SELECT VERSION();
Alternatively, you can check directly from the command line as follows:
mariadb --version
Example output:
mariadb  Ver 15.1 Distrib 10.6.16-MariaDB, for Linux (x86_64)

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

Please check the following:
  • The package list is not up to date → Execute sudo apt update or sudo yum update and try again
  • The official MariaDB repository is not added correctly → Check the repository configuration file (/etc/apt/sources.list.d/ or /etc/yum.repos.d/)
  • The distribution and MariaDB version are not compatible → Reconfirm the compatible versions on the official MariaDB Download page

Q3. How do I uninstall MariaDB?

For Ubuntu/Debian systems:
sudo apt remove --purge mariadb-server mariadb-client -y
sudo apt autoremove -y
For CentOS/RHEL systems:
sudo yum remove mariadb-server -y
After that, if you also want to delete data and configuration files, execute the following:
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/my.cnf /etc/mysql
* If you have necessary data, be sure to back it up before deleting.

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

Backup (creating 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 very useful for MariaDB migration or disaster recovery.

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

The following causes are possible:
  • bind-address is set to 127.0.0.1 → Change to bind-address = 0.0.0.0 in /etc/mysql/my.cnf or /etc/my.cnf (be careful with security)
  • The firewall is blocking port 3306 → Open the port with sudo ufw allow 3306 etc.
  • The user is not permitted for external connections → You need to create the user in the format user@'%'