The World of MySQL & MariaDB

MariaDB Installation & Basics: A Beginner-Friendly Guide

Basic Operations
目次

1. What is MariaDB

MariaDB is an open-source relational database management system (RDBMS) that originated from MySQL. It was originally created by the developers of MySQL and is designed with a focus on data safety and performance. It is highly reliable for both enterprises and individual users, and a major feature is that it is free to use.

Overview and Features of MariaDB

MariaDB is compatible with MySQL and has evolved on MySQL version 5.5. Therefore, migration is easy for MySQL users, and existing databases and code can often be used as‑is. It also incorporates the latest technologies that emphasize security and speed, and it runs efficiently in cloud environments.

Differences from MySQL

MariaDB and MySQL are very similar, but their development direction and licensing differ. MariaDB is actively developed by the open‑source community, with new features and performance improvements released frequently. In contrast, MySQL is managed by Oracle and often includes features geared toward commercial use. Additionally, MariaDB adopts some unique SQL syntax and extensions, so there are features that are only available in MariaDB.

Main Uses and Benefits of MariaDB

MariaDB is used in a wide range of scenarios, from small websites to large‑scale data analytics. It especially shines in environments that demand speed and scalability. The fact that many cloud service providers adopt MariaDB also demonstrates its high performance and reliability.

2. Main Features and Benefits of MariaDB

MariaDB includes many features designed to speed up database processing. Because it is open source, it can be freely used and customized.

High-Performance Database Engines

MariaDB supports multiple database engines such as InnoDB and MyISAM, just like MySQL. In particular, the InnoDB engine excels at transaction management, enabling fast processing while maintaining data integrity. In systems that require large-scale data processing, leveraging these engines can maximize performance.

Free Use Through Open Source

MariaDB is provided under the GPL (GNU General Public License), allowing anyone to use, modify, and distribute it for free. This enables businesses and individuals to freely build systems with MariaDB and customize it as needed. Additionally, the open‑source community offers robust support, making it easy to find information when issues arise.

Support for Large Data and Cluster Environments

MariaDB offers high scalability and operates reliably in large databases and distributed environments. By using features such as Galera Cluster and Replication, data can be spread across, enhancing fault tolerance and performance. This makes it possible to build databases that grow alongside your business.

3. Preparing for MariaDB Installation

Installing MariaDB requires that you prepare the system environment and dependencies. Below we explain the points you should check before installation.

Checking System Requirements

To install MariaDB, the server must meet system requirements such as memory and disk space. In particular, large data volumes or high‑load environments demand sufficient memory and CPU power. Check the latest requirements on the official site and ready your infrastructure.

Verifying Required Software and Dependencies

Installing MariaDB requires that certain packages and dependencies be installed in advance. For example, on Linux you use package managers like apt or yum to install the needed packages. Also, review firewall settings to ensure access to MariaDB isn’t blocked.

The Importance of Backups

Before installing a new database, it’s strongly recommended to back up any existing databases. Especially when migrating from MySQL to MariaDB, compatibility issues can arise, so a pre‑installation backup is essential for data safety.

4. MariaDB Installation Steps

The method for installing MariaDB varies by operating system. Below, we explain the installation steps for Windows, Linux, and macOS environments.

For Windows

