MySQL system variable (variables)

The MySQL system variable (variables) is actually a system parameter that initializes or sets the database's use of system resources, the location of files, and so on. These system variables can be divided into global and session-level levels to modify, and some can be dynamically modified. This article mainly introduces some concepts of system variables and how to set them to view these system variables.

1. What is a system variable
System variables are actually used to control some of the behavior and manner of the database parameters. For example, when we start the database to set how much memory, using what isolation level, log file size, storage location and so on a series of things. Of course, after the database system starts, some system variables (parameters) can also be modified dynamically to adjust the database in a timely manner. This system variable is controlled by pfile or SPFile in Oracle, which is called a parameter and is a meaning.
System variable values: There are default values that can be modified at startup and after startup.
Set scope: Global and Reply level, the global level requires Super permissions, and session level only affects the session itself.
Setup method: Can be modified by configuration file and startup option before starting, set by the SET clause after startup.
Effective period: Global variables are globally visible, but only affect the client that initializes the corresponding session variable from the global variable after the change. The current session and the logged-on session are not affected.

    for setting values that involve size, you can use the suffix K, m, or G to represent kilobytes, megabytes, or gigabytes, respectively, Case insensitive.
  

-current version mysql> show variables like ' version% '; +-------------------------+------------------------------+| variable_name | Value |+-------------------------+------------------------------+| Version | 5.5.37 | | version_comment | MySQL Community Server (GPL) | | Version_compile_machine | x86_64 | | Version_compile_os | Linux |+-------------------------+------------------------------+--get help mysql> for set SetName : ' SET ' Description:Syntax:SET variable_assignment [, variable_assignment] ... variable_assignment:user_var_name = exp R | [GLOBAL | SESSION] System_var_name = Expr |  [@ @global. @ @session. | @@]system_var_name = expr--View all system variables [email protected][tempdb]> show variables; --This command will output all system variables of the current system--view sort_buffermysql> show variables like ' sort_buffer% '; +------------------+---------+| variable_name | Value |+------------------+---------+| Sort_buffer_size | 2097152 |+------------------+---------+--The session level mysql> set sort_buffer_size=1024* in the case of omitting global and session keywords   1024*4; --set to 4mmysql> show variables like ' sort_buffer% '; +------------------+---------+| variable_name | Value |+------------------+---------+| Sort_buffer_size | 4194304 |+------------------+---------+--revert to default mysql> set sort_buffer_size=default;mysql> show variables like ' sort_buffer% '; +------------------+---------+| variable_name | Value |+------------------+---------+| Sort_buffer_size | 2097152 |+------------------+---------+

--How to set the isolation level mysql> help isolationname: ' Isolation ' Description:Syntax:SET [GLOBAL | SESSION] TRANSACTION Isolation level {repeatable READ | READ COMMITTED | READ Uncommitted | SERIALIZABLE}--Below we set the global and session level variables by demonstrating the isolation level--see how the current session level is isolated [email protected][(none)]> Show Variables like '%isolation% '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| tx_isolation | Repeatable-read |+---------------+-----------------+--Modify the current session level isolation mode to read-committed[email protected][( None)]> set Session transaction isolation level Read Committed; [Email protected] [(None)]> show variables like '%isolation% '; +---------------+----------------+| variable_name | Value |+---------------+----------------+| tx_isolation | read-committed |+---------------+----------------+--another session, The logged-on user inherits the global isolation level for fred--current sessioin level to repeatable-read[email protected][(none)]> show variables like '%isolation% '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| tx_isolation | Repeatable-read |+---------------+-----------------+--Set the global isolation level to serializable[email protected][in the root session (none ]> set global transaction ISOLATION LEVEL serializable;--Note that session levels are still read-committed[email  in the root session protected][(none)]> show variables like '%isolation% '; +---------------+----------------+| variable_name | Value |+---------------+----------------+| tx_isolation | read-committed |+---------------+----------------+--in the root session I can see that the global value has changed to serializable[email protected][( None)]> show global variables like '%isolation% '; +---------------+--------------+| variable_name | Value |+---------------+--------------+| tx_isolation | SERIALIZABLE |+---------------+--------------+--in Fred also became serializable[email protected][(none)]> Show Global variables like '%isolation% '; +---------------+--------------+| Variable_name | Value |+---------------+--------------+| tx_isolation | SERIALIZABLE |+---------------+--------------+--from the demo above, regardless of how the global level is set, Does not affect the settings at the current session level--below we use a new user login to see if the global settings will affect the session [email protected]:~> MySQL- urobin--the isolation level of the new session is equal to the global isolation level [email protected][(none)]> show variables like '%isolation% '; +--------------- +--------------+| variable_name | Value |+---------------+--------------+| tx_isolation | SERIALIZABLE |+---------------+--------------+

4, how to get variable values

In addition to using the show global|session variables like ' vari_name ' method shown earlier, we can get the values of these variables by querying the specific tables in the INFORMATION_SCHEMA data. Table Global_variables[email protected][information_schema]> Select Variable_value by querying the data information_schema From Global_variables where, variable_name= ' tx_isolation '; +----------------+| Variable_value |+----------------+| SERIALIZABLE |+----------------+--author:leshami--blog:http://blog.csdn.net/leshami[email protected][ Information_schema]> SELECT @ @global. tx_isolation;+-----------------------+| @ @global. tx_isolation |+-----------------------+| SERIALIZABLE |+-----------------------+[email protected][information_schema]> SELECT @ @session. tx_isolati on;+------------------------+| @ @session. tx_isolation |+------------------------+| read-committed |+------------------------+--The following query session_variables results and query global_variables obtained the same value, the reason is further research in [email  protected][information_schema]> SELECT * from session_variables where variable_name= ' tX_isolation '; +---------------+----------------+| variable_name | Variable_value |+---------------+----------------+| tx_isolation | SERIALIZABLE |+---------------+----------------+

B. Search Settings
To retrieve the value of a global variable, use the following syntax:
Mysql> SELECT @ @global. sort_buffer_size;
Mysql> SHOW GLOBAL VARIABLES like ' sort_buffer_size ';

To retrieve the value of a session variable, use the following syntax:
mysql> SELECT @ @sort_buffer_size;
Mysql> SELECT @ @session. sort_buffer_size;
Mysql> SHOW SESSION VARIABLES like ' sort_buffer_size ';
Here, Local is also synonymous with the session.

C, Other matters needing attention
When you search for a variable with the SELECT @ @var_name (that is, do not specify global, session, or Local.),
MySQL returns the session value (if present), otherwise the global value is returned.
For show VARIABLES, if you do not specify global, session, or Local,mysql, the session value is returned.

MySQL system variable (variables)