Contents

Database - 安裝 MariaBD 與配置

關聯式資料庫將結構化的資料儲存在一組互相關聯的資料表內,大部分關聯式資料庫都是透過 SQL (Structured Query Language) 語言來查詢及管理數據,常見的 Web 解決方案 LAMP ( LNMP ),就會使用到 MySQL、MariaDB 或是 PostgreSQL 做為資料儲存。

練習環境
可以使用 HowHow 的創建 Lab 練習環境,來實作 MariaDB 安裝及操作,使用方式請參閱 HowHow 網站 - Linux 練習 Lab 第二版

1. MariaDB 簡介

MariaDB 是一個開源的關聯式資料庫管理系統,它是 MySQL 的分支版本,提供了更多的功能和性能優化。它支援多種作業系統,包括 Linux、Windows 和 macOS 等,並且能夠與許多不同的程式語言進行互動,例如 PHP、Java 和 Python 等。它還支援多種儲存引擎,包括 InnoDB、MyISAM、Memory 等,使用者可以根據需要進行選擇。 MariaDB 還提供了許多高級功能,例如支援 JSON、GIS 和虛擬列等,這些功能能夠讓使用者更加方便地進行資料管理和查詢。

2. 安裝 MariaDB

1
使用 Server A 安裝 MariaDB 範例

2.1. 列出 AppStream 所有可用的 MariaDB 版本

1
2
3
4
5
6
7
8
[root@servera ~]# yum module list mariadb
Last metadata expiration check: 0:00:07 ago on Tue 04 Apr 2023 11:08:34 PM CST.
Rocky Linux 8 - AppStream
Name              Stream             Profiles                             Summary
mariadb           10.3 [d]           client, galera, server [d]           MariaDB Module
mariadb           10.5               client, galera, server [d]           MariaDB Module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
Aapp Streams Life Cycle
MariaDB 10.3 是在 2019 年 5 月随 RHEL 8.0.0 提供的,其官方 support 至 2023 年 5 月結束,相關訊息可以參閱 - Red Hat Enterprise Linux Application Streams Life Cycle - Red Hat Customer Portal

2.2. 安裝指定版本

1
2
3
4
5
[root@servera ~]# yum module install mariadb:10.3/server -y | tail -n 4
  perl-threads-1:2.21-2.el8.x86_64
  perl-threads-shared-1.58-2.el8.x86_64

Complete!

2.3. 啟動服務及開放防火牆規則

1
2
3
4
5
6
7
8
[root@servera ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@servera ~]# firewall-cmd --permanent --add-service=mysql
success
[root@servera ~]# firewall-cmd --reload
success

3. 保護 MariaDB 初始設定

預設啟動的 MariaDB 會有測試資料庫及一些不安全的設定,可以透過 mysql_secure_installation 腳本,使得 Database 更加安全。

其腳本會執行下列更改 :

  • 設定 root 帳號的密碼。
  • 移除從外部主機使用 root 帳號存取。
  • 移除 anonymous-user 帳號。
  • 移除 test 資料庫。

3.1. mysql_secure_installation 腳本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
[root@servera ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): Enter
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password: redhat
Re-enter new password: redhat
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

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

Thanks for using MariaDB!

3.2. 驗證 root 需輸入密碼登入

1
2
[root@servera ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

3.3. 驗證 test 資料庫移除

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@servera ~]# mysql -u root -p
Enter password: redhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> exit;
Bye

4. 小結

在 Linux 上安裝及啟用 MariaDB 是非常容易的,並且對於測試驗證都可以數分鐘內完成搭建,如果只需要使用 MariaDB 的客戶端,可以只安裝 mariadb 或是 mysql 上述任一套件,就具備客戶端 CLI 套件。



如果你還沒有註冊 Like Coin,你可以在文章最下方看到 Like 的按鈕,點下去後即可申請帳號,透過申請帳號後可以幫我的文章按下 Like,而 Like 最多可以點五次,而你不用付出任何一塊錢,就能給我寫這篇文章的最大的回饋!