Debian 13 安裝資料庫MariaDB 11.8與配置，VPS 2 vCPU/2GB RAM 優化

Debian 13 安裝資料庫MariaDB 11.8與配置，VPS 2 vCPU/2GB RAM 優化


-------------
前言
-------------


適用環境

作業系統：Debian 13

資料庫：MariaDB 11.8

網頁伺服器：Nginx

PHP：PHP 8.4

VPS規格：2 vCPU / 2GB RAM



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

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



安裝必要工具

apt update


apt full-upgrade -y


apt install -y curl gnupg lsb-release




下載 MariaDB GPG Key
curl -fsSL https://mariadb.org/mariadb_release_signing_key.asc | gpg --dearmor | tee /usr/share/keyrings/mariadb.gpg > /dev/null



驗證 GPG 金鑰指紋
gpg --show-keys --with-fingerprint /usr/share/keyrings/mariadb.gpg





預期輸出 (應包含以下指紋)：
pub   ****************
      ******************************
uid         ***************************************



安全提醒：

若指紋與 [MariaDB 官方網站]https://mariadb.org/download/  公告的不符，請立即停止安裝

金鑰驗證是防範供應鏈攻擊的重要步驟，務必執行






新增 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





確認內容是否正確
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 trixie main




更新套件清單
apt update



建議使用 full-upgrade 以確保依賴關係完整更新
apt full-upgrade -y




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





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

安裝過程中的提示，詢問是否啟用 匿名使用資訊回報

選 <No> ，關閉




確認MariaDB版本
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]


選 Y，只在 localhost 本機







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 後，必須以 root 系統使用者身份 (或加 sudo) 才能免密登入 MariaDB，

這比純密碼登入更加安全，因為攻擊者即使知道密碼也無法從遠端直接登入 root 帳號





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

重新啟動MariaDB
systemctl restart mariadb






-------------------------------------
MariaDB client 用戶端 設定檔
-------------------------------------

/etc/mysql/mariadb.conf.d/50-client.cnf 是 MariaDB 用戶端 (client) 的設定檔，影響所有透過命令列工具 (mysql、mysqldump、mysqlcheck 等) 發起的連線行為

它不影響 MariaDB 伺服器本身的效能，但正確設定能避免備份失敗、字元集錯誤、連線逾時等常見問題




編輯
vi /etc/mysql/mariadb.conf.d/50-client.cnf



貼上內容




# ============================================================
# /etc/mysql/mariadb.conf.d/50-client.cnf
# ============================================================

[client]

# ── 連線基本設定 ──────────────────────────────────────────
# 標準 TCP 連接埠 (保持預設即可，除非有多實例需求)
port     = 3306

# 優先使用 Unix Socket (本機連線效能較 TCP 好且更安全)
# 若應用程式與 MariaDB 在同一台機器，請保留此設定
socket = /var/run/mysqld/mysqld.sock


# ── 字元集設定 ────────────────────────────────────────────
# utf8mb4 支援完整 Unicode，包含 Emoji (論壇必備)
# 必須與伺服器端 [mysqld] 中的 character-set-server 一致
default-character-set = utf8mb4

[client-mariadb]







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






儲存後，重啟 MariaDB
systemctl restart mariadb






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



以密碼方式登入資料庫
mariadb -u root -p


若啟用 unix_socket，可免密碼登入
sudo mariadb


在MariaDB提示字元下執行：

SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%';


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




離開資料庫
exit





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

以下設定針對 VPS 2 vCPU / 2GB RAM 最佳化

請務必依照實際硬體調整標示重要的參數


先備份原始設定檔
cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.bak



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



複製貼上以下內容




# /etc/mysql/mariadb.conf.d/50-server.cnf
# 適用環境：VPS 2 vCPU / 2GB RAM
[server]

[mariadbd]

# ===== 字元集設定 =====
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

# ===== 基本設定 =====
port        = 3306
user        = mysql
pid-file   = /run/mysqld/mysqld.pid
basedir  = /usr
datadir  = /var/lib/mysql
tmpdir   = /tmp

# 關閉反向 DNS 解析，提升連線速度
# 若存在以 hostname 設定的權限，請勿啟用此選項
skip-name-resolve = ON

# 僅允許 localhost 本機連線
# 如需遠端連線，請改為 0.0.0.0，並搭配防火牆限制來源IP
bind-address = 127.0.0.1

# ===== 連線與執行緒調校 =====

# 最大同時連線數
# 每條連線約佔 1～2 MB 記憶體，2GB環境建議 80-100 (重要)
max_connections = 80

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

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

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

# ===== 緩衝區設定 =====

# MyISAM 索引緩衝 (純InnoDB環境影響有限)
key_buffer_size = 32M

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

# 資料表快取數量
table_open_cache         = 2048

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

# MyISAM 排序緩衝
myisam_sort_buffer_size  = 8M

# 記憶體內暫存表上限
# 若 Created_tmp_disk_tables 數值偏高，可適度調大 (重要)
tmp_table_size           = 64M
max_heap_table_size      = 64M

explicit_defaults_for_timestamp = ON

