作者：Arunjith Aravindan

翻譯：管長(zhǎng)龍

本文來(lái)源：https://www.percona.com/blog/2020/10/27/using-MySQL-8-persisted-system-variables/

MySQL 8 之前，使用的動(dòng)態(tài)變量不是永久性的，并且在重啟后會(huì)重置。可在運(yùn)行時(shí)使用 SET 語(yǔ)句更改這些變量，以影響當(dāng)前實(shí)例的操作，但是我們必須手動(dòng)更新 my.cnf 配置文件以使其持久化。

在許多情況下，從服務(wù)端更新 my.cnf 并不是一個(gè)方便的選擇，并且使變量?jī)H被更新才能在后續(xù)重新啟動(dòng)時(shí)動(dòng)態(tài)還原，而沒有任何歷史記錄。

持久化系統(tǒng)變量是 MySQL 8 中引入的功能之一。新功能可幫助 DBA 動(dòng)態(tài)更新變量并注冊(cè)它們，而無(wú)需從服務(wù)器端訪問配置文件。

如何持久化全局系統(tǒng)變量？

與 SET GLOBAL 一樣，SET PERSIST 是可用于在運(yùn)行時(shí)更新系統(tǒng)變量并使它們?cè)谥匦聠?dòng)后保持不變的命令。當(dāng)我們使用 PERSIST 關(guān)鍵字時(shí)，變量更改將更新到數(shù)據(jù)目錄中的 mysqld-auto.cnf 選項(xiàng)文件。mysqld-auto.cnf 是僅在第一次執(zhí)行 PERSIST 或 PERSIST_ONLY 語(yǔ)句時(shí)創(chuàng)建的 JSON 格式文件。

讓我們以更新最大連接數(shù)為例，看看此功能的工作原理。

mysql> SET PERSIST max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@max_connectionsG
*************************** 1. row ***************************
@@max_connections: 1000
1 row in set (0.00 sec)

生成的 mysqld-auto.cnf 如下所示：

cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {
     "max_connections" : {
         "Value" : "1000" , "Metadata" : {
             "Timestamp" : 1602543199335909 , "User" : "root" , "Host" : "localhost" } } } }

如何保留只讀的系統(tǒng)變量？

當(dāng)需要更改只讀變量時(shí)，我們需要使用 PERSIST_ONLY 關(guān)鍵字。該子句更新 mysqld-auto.cnf 文件中的更改，但不適用于 MySQL，在下一次 MySQL 重新啟動(dòng)時(shí)繼續(xù)存在。這使得 PERSIST_ONLY 適合配置只能在服務(wù)器啟動(dòng)時(shí)設(shè)置的只讀系統(tǒng)變量。

mysql>  SET PERSIST innodb_log_file_size=50331648*2;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read-only variable
mysql> set persist_only innodb_log_file_size=50331648*2;
Query OK, 0 rows affected (0.01 sec)

如何清除永久系統(tǒng)變量設(shè)置？

我們可以使用 RESET PERSIST 命令從 mysqld-auto.cnf 中刪除持久設(shè)置。運(yùn)行不帶特定變量名的命令時(shí)要小心，因?yàn)樗鼘呐渲梦募袆h除所有設(shè)置。實(shí)際上，它從 mysqld-auto.cnf 中刪除了持久設(shè)置，但沒有從 MySQL中 刪除。

看幾個(gè)例子：

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)
cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {  } }

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              1000 |
+-------------------+
1 row in set (0.01 sec)

如果想清除特定變量而不是清除配置文件中的所有設(shè)置，則以下示例向我們展示了如何執(zhí)行此操作。如果我們嘗試刪除 mysqld-auto.cnf 中不存在的變量，則會(huì)導(dǎo)致錯(cuò)誤，如下所示，我們可以使用 IF EXISTS 子句來(lái)抑制該錯(cuò)誤。

