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

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注