MySQL system variable (systemvariables) _ MySQL

MySQL system variables are actually some system parameters used to initialize or set the occupation of system resources by the database, and the storage location of files. These system variables can be modified at the global and session level, and some can also be modified dynamically. This MySQL system variable is actually a number of system parameters used to initialize or set the database's occupation of system resources, file storage location, and so on. 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?

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

-- 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 settings 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 the variable Value

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: http://blog.csdn.net/leshamiroot@localhost [Information_schema]> 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

B. search settings

C. Other precautions