(mysql.info.gz) System Variables
(mysql.info.gz) Language Structure
9.4 System Variables
* Structured System Variables Structured System Variables
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 initialized from the global variable only for clients
that connect after the change. It 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).
Global or session variables may be set or retrieved using several syntax
forms. The following examples use `sort_buffer_size' as a sample
To set the value of a `GLOBAL' variable, use one of the following
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
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. `SET OPTION' SET OPTION.
To retrieve the value of a `GLOBAL' variable, use one of the following
mysql> SELECT @@global.sort_buffer_size;
mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';
To retrieve the value of a `SESSION' variable, use one of the following
mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
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 `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.
Further information about system startup options and system variables
can be found in Server options and Server system
variables. A list of the variables that can be set at runtime is
given in Dynamic System Variables.
(mysql.info.gz) Language Structure
automatically generated byinfo2html