MySQL 服务器系统变量

没有评论

MySQL

MySQL 服务器 mysqld 具有许多命令选项和系统变量，可以在启动时或在运行时动态设置。

配置系统变量

查看选项和变量

  • 查看帮助

    服务器选项、系统变量和状态变量的对照关系：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

    shell> mysqld --verbose --help
shell> mysqld --no-defaults --verbose --help  # 只查看默认值，不看配置文件

    Note: –verbose 表示详细输出，这个帮助命令会把所有 mysqld 选项和系统变量输出到屏幕，另外，它还会读取所有的配置文件。

  • 查看系统变量

    系统变量列表：https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

    mysql> show variables;
shell> mysqladmin -uroot -p -S /tmp/mysql.sock variables
  • 查看状态变量

    状态变量列表：https://dev.mysql.com/doc/refman/8.0/en/server-status-variable-reference.html

    mysql> show status;
shell> mysqladmin -uroot -p -S /tmp/mysql.sock extended-status
  • 查看系统表
    • Performance_schema.global_variables
      • 全局变量
    • Performance_schema.global_status
      • 全局状态
    • Performance_schema.session_variables
      • 会话变量
    • Performance_schema.session_status
      • 会话状态
    • Performance_schema.persisted_variables
      • 持久化变量
    • performance_schema.variables_info
      • 所有变量的设置信息

设置方法

  • 启动时在命令行中设置
  • 在配置文件中设置
  • 使用 SET 在线动态设置

验证服务器配置

  • –defaults-file 
    shell> mysqld --defaults-file=./my.cnf-test --validate-config
2018-11-05T10:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable 'tx_read_only=ON'.
2018-11-05T10:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting
    • 可以和 –validate-config 一起使用，但是，此选项必须是命令行上第一个选项
  • –validate-config 
    mysqld --defaults-file=/etc/my.cnf --validate-config
    • 不需要正常启动就可以检查启动配置 (包括配置文件中的选项)。
    • 配置检查不会初始化存储引擎和其他插件、组件等，也不会验证那些与未初始化的子系统关联的选项。
    • 遇到错误立即退出，适用于升级检查
  • –log_error_verbosity=# 
    [root@yingzai ~]$ mysqld --defaults-file=/etc/my.cnf --validate-config --log-error-verbosity=2 --no-such-option --read-only=s --transaction-read-only=s
2021-03-04T00:51:10.448275Z 0 [Warning] [MY-000076] [Server] option 'autocommit': boolean value 's' was not recognized. Set to OFF.
2021-03-04T00:51:10.448287Z 0 [Warning] [MY-000076] [Server] option 'read_only': boolean value 's' was not recognized. Set to OFF.
2021-03-04T00:51:10.448290Z 0 [Warning] [MY-000076] [Server] option 'transaction-read-only': boolean value 's' was not recognized. Set to OFF.
2021-03-04T00:51:10.453919Z 0 [ERROR] [MY-000068] [Server] unknown option '--no-such-option'.
2021-03-04T00:51:10.453990Z 0 [ERROR] [MY-010119] [Server] Aborting
    • 显示错误日志的详细程度
    • 选项值
      • 1：(默认) 仅显示 errors
      • 2：显示 errors 和 warnings
      • 3：显示 errors、warnings 和 notes

系统变量的操作权限

系统变量分为全局值和会话值，全局值会影响整个服务器的操作，会话值仅影响当前会话。

动态系统变量，可以使用 SET 设置。

某些全局系统变量，可以使用 SET 进行持久化设置，值写入 mysqld-auto.cnf 文件中，后续重启仍然生效。

临时设置

  • SET GLOBAL
    • 设置全局变量
    • 所需权限
      • SYSTEM_VARIABLES_ADMIN
  • SET SESSION
    • 设置会话级变量
    • 通常不需要特殊权限
      • 降低角色权限
        • 通过将角色权限修改为具有 SESSION_VARIABLES_ADMIN 而不是 SYSTEM_VARIABLES_ADMIN，可以将角色权限降低为设置受限会话系统变量的能力，而不能进行其他任何设置。
      • SESSION_VARIABLES_ADMIN
        • 任何具有 SYSTEM_VARIABLES_ADMIN 或 SUPER 权限的用户都具有 SESSION_VARIABLES_ADMIN 的权限，因此无需明确授予这个会话级权限

