MySQL maintains two kinds of system variables. Global and session variables:

Global variables affect the overall operation of the server. While 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 its effective area.

Global variables are initialized when MySQL server starts, while session variables are maintained only for each client connect session. Session variables are initialized at connect time using the current values of the corresponding global variables. Both types of variables can be changed.

Set Global variables

When the server starts, it initializes all global variables to their default values.

1. Set in my.cnf configuration file

In /etc/my.cnf, change variables like:


2. Set when starting server

mysqld --query_cache_size=16M --max_allowed_packet=1G

The value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243; that is, units of kilobytes, megabytes, or gigabytes, respectively.

3. Use set command

Many system variables are dynamic variables and can be changed while the server runs by using the SET statement.

SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@sort_buffer_size=1000000;

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).

Set Session variables

Session variables are initialized at connect time using the current values of the global variables.
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.

How to change it ?

1. Use set command

To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. 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 and @@local. are synonyms for SESSION and @@session..
If no modifier is present, SET changes the session variable.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;

2. specify the variable when session connects

shell> mysql --max_allowed_packet=16M

Some system variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not.

Display system variables

To display system variable names and values, use the SHOW VARIABLES statement, or Check the list of MySQL system variables

Comments powered by CComment