MySQL中的持久化系统变量

icysmile131

于 2024-06-24 09:32:54 发布

阅读量984

点赞数 26

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u011565038/article/details/139911578

版权

MySQL 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

MySQL服务器维护用于配置其操作的系统变量。系统变量可以具有影响整个服务器操作的全局值，也可以具有影响当前会话的会话值，或者两者兼而有之。许多系统变量是动态的，可以在运行时使用SET语句进行更改，以影响当前服务器实例的操作。SET还可以用于将某些全局系统变量持久化到数据目录中的mysqld-auto.cnf文件中，以影响后续启动的服务器操作。RESET PERSIST从mysqld-auto.cnf中删除持久化设置。

以下讨论描述了持久化系统变量的几个方面：

2 持久化系统变量概述

在运行时持久化全局系统变量的功能使服务器配置能够在服务器启动时持久化。尽管许多系统变量可以在启动时从my.cnf选项文件设置，也可以在运行时使用set语句设置，但这些配置服务器的方法要么需要登录访问服务器主机，要么不提供在运行时或远程持久配置服务器的能力：

修改选项文件需要直接访问该文件，这需要登录MySQL服务器主机。这并不总是方便的。

使用SET GLOBAL修改系统变量是一种运行时功能，可以在本地运行的客户端或远程主机上完成，但更改仅影响当前运行的服务器实例。这些设置不是持久的，不会延续到后续的服务器启动中。

为了增强服务器配置的管理功能，使其超出编辑选项文件或使用SET GLOBAL所能实现的范围，MySQL提供了SET语法的变体，将系统变量设置持久化到数据目录中名为mysqld-auto.cnf文件的文件中。示例：

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

SET PERSIST_ONLY back_log = 100;
SET @@PERSIST_ONLY.back_log = 100;

MySQL还提供了一个RESET PERSIST语句，用于从mysqld-auto.cnf中删除持久化的系统变量。

通过持久化系统变量执行的服务器配置具有以下特征：

持久化设置是在运行时进行的。
持久化设置是永久性的。它们适用于服务器重新启动时。
可以从本地客户端或从远程主机连接的客户端进行持久化设置。这提供了从中央客户端主机远程配置多个MySQL服务器的便利性。
要持久化系统变量，您不需要对MySQL服务器主机具有登录访问权限，也不需要对选项文件具有文件系统访问权限。持久化设置的能力是使用MySQL特权系统控制的。
具有足够权限的管理员可以通过保留系统变量来重新配置服务器，然后通过执行RESTART语句使服务器立即使用更改后的设置。
持久化设置提供有关错误的即时反馈。手动输入的设置中的错误可能要过很久才能发现。持久化系统变量的SET语句避免了设置格式错误的可能性，因为存在语法错误的设置不会成功，也不会更改服务器配置。

2 持久化系统变量的语法

这些SET语法选项可用于持久化系统变量：

（1）在 MySQL 中，如果你想要将某个全局系统变量的值持久化（即保存到配置文件中，使得 MySQL 服务器重启后该值仍然保持），你可以使用 PERSIST 关键字或者 @@PERSIST. 前缀来设置这个变量。但需要注意的是，这个功能是在 MySQL 8.0 及更高版本中引入的。

使用 PERSIST 关键字或 @@PERSIST. 前缀设置变量时，MySQL 会将该变量的值写入到 mysqld-auto.cnf 文件中（如果该文件存在的话）。这个文件通常位于 MySQL 的数据目录中，并且它会在 MySQL 服务器重启时被读取，以恢复之前设置的 PERSIST 变量的值。

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

与SET GLOBAL一样，SET PERSIST设置全局变量运行时值，但也将变量设置写入mysqld-auto.cnf文件（如果有，则替换任何现有的变量设置）。

（2）在 MySQL 8.0 及更高版本中，如果您想要将一个全局系统变量的值持久化到 mysqld-auto.cnf 文件中，但不希望在运行时立即更改该变量的值，您可以使用 PERSIST_ONLY 关键字或 @@PERSIST_ONLY. 前缀。

使用 PERSIST_ONLY 可以确保变量的值在下次 MySQL 服务器重启时生效，但不会在当前会话或全局范围内立即更改。

