(mysql.info) using-system-variables
Info Catalog
(mysql.info) server-system-variables
(mysql.info) mysqld
(mysql.info) server-status-variables
5.2.3 Using System Variables
----------------------------
Menu
* structured-system-variables Structured System Variables
* dynamic-system-variables Dynamic System Variables
The `mysql' server maintains many system variables that indicate how it
is configured. server-system-variables, describes the meaning
of these variables. 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
while the server is running by means of the `SET' statement, which
enables you to modify operation of the server without having to stop
and restart it. You can refer to system variable values in expressions.
The server maintains two kinds of system variables. Global variables
affect the overall operation of the server. Session variables affect
its operation for individual client connections. A given system
variable can have both a global and a session value. Global and session
system variables are related as follows:
* When the server starts, it initializes all global variables to
their default values. These defaults can be changed by options
specified on the command line or in an option file. (See
program-options.)
* The server also maintains a set of session variables for each
client that connects. The client's session variables are
initialized at connect time using the current values of the
corresponding global variables. For example, the client's SQL mode
is controlled by the session `sql_mode' value, which is initialized
when the client connects to the value of the global `sql_mode'
value.
System variable values can be set globally at server startup by using
options on the command line or in an option file. When you use a
startup option to set a variable that takes a numeric value, the value
can be given with a suffix of `K', `M', or `G' (either uppercase or
lowercase) to indicate a multiplier of 1024, 10242 or 10243; that is,
units of kilobytes, megabytes, or gigabygtes, respectively. Thus, the
following command starts the server with a query cache size of 16
megabytes and a maximum packet size of one gigabyte:
mysqld --query_cache_size=16M --max_allowed_packet=1G
Within an option file, those variables are set like this:
[mysqld]
query_cache_size=16M
max_allowed_packet=1G
The lettercase of suffix letters does not matter; `16M' and `16m' are
equivalent, as are `1G' and `1g'.
If you want to restrict the maximum value to which a system variable
can be set at runtime with the `SET' statement, you can specify this
maximum by using an option of the form -maximum-VAR_NAME at server
startup. For example, to prevent the value of `query_cache_size' from
being increased to more than 32MB at runtime, use the option
-maximum-query_cache_size=32M.
Many system variables are dynamic and can be changed while the server
runs by using the `SET' statement. For a list, see
dynamic-system-variables. To change a system variable with `SET',
refer to it as VAR_NAME, optionally preceded by a modifier:
* To indicate explicitly that a variable is a global variable,
precede its name by `GLOBAL' or `@@global.'. The `SUPER' privilege
is required to set global variables.
* To indicate explicitly that a variable is a session variable,
precede its name by `SESSION', `@@session.', or `@@'. Setting a
session variable requires no special privilege, but a client can
change only its own session variables, not those of any other
client.
* `LOCAL' and `@@local.' are synonyms for `SESSION' and `@@session.'.
* If no modifier is present, `SET' changes the session variable.
A `SET' statement can contain multiple variable assignments, separated
by commas. If you set several system variables, the most recent
`GLOBAL' or `SESSION' modifier in the statement is used for following
variables that have no modifier specified.
Examples:
SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
When you assign a value to a system variable with `SET', you cannot use
suffix letters in the value (as can be done with startup options).
However, the value can take the form of an expression:
SET sort_buffer_size = 10 * 1024 * 1024;
The `@@VAR_NAME' syntax for system variables is supported for
compatibility with some other database systems.
If you change a session system variable, the value remains in effect
until your session ends or until you change the variable to a different
value. The change is not visible to other clients.
If you change a global system variable, the value is remembered and
used for new connections until the server restarts. (To make a global
system variable setting permanent, you should set it in an option
file.) The change is visible to any client that accesses that global
variable. However, the change affects the corresponding session
variable only for clients that connect after the change. The global
variable change does not affect the session variable for any client
that is currently connected (not even that of the client that issues
the `SET GLOBAL' statement).
To prevent incorrect usage, MySQL produces an error if you use `SET
GLOBAL' with a variable that can only be used with `SET SESSION' or if
you do not specify `GLOBAL' (or `@@global.') when setting a global
variable.
To set a `SESSION' variable to the `GLOBAL' value or a `GLOBAL' value
to the compiled-in MySQL default value, use the `DEFAULT' keyword. For
example, the following two statements are identical in setting the
session value of `max_join_size' to the global value:
SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to `DEFAULT'. In such cases, use of
`DEFAULT' results in an error.
You can refer to the values of specific global or sesson system
variables in expressions by using one of the `@@'-modifiers. For
example, you can retrieve values in a `SELECT' statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as `@@VAR_NAME'
(that is, when you do not specify `@@global.' or `@@session.'), MySQL
returns the session value if it exists and the global value otherwise.
(This differs from `SET @@VAR_NAME = VALUE', which always refers to the
session value.)
_Note_: Some system variables can be enabled with the `SET' statement
by setting them to `ON' or `1', or disabled by setting them to `OFF' or
`0'. However, to set such a variable on the command line or in an
option file, you must set it to `1' or `0'; setting it to `ON' or `OFF'
will not work. For example, on the command line, -delay_key_write=1
works but -delay_key_write=ON does not.
To display system variable names and values, use the `SHOW VARIABLES'
statement.
mysql> SHOW VARIABLES;
+--------+--------------------------------------------------------------+
| Variable_name | Value |
+--------+--------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
...
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
...
| version | 5.0.19-Max |
| version_comment | MySQL Community Edition - Max (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+--------+--------------------------------------------------------------+
With a `LIKE' clause, the statement displays only those variables that
match the pattern. To obtain a specific variable name, 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.
For `SHOW VARIABLES', if you specify neither `GLOBAL' nor `SESSION',
MySQL returns `SESSION' values.
The reason for requiring the `GLOBAL' keyword when setting
`GLOBAL'-only variables but not when retrieving them is to prevent
problems in the future. If we were to remove a `SESSION' variable that
has the same name as a `GLOBAL' variable, a client with the `SUPER'
privilege might accidentally change the `GLOBAL' variable rather than
just the `SESSION' variable for its own connection. If we add a
`SESSION' variable with the same name as a `GLOBAL' variable, a client
that intends to change the `GLOBAL' variable might find only its own
`SESSION' variable changed.
Info Catalog
(mysql.info) server-system-variables
(mysql.info) mysqld
(mysql.info) server-status-variables
automatically generated byinfo2html