MySQL實驗之-持久化變量

簡介

MySQL8 版本以前為了保證修改後的變量的值在 MySQL 運作時及 MySQL 重新開機後生效,需要分别修改記憶體和配置檔案中變量的值

SET global/session 語句設定記憶體中全局/目前會話的變量的值, 修改 my.cnf 或 my.ini 中的值

此時你會發現, 配置⽂件需要 通過 ps -ef | grep mysql 檢視程序啟動指令中帶⼊的選項 --defaults-file 以确定配置⽂件, 如果啟動指令沒有帶⼊選項 --defaults-file, 那麼預設配置⽂件為 /etc/my.cnf你會發現修改配置⽂件時, 需要确定配置⽂件, 以及修改配置⽂件, 這個步驟其實蠻繁瑣的, ⽽且如果你⼀不小心寫錯了變量名或值, 可能會直接導緻 MySQL 啟動失敗, 如果此時你有其他政策依賴 MySQL 的啟動/重新開機,那麼這個 MySQL 啟動失敗可能⼜會導緻你政策判斷不符合預期, 造成其他不好的後果, 這可能是就是亞馬遜飓風初始的那隻蝴蝶的翅膀

修改方式

MySQL8 以後, 對于持久化變量的值, 有了其他方式

● SET PERSIST 語句可以修改記憶體中變量的值, 并且将修改後的值寫⼊資料⽬錄中的 mysqld-auto.cnf 中

● SET PERSIST_ONLY 語句不會修改記憶體中變量的值, 隻是将修改後的值寫⼊資料⽬錄中的 mysqld-auto.cnf 中

注意

PERSIST 和 PERSIST_ONLY 設定的都是 Global 級别, 不是 Session 級别

删除 mysqld-auto.cnf ⽂件 或設定 persisted_globals_load 為 off 都不會加載變量持久化的配置

通過語句 SET PERSIST / PERSIST_ONLY 去持久化變量的值到⽂件中, 相較于直接修改配置⽂件⽽⾔, 使⽤語句修改的⽅式更安全, 因為語句會校驗變量和值的準确性, 不會産⽣因為修改錯誤導緻的 MySQL 啟動失敗的問題; 因為 MySQL 啟動期間, 最後才讀取 mysqld-auto.cnf ⽂件, 也就是說 mysqld-auto.cnf ⽂件中的變量會覆寫配置⽂件中的變量的值, 是以推薦直接使⽤ SET PERSIST/ PERSIST_ONLY 修改變量的值, ⽽不是⼿⼯修改配置⽂件, 除⾮變量隻能通過修改配置⽂件的⽅式修改

權限說明

● SET PERSIST 需要 system_variables_admin 權限

● SET PERSIST_ONLY 需要 system_variables_admin 和 persist_ro_variables_admin 權限

權限補充說明

MySQL 安裝完成後, root 賬号是不存在 system_variables_admin, persist_ro_variables_admin 權限的

但是 root 賬号存在 WITH GRANT OPTION 權限,是以 root 可以給自己/其他賬号授予 system_variables_admin, persist_ro_variables_admin 權限

GRANT system_variables_admin, persist_ro_variables_admin ON *.* TO user_name@'%'

全局變量儲存位置

1. 資料⽬錄下的 mysqld-auto.cnf 持久化資訊以 json 格式儲存, metadata 記錄了這次修改的使用者及時間資訊. 檔案中還區分了變量的類型: mysql_server (動态變量)和 mysql_server_static_options (隻讀變量); 在資料庫啟動時, 會首先讀取其他配置⽂件, 最後才讀取 mysqld-auto.cnf 檔案. 不建議⼿動修改 mysqld-auto.cnf ⽂件, 其可能導緻資料庫在啟動過程中因解析錯誤⽽失敗. 如果出現這種情況,可⼿動删除 mysqld-auto.cnf ⽂件或設定 persisted_globals_load 為 off 來避免該⽂件的加載

2. 表 performance_schema.persisted_variables

涉及參數

persisted_globals_load = ON -- 8.0.0添加, 預設是 ON, 表示 MySQL 啟動時, 讀取資料⽬錄下的 mysqld-auto.cnf ⽂件中的變量, 設定為 OFF 時, 則不會讀取檔案中的變量

涉及檔案

資料目錄下的 mysqld-auto.cnf ⽂件, 資料目錄通過 select @@global.datadir; 擷取. 打開該檔案可以看到是以json儲存的文本檔案.

實驗舉例

show variables like "max_connections"; -- 檢視 max_connections 的值
set persist max_connections=800; -- persist 不僅修改了記憶體中的值,還會持久化到 mysqld-auto.cnf ⽂件中
set @@persist.max_connections=800; -- 效果同上
set persist max_connections=default; -- 把 max_connections 設定為預設值
set persist_only max_connections=300; -- persist_only 隻會将全局變量持久化到配置檔案中,不會修改記憶體中的值
set @@persist_only.back_log=300; -- 效果同上
set persist_only back_log=1000, persist max_connections=2000; -- 同時設定多個變量的值
select * from performance_schema.persisted_variables; -- 檢視已持久化的參數
reset persist back_log; -- 清除指定的已持久化的變量(mysql-auto.cnf 和 performance_schema.persisted_variables)
reset persist if exists back_log; -- 清除指定的變量, 如果變量沒在持久化配置檔案裡, 那麼就會告警, 而不是報錯 (幂等操作)
reset persist; -- 清除所有的已持久化的變量
set persist net_read_timeout=50; -- persist 隻會設定變量的 global 值, 不會設定變量的 session 值
set @@session.net_read_timeout=@@global.net_read_timeout; -- 設定目前 session 的變量值為全局變量的值