https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

5.1.1 Configuring the Server

To see the current system variable values actually used by the server as it runs, connect to it and execute this statement:

SHOW VARIABLES;

To see some statistical and status indicators for a running server, execute this statement:

SHOW STATUS;

5.1.8 Server System Variables (服务系统变量)

The MySQL server maintains many system variables that configure its operation. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically at runtime using the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can also use system variable values in expressions.

Setting a global system variable runtime value normally requires the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege). Setting a session system runtime variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 5.1.9.1, “System Variable Privileges”

There are several ways to see the names and values of system variables:

  • To see the values that a server uses based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
  • To see the values that a server uses based only on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
  • To see the current values used by a running server, use the SHOW VARIABLES statement or the Performance Schema system variable tables. See Section 27.12.14, “Performance Schema System Variable Tables”.

This section provides a description of each system variable. For a system variable summary table, see Section 5.1.5, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 5.1.9, “Using System Variables”.

For additional system variable information, see these sections:

  • Section 5.1.9, “Using System Variables”, discusses the syntax for setting and displaying system variable values.

  • Section 5.1.9.2, “Dynamic System Variables”, lists the variables that can be set at runtime.

  • Information on tuning system variables can be found in Section 5.1.1, “Configuring the Server”.

  • Section 15.14, “InnoDB Startup Options and System Variables”, lists InnoDB system variables.

  • Section 23.4.3.9.2, “NDB Cluster System Variables”, lists system variables which are specific to NDB Cluster.

  • For information on server system variables specific to replication, see Section 17.1.6, “Replication and Binary Logging Options and Variables”.

Note

Some of the following variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. Boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case-sensitive), as well as 1 and 0. See Section 4.2.2.4, “Program Option Modifiers”.

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server adjusts a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server sets the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Some system variables take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is /var/mysql/data. If a file-valued variable is given as a relative path name, it is located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

官网下面是具体的变量介绍根据需要查看即可。

5.1.5 Server System Variable Reference

https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

深入浅出MySQL Server System Variables-LMLPHP
一个关于变量的汇总表格

5.1.9 Using System Variables

5.1.9.2 Dynamic System Variables

https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html

这一节给出了一个表格,里面有可以动态设置的系统变量值。

13.7.7.41 SHOW VARIABLES Statement

https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

SHOW VARIABLES shows the values of MySQL system variables (see Section 5.1.8, “Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

System variable information is also available from these sources:

  • Performance Schema tables. See Section 27.12.14, “Performance Schema System Variable Tables”.

  • The mysqladmin variables command. See Section 4.5.2, “mysqladmin — A MySQL Server Administration Program”.

For SHOW VARIABLES, a LIKE clause, if present, indicates which variable names to match. A WHERE clause can be given to select rows using more general conditions, as discussed in Section 26.8, “Extensions to SHOW Statements”.

  • With a GLOBAL modifier, the statement displays global system variable values. These are the values used to initialize the corresponding session variables for new connections to MySQL. If a variable has no global value, no value is displayed.

  • With a SESSION modifier, the statement displays the system variable values that are in effect for the current connection. If a variable has no session value, the global value is displayed. LOCAL is a synonym for SESSION.

  • If no modifier is present, the default is SESSION.

The scope for each system variable is listed at Section 5.1.8, “Server System Variables”.

SHOW VARIABLES is subject to a version-dependent display-width limit. For variables with very long values that are not completely displayed, use SELECT as a workaround. For example:

SELECT @@GLOBAL.innodb_data_file_path;

Most system variables can be set at server startup (read-only variables such as version_comment are exceptions). Many can be changed at runtime with the SET statement. See Section 5.1.9, “Using System Variables”, and Section 13.7.6.1, “SET Syntax for Variable Assignment”.

Partial output is shown here. The list of names and values may differ for your server. Section 5.1.8, “Server System Variables”, describes the meaning of each variable, and Section 5.1.1, “Configuring the Server”, provides information about tuning them.

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern. To obtain the row for a specific variable, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the % wildcard character in a LIKE clause:

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because _ is a wildcard that matches any single character, you should escape it as _ to match it literally. In practice, this is rarely necessary.

SHOW VARIABLES 总结

变量分为全局和会话两个级别,变量很长时使用select语句显示所有的值,可以使用like语句来模糊匹配。

SHOW VARIABLES Statement实战

SHOW [GLOBAL | SESSION] VARIABLES  [LIKE 'pattern' | WHERE expr]

在8.0.29版本下执行 SHOW VARIABLES 可以得到 653条记录。

binlog

SHOW VARIABLES like '%binlog%'

深入浅出MySQL Server System Variables-LMLPHP

innodb

SHOW VARIABLES like '%innodb%'

深入浅出MySQL Server System Variables-LMLPHP

15.14 InnoDB Startup Options and System Variables

  • System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.

  • Some variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. Boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case-sensitive), as well as 1 and 0. See Section 4.2.2.4, “Program Option Modifiers”.

  • System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

  • Many system variables can be changed at runtime (see Section 5.1.9.2, “Dynamic System Variables”).

  • For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.

  • Certain options control the locations and layout of the InnoDB data files. Section 15.8.1, “InnoDB Startup Configuration” explains how to use these options.

  • Some options, which you might not use initially, help tune InnoDB performance characteristics based on machine capacity and your database workload.

  • For more information on specifying options and system variables, see Section 4.2.2, “Specifying Program Options”.

12-06 05:27