安裝資料庫 MariaDB 11.8 與配置，最佳化 2GB RAM

安裝資料庫 MariaDB 11.8 與配置，最佳化 2GB RAM



適用環境

作業系統：Debian 13

資料庫版本：MariaDB 11.8

VPS/雲主機 硬體規格：2GB RAM




--------------------------------
安裝MariaDB與配置
--------------------------------

Debian 13內建的MariaDB版本通常落後於官方最新版本，建議直接使用MariaDB官方Repository以取得11.8版本及後續安全更新



下載並安裝GPG 金鑰
curl -sSL https://mariadb.org/mariadb_release_signing_key.asc | gpg --dearmor > /usr/share/keyrings/mariadb.gpg



加入 MariaDB 11.8 官方 Repository
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/mariadb.gpg] https://deb.mariadb.org/11.8/debian $(lsb_release -sc) main" > /etc/apt/sources.list.d/mariadb.list



驗證金鑰的指紋 (Fingerprint)
gpg --no-default-keyring --keyring /usr/share/keyrings/mariadb.gpg --fingerprint



驗證APT倉庫配置是否正確

確認倉庫配置文件正確指向了這個金鑰

cat /etc/apt/sources.list.d/mariadb.list


你應該會看到類似這樣的內容  deb [arch=amd64 signed-by=/usr/share/keyrings/mariadb.gpg] https://deb.mariadb.org/11.8/debian bookworm main
重點是有 signed-by 參數指向你的金鑰檔案




更新套件清單
apt update


apt upgrade -y



安裝 MariaDB Server 與 Client
apt install -y mariadb-server mariadb-client




Configuring mariadb-server

Feedback plugin collects basic anonymous statistical information that can be used by the developers to improve MariaDB.

Enable the Feedback plugin and submit anonymous usage information? <Yes> /<No>

詢問是否啟用 Feedback Plugin 匿名回傳統計資料

選 <No> 關閉





安裝完成後，驗證版本號
mariadb -V


預期輸出範例 11.8.6-MariaDB



-------------------
安全初始化設定
-------------------

安裝完成後，務必執行安全初始化腳本，移除預設的高風險設定

進行安全性設定
mariadb-secure-installation




Enter current password for root (enter for none)

輸入root的目前密碼

直接按 Enter，首次安裝尚未設定密碼





Switch to unix_socket authentication [Y/n] 

切換到 unix_socket 身份驗證 [Y/n]

只在 localhost 本機管理資料庫，選 Y，可進一步強化安全性






Change the root password? [Y/n] 

更改root密碼？ [Y/n] 

選 Y，並設定一組高強度密碼








Remove anonymous users? [Y/n] 

移除匿名用戶？ [Y/n] 

選 Y。應移除








Disallow root login remotely? [Y/n] 

禁止root遠端登入？ [Y/n] 

選 Y。即使需要遠端管理，也應另建立具有最小必要權限的帳號，而非直接用 root 遠端連線








Remove test database and access to it? [Y/n] 

移除 test 資料庫？ [Y/n] 

選 Y，移除測試資料庫






Reload privilege tables now? [Y/n] 

立即重新載入權限表？ [Y/n] 

選 Y，讓所有變更立即生效






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

Thanks for using MariaDB!







-----------------
驗證 root 登入
-----------------

初始化完成後，確認可以本機登入


登入資料庫
mariadb -u root -p


若啟用 unix_socket，也可用以下方式免密登入
sudo mariadb



輸入剛才設定的密碼，成功後應出現 MariaDB [(none)]> 提示


輸入 exit 離開
exit






注意：若 unix_socket 身份驗證 [Y/n]  選 Y，則需以 sudo mariadb 或在 root 身份下直接執行 mariadb 登入，無需輸入密碼



---------------------
重新啟動MariaDB
---------------------

重新啟動MariaDB
systemctl restart mariadb








-----------------------------
設定 UTF-8 編碼 (utf8mb4)
-----------------------------

MariaDB預設字元集未必是 utf8mb4

utf8mb4 是目前最完整的Unicode實作，支援四位元組字元 (包含表情符號)，應優先採用



編輯客戶端設定檔
vi /etc/mysql/mariadb.conf.d/50-client.cnf


在 [client] 區塊加入以下內容

default-character-set = utf8mb4



儲存檔案並離開vi編輯器
按 Esc，輸入 :wq，按 Enter




儲存後重啟 MariaDB
systemctl restart mariadb






確認資料庫字元集是否已正確套用，可登入資料庫後執行



登入資料庫
mariadb -u root -p


若啟用 unix_socket，也可用以下方式免密登入
sudo mariadb


確認資料庫字元集是否已正確設定
SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';


應顯示 character_set_client、character_set_connection、character_set_results、character_set_server 等均為 utf8mb4




離開資料庫
exit





-----------------------------
效能調校 2GB RAM
-----------------------------

以下設定針對 2GB 記憶體的VPS環境最佳化

