MySQL system variables (system variables), mysqlvariables

MySQL system variables (system variables), mysqlvariables

MySQL system variables are actually some system parameters used to initialize or set the database's occupation of system resources and file storage location. These System variables can be modified at the global and session level, and some can also be modified dynamically. This article describes some concepts of system variables and how to set and view these system variables.

 

1. What are system variables?
System variables are actually used to control some database behavior and method parameters. For example, when we start the database, we set the memory size, isolation level, log file size, storage location, and so on. Of course, after the database system is started, some system variables (parameters) can also be dynamically modified to timely adjust the database. In Oracle, this system variable is controlled by pfile or spfile. It is called a parameter.
System variable values: All values have default values, which can be modified at and after startup.
Set range: Global and session level. The global level requires the super permission. The session level only affects the session itself.
Setting Method: you can modify the settings by using the configuration file and startup options, and SET the settings by using the SET clause after startup.
Effective Period: The global variables are globally visible, but only the clients that connect after the change initialize the corresponding session variables from the global variables. The current session and logged-on session are not affected.

For size-related settings, you can use the suffix K, M, or G to indicate kilobytes, megabytes, or gigabytes, which are case insensitive.

2. set the usage of System Variables

-- The current mysql version> show variables like 'version % '; + ------------------------- + response + | Variable_name | Value | + ----------------------- + response + | version | 5.5.37 | version_comment | MySQL Community Server (GPL) | version_compile_machine | x86_64 | version_compile_ OS | Linux | + Platform + -- obtain help for the set mysql> help setName: 'set' Description: Syntax: SET variable_assignment [, variable_assignment]... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@ global. | @ session. | @] system_var_name = expr -- View All system variables root @ localhost [tempdb]> show variables; -- this command outputs all system variables of the current system -- View sort_buffermysql> show variables like 'sort _ buffer % '; + bytes + --------- + | Variable_name | Value | + ------------------ + --------- + | sort_buffer_size | 2097152 | + ------------------ + --------- + -- session-level mysql if the global and session keywords are omitted> set sort_buffer_size = 1024*1024*4; -- set it to 4 Mmysql> show variables like 'sort _ buffer % '; + Recovery + --------- + | Variable_name | Value | + ------------------ + --------- + | sort_buffer_size | 4194304 | + ------------------ + --------- + -- restore to the default Value mysql> set sort_buffer_size = default; mysql> show variables like 'sort _ buffer % '; + ------------------ + --------- + | Variable_name | Value | + ------------------ + --------- + | sort_buffer_size | 2097152 | + ------------------ + ---------

3. Global and session level setting example

-- 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} -- we will demonstrate how to set global and session-LEVEL variables at the isolation level -- view the current session LEVEL root @ localhost [(none)]> show variables like '% isolation %'; + --------------- + | Variable_name | Value | + --------------- + ----------------- + | tx_iso Lation | REPEATABLE-READ | + --------------- + -- modify the current session-level isolation Mode to READ-COMMITTEDroot @ localhost [(none)]> set session transaction isolation level read committed; root @ localhost [(none)]> show variables like '% isolation % '; + --------------- + -------------- + | Variable_name | Value | + --------------- + ---------------- + | tx_isolation | READ-COMMITTED | + --------------- + ---------------- + -- another Session. The logon user is fred. The current sessioin level inherits the global isolation level REPEATABLE-READfred @ localhost [(none)]> show variables like '% isolation % '; + --------------- + percent + | Variable_name | Value | + --------------- + percent + | tx_isolation | REPEATABLE-READ | + --------------- + percent + -- set the global isolation level to serializableroot @ localhost [( none)]> set global transaction isolation level serializable; -- Note: In the root session The session level is still READ-COMMITTEDroot @ localhost [(none)]> show variables like '% isolation % '; + region + ---------------- + | Variable_name | Value | + --------------- + ---------------- + | tx_isolation | READ-COMMITTED | + region + -------------- + -- in the root session, I can see that the global Value has changed SERIALIZABLEroot @ localhost [(none)]> show global variables like '% isolation %'; + --------------- + -------------- + | Variable_name | Value | + --------------- + -------------- + | Tx_isolation | SERIALIZABLE | + --------------- + -------------- + -- in fred, the global result is also SERIALIZABLEfred @ localhost [(none)]> show global variables like '% isolation % '; + parameters + -------------- + | Variable_name | Value | + --------------- + -------------- + | tx_isolation | SERIALIZABLE | + --------------- + ------------ + -- in the preceding demonstration, no matter how the global level is set, the current session-level settings are not affected. Next we use a new user to log on. To see if global settings affect the new session robin @ SZDB: ~> Mysql-urobin -- The following query queries whether the isolation level of a new session is equal to the global isolation level. robin @ localhost [(none)]> show variables like '% isolation % '; + --------------- + ------------ + | Variable_name | Value | + --------------- + -------------- + | tx_isolation | SERIALIZABLE | + --------------- + -------------- +

4. How to obtain variable values

In addition to the show global | session variables like 'vari _ name' method described above, we can query tables in information_schema data to obtain the values of these variables. Query the globalion_schema table global_variablesroot @ localhost [information_schema]> select variable_value from global_variables where-> variable_name = 'tx _ isolation '; + ---------------- + | variable_value | + ---------------- + | SERIALIZABLE | + ---------------- + -- Author: Leshami -- Blog: Blog> select @ global. tx_isolation; + --------------------- + | @ global. tx_isolation | + --------------------- + | SERIALIZABLE | + ----------------------- + root @ localhost [information_schema]> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ---------------------- + | READ-COMMITTED | + ------------------------ + -- the query result of session_variables is the same as the query value of global_variables, root @ localhost [information_schema]> select * from session_variables where variable_name = 'tx _ isolation '; + --------------- + -------------- + | VARIABLE_NAME | VARIABLE_VALUE | + ----------------- + ---------------- + | TX_ISOLATION | SERIALIZABLE | + --------------- + ---------------- +

5. Summary
A. Setting Method
To set the value of a GLOBAL variable, use the following syntax:
Mysql> set global sort_buffer_size = value;
Mysql> SET @ global. sort_buffer_size = value;

To set the value of a SESSION variable, use the following syntax:
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 a SESSION.
If GLOBAL, SESSION, or LOCAL is not specified when setting variables, the SESSION is used by default.

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 a synonym for SESSION.

C. Other Precautions
When you use SELECT @ var_name to search for a variable (that is, global., session. or local. is not specified .),
MySQL returns the SESSION value (if any); otherwise, the GLOBAL value is returned.
For show variables, if GLOBAL, SESSION, or LOCAL is not specified, MySQL returns the SESSION value.

 

Mysql show variables the variables variable is included in the file

Some of them are read from my. cnf... but they are all in the performance_schema database... for example, many of the GLOBAL_VARIABLES tables are varbles information ..

How does mySQL configure environment variables?

This is irrelevant to environment variables. Check whether the service name is correct, for example, mysqld.