DEV Community

Cover image for Setting up a MariaDB Server
Waji
Waji

Posted on

Setting up a MariaDB Server

Introduction

A database is a collection of data that is organized in a way that allows for efficient storage, retrieval, and manipulation of that data

A DBMS is a software system that enables users to create, maintain, and manipulate databases

I will be configuring MariaDB on Linux System

💡 MariaDB is a popular open-source relational DBMS that was forked from MySQL in 2009, after concerns arose about MySQL's acquisition by Oracle Corporation


Installing and setting up MariaDB

We will use the yum command to install MariaDB

yum -y install mariadb-*

# Confirming
rpm -qa | grep mariadb
mariadb-embedded-5.5.68-1.el7.x86_64
mariadb-bench-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
mariadb-embedded-devel-5.5.68-1.el7.x86_64
mariadb-test-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-devel-5.5.68-1.el7.x86_64
Enter fullscreen mode Exit fullscreen mode

Starting the service

systemctl start mariadb
systemctl enable mariadb
Enter fullscreen mode Exit fullscreen mode

There is a security script included in MariaDB. We can start that using

mysql_secure_installation
Enter fullscreen mode Exit fullscreen mode

This will ask for the root password. It is asking for the DBMS root user, not the Linux's root user

After setting the password, there will be some steps that the security script will ask and we need to select between 'Y' and 'n'

We can 'Y' the following queries:

  • Set root password
  • Remove Anonymous user
  • Disallow remote root login
  • Remove test database
  • Reload privilege tables

👉 Removing the anonymous user and removing test database steps are used mainly for moving into the production environment

Upon completion, the script should say

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
Enter fullscreen mode Exit fullscreen mode

After this, we will check the .cnf file

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
Enter fullscreen mode Exit fullscreen mode

👉 I needed the utf8 encoding because I use Korean data so I added

character-set-server=utf8
Enter fullscreen mode Exit fullscreen mode

Starting a session with MariaDB

We will use the following command to enter the MariaDB server

mysql -u root -p -h localhost mysql
Enter fullscreen mode Exit fullscreen mode

Here,

  • mysql: This is the command to start the MariaDB client
  • -u root: This specifies the username to use when connecting to the MariaDB server
  • -p: This option tells MariaDB to prompt for a password before allowing the user to connect
  • -h localhost: This specifies the host name or IP address of the MariaDB server to connect to
  • mysql: This is the name of the database to use. The default database called "mysql" was created by MariaDB when it was first installed

If we see tables inside this database:

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
Enter fullscreen mode Exit fullscreen mode

💡 We can see that the default database mysql contains important information and some log files too. We can also see 'user' table that contains the user information of MariaDB server

MariaDB [mysql]> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
Enter fullscreen mode Exit fullscreen mode

Conclusion

✨ We installed and connected to the MariaDB server created inside the local host of the Linux system. There are many SQL statements, functions, and utilities. I recommend going through the official MariaDB documentation to learn more about MariaDB 😀

Top comments (0)