以下是使用 PERSIST_ONLY 关键字或 @@PERSIST_ONLY. 前缀来持久化一个全局变量的示例：

SET PERSIST_ONLY back_log = 1000;
SET @@PERSIST_ONLY.back_log = 1000;

与PERSIST一样，PERSIST_ONLY将变量设置写入mysqld-auto.cnf。但是，与PERSIST不同，PERSIST_ONLY不修改全局变量运行时值。这使得PERSIST_ONLY适合配置只读系统变量，这些变量只能在服务器启动时设置。

这些RESET PERSIST语法选项可用于删除持久化的系统变量：

（1）要从 mysqld-auto.cnf 文件中移除所有已持久化的变量，您可以使用 RESET PERSIST 命令，而不需要指定任何特定的系统变量名。这将清除 mysqld-auto.cnf 文件中通过 SET PERSIST 或 SET @@PERSIST. 设置的所有变量。

RESET PERSIST;

（2）要从 mysqld-auto.cnf 文件中移除一个特定的已持久化的变量，您需要在 RESET PERSIST 语句中指定该变量的名称。但是，RESET PERSIST 本身并不直接支持通过指定变量名来移除单个持久化变量。不过，您可以通过设置该变量的值为一个默认或您想要的新值（使用 SET PERSIST 或 SET @@PERSIST.），从而覆盖 mysqld-auto.cnf 中之前的设置。

RESET PERSIST system_var_name;

当涉及到插件系统变量时，即使当前没有安装该插件，你仍然可以尝试将其持久化到 mysqld-auto.cnf 文件中。但是，如果尝试持久化一个不存在的变量（无论是因为插件未安装还是其他原因），MySQL 通常会返回一个错误。

如果你尝试设置一个不存在的系统变量（无论是否是插件特定的），你可能会收到类似于以下内容的错误消息：

ERROR 1193 (HY000): Unknown system variable 'variable_name'

（3）要从mysqld-auto.cnf中删除特定的持久化变量，但如果该变量不在文件中，则会产生警告而不是错误，请在前面的语法中添加if EXISTS子句：

RESET PERSIST IF EXISTS system_var_name;

在 MySQL 中，当您使用 SET PERSIST 命令将全局系统变量设置为 DEFAULT 值或它的字面默认值时，该变量将被赋予其默认值，并且该变量的设置会被添加到 mysqld-auto.cnf 文件中。

例如：

SET PERSIST max_connections = DEFAULT;

这条命令会将 max_connections 变量设置为它的默认值，并将这个设置持久化到 mysqld-auto.cnf 文件中。

如果您之后想要从 mysqld-auto.cnf 文件中移除这个变量的设置，您应该使用 RESET PERSIST 命令，并且不需要指定变量名（因为您想要移除所有通过 SET PERSIST 设置的变量）：

RESET PERSIST;

请注意，RESET PERSIST 命令会移除所有通过 SET PERSIST 设置的变量，而不仅仅是您指定的那一个。如果您只想移除特定的变量，您目前不能直接通过 SQL 命令来实现。相反，您可能需要手动编辑 mysqld-auto.cnf 文件，或者使用脚本工具来自动完成这个操作。

如果在执行SET语句时安装了插件，则插件实现的系统变量可以持久化。如果插件仍在安装，则持久化插件变量的分配将在随后的服务器重新启动时生效。如果不再安装插件，则当服务器读取mysqld-auto.cnf文件时，插件变量不存在。在这种情况下，服务器会在错误日志中写入警告并继续：

currently unknown variable 'var_name'
was read from the persisted config file

3 获取有关持久化系统变量的信息

MySQL 的 Performance Schema 提供了一个 persisted_variables 表，这个表为 mysqld-auto.cnf 文件中的持久化变量提供了一个 SQL 接口。通过这个接口，你可以在运行时使用 SELECT 语句来检查 mysqld-auto.cnf 文件中持久化变量的内容。

要使用这个特性，你需要确保你的 MySQL 版本支持 Performance Schema，并且它已经被启用。在大多数现代的 MySQL 安装中，Performance Schema 默认是启用的。

