移至主內容

Debian/Ubuntu 環境安裝 MariaDB 資料庫伺服器

此篇文章主要記錄在Debian/Ubuntu系統上可安裝及還有在支援的MariaDB資料庫伺服器版本,以方便筆者自己日後在Debian/Ubuntu環境上用到需要的MariaDB版本時可以做一個參考。

此篇文章筆者會持續更新,且只會更新目前還有在支援MariaDB的版本。另外,系統部分此篇文章只會記錄Debian與Ubuntu兩個不同的Linux發行版,系統也是一樣僅會更新還有在支援的。

**關於MariaDB目前還有在支援的版本,可以查看MariaDB官方網站的MariaDB Server Releases文章。

**若本教學提供的儲存庫連結失效或金鑰過期,甚至你是使用其他不同的Linux發行版來安裝MariaDB,那可以連結到Downloads Setting up MariaDB Repositories來取得MariaDB儲存庫資訊。

1)查詢Debian或Ubuntu預設所提供的MariaDB是否為你所要的版本。

**下面使用Debian 11系統版本為例:

#使用『apt-cache show PACKAGE_NAME』指令,可以查詢套件詳細資訊
apt-cache show mariadb-server mariadb-client
#如下面所示的資訊,分別可以看到MariaDB的『mariadb-server』和『mariadb-client』在Debian 11環境預設提供的版本為10.5
#Package: mariadb-server
#Source: mariadb-10.5
#Version: 1:10.5.11-1
#Installed-Size: 71
#Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
#Architecture: all
#Depends: mariadb-server-10.5 (>= 1:10.5.11-1)
#Description-en: MariaDB database server (metapackage depending on the latest version)
# This is an empty package that depends on the current "best" version of
# mariadb-server (currently mariadb-server-10.5), as determined by the MariaDB
# maintainers. Install this package if in doubt about which MariaDB
# version you need. That will install the version recommended by the
# package maintainers.
# .
# MariaDB is a fast, stable and true multi-user, multi-threaded SQL database
# server. SQL (Structured Query Language) is the most popular database query
# language in the world. The main goals of MariaDB are speed, robustness and
# ease of use.
#Description-md5: a887647d25d472f89e220ceda2b6e041
#Homepage: https://mariadb.org/
#Tag: devel::lang:c++, devel::lang:sql, devel::library, implemented-in::c++,
# interface::commandline, interface::daemon, network::server,
# protocol::db:mysql, role::devel-lib, role::metapackage, role::program,
# works-with::db
#Section: database
#Priority: optional
#Filename: pool/main/m/mariadb-10.5/mariadb-server_10.5.11-1_all.deb
#Size: 34796
#MD5sum: 46da4581f602e6f9d3d3cac166fed1a7
#SHA256: f6c694a5bcd0d33bdcd72c509ffb2798250f47e9568a517634f80d1c4191dd27
#
#Package: mariadb-client
#Source: mariadb-10.5
#Version: 1:10.5.11-1
#Installed-Size: 71
#Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
#Architecture: all
#Depends: mariadb-client-10.5 (>= 1:10.5.11-1)
#Description-en: MariaDB database client (metapackage depending on the latest version)
# This is an empty package that depends on the current "best" version of
# mariadb-client (currently mariadb-client-10.5), as determined by the MariaDB
# maintainers.  Install this package if in doubt about which MariaDB version
# you want, as this is the one considered to be in the best shape.
#Description-md5: 6501fd3178a212adaa785da108f5aaad
#Homepage: https://mariadb.org/
#Tag: devel::lang:sql, implemented-in::c++, interface::commandline,
# interface::text-mode, network::client, protocol::db:mysql,
# role::metapackage, role::program, scope::suite, works-with::db
#Section: database
#Priority: optional
#Filename: pool/main/m/mariadb-10.5/mariadb-client_10.5.11-1_all.deb
#Size: 34692
#MD5sum: 02d4818a401bfff9989b487b6105c7bc
#SHA256: 2bf3e49dc84b2b7faba6d18c30270355b711eb4476ea60e742e20c939d11b713
#若你查到的MariaDB並不是你要的版本,那接下來部分,可以參考下面的方式,從MariaDB官方或第三方的儲存庫來獲取你要的MariaDB版本

 

2)Debian或Ubuntu新增第三方PPA儲存庫。

Debian

**以下指令請自行將第三列『<MariaDB 版本>』替換成你要的MariaDB版本,目前還在支援的版本為『10.6』,『10.5』,『10.4』,『10.3』和『10.2』。

