設定 MySQL

預設值不一定適合你的伺服器

MySQL 安裝完就能使用，但它的預設值是為了「在任何電腦上都跑得起來」而設計的，例如：

• InnoDB 用來快取資料與索引的記憶體（buffer pool）預設只有 128 MB，伺服器有 32 GB 記憶體也只用這麼多
• 同時連線數上限預設 151，網站流量大時可能不夠
• 慢查詢紀錄（slow query log）預設關閉，查詢變慢時找不到原因

調整這些行為有兩個地方：

設定檔裡的每個選項，大多對應一個同名的系統變數：設定檔寫 max_connections = 300，啟動後 @@max_connections 就是 300。 本課先看設定檔，再看如何在 SQL 裡查詢與修改系統變數。

設定檔在哪裡、長什麼樣子

本課以 Ubuntu 24.04 用 apt install mysql-server 安裝的 MySQL 8.0 為準：

Windows 用 MySQL Installer 安裝時，設定檔是 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini（ProgramData 是隱藏資料夾）。 投影片使用的 XAMPP 是把 MySQL（新版是 MariaDB）和網頁伺服器包在一起的教學套件，設定檔在 C:\xampp\mysql\bin\my.ini，正式伺服器不會這樣安裝。

設定檔的格式：

# 井字號開頭是註解
[mysqld]                     # 區段：下面的選項給伺服器程式 mysqld
port                    = 3306
bind-address            = 127.0.0.1
max_connections         = 300
innodb_buffer_pool_size = 1G

[client]                     # 區段：給所有用戶端程式（mysql、mysqldump…）
default-character-set   = utf8mb4

• [mysqld] 是伺服器的設定，[client] 是所有用戶端程式共用的設定，[mysql] 只給 mysql 命令列工具
• 選項名稱中的 - 和 _ 可以互換：bind-address 與 bind_address 相同
• 大小可以加單位 K、M、G；開關可以寫 1／0 或 ON／OFF
• 設定檔只在 MySQL 啟動時讀取，修改後要重新啟動服務才會生效

常見的伺服器選項

innodb_buffer_pool_size 是影響效能最大的選項。官方文件建議，專用的資料庫伺服器可以設到實體記憶體的 80% 左右； 和網頁伺服器、其他程式共用一台主機時要保守，留記憶體給其他程式。

投影片的選項在 MySQL 8.0 的寫法：

在 SQL 裡查看系統變數

連進 MySQL 之後，用兩種方式查看系統變數：

SHOW VARIABLES LIKE 'max_connections';      -- 名稱可以用 % 萬用字元，例如 'innodb_buffer_pool%'
SELECT @@max_connections;                   -- 取出一個變數的值，可以拿來計算

SHOW VARIABLES 適合「找」變數：不記得完整名稱時，用 LIKE '%log%' 搜尋。SELECT @@變數 適合「用」變數： 例如把位元組換算成 MB。設定檔的 bind-address 在系統變數裡寫成 bind_address（底線）。

本課的範例在 CodeLab 的模擬 MySQL 裡執行，變數值是模擬的預設值，只收錄常用的系統變數（真實的 MySQL 8.0 有六百多個）； 你自己的伺服器數值會依安裝方式與設定不同。

GLOBAL 與 SESSION：改全部還是只改自己

系統變數有兩種範圍：

• GLOBAL（全域）：整個伺服器的值。新的連線建立時，會從 GLOBAL 複製一份成為自己的 SESSION 值
• SESSION（工作階段）：只屬於目前這條連線的值，連線結束就消失，不影響別人

SET GLOBAL max_connections = 300;            -- 改全域值，需要 SYSTEM_VARIABLES_ADMIN（或舊的 SUPER）權限
SET SESSION sql_mode = 'STRICT_TRANS_TABLES'; -- 只改自己這條連線；SESSION 可以省略
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode; -- 分別取出兩種範圍的值；只寫 @@sql_mode 是 SESSION 值
SET GLOBAL max_connections = DEFAULT;         -- 改回預設值

變數不一定兩種範圍都有，也不一定能在執行中修改。下面是在本機真實 MySQL 8.0.44 執行的結果：

mysql> SET SESSION max_connections = 300;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
mysql> SELECT @@SESSION.max_connections;
ERROR 1238 (HY000): Variable 'max_connections' is a GLOBAL variable
mysql> SET GLOBAL datadir = '/tmp';
ERROR 1238 (HY000): Variable 'datadir' is a read only variable

• max_connections 只有 GLOBAL：連線數上限是整台伺服器的事，沒有「我自己的連線數上限」
• datadir、port、bind_address 是唯讀變數：只能寫在設定檔，重新啟動才會改變
• sql_mode、long_query_time 兩種範圍都有

SET GLOBAL 不會改變已經連線者的 SESSION 值，包括你自己這條連線。 例如執行 SET GLOBAL long_query_time = 2 之後， SELECT @@SESSION.long_query_time 仍然是 10，要重新連線才會拿到 2。 下面的範例用「另一個帳號重新連線」，觀察新連線拿到的是新的 GLOBAL 值。

SET PERSIST：修改並保留到下次啟動

SET GLOBAL 立即生效，但只存在記憶體裡，MySQL 重新啟動就恢復成設定檔的值。以前要讓修改永久有效，得同時改設定檔，兩邊很容易不一致。 MySQL 8.0 新增了 SET PERSIST：

SET PERSIST max_connections = 500;            -- 像 SET GLOBAL 一樣立即生效，同時寫進 mysqld-auto.cnf
SET PERSIST_ONLY back_log = 100;              -- 只寫進 mysqld-auto.cnf，下次啟動才生效（用於唯讀變數）
RESET PERSIST max_connections;                -- 從 mysqld-auto.cnf 移除這一項（目前的值不變）
SELECT * FROM performance_schema.persisted_variables;  -- 查看保存了哪些設定

mysqld-auto.cnf 是 MySQL 自己管理的 JSON 檔案，放在資料目錄（Ubuntu 是 /var/lib/mysql/mysqld-auto.cnf）， 記錄每個變數的值、由哪個帳號在什麼時間設定。MySQL 啟動時最後讀它，所以它的值會蓋過 mysqld.cnf。 不要手動編輯這個檔案，用 SET PERSIST、RESET PERSIST 管理。

要查某個設定「到底從哪裡來」，performance_schema.variables_info 的 VARIABLE_SOURCE 欄位會顯示 COMPILED（程式預設）、GLOBAL（設定檔）、PERSISTED（mysqld-auto.cnf）或 DYNAMIC（執行中用 SET 修改）。

重點整理

• 改設定檔前先備份；選項打錯字 MySQL 會拒絕啟動，看 /var/log/mysql/error.log
• 唯讀變數（datadir、port、bind_address）只能寫在設定檔或用 SET PERSIST_ONLY，重新啟動才生效
• 投影片的 set-variable =、log-slow-queries、innodb_additional_mem_pool_size 在 8.0 都已移除

下一課介紹日常管理用的 SHOW 指令、伺服器狀態與 mysqladmin。