[EN] How to install MariaDB on Raspberry Pi?

This article is a quick note of installing MariaDB database management system on the Raspberry Pi board to store and manage data. It creates a unique database for users who can log into the Raspberry Pi’s system.

Installation procedure

System update

1. sudo apt update
2. If there is an item that needs to be upgraded, run the following commands to perform the upgrade.
   sudo apt -y upgrade

Install MariaDB

sudo apt install mariadb-server

Setup and Security

1. Enter the security settings program with the following commands.
   sudo mysql_secure_installation
2. Steps for assigning a password to the MariaDB root
   1. Enter the original password of the root (MariaDB) that you have set. If it hasn’t been set up before, press the enter key for the next step.
   2. Enter the desired root password.
   3. Enter the root password again to confirm.
3. Answer ‘y’ to the following security topics.
   1. Remove anonymous users
   2. Disallow root login remotely
   3. Remove the test database
4. Answer ‘y’ to reload the system privileges.

Create a database for users

1. Enter MariaDB database management in CUI (Console User Interface) or text mode with the following commands.
   sudo mysql -u root -p
2. Enter root password
3. Create a database with the following command.
   CREATE DATABASE db_name ;
4. Create users (If this user has not been added before)
   CREATE USER ‘ชื่อ’@’localhost’ IDENTIFIED BY ‘รหัสผ่าน’ ;
5. Assign management permissions to the database created in step 3 to the user in step 4 with the following command.
   GRANT ALL PRIVILEGES ON ชื่อฐานข้อมูล.* TO ‘ชื่อ’@’localhost’ ;
6. Update the permissions with the command below.
   FLUSH PRIVILEGES ;
7. Exit MariaDB database manager with the following command.
   exit

Conclusion

From this article before proceeding to assign user privileges in the system, you must first design who can access to which database. And when it comes to the process of creating a database and granting permissions, it can be done easily.

Finally, I hope this article will be useful for those who want to install a structured database management system on the Raspberry Pi board’s Raspbian operating system.

CREATE DATABASE db_name;
SHOW DATABASES;
USE db_name;
SHOW TABLES;
SELECT * FROM table;
INSERT INTO table VALUES( … );
DELETE FROM table WHERE condition;
UPDATE table SET … WHERE condition;

and more

Reference

1. How to Install MariaDB on Raspberry Pi? (MySQL Server)
2. SQL Tutorial

(C) 2020, By Jarut Busarathid and Danai Jedsadathitikul
Updated 2021-08-26