持久化设置

  • SET PERSIST
    • 持久化设置全局变量，写入 mysqld-auto.cnf ，设置运行值
      • 写入配置文件，且应用
    • 所需权限
      • SYSTEM_VARIABLES_ADMIN 或 SUPER
  • SET PERSIST_ONLY
    • 持久化设置全局变量，写入 mysqld-auto.cnf ，但是不设置运行值

      一些全局系统变量是持久限制的，要保留这些变量，只能使用这个语句。

      此外，必须使用加密连接连接到服务器，并提供 SSL 证书，其 SSL 证书的值由 persist_only_admin_x509_subject 系统变量指定。

      • 写入配置文件，但不应用
    • 所需权限
      • SYSTEM_VARIABLES_ADMIN 和 PERSIST_RO_VARIABLES_ADMIN
  • RESET PERSIST

    对于持久的全局系统变量，可以使用 RESET PERSIST 从 mysqld-auto.cnf 文件中删除。

    • 重置持久化变量
    • 所需权限
      • 对于动态系统变量
        • SYSTEM_VARIABLES_ADMIN 或 SUPER
      • 对于只读系统变量
        • SYSTEM_VARIABLES_ADMIN 和 PERSIST_RO_VARIABLES_ADMIN
      • 对于持久性限制的变量
        • RESET PERSIST 不需要使用特定 SSL 证书建立的到服务器的加密连接。

系统变量分类

动态系统变量

变量列表：https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html

  • 动态系统变量，可以在运行时设置

持久化系统变量

SET 还可以用于将某些全局系统变量持久保存到数据目录下的 mysqld-auto.cnf 文件中，以影响后续启动时服务器的操作。

RESET PERSIST 从 mysqld-auto.cnf 中删除持久设置。

使用 SET GLOBAL 修改系统变量，不能持久保存。服务器重启后失效。

  • 变量持久化设置的特点
    • 持久化设置在运行时进行 (动态设置)
    • 持久化设置是永久的，重启后仍然有效 (因为会写入配置文件)
    • 可以从本地或远程客户端进行设置
    • 持久化设置不需要服务器主机文件系统的登录或访问权限，它是由 MySQL 的权限控制的
    • 可以通过持久化设置来重置服务器，执行 RESTART 语句后立即生效
    • 持久化设置可立即看到错误反馈
  • 语法
    • SET PERSIST 
      SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;
      • 持久化设置全局变量，写入 mysqld-auto.cnf ，设置运行值
    • SET PERSIST_ONLY 
      SET PERSIST_ONLY back_log = 1000;
SET @@PERSIST_ONLY.back_log = 1000;
      • 持久化设置全局变量，写入 mysqld-auto.cnf ，但是不设置运行值
    • RESET PERSIST 
      -- 删除所有持久化变量 (使用此语句删除，不要直接删除 mysqld-auto.cnf 文件)
RESET PERSIST;
-- 删除某个持久化变量
RESET PERSIST system_var_name;
RESET PERSIST IF EXISTS system_var_name;
      • 使用 RESET PERSIST 语句，可以删除 mysqld-auto.cnf 文件中的持久化变量
  • 查看持久化信息
    • performance_schema.persisted_variables 
      mysql> select * from performance_schema.persisted_variables;
+----------------------------------------------------+----------------+
| VARIABLE_NAME                                      | VARIABLE_VALUE |
+----------------------------------------------------+----------------+
| autocommit                                         | ON             |
| group_replication_enforce_update_everywhere_checks | ON             |
| group_replication_single_primary_mode              | OFF            |
+----------------------------------------------------+----------------+
      • 这个性能表是 mysqld-auto.cnf 文件的 SQL 接口，可以通过 SQL 语句查询
      • 因为 RESET PERSIST 会修改 mysqld-auto.cnf 文件内容，所以也会影响此表内容
    • performance_schema.variables_info 
      mysql> select * from performance_schema.variables_info limit 2\G