# 預設儲存引擎
default_storage_engine = InnoDB

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

# ===== Logging =====
# 錯誤日誌：預設由 systemd journald 處理
# 如需獨立日誌檔案，取消下列行的註解並確認目錄權限
#log_error = /var/log/mysql/error.log

# 慢查詢日誌 (強烈建議在生產環境開啟，用於找出效能瓶頸)
# 記錄執行時間超過 long_query_time (秒) 的查詢
#slow_query_log = ON
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 2
#log_slow_verbosity = query_plan,explain


# ===== 安全設定 =====
# 禁止伺服器向客戶端請求本地檔案，防止資料洩漏
local-infile = 0

# ===== InnoDB 調校（最重要部分）=====

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

# InnoDB 緩衝池大小 (最重要的效能參數)
# 建議值：實體記憶體的 50%～70%
# 2GB RAM → 建議 768M (其餘留給 OS、Nginx、PHP-FPM)
# 設定過大可能導致系統 OOM，請謹慎評估
innodb_buffer_pool_size = 768M

# Redo log 日誌檔大小 (約為 buffer pool 的 1/8)
# MariaDB 11.x 已可自動管理
# 若調整後無法啟動，請先刪除 /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 效能 (SSD優化)
innodb_flush_neighbors = 0

innodb_io_capacity = 200
innodb_io_capacity_max = 400

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

# ===== 工具設定 =====

[mysqldump]
quick

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

[mysql]
no-auto-rehash

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

[mysqlhotcopy]
interactive-timeout

[embedded]

[mariadbd]

[mariadb-11.8]




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








----------------
驗證設定檔
----------------


驗證設定檔語法
mariadbd --help --verbose > /dev/null


當你執行這個指令時，mariadbd 會：
讀取所有設定檔 (如 /etc/mysql/my.cnf)
解析所有參數 (檢查語法是否正確)


輸出結果：
如果設定檔有錯誤 → 顯示 [ERROR]

如果設定檔有警告 → 顯示 [Warning]

如果設定檔完全正確 → 沒有任何輸出顯示





-----------------------
開機自動啟動
-----------------------

開機自動啟動
systemctl enable mariadb


重新啟動
systemctl restart mariadb


檢查狀態
systemctl status mariadb


應顯示 Active: active (running)



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

完成所有設定後，重開機是最可靠的驗證方式，確認所有服務能自動恢復正常運作


重開機
reboot


重開機後確認服務狀態
systemctl status mariadb


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


檢查記憶體使用
free -h


檢查 MariaDB 處理程序記憶體
ps aux | grep mariadb



以密碼方式登入資料庫
mariadb -u root -p


若啟用 unix_socket，可免密碼登入
sudo mariadb



資料庫內部驗證

在 MariaDB 提示字元下執行

檢查 InnoDB 緩衝池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';


檢查字元集設定
SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%';


檢查最大連線數
SHOW VARIABLES LIKE 'max_connections';


檢查目前連線數
SHOW STATUS LIKE 'Threads_connected';


檢查歷史最大連線數 (用於調整 max_connections 的依據)
SHOW STATUS LIKE 'Max_used_connections';


離開資料庫
exit











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

問題：MariaDB無法啟動

可能原因：
設定檔語法錯誤
innodb_log_file_size 調整後未刪除舊檔
權限問題

解決方法：

查看錯誤日誌
journalctl -u mariadb -n 50








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

可能原因：
bind-address 設定為 127.0.0.1
防火牆阻擋
連線數超過限制

解決方法：

檢查 bind-address 設定
grep bind-address /etc/mysql/mariadb.conf.d/50-server.cnf

若需遠端連線，修改為 0.0.0.0，並設定防火牆
iptables -A INPUT -p tcp --dport 3306 -s 允許的IP位址 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

檢查目前連線數
sudo mariadb -e "SHOW STATUS LIKE 'Threads_connected';"
sudo mariadb -e "SHOW STATUS LIKE 'Max_used_connections';"








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

解決方法：

以 root 身份免密碼登入
sudo mariadb

重設密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
FLUSH PRIVILEGES;

或使用 mysqladmin (若忘記密碼)
systemctl stop mariadb
mysqld_safe --skip-grant-tables &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
exit
systemctl restart mariadb


警告：若連 sudo mariadb 也無法登入 (例如 unix_socket 損壞)，可使用 --skip-grant-tables 緊急模式，但此模式無安全驗證，操作完畢後務必立即重啟








問題：Too many connections 錯誤

可能原因：
max_connections 設定過低
應用程式未使用連線池
連線未正常關閉

解決方法：

暫時調高連線數 (立即生效)
sudo mariadb -e "SET GLOBAL max_connections = 150;"

永久調整：修改設定檔
# max_connections = 150

查看閒置連線
sudo mariadb -e "SHOW PROCESSLIST;"

kill 閒置過久的連線
sudo mariadb -e "SHOW PROCESSLIST;" | grep Sleep | awk '{print "KILL "$1";"}' | mysql


提示：應用端建議實作連線池 (如 PHP PDO persistent connection、HikariCP、PgBouncer 等)，可大幅降低連線開銷