MySQL 的 Performance Schema 包含一个 variables_info 表，该表提供了关于系统变量何时以及由哪个用户最近设置的信息。这个表特别有用，因为它可以帮助你跟踪系统变量的更改历史，了解谁更改了它们以及何时更改的。

RESET PERSIST 命令影响 persisted_variables 表的内容，因为该表的内容与 mysqld-auto.cnf 文件的内容相对应。当你执行 RESET PERSIST 时，它会移除 mysqld-auto.cnf 文件中所有通过 SET PERSIST 设置的变量，因此这些变量在 persisted_variables 表中的条目也会被删除。

然而，RESET PERSIST 并不改变系统变量的当前值。它只是移除了持久化的设置，这些设置会在服务器重启后生效。因此，在服务器重启之前，variables_info 表的内容不会反映这些持久化设置的移除。variables_info 表记录的是变量何时以及由哪个用户被更改的信息，而 RESET PERSIST 并不直接修改系统变量的值或 variables_info 表中的信息。

4 mysqld-auto.cnf文件的格式与服务器处理

mysqld-auto.cnf文件使用如下JSON格式（为了可读性，稍微重新格式化）：

{
  "Version": 1,
  "mysql_server": {
    "max_connections": {
      "Value": "152",
      "Metadata": {
        "Timestamp": 1519921341372531,
        "User": "root",
        "Host": "localhost"
      }
    },
    "transaction_isolation": {
      "Value": "READ-COMMITTED",
      "Metadata": {
        "Timestamp": 1519921553880520,
        "User": "root",
        "Host": "localhost"
      }
    },
    "mysql_server_static_options": {
      "innodb_api_enable_mdl": {
        "Value": "0",
        "Metadata": {
          "Timestamp": 1519922873467872,
          "User": "root",
          "Host": "localhost"
        }
      },
      "log_slave_updates": {
        "Value": "1",
        "Metadata": {
          "Timestamp": 1519925628441588,
          "User": "root",
          "Host": "localhost"
        }
      }
    }
  }
}

在MySQL服务器启动时，它会按照一定的顺序处理配置文件，mysqld-auto.cnf 文件是在所有其他选项文件之后被处理的。服务器对 mysqld-auto.cnf 文件内容的处理方式如下：

如果 persisted_globals_load 系统变量被禁用，则服务器会忽略 mysqld-auto.cnf 文件。
"mysql_server_static_options" 部分包含使用 SET PERSIST_ONLY 持久化的只读变量。尽管该部分名为“static_options”，但它也可能包含某些非只读的动态变量。此部分中的所有变量都将被添加到命令行中，并与其他命令行选项一起处理。
所有剩余的持久化变量将在服务器开始监听客户端连接之前，通过执行等效的 SET GLOBAL 语句来设置。因此，这些设置直到启动过程的后期才会生效，这可能不适合某些系统变量。对于这类变量，可能更适合在 my.cnf 中设置，而不是在 mysqld-auto.cnf 中。

管理 mysqld-auto.cnf 文件应该留给MySQL服务器本身来处理。对文件的操作应该仅通过 SET PERSIST 和 RESET PERSIST 语句来执行，而不应该手动编辑文件。

如果删除了 mysqld-auto.cnf 文件，那么在下一次服务器启动时，所有持久化设置都将丢失。（如果你的意图是在没有这些设置的情况下重新配置服务器，这是允许的。）

要删除 mysqld-auto.cnf 文件中的所有设置而不删除文件本身，应该使用 RESET PERSIST 语句。例如，如果你想要重置所有使用 SET PERSIST 设置的变量，可以执行以下SQL语句：

RESET PERSIST;

手动更改 mysqld-auto.cnf 文件可能会导致服务器在启动时解析错误。如果发生这种情况，服务器会报告一个错误并退出。

为了解决这个问题，可以采取以下几种方法之一：