*************************** 1. row ***************************
  VARIABLE_NAME: activate_all_roles_on_login
VARIABLE_SOURCE: EXPLICIT
  VARIABLE_PATH: /etc/my.cnf
      MIN_VALUE: 0
      MAX_VALUE: 0
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
*************************** 2. row ***************************
  VARIABLE_NAME: admin_address
VARIABLE_SOURCE: EXPLICIT
  VARIABLE_PATH: /etc/my.cnf
      MIN_VALUE: 0
      MAX_VALUE: 0
       SET_TIME: NULL
       SET_USER: NULL
       SET_HOST: NULL
2 rows in set (0.00 sec)
      • 这个性能表包含了最近设置每个系统变量的时间和对象信息
      • 因为 RESET PERSIST 没有更改变量值，所以不会影响此表内容
  • mysqld-auto.cnf 文件
    • 使用 JSON 格式，在处理完其他配置文件之后才处理此文件
      • 优先级最高
    • 此文件只能通过 SET 和 RESET PERSIST 语句来设置，不应该手动修改

    • 默认存放在数据目录下

非持久性和持久性限制的系统变量

并非所有系统变量都可以持久化，或者仅在某些限制性条件下才能持久化。

不可持久的系统变量在任何情况下都不能持久化。

  • 某些系统变量不可持久或受持久限制的原因
    • 会话级系统变量无法持久保存
    • 全局系统变量可能涉及敏感数据，因此只能由直接访问服务器主机的用户才能设置
    • 全局系统变量可能是只读的 (仅由服务器设置)
    • 全局系统变量可能仅供内部使用
  • 可以使用 SET PERSIST_ONLY 持久存储受限制的系统变量，但是必须满足一定条件
    • persist_only_admin_x509_subject 系统变量设置为 SSL 证书 X.509 对象值
    • 用户使用加密连接连接到服务器，并提供具有指定主题值的 SSL 证书
    • 用户具有使用 SET PERSIST_ONLY 权限
  • 允许服务器进行持久化设置
    • 确保将 MySQL 配置为支持加密连接

    • 指定 SSL 证书 X.509 对象值，该值表示持久存储受限制的系统变量并生成具有该主题的证书

    • 启动时指定 persist_only_admin_x509_subject 的对象值

      [mysqld]
persist_only_admin_x509_subject="subject-value"
    • 重启服务

    • 将具有指定对象值的 SSL 证书分发给允许持久存储受限制的系统变量的用户

结构化系统变量

  • 用途

    • 这种类型的变量用于控制 MyISAM 键缓存的操作
  • 引用
    • 引用格式 
      instance_name.component_name
      • 对于每个结构化 (structured) 系统变量，始终会预先定义一个名称为 default 的实例。如果引用时未指定实例名称，默认使用 default 实例。
      • default.key_buffer_size 和 key_buffer_size 等效。
    • 示例
      • 在命令行中指定 
        shell> mysqld --key_buffer_size=6M \
         --hot_cache.key_buffer_size=2M \
         --cold_cache.key_buffer_size=2M
      • 编辑配置文件 
        [mysqld]
hot_cache.key_buffer_size=64K
      • 动态设置 
        mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql> SET @@GLOBAL.hot_cache.key_buffer_size = 10*1024*1024;
mysql> SELECT @@GLOBAL.hot_cache.key_buffer_size;
  • 变量列表
    • key_buffer_size
    • key_cache_block_size
    • key_cache_division_limit
    • key_cache_age_threshold
发表于：MySQL
2021-04-22
# MySQL# MySQL 服务器# MySQL 服务器管理# 系统变量
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-04-22发表，共计6125字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
mysqladmin-服务器管理程序
Server SQL Modes
perror-显示错误信息
升级 MySQL
评论（没有评论）