MySQL 服務維護實戰筆記：架構規劃、啟動腳本、備份策略與效能優化

高負載站點中，PHP 與 MySQL 同機運行是效能瓶頸。本文分享將 MySQL 獨立部署為專用資料庫伺服器的架構規劃、多實例啟動腳本、滾動備份策略、應用設計要點與一次實戰優化案例。

架構規劃：程式與資料分離、分盤管理

/ (系統盤)          → 作業系統、MySQL 程式碼 (/home/mysql 為最新版連結)
/home/mysql-3.23.54/ → 具體版本安裝目錄
/home/mysql-old/     → 舊版本備份
/data/app_1/         → 應用 1：資料目錄、啟動/停止腳本、socket、pid
/data/app_2/         → 應用 2：獨立資料與實例
/data/app_3/         → 應用 3：...

• 不同硬碟分離： 程式碼（系統盤）與資料（資料盤）分離，硬碟故障時只需掛載資料盤到新機器即可秒級恢復。
• 版本升級無痛： 新版本安裝至 /home/mysql-new/，修改 /home/mysql 連結指向新版，重啟實例即可；回滾同理。
• 多實例隔離： 每個應用擁有獨立 datadir、socket、port、pid，互不干擾。

MySQL 編譯安裝建議

./configure --prefix=/home/mysql --without-innodb
make && make install

• --without-innodb：若不需交易/外鍵，移除 InnoDB 減少記憶體佔用、提升 MyISAM 速度（視業務評估）。
• 避免 --with-charset 固定字元集，改由 my.cnf 或啟動參數指定，利於遷移。

多實例啟動/停止腳本

start_mysql.sh（放於 /data/app_1/）

#!/bin/sh
rundir=$(dirname "$0")
echo "$rundir"
/home/mysql/bin/mysqld_safe \
  --user=mysql \
  --pid-file="$rundir/mysql.pid" \
  --socket="$rundir/mysql.sock" \
  --datadir="$rundir/var" \
  -O max_connections=500 \
  -O wait_timeout=600 \
  -O key_buffer=32M \
  --port=3402 &

stop_mysql.sh

#!/bin/sh
rundir=$(dirname "$0")
echo "$rundir"
/home/mysql/bin/mysqladmin -u mysql -S"$rundir/mysql.sock" shutdown

關鍵參數說明

rc.local 開機自啟

/data/app_1/start_mysql.sh
/data/app_2/start_mysql.sh
/data/app_3/start_mysql.sh

日常管理便利指令

# 查看所有實例 PID
cat /data/*/mysql.pid

# 查看所有錯誤日誌
cat /data/*/var/*.err

# 進入特定實例客戶端
/home/mysql/bin/mysql -S /data/app_1/mysql.sock -u root -p

備份策略：mysqldump + 壓縮 + 輪替 + 異地

# 每日 04:15 執行（負載最低時段）
15 4 * * * /home/mysql/bin/mysqldump -S /data/app_1/mysql.sock -u mysql db_name | gzip -f > /backup/db_name.$(date +\%w).dump.gz

• date +\%w 產生 0-6 對應週日-週六，自動輪替 7 份。
• crontab 中 % 需轉義 \%。
• 遠端備份：將 -S socket 改為 -h IP，或建立專用備份帳號從遠端拉取。
• 先本地備份再 rsync/scp 至備份伺服器，避免網路抖動導致備份不完整。

應用設計要點

1. 連線池： 應用層使用連線池（如 Java HikariCP、PHP mysqli 持久連線、Go sql.DB），減少建連開銷。
2. 預處理語句： 全面採用 Prepared Statements，防注入、利用查詢計畫快取。
3. 索引策略： 依查詢模式建立複合索引，避免過度索引拖慢寫入。
4. 分頁優化： 大偏移改用游標分頁 WHERE id > last_id LIMIT 20。
5. 唯一鍵業務鍵： 避免單純自增 ID 作為業務查詢依據，加上 UNIQUE KEY (biz_code)。

實戰優化案例：單表百萬筆查詢從 8 秒降至 0.05 秒

問題

報表查詢 SELECT * FROM logs WHERE create_time BETWEEN ... AND type=5 ORDER BY id DESC LIMIT 20 執行 8 秒。

分析

• EXPLAIN 顯示 type=ALL 全表掃描，key=NULL。
• 表有單欄索引 idx_type(type)、idx_time(create_time)，但複合查詢用不到。

解決

ALTER TABLE logs ADD INDEX idx_type_time_id (type, create_time, id DESC);

• 複合索引符合 WHERE type=5 AND create_time BETWEEN ... ORDER BY id DESC，實現索引下推 + 索引排序 + 覆蓋索引（若 SELECT 欄位包含於索引）。
• 執行時間降至 0.05 秒，QPS 提升 160 倍。

擴容判斷指標

單實例 CPU 持續 > 10%、連線數逼近 max_connections、Threads_connected 高峰超 80% → 考慮新增實例分流（不同 port）、讀寫分離（主從）、分庫分表。

系統化的架構規劃、標準化的運維腳本、自動化的備份驗證，是 MySQL 穩定服務高併發業務的基石。

1. 上一篇文章： SQL 模糊查詢完全指南：LIKE 謓詞、四大萬用字元、逸出處理與 Access/SQL Server 差異
2. 下一篇文章： 從 PHP 計數器實例學 MySQL 查詢、插入、更新、刪除核心語法

MySQL 資料庫相關知識

MySQL 圖片/二進位資料處理實戰：從結果集運算到特殊字元轉義

在實際開發中，MySQL 不僅存儲結構化文字資料，處理圖片、

MySQL 下載安裝部署與圖形化工具完整教程（Windows MSI 版）

版本選擇策略 穩定性優先：選 GA (Generally

MySQL 事件調度器啟用與定時任務實戰指南

事件調度器核心操作 1. 檢查/啟用調度器 -- 查看狀態

MySQL 啟動失敗：mysql.sock 丟失？教你用錯誤日誌定位真兇

現象重現 ERROR 2002 (HY000): Cant