問題：磁碟空間持續增加

可能原因：
Binary Log 累積
慢查詢日誌過大
錯誤日誌暴增

解決方法：

檢查磁碟使用
df -h
du -sh /var/lib/mysql/


檢查 Binary Log 使用
sudo mariadb -e "SHOW BINARY LOGS;"

手動清除舊的 Binary Log
sudo mariadb -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"


若完全不需要複製，可關閉 binlog
註解設定檔中的 log-bin 相關行

檢查日誌大小
ls -lh /var/log/mysql/


設定 logrotate (若未自動)
cat > /etc/logrotate.d/mariadb << EOF
/var/log/mysql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        systemctl reload mariadb > /dev/null 2>&1 || true
    endscript
}
EOF












問題：效能未明顯提升

可能原因：
innodb_buffer_pool_size 設定不足
索引缺失
查詢未最佳化

解決方法：

確認 buffer pool 設定
sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"


計算 buffer pool 命中率
命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%
理想值應大於 99%

啟用慢查詢日誌
在設定檔中取消慢查詢註解，重啟後分析
sudo mariadb -e "SET GLOBAL slow_query_log = ON;"
sudo mariadb -e "SET GLOBAL long_query_time = 2;"

安裝 pt-query-digest 分析慢查詢
apt install percona-toolkit
pt-query-digest /var/log/mysql/mariadb-slow.log


檢查未使用索引的查詢
sudo mariadb -e "SELECT * FROM sys.schema_unused_indexes;"















問題：innodb_buffer_pool_size 設定後 OOM

可能原因：
設定值過高
系統記憶體不足
其他服務佔用過多記憶體

解決方法：

檢查記憶體使用
free -h
top -o %MEM

計算建議值
保留至少 512MB 給系統 + 其他服務
2GB 總記憶體 → 建議 innodb_buffer_pool_size = 768M

緊急處理：降低 buffer pool 並重啟
修改設定檔後重啟
systemctl restart mariadb

啟用 swap (若尚未啟用)
fallocate -l 1G /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab


警告：Swap僅作為緊急緩衝，不應依賴 swap 來支撐正常運作

若系統頻繁使用 swap，應考慮升級記憶體或降低 buffer pool











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

可能原因：
編輯錯誤的設定檔
未重新啟動服務
設定檔語法錯誤被忽略

解決方法：

確認目前生效的設定
mariadbd --print-defaults


找出所有載入的設定檔
mariadbd --help --verbose | grep -A 1 "Default options"


確認修改的檔案是否在列表中

檢查設定檔語法
mariadbd --validate-config

強制重新載入
systemctl daemon-reload
systemctl restart mariadb

確認特定參數
sudo mariadb -e "SHOW VARIABLES LIKE '參數名稱';"








效能監控建議

日常監控指令

快速健康檢查腳本
cat > ~/mysql_healthcheck.sh << 'EOF'
#!/bin/bash
echo "=== MariaDB 健康檢查 ==="
echo "時間：$(date)"
echo ""

echo "--- 服務狀態 ---"
systemctl status mariadb --no-pager | grep "Active:"

echo ""
echo "--- 連線狀況 ---"
sudo mariadb -e "SHOW STATUS LIKE 'Threads_connected';"
sudo mariadb -e "SHOW STATUS LIKE 'Max_used_connections';"

echo ""
echo "--- InnoDB Buffer Pool ---"
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

echo ""
echo "--- 慢查詢統計 ---"
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

echo ""
echo "--- 磁碟使用 ---"
df -h /var/lib/mysql

echo ""
echo "--- 記憶體使用 ---"
ps aux | grep mariadb | grep -v grep
EOF

chmod 700 ~/mysql_healthcheck.sh




設定定期監控

建立 cron job
crontab -e

加入以下內容 (每小時檢查)
0 * * * * ~/mysql_healthcheck.sh >> /var/log/mysql_health.log 2>&1




使用內建效能 Schema

啟用 performance_schema

在設定檔的 [mariadbd] 區塊加入以下設定後重啟
performance_schema = ON


查詢耗時最久的 SQL
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10\G


查詢最常執行的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY COUNT_STAR DESC LIMIT 10;


注意：performance_schema 會增加約 5~10% 的記憶體用量

2GB 環境若資源已緊繃，可視情況保持關閉，改用慢查詢日誌






常用指令速查

啟動 MariaDB
systemctl start mariadb

停止 MariaDB
systemctl stop mariadb

重新啟動 MariaDB
systemctl restart mariadb

查看服務狀態
systemctl status mariadb


設定開機自動啟動
systemctl enable mariadb


查看最近 100 行系統日誌
journalctl -u mariadb -n 100


驗證設定檔語法
mariadbd --validate-config


顯示目前生效的所有設定
mariadbd --print-defaults


以 root 免密碼登入資料庫 (unix_socket)
sudo mariadb


以密碼登入資料庫
mariadb -u root -p


執行安全初始化
mariadb-secure-installation

本帖最后于3月27日3月27日，由Jack编辑