Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of mysql about variable". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the knowledge points of mysql about variable"?

1. MySQL's my.cnf file is similar to Oracle's parameter file, which can be understood as oracle's pfile static parameter file.

2. MySQL can use the set command to dynamically modify variables, and modifying session variables only affects the current session; modifying global variables has an impact on global variables and session variables (except in special cases, such as wait_timeout variables) of all new sessions

3. The variables modified by the set command will not be written to the my.cnf file, and the variables modified by set before DB restart will become invalid. If you want to take effect permanently, write the variables to the my.cnf file.

4. Read-only variables cannot be modified using set. They can only take effect by modifying the my.cnf file and then restarting.

5. The global (GLOBAL) level variable is valid for the entire MySQL server; the session (SESSION) level variable only affects the current session. Some variables have both levels. MySQL will initialize session-level variables with global-level variables when establishing a connection, but once the connection is established, changes in global-level variables will not affect session-level variables

6. The global (GLOBAL) level variable can be understood as the system parameter of oracle, and the SESSION level variable can be understood as the session parameter of oracle.

7. Mysql does not have the function of saving all parameters in memory in create pfile from memory similar to oracle. If mysql wants to save all modified parameters but does not write to my.cnf, it is select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES to query them and save them.

View the value of the system variabl

Method 1

Mysql > show variables like'% wait_timeout%'

Mysql > show global variables like'% wait_timeout%'

Mysql > show session variables like'% wait_timeout%'

Note: show variables gives priority to the value of session-level variables. If this value does not exist, it displays the value of global-level variables. Of course, you can also add the SESSION keyword to distinguish.

Method 2

Mysql > set global show_compatibility_56=on

Mysql > select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES where variable_name like'% wait_timeout%'

Mysql > select * from INFORMATION_SCHEMA.SESSION_VARIABLES where variable_name like'% wait_timeout%'

Modify the value of the system variable

Mysql > set wait_timeout = xxx

Mysql > set global wait_timeout = yyy

Read-only system scalar, which cannot be modified using set

Mysql > show variables like 'log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > set log_bin=off

ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

Only global variables, no session variables

When the global variable is modified, the session variables and global variables of other newly opened sessions are the modified values.

Session 1

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

Mysql > set server_id=2

ERROR 1229 (HY000): Variable 'server_id' is a GLOBAL variable and should be set with SET GLOBAL

Mysql > set GLOBAL server_id=2

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

Mysql > show global variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

Open another session

When the global variable is modified, the session variables and global variables of other newly opened sessions are the modified values.

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

Mysql > show global variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 2 | |

+-+ +

There are global variables and session variables

When a session variable is modified, it does not affect its own global variables, nor does it affect the session variables and global variables of other newly opened sessions

When the global variable is modified, the session variables and global variables of other newly opened sessions are the modified values.

Session 1

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Mysql > set read_buffer_size=212992

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 212992 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Open another session 2

When a session variable is modified, it does not affect its own global variables, nor does it affect the session variables and global variables of other newly opened sessions

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Session 1 continues as follows

Mysql > set global read_buffer_size=16384

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 212992 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 16384 | |

+-+ +

Open another session 3

When the global variable is modified, the session variables and global variables of other newly opened sessions are the modified values.

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 16384 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 16384 | |

+-+ +

After restart

After restart, all set modified variable values are gone and go back to the original value.

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

Mysql > show global variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 1 | |

+-+ +

Mysql > show variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Mysql > show global variables like 'read_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | read_buffer_size | 131072 | |

+-+ +

Thank you for your reading, these are the contents of "mysql about variable knowledge points". After the study of this article, I believe you have a deeper understanding of what mysql knowledge points about variable have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.