MySQL 服務維護筆記（續）：備份自動化、災難恢復、應用設計優化與效能調校實戰

前言

承接上篇架構規劃，本文深入備份腳本自動化、資料恢復策略、應用層設計原則、SQL 優化技巧、資料表拆分策略等實戰主題。

一、自動化備份腳本：按週期輪循

/home/mysql/bin/mysqldump -S /data/app_1/mysql.sock -u mysql db_name | gzip -f > /path/to/backup/db_name.`date +%w`.dump.gz

Crontab 設定（每天凌晨 4:15 執行）

15 4 * * * /home/mysql/bin/mysqldump -S /data/app_1/mysql.sock -u mysql db_name | gzip -f > /path/to/backup/db_name.`date +\%w`.dump.gz

注意： Crontab 中 % 需轉義為 \%；選在負載最低的凌晨 4-6 點執行。

遠端備份策略

• 本地壓縮後傳送至遠端備份伺服器。
• 或直接建立遠端備份帳號，將 -S socket 改為 -h IP 遠端直備。

二、資料恢復與系統升級

日常維護/遷移（資料盤完好）

硬碟壽命最短。升級作業系統或硬體時：裝好新機 → 掛載資料盤（硬碟2） → 重新加入啟動腳本 → 服務即恢復。

災難恢復（資料損壞）

1. 確認損壞時間點。
2. 從對應備份檔恢復：zcat backup.gz | mysql -u root -p db_name
3. 若有 Binary Log，可做時間點恢復 (PITR)。

三、應用設計核心原則

1. CPU 佔用超過 10% 即需優化

若可用非資料庫方案替代（如計數器用 Web Log 分析），優先移除資料庫依賴。

2. 非即時需求：檔案/DBM 先行，定期導入資料庫

簡單 Key-Value 結構可用 HEAP (Memory) 表，效能極高（適合線上使用者、暫存日誌），但斷電會丟資料。

3. 引擎選擇：MyISAM vs InnoDB

實測將 InnoDB 改為 MyISAM，效能提升 20 倍（無交易需求時）。僅在嚴格商業邏輯/交易完整性時才需 InnoDB/Oracle。

4. 連線數瓶頸：多實例分流

• 單服務極限約 500 併發，調度開銷大。
• 建議：一台 1GB 記憶體跑 10 個 MySQL 實例（Port 3301~3310），各承擔 100 連線，總支撐 1000+ 併發，效率遠優於 2 個實例各 500。
• 代價：應用程式需實現連線池/路由邏輯。

5. 專用資料庫伺服器：搶記憶體做快取

分離 Web 與 DB 伺服器，MySQL 獨享記憶體，可大幅增加 key_buffer（如 32MB vs 預設 8MB），提升索引快取命中率。

6. 連線池與 Persistent Connection 取捨

pconnect 減少建立連線開銷，但會佔用固定連線槽，需配合連線池機制平衡。

四、資料表拆分策略

橫向拆分：冷熱資料分離（快慢表）

• 最常訪問 10% 熱資料 → 小表（快表）
• 90% 歷史資料 → 歸檔表（慢表）
• 定期「搬家」與清理無效資料
• 論壇類應用：2個月前資料訪問率極低，拆分後快表維持 10萬筆以內，查詢極快
• 統計近似值：快表 60 筆 × 比例 20 = 估算 1200 筆（Google 搜尋結果同理）

縱向拆分：定長/變長欄位分離

• 定長欄位 (CHAR, INT) → 主表（固定長度，掃描快）
• 變長欄位 (VARCHAR, TEXT, BLOB) → 副表
• 減少主表行大小，單頁容納更多行，提升全表掃描與範圍查詢效能

五、欄位設計細節

• 能用 INT 別用 BIGINT；能用 CHAR 別用 VARCHAR（固定長度優化）。
• NOT NULL 預設值優於 NULL（節省位元圖空間）。
• ENUM 替代常用字串欄位（儲存為數字，極省空間）。

小結

備份自動化、冷熱分離、多實例分流、專用 DB 伺服器、欄位精簡設計，構成高效能 MySQL 運維的完整體系。持續監控、定期審查 Slow Query Log、EXPLAIN 執行計畫，方能長治久安。