MySQL進階配置實戰：網絡監聽、字符集、InnoDB表空間與多實例部署優化

網絡層精細化控制

LOAD DATA LOCAL 安全控制

• 服務端：啟動參數--local-infile=0禁用（默認開啟）
• 客戶端：需顯式--local-infile=1才能使用
• 風險：惡意服務端可讀取客戶端任意文件（如/etc/passwd），生產環境雙端均建議禁用

國際化與字符集最佳實踐

字符集演進與選型

• utf8mb4：真正UTF-8，支持Emoji/補充字符，**MySQL 5.5.3+必選默認字符集**
• utf8：MySQL歷史偽UTF-8，僅支持BMP平面（3字節），新項目禁用
• latin1：單字節，無轉換開銷，適合純西文/二進制存儲場景

三層字符集設置機制

# 服務級默認
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# 庫級覆蓋
CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 表/列級覆蓋
CREATE TABLE t (c VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

連接層字符集協商（關鍵！）

# 客戶端執行（或連接串指定）
SET NAMES utf8mb4;  -- 等同於同時設置 client/connection/results 三個會話變量

# 驗證
SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_results    | utf8mb4 |
+--------------------------+---------+

亂碼根因90%為連接層未正確SET NAMES，ORM框架需在連接池初始化時執行。

舊庫升級utf8mb4操作規範

1. mysqldump --default-character-set=utf8mb4 --set-charset=utf8mb4 --single-transaction db > dump.sql
2. 修改dump.sql中CHARSET=utf8 → CHARSET=utf8mb4
3. 調整列長度：VARCHAR(255) → VARCHAR(191)（唯一索引前綴長度限制767字節/4=191）
4. 導入驗證：mysql --default-character-set=utf8mb4 db < dump.sql

InnoDB表空間架構與管理

系統表空間 vs 獨立表空間

• innodb_file_per_table=ON（默認）：每表獨立.ibd，支持DISCARD/IMPORT TABLESPACE、Online DDL、單表備份
• 系統表空间ibdata1：存儲數據字典、Undo Log、雙寫緩存、插入緩存，**不可縮小**，僅增長

表空間擴容三種模式

# 1. 自動擴展（默認最後一文件）
innodb_data_file_path=ibdata1:12M:autoextend:max:10G

# 2. 新增文件（需停機）
# 步驟：停服 → 修改配置添加新文件 → 啟動
innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend

# 3. 使用裸設備（繞過文件系統限制，極致性能）
innodb_data_home_dir=
innodb_data_file_path=/dev/nvme0n1:500Gnewraw
# 初始化後改為 raw
innodb_data_file_path=/dev/nvme0n1:500Graw

Undo Log獨立表空間（MySQL 5.7+）

[mysqld]
innodb_undo_directory=/mnt/undo_logs
innodb_undo_tablespaces=4
innodb_undo_log_truncate=ON
innodb_max_undo_log_size=1G

將高頻寫入Undo Log隔離到高IOPS存儲，防膨脹影響主表空間，支持在線截斷回收空間。

多實例部署模板

# /etc/mysql/my3307.cnf
[mysqld]
server-id=3307
port=3307
socket=/var/run/mysqld/mysqld3307.sock
datadir=/data/mysql3307
log-error=/var/log/mysql/mysql3307.error
pid-file=/var/run/mysqld/mysqld3307.pid
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
# ... 其他差異化配置

# systemd 管理
systemctl start mysqld@3307
systemctl enable mysqld@3307

核心隔離點：datadir、socket、port、pid-file、log-error、server-id 必須唯一。