DEVELOPER ZONE ::
Login / Register
The mysql server maintains many system variables that indicate how it is configured. Section 5.2.3, “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.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 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 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
--maximum- 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
Many system variables are dynamic and can be changed while the server runs by using the
SET statement. For a list, see Section 126.96.36.199, “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
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.
@@local. are synonyms for
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
SESSION 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;
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;
@@ 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.) 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 @@[email protected]@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
@@ (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.)
Note: Some system variables can be enabled with the
SET 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 | / | | 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 | | version_comment | MySQL Community Edition - (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | | wait_timeout | 28800 | +--------+--------------------------------------------------------------+
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
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
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.