禁用 persisted_globals_load 系统变量：在启动服务器时，可以通过设置 --persisted_globals_load=OFF 选项来禁用持久化全局变量的加载。这样，服务器将忽略 mysqld-auto.cnf 文件中的任何设置。
使用 --no-defaults 选项：使用 --no-defaults 选项启动服务器将告诉MySQL不要从任何默认选项文件中读取设置。这包括 my.cnf、my.ini 和 mysqld-auto.cnf 文件。请注意，这也会禁用所有其他默认选项文件的加载，而不仅仅是 mysqld-auto.cnf。
移除 mysqld-auto.cnf 文件：如前所述，删除 mysqld-auto.cnf 文件将导致所有持久化设置丢失。如果确定不再需要这些设置，并且知道删除文件的风险，可以手动删除该文件。

在决定采取哪种方法之前，请确保备份了 mysqld-auto.cnf 文件（如果可能的话），以便在出现问题时可以恢复。

5 保持敏感系统变量

从 MySQL 8.0.29 开始，MySQL 服务器具有安全存储包含敏感数据（如私钥或密码）的持久化系统变量值，并限制查看这些值的能力。虽然目前没有任何 MySQL 服务器系统变量被标记为敏感，但这一新功能允许将来安全地持久化包含敏感数据的系统变量。

在升级到 MySQL 8.0.29 之后，mysqld-auto.cnf 选项文件的格式保持不变，直到第一次执行 SET PERSIST 或 SET PERSIST ONLY 语句。即使所涉及的系统变量不是敏感的，该文件的格式也会在那个时间点更改为新格式。在新格式中，旧版本的 MySQL 服务器将无法读取该文件。

这种更改确保了敏感数据的安全性，并允许 MySQL 服务器在未来支持更安全的持久化设置。如果您正在使用或计划使用 SET PERSIST 或 SET PERSIST ONLY 语句来持久化包含敏感数据的系统变量，那么请确保您的 MySQL 服务器版本至少是 8.0.29 或更高版本，以便能够利用这一新功能。

同时，如果您正在管理一个跨多个版本的 MySQL 服务器集群，那么在升级到一个支持新格式 mysqld-auto.cnf 的版本后，需要确保所有服务器都升级到了相同的版本，以避免因文件格式不兼容而导致的问题。

注：必须在MySQL Server实例上启用keyring组件，以支持持久化系统变量值的安全存储，而不是不支持该功能的keyring插件。

在mysqld-auto.cnf选项文件中，敏感系统变量的名称和值以加密的格式存储，以及生成的用于解密它们的文件密钥。生成的文件密钥依次使用存储在密钥环中的主密钥（persisted_variables_key）进行加密。当服务器启动时，将解密并使用持久敏感的系统变量。默认情况下，如果选项文件中存在加密值，但在启动时无法成功解密，则使用其默认设置。如果无法解密加密的值，则可选的最安全设置会使服务器停止启动。

系统变量persist_sensitive_variables_in_plaintext控制是否允许服务器以未加密的格式存储敏感系统变量的值，如果在使用SET persist设置值时不支持keyring组件。如果加密的值无法解密，它还控制服务器是否可以启动。

默认设置ON在密钥环组件支持可用的情况下对值进行加密，如果不支持，则保持未加密的值（并发出警告）。下次设置任何持久化系统变量时，如果当时支持密钥环，则服务器会对任何未加密的敏感系统变量的值进行加密。ON设置还允许服务器在加密的系统变量值无法解密的情况下启动，在这种情况下会发出警告并使用系统变量的默认值。在这种情况下，它们的值在被解密之前无法更改。

最安全的设置OFF表示，如果密钥环组件支持不可用，敏感的系统变量值将无法持久化。OFF设置还意味着，如果无法解密加密的系统变量值，则服务器不会启动。

SENSITIVE_VARIABLES_OBSERVER特权允许持有者查看性能模式表global_VARIABLES、session_variable斯、VARIABLES_by_thread和persisted_vvariables中敏感系统变量的值，发出SELECT语句返回其值，并在连接的会话跟踪器中跟踪对其的更改。没有此权限的用户无法查看或跟踪这些系统变量值。

如果为敏感的系统变量发出SET语句，则在将查询记录到常规日志和审核日志之前，会重写查询以将值替换为“＜redated＞”。即使通过密钥环组件的安全存储在服务器实例上不可用，也会发生这种情况。