(mysql.info.gz) System Variables

Info Catalog

(mysql.info.gz) Variables

(mysql.info.gz) Language Structure

(mysql.info.gz) Comments

 
 9.4 System Variables
 ====================
 

Menu

 
* 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
 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.   `SET OPTION' SET OPTION.
 
 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 'sort_buffer_size';
 
 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 '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.
 

Info Catalog

(mysql.info.gz) Variables

(mysql.info.gz) Language Structure

(mysql.info.gz) Comments