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