When installing MariaDB on a Windows environment, download the installer from the official site and run it. 1. How to download from the official site Visit the MariaDB official site (https://mariadb.org/) and download the Windows installer. You can select the version on the site, and using the latest version is recommended. 2. Setup steps using the installer Run the downloaded installer and follow the installation wizard’s prompts to set up MariaDB. The wizard lets you choose components and the installation directory. Also, because you’ll be asked to set a root user password on first launch, set a strong password for security. 3. Initial configuration and how to start the service After installation completes, the MariaDB service starts automatically. If you need to manage the service manually, you can start or stop MariaDB from the Windows “Services” management tool. Once the service is configured, connect via the command prompt or a MariaDB client tool to verify it’s running.

For Linux

Installing MariaDB on Linux uses different package managers (such as apt or yum) depending on the distribution. Here we present the steps for the common Ubuntu and CentOS. 1. Installation steps using a package manager (Ubuntu)
sudo apt update
sudo apt install mariadb-server
On Ubuntu, you can install MariaDB with the commands above. After installation, the MariaDB service starts automatically. 2. Adding and updating the repository (CentOS) On CentOS, the official repositories may not include MariaDB, so you need to add MariaDB’s official repository before installing.
# Add the repository
sudo yum install -y https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

# Install MariaDB
sudo yum install -y MariaDB-server MariaDB-client
This installs MariaDB, after which you can start the service. 3. Initial configuration and how to start the service After installation, use the following commands to start the MariaDB service and enable it to start on boot.
sudo systemctl start mariadb
sudo systemctl enable mariadb
Once the service is running correctly, perform initial configuration and harden security. Run the mysql_secure_installation script with the next command to set the root password, remove anonymous users, etc.
sudo mysql_secure_installation
This completes the basic MariaDB setup on Linux.

For macOS

On macOS, you can easily install MariaDB using Homebrew. Follow the steps below to install and configure it. 1. Installation steps using Homebrew First, ensure Homebrew is installed. If it isn’t, follow the instructions on the official site to install it.
brew install mariadb
Running this command installs MariaDB on macOS. 2. Initial configuration and how to start the service After installation, start the MariaDB service.
brew services start mariadb
After it starts, run the following command to perform initial configuration and harden security.
mysql_secure_installation
Running this script lets you set the root password, remove unnecessary users, delete the test database, etc. This completes the MariaDB setup on macOS.

5. Initial MariaDB Configuration

After installing MariaDB, it’s important to perform initial configuration to improve security. Below, we explain basic security settings and how to remove unnecessary databases and users.

Setting the root user password

Immediately after installation, the root user often has no password, so set a strong password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
This command sets a password for the root user, improving security.

Removing unnecessary databases and users

MariaDB may include test databases or users right after installation. Use the following commands to delete unnecessary databases and strengthen security.
DROP DATABASE test;
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;
This removes the test database and anonymous users, enhancing MariaDB’s security.

Strengthening security settings

In MariaDB, you can strengthen the default security settings by using the mysql_secure_installation command. Follow this command to set the root password, remove anonymous users, disable remote access, and more.

6. Basic Operations for MariaDB

Once the installation and initial configuration of MariaDB are complete, learning basic database operations will enable you to use MariaDB efficiently. Below, we explain how to create databases and users, and how to work with tables.

Creating and Dropping Databases

Creating a Database To create a new database in MariaDB, use the following command.
CREATE DATABASE database_name;
Example:
CREATE DATABASE example_db;
This command creates a database named “example_db”. Dropping a Database To delete a database you no longer need, use the following command.
DROP DATABASE database_name;
Example:
DROP DATABASE example_db;
Since deleting a database cannot be undone, proceed with caution.

Creating Users and Setting Permissions

Creating a User When creating a new user, use the following command.
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
Example:
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
Granting Permissions To grant a user access rights to a specific database, use the following command.
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
Example:
GRANT ALL PRIVILEGES ON example_db.* TO 'user1'@'localhost';
To apply the permission changes, run the following command.
FLUSH PRIVILEGES;

Creating Tables and Inserting Data

Creating a Table To create a new table in MariaDB, use the following command.
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);
Example:
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    position VARCHAR(50)
);
Inserting Data To insert data into the created table, use the following command.
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO employees (id, name, age, position) VALUES (1, 'Alice', 28, 'Developer');
Updating and Deleting Data To update existing data, use the following command.
UPDATE table_name SET column = new_value WHERE condition;
Example:
UPDATE employees SET age = 29 WHERE id = 1;
To delete data, use the following command.
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM employees WHERE id = 1;

7. Troubleshooting

Here are some common errors and solutions that can occur during MariaDB installation and use.

Common Installation Errors and Solutions

During MariaDB installation, errors can occur due to dependencies or package versions. Updating the package manager to the latest version repository resolves many issues.

Checklist When the Service Fails to Start

If MariaDB service does not start properly, check the log files (e.g., /var/log/mariadb) to identify the cause. Also verify that there are no port conflicts and that the service isn’t already running.

Causes and Solutions for Connection Errors

If you cannot connect from outside, check the firewall settings and the MariaDB configuration file (my.cnf) for the bind-address setting, and enable remote connections as needed.

8. Summary

In this article, we detailed the overview of MariaDB, its installation, basic operations, and troubleshooting. MariaDB is a high‑performance, flexible database that is widely loved as an open‑source solution. By performing proper installation and strengthening security, and mastering the basic operations, you can improve your database administration skills. Going forward, you can deepen your knowledge of MariaDB by consulting the official documentation and online communities for further learning.