Starting from MySQL 4.0.3, we provide better access to a lot of system and connection variables. Many variables can be changed dynamically while the server is running. This allows you to modify server operation without having to stop and restart it.
The mysqld server maintains two kinds of variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections.
When the server starts, it initializes all global variables to their default values. These defaults may be changed by options specified in option files or on the command line. After the server starts, those global variables that are dynamic can be changed by connecting to the server and issuing a SET GLOBAL var_name statement. To change a global variable, you must have the SUPER privilege.
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 those session variables that are dynamic, the client can change them by issuing a SET SESSION var_name statement. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.
A change to a global variable is visible to any client that accesses that global variable. However, it affects the corresponding session variable that is intialized from the global variable only for clients that connect after the change. It does not affect the session variable for any client that is already connected (not even that of the client that issues the SET GLOBAL statement).
Global or session variables may be set or retrieved using several syntax forms. The following examples use sort_buffer_size as a sample variable name.
To set the value of a GLOBAL variable, use one of the following syntaxes:
mysql> SET GLOBAL sort_buffer_size=value; mysql> SET @@global.sort_buffer_size=value;
To set the value of a SESSION variable, use one of the following syntaxes:
mysql> SET SESSION sort_buffer_size=value; mysql> SET @@session.sort_buffer_size=value; mysql> SET sort_buffer_size=value;
LOCAL is a synonym for SESSION.
If you don't specify GLOBAL, SESSION, or LOCAL when setting a variable, SESSION is the default.
To retrieve the value of a GLOBAL variable, use one of the following statements:
mysql> SELECT @@global.sort_buffer_size; mysql> SHOW GLOBAL VARIABLES like
To retrieve the value of a SESSION variable, use one of the following statements:
mysql> SELECT @@sort_buffer_size; mysql> SELECT @@session.sort_buffer_size; mysql> SHOW SESSION VARIABLES like
Here, too, LOCAL is a synonym for SESSION.
When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local., MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.
For SHOW VARIABLES, if you do not specify GLOBAL, SESSION, or LOCAL, MySQL returns the SESSION value.
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 remove a SESSION variable with the same name as a SESSION 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 SESSION variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.
22.214.171.124.1 Structured System Variables
Structured system variables are supported beginning with MySQL 4.1.1. A structured variable differs from a regular system variable in two respects:
Currently, MySQL supports one structured variable type. It specifies parameters that govern the operation of key caches. A key cache structured variable has these components:
The purpose of this section is to describe the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 6.4.6, "The MyISAM Key Cache."
To refer to a component of a structured variable instance, you can use a compound name in instance_name.component_name format. Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus, default.key_buffer_size and key_buffer_size both refer to the same system variable.
The naming rules for structured variable instances and components are as follows:
At the moment, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.
With one exception, it is allowable to refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:
shell> mysqld --hot_cache.key_buffer_size=64K
In an option file, do this:
If you start the server with such an option, it creates a key cache named hot_cache with a size of 64KB in addition to the default key cache that has a default size of 8MB.
Suppose that you start the server as follows:
shell> mysqld --key_buffer_size=256K \
In this case, the server sets the size of the default key cache to 256KB. (You could also have written --default.key_buffer_size=256K.) In addition, the server creates a second key cache named extra_cache that has a size of 128KB, with the size of block buffers for caching table index blocks set to 2048 bytes.
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
shell> mysqld --key_buffer_size=6M \
Structured variable values may be set and retrieved at runtime as well. For example, to set a key cache named hot_cache to a size of 10MB, use either of these statements:
mysql> SET GLOBAL hot_cache.key_buffer_size =
To retrieve the cache size, do this:
mysql> SELECT @@global.hot_cache.key_buffer_size;
However, the following statement does not work. The variable is not interpreted as a compound name, but as a simple string for a LIKE pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.
blog comments powered by Disqus