**以下指令請自行將第三列『<Debian 代號>』替換成你使用的Debian系統版本代號,比如Debian 11的代號為『bullseye』,Debian 10的代號為『buster』和Debian 9的代號為『stretch』。

sudo apt-get install software-properties-common dirmngr -y
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://ftp.ubuntu-tw.org/mirror/mariadb/repo/<MariaDB 版本>/debian <Debian 代號> main'

假設筆者要在Debian 11安裝MariaDB的10.6版本,那指令範例如下:

sudo apt-get install software-properties-common dirmngr -y
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.6/debian bullseye main'

 

Ubuntu

**以下指令請自行將第三列『<MariaDB 版本>』替換成你要的MariaDB版本,目前還在支援的版本為『10.6』,『10.5』,『10.4』,『10.3』和『10.2』。

**以下指令請自行將第三列『<Ubuntu 代號>』替換成你使用的Ubuntu系統版本代號,比如Ubuntu 20.04 LTS代號為『focal』,而Ubuntu 18.04 LTS代號為『bionic』。

sudo apt-get install software-properties-common -y
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://ftp.ubuntu-tw.org/mirror/mariadb/repo/<MariaDB 版本>/ubuntu <Ubuntu 代號> main'

假設筆者要在Ubuntu 20.04 LTS安裝MariaDB的10.6版本,那指令範例如下:

sudo apt-get install software-properties-common -y
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.6/ubuntu focal main'

 

3)Debian或Ubuntu安裝MariaDB資料庫。

sudo apt-get update
sudo apt-get install mariadb-server mariadb-client -y

 

4)初始設定MariaDB資料庫的安全性。

sudo mysql_secure_installation

接著會看到以下訊息,逐步完成MariaDB資料庫安全性設定:

**下面的安全性設定,隨時都可以進行變更。

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
haven’t set the root password yet, you should just press enter here.

#按『Enter鍵』跳到下一步
Enter current password for root (enter for none):
OK, successfully used password, moving on...

#下面兩個是跟密碼設定相關的問題,請視情況來進行設定,以本篇教學的環境來說來說,
#因筆者系統的root權限已經設定了密碼,所以筆者這兩題都輸入『n』跳過設定;
#若MariaDB沒有密碼,請在第一題輸入『n』,第二題輸入『y』來設定MariaDB root的新密碼。
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

#第三個問題:預設有建立了一個匿名使用者,輸入『y』來移除匿名使用者。
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!

#第四個問題:只允許root從localhost訪問DB,故輸入『y』不允許遠端登入。
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!

#第五個問題:是否移除『test』測試資料庫,輸入『y』進行移除。
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!

#第六個問題:是否重新刷新權限表,輸入『y』立即更新以完成所有的初始化設定。
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.

#上面步驟完成後,MariaDB會自動退出安全性設定介面。
Thanks for using MariaDB!

 

5)建立一個新的資料庫。

**下面的範例分別新增了一個資料庫及一個使用者,及賦予此使用者訪問該資料庫的完整權限。

sudo mysql -u root -p
CREATE DATABASE <資料庫名稱>;
CREATE USER <資料庫使用者名稱>@localhost IDENTIFIED BY '<密碼>';
GRANT ALL PRIVILEGES ON <資料庫名稱>.* TO <資料庫使用者名稱>@localhost IDENTIFIED BY '<密碼>';
FLUSH PRIVILEGES;
\q

 

6)從Debian或Ubuntu環境移除MariaDB資料庫。

Step 1:備份資料庫資料(若有需要備份的話)。

mkdir ~/DB-backup

sudo mysqldump --all-databases -u root -p > /home/$USER/DB-backup/backup.sql

若僅要備份某個資料庫,指令參考如下(建議用下面這個):

mkdir ~/DB-backup

sudo mysqldump -u <資料庫使用者名稱> -p <資料庫名稱> > /home/$USER/DB-backup/backup.sql

 

Step 2:查看MariaDB資料庫版本和執行狀態:

sudo systemctl status mariadb

 

Step 3:停止MariaDB服務。

sudo systemctl stop mariadb

 

Step 4:執行『purge』指令從系統移除MariaDB相關套件,移除過程中會再跟你確認是否需要移除所有資料庫的資料,選擇『Yes』進行移除。

sudo apt-get purge "mariadb*" -y

 

Step 5:執行『autoremove』指令來移除MariaDB殘留的相關配置檔案:

sudo apt-get autoremove -y

 

Step 6:再次查看MariaDB狀態,應該可以看到『service could not be found』找不到服務訊息。

sudo systemctl status mariadb

 

此篇文章上次修改日期:
2021/09/15