The MySQL server maintains many system variables that indicate how it is configured. Section
5.1.4, "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 Section 4.2.3, "Specifying 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
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
G (either uppercase or lowercase) to indicate a multiplier of 1024,
10242 or 10243; that is, units of kilobytes, megabytes, or gigabytes, respectively. Thus,
the following command starts the server with a query cache size of 16 megabytes and a maximum packet size of one
mysqld --query_cache_size=16M --max_allowed_packet=1G
Within an option file, those variables are set like this:
The lettercase of suffix letters does not matter;
16m are equivalent, as are
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
at server startup. For example, to prevent the value of
query_cache_size from being increased to more than 32MB at runtime, use the
Many system variables are dynamic and can be changed while the server runs by using the
SET statement. For a list, see Section
18.104.22.168, "Dynamic System Variables". To change a system variable with
SET, refer to it as
optionally preceded by a modifier:
To indicate explicitly that a variable is a global variable, precede its name by
SUPER privilege is required to set global variables.
To indicate explicitly that a variable is a session variable, precede its name by
Setting a session variable requires no special privilege, but a client can change only its own session
variables, not those of any other client.
If no modifier is present,
SET changes the session variable.
SET statement can contain multiple variable assignments, separated by commas. If
you set several system variables, the most recent
modifier in the statement is used for following variables that have no modifier specified.
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;
@@ 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
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.) 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
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 session 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
(that is, when you do not specify
@@session.), MySQL returns the session value if it exists and the global value
otherwise. (This differs from
SET @@, which always refers to the session value.)
Some variables displayed by
SHOW VARIABLES may not be available using
SELECT @@ syntax; an
Unknown system variable occurs. As a workaround in such cases, you can use
SHOW VARIABLES LIKE '.
Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set
the value with
SET at runtime. On the other hand, with
SET you can assign a variable's value using an expression, which is not true
when you set a variable at server startup. For example, the first of the following lines is legal at server
startup, but the second is not:
Conversely, the second of the following lines is legal at runtime, but the first is not:
SET GLOBAL max_allowed_packet=16M;mysql>
SET GLOBAL max_allowed_packet=16*1024*1024;
Some system variables can be enabled with the
statement by setting them to
1, or disabled
by setting them to
0. However, to set such
a variable on the command line or in an option file, you must set it to
0; setting it to
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:
SHOW VARIABLES;+---------------------------------+-----------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------+| auto_increment_increment | 1 || auto_increment_offset | 1 || automatic_sp_privileges | ON || back_log | 50 || basedir | /home/mysql/ || 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 | /home/mysql/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_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.1.6-alpha-log || version_comment | Source distribution || version_compile_machine | i686 || version_compile_os | suse-linux || wait_timeout | 28800 |+---------------------------------+-----------------------------------+
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
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, if you specify neither
SESSION, MySQL returns
The reason for requiring the
GLOBAL keyword when setting
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
variable, a client with the
SUPER privilege might accidentally change the
GLOBAL variable rather than just the
variable for its own connection. If we add a
SESSION variable with the same name as
GLOBAL variable, a client that intends to change the
GLOBAL variable might find only its own