mysql> RESET PERSIST max_connections;
Query OK, 0 rows affected (0.00 sec)
mysql> RESET PERSIST innodb_max_dirty_pages_pct;
ERROR 3615 (HY000): Variable innodb_max_dirty_pages_pct does not exist in persisted config file
mysql>
mysql> RESET PERSIST IF EXISTS innodb_max_dirty_pages_pct;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
| Warning | 3615 | Variable innodb_max_dirty_pages_pct does not exist in persisted config file |

有沒有辦法禁用持久性？

是的，persisted_globals_load 參數(shù)用于啟用或禁用持久化的系統(tǒng)變量。禁用后，服務(wù)器啟動(dòng)順序?qū)⒑雎?mysqld-auto.cnf 文件。手動(dòng)更改為 mysqld-auto.cnf 文件可能會(huì)在服務(wù)器啟動(dòng)時(shí)導(dǎo)致解析錯(cuò)誤。在這種情況下，服務(wù)器報(bào)告錯(cuò)誤并退出。如果發(fā)生此問題，則必須在禁用 persisted_globals_load 系統(tǒng)變量或使用以下示例中提供的 --no-defaults 選項(xiàng)的情況下啟動(dòng)服務(wù)器。

mysql> select @@persisted_globals_load ;
+-------------------------------------+
| @@persisted_globals_load            |
+-------------------------------------+
|                 1                   |
+-------------------------------------+
1 row in set (0.00 sec)

grep -i persisted_globals_load /etc/my.cnf
persisted_globals_load=0

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@persisted_globals_load ;
+-----------------------------------+
|    @@persisted_globals_load       |
+-----------------------------------+
|                 0                 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               500 |
+-------------------+
1 row in set (0.00 sec)

需要什么授權(quán)？

考慮到安全性，正確用戶的正確權(quán)限絕對(duì)是最佳實(shí)踐。SYSTEM_VARIABLES_ADMIN 和 PERSIST_RO_VARIABLES_ADMIN 是用戶使用 SET PERSIST_ONLY 將全局系統(tǒng)變量持久保存到 mysqld-auto.cnf 的必需特權(quán)。

用戶還需要具有 SHUTDOWN 特權(quán)才能使用 RESTART 命令。它提供了一種從客戶端會(huì)話重新啟動(dòng) MySQL 的方法，而無(wú)需在服務(wù)器主機(jī)上進(jìn)行命令行訪問。

mysql> CREATE USER 'admin_persist'@'localhost' IDENTIFIED BY '*********';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN on *.* to 'admin_persist'@'localhost';
Query OK, 0 rows affected (0.03 sec)

如何監(jiān)視變量？

要列出使用 PERSIST 選項(xiàng)更新的變量，我們可以查詢 performance_schema.persisted_variables 表以及其他幾個(gè)表，如下所示。這是一個(gè)如何從 MySQL 端監(jiān)視變量的簡(jiǎn)單示例，您可以根據(jù)需要修改查詢。

mysql> select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_log_file_size
  current_value: 50331648
  persist_value: 100663296
       SET_TIME: 2020-10-12 18:54:35.725177
       SET_USER: arun
VARIABLE_SOURCE: COMPILED
  VARIABLE_PATH:

*************************** 2. row ***************************
  VARIABLE_NAME: max_connections
  current_value: 1000
  persist_value: 1000
       SET_TIME: 2020-10-12 18:53:19.336115
       SET_USER: root
VARIABLE_SOURCE: DYNAMIC
  VARIABLE_PATH:
2 rows in set (0.06 sec)

mysql> restart;
Query OK, 0 rows affected (0.01 sec)

select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_log_file_size
  current_value: 100663296
  persist_value: 100663296
       SET_TIME: 2020-10-12 18:54:35.725177
       SET_USER: arun
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf

*************************** 2. row ***************************
  VARIABLE_NAME: max_connections
  current_value: 1000
  persist_value: 1000
       SET_TIME: 2020-10-12 18:53:19.335909
       SET_USER: root
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf
2 rows in set (0.16 sec)