請務必依照實際硬體調整標示「依照記憶體調整」的參數


編輯主要設定檔
vi /etc/mysql/mariadb.conf.d/50-server.cnf


複製貼上以下內容


# 適用環境：VPS 2GB RAM
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see this is read by the standalone daemon and embedded servers
[server]

# this is only for the mariadbd daemon
[mariadbd]

# 字符集
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

#
# * Basic Settings
#
port        = 3306
user        = mariadb
pid-file   = /run/mysqld/mysqld.pid
basedir  = /usr
datadir  = /var/lib/mysql
tmpdir   = /tmp

# Broken reverse DNS slows down connections considerably and name resolve is safe to skip if there are no "host by domain name" access grants
# 關閉反向 DNS 解析，提升連線速度
# 若存在以 hostname 設定的權限，請勿啟用此選項
skip-name-resolve = ON

# Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.
# 僅允許 localhost 本機連線 (如需遠端連線請改為 0.0.0.0，並搭配防火牆限制來源IP)
bind-address = 127.0.0.1

#
# * Fine Tuning
#

# MyISAM 索引緩衝，多數純 InnoDB 環境影響有限，維持較小值即可
key_buffer_size = 32M

# 單次封包最大允許大小，大型資料庫匯入時可視需求提高
max_allowed_packet = 1G

# 開啟的資料表快取數量
table_open_cache         = 2048

# 每個 session 的排序緩衝，勿設定過大 (會乘以連線數佔用記憶體)
sort_buffer_size         = 768K
net_buffer_length        = 8K
read_buffer_size         = 768K

# 讀取隨機行序結果時的緩衝大小，適度即可
read_rnd_buffer_size     = 512K
myisam_sort_buffer_size  = 8M

# 執行緒快取數，減少頻繁建立/銷毀執行緒的開銷
thread_cache_size        = 50

# 記憶體內暫存表上限
# 若 SHOW STATUS LIKE 'Created_tmp_disk_tables' 數值持續偏高，可適度調大
tmp_table_size           = 64M
max_heap_table_size      = 64M
explicit_defaults_for_timestamp = ON

# 最大同時連線數 (一般 Web 應用 100～300 即可)
# 每條連線約佔 1～2 MB 記憶體，過大會消耗大量記憶體
max_connections = 80

# 連線錯誤次數上限 (超過後該IP暫時被封鎖，執行 FLUSH HOSTS 可解鎖)
max_connect_errors = 100

# 開啟檔案數上限 (需與 systemd LimitNOFILE 一致)
open_files_limit = 65535

# 預設儲存引擎
default_storage_engine = InnoDB

#
# * Binary Log / Replication
#
# Binary Log (binlog) 記錄所有資料變更操作，是主從複製和時間點還原 (PITR) 的基礎
# 若不需要上述功能，可將四個參數全部註解掉，以節省磁碟 I/O 和空間
log-bin          = mysql-bin
binlog_format    = row
server-id        = 1
expire_logs_days = 10
# 啟用 binlog 後，每天都會產生新的日誌檔案，expire_logs_days 控制保留天數，建議至少設定此值避免磁碟爆滿

#
# * Logging
# 錯誤日誌預設交由 systemd journald 處理
# 如需獨立檔案，取消下行註解並確認目錄權限
#log_error = /var/log/mysql/error.log
# 慢查詢日誌 (建議在生產環境開啟以找出效能瓶頸)
# 記錄執行時間超過 log_slow_query_time (秒) 的查詢
#log_slow_query_file = /var/log/mysql/mariadb-slow.log
#log_slow_query_time = 2
#log_slow_verbosity  = query_plan,explain
#log-queries-not-using-indexes

#
# * InnoDB 調校
#

# 每張資料表獨立 .ibd 檔案，便於空間回收與個別備份
innodb_file_per_table = ON

# Galera Cluster / 多來源複製必須設為 2；單機保持 2 亦無妨
innodb_autoinc_lock_mode = 2

# 緩衝池實例數
innodb_buffer_pool_instances = 1

# InnoDB 並發執行緒數 (0 = 由 InnoDB 自行管理，通常最佳)
innodb_thread_concurrency = 0

# 最重要的效能參數，請依照實際記憶體調整 
# 緩衝池大小建議設為實體記憶體的 50%～70%
#   2 GB RAM → 建議 768M
# 設定過大將導致系統 OOM (記憶體不足)，請謹慎評估
innodb_buffer_pool_size = 768M


# Redo log 日誌檔大小 (約為 buffer pool 的 1/8)
# 注意：MariaDB 11.x 已可自動管理，此處保留以相容舊有行為
# 若調整此值後 MariaDB 無法啟動，請先刪除 /var/lib/mysql/ib_logfile* 再重啟 (系統會自動重建)
innodb_log_file_size = 256M

# Redo log 寫入前的記憶體緩衝 (大量寫入時可適度調大)
innodb_log_buffer_size = 16M

# 交易提交時的刷寫策略
#   1 = 最安全（每次交易刷寫磁碟，正式環境建議值）
#   2 = 效能較佳（每秒刷寫一次，可能遺失最後一秒的資料）
innodb_flush_log_at_trx_commit = 1

# 減少雙重緩衝，提升 I/O 效能 (僅適用 Linux，SSD效果尤為明顯)
innodb_flush_method = O_DIRECT

# 等待鎖定超時秒數 (超過後自動回滾，避免 Deadlock 長時間佔用)
innodb_lock_wait_timeout = 50

# ──────────────────────────────────────────
# 工具設定
# ──────────────────────────────────────────

[mysqldump]
quick

# 單次匯出封包最大值，大型資料庫時可視需求提高
max_allowed_packet = 1G

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size  = 20M
sort_buffer_size = 20M
read_buffer      = 2M
write_buffer     = 2M

[mysqlhotcopy]
interactive-timeout

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB, you can put MariaDB-only options here
[mariadbd]

# This group is only read by MariaDB-11.8 servers.
# If you use the same .cnf file for MariaDB of different versions, use this group for options that older servers don't understand
[mariadb-11.8]




儲存檔案並離開vi編輯器
按 Esc，輸入 :wq，按 Enter



----------------------------------
系統層級的檔案限制 (OS Level)
----------------------------------

MariaDB 設定了 open_files_limit，但若系統層級的限制過低，資料庫仍會報錯

請建立資料夾並編輯限制檔

mkdir -p /etc/systemd/system/mariadb.service.d/


vi /etc/systemd/system/mariadb.service.d/limits.conf


加入內容


[Service]

LimitNOFILE=65535



儲存檔案並離開vi編輯器
按 Esc，輸入 :wq，按 Enter




重新載入
systemctl daemon-reload


systemctl restart mariadb



驗證 open_files_limit 是否已套用

sudo mariadb -e "SHOW VARIABLES LIKE 'open_files_limit';"

應顯示 65535








-----------------------
設定開機自動啟動
-----------------------

開機自動啟動
systemctl enable mariadb


重啟
systemctl restart mariadb



檢查服務狀態
systemctl status mariadb


應顯示 Active: active (running)



-------------------
重開機並驗證
-------------------


重開機
reboot


重開機後，再次檢查狀態
systemctl status mariadb


確認狀態為 active (running) 即代表安裝與設定成功




登入資料庫
mariadb -u root -p


若啟用 unix_socket，也可用以下方式免密登入
sudo mariadb



確認字元集與 InnoDB 設定是否正確套用
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'character_set%';



離開資料庫
exit











------------------
常見問題與排除
------------------

問題：MariaDB無法啟動

可能原因：設定檔語法錯誤

解決方法：執行 journalctl -u mariadb -n 50 查看詳細錯誤日誌

調整 innodb_log_file_size 後需刪除 /var/lib/mysql/ib_logfile* 讓MariaDB重建

若是調整 innodb_log_file_size 後發生，需刪除 /var/lib/mysql/ib_logfile* 讓MariaDB重建

rm /var/lib/mysql/ib_logfile*

systemctl start mariadb

若為設定檔語法錯誤，可執行 mariadbd --validate-config 驗證




問題：連線被拒絕 (Connection refused)

可能原因：bind-address 設定為 127.0.0.1 時僅接受本機連線

解決方法：若需遠端連線，請將 bind-address 改為 0.0.0.0

在防火牆新增規則，限制僅允許特定IP連入3306埠

systemctl restart mariadb






問題：密碼錯誤 / 無法登入

解決方法：以root身份免密碼登入後重設
sudo mariadb

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';

FLUSH PRIVILEGES;



問題：Too many connections 錯誤

解決方法：適度調高 max_connections (注意每條連線約佔 1～2 MB)

從應用端改用連線池 (Connection Pool) 是更根本的解法

暫時觀察用：SHOW STATUS LIKE 'Max_used_connections';



問題：磁碟空間持續增加

可能原因：通常為 Binlog 累積所致

解決方法：確認 expire_logs_days 已設定 (建議 7～14 天)

手動清除 PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

若完全不需要複製功能，可在設定檔將 log-bin 相關行全部加 # 註解



問題：效能未明顯提升

解決方法：確認 innodb_buffer_pool_size 設定是否已套用

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'

調高至實體記憶體的 60%～70%

啟用慢查詢日誌找出低效查詢並建立適當 INDEX



問題：innodb_buffer_pool_size 設定後 OOM

解決方法：主機記憶體不足。設定前務必以 free -h 確認可用記憶體，保留系統及其他服務至少 1 GB 的空間

將 innodb_buffer_pool_size 降至 50%～60% 的實體記憶體




問題：設定檔改了但未生效

解決方法：確認編輯的是正確的設定檔，並執行 systemctl restart mariadb

可用 mariadbd --print-defaults 確認目前生效的參數值

本帖最后于2月28日2月28日，由Jack编辑