The range of Global, Session, and Both (Global & amp; Session) in MySQL

In MySQL, the range and setting of Dynamic and NotDynamic variables in the range of Global, Session, and Both (GlobalSession) are as follows: 1. GlobalDynamicexample: slow_query_log? Show the variable: Current set to OFF? Set the variables of the GlobalDynamic type in a session: You can see that for glo

In MySQL, the range and setting of Dynamic and Not Dynamic variables in the range of Global, Session, and Both (Global Session) are as follows: 1. Global Dynamic example: slow_query_log? Show the variable: Current set to OFF? Set the Global Dynamic type variable in a session: You can see that for glo

In MySQL, the range of Dynamic and Not Dynamic variables in the Global, Session, and Both (Global & Session) scopes and their setting methods

1. Global & Dynamic

Example: slow_query_log

? Show the value of this variable:

Set to OFF

? Set the Global & Dynamic type variable in a session:

We can see that the global type variables must be set with the global keyword.

? Review the value of this variable in the same session:

It is found that the variable value has changed.

? Use another session to view the value of this variable:

Ü this session already exists when changing the variable value

Ü this session does not exist when the variable value is changed. It is a newly created session.

It is found that the changed variable value can be seen in other sessions that already exist when the variable value is changed or in newly created sessions.

? Restart the mysql server and check the variable value again:

We can see that it is restored to the default situation. (Also in other sessions)

Conclusion:

L for variables of the Global & Dynamic type, you must use the set global statement to set the variable value. After the variable is set, you can immediately see the set value in this session and other sessions (existing and connected. However, the value set after the server is restarted becomes invalid and is restored to the default value. (The reason is obvious because the set value is not written into the configuration file .)

L The Global variables show, show session, and show global display results are consistent because the Global variables are visible to all sessions, setting the value of this variable through setglobal is equivalent to setting the value of this variable in each session immediately.

2. Global & Not Dynamic

Example: bind_address

? Show the value of this variable:

? Set the Global & Not Dynamic type variable in a session:

It is found that Not Dynamic type variables cannot be set in this way.

Conclusion:

L for variables of the Not Dynamic type, you cannot directly set the value using commands.

L because it is of the Global type, the results displayed by executing show, show session, and show global in each session are consistent.

3. Session & Dynamic

Example: gtid_next

? Show the value of this variable:

For Session-type variables, the value is not obtained through show global. You must use the show and show session commands to view the value.

? Set the variables of the session & Dynamic type in a Session:

? Review the value of this variable in the same session:

You can see that the variable value has changed to the set value.

? Use another session to view the value of this variable:

Ü this session already exists before the variable value is changed

As you can see, in other existing sessions, the variable value is still the value before the setting.

Ü this session does not exist before the variable value is changed. It is the newly connected session after the variable value is changed.

It can be seen that the value of this variable is still set in other existing sessions or newly created sessions.

? Restart the mysql server and check the variable value again:

As you can see, the variable value is restored to the default value before the setting.

Conclusion:

L for the Session and Dynamic type variables, you can directly set the value through the set and set session commands, but the set variable value is only valid for the session used during the setting, other sessions (existing and created later) are invisible. The variable value set after the mysql server is restarted fails.

L for Session-type variables, the result cannot be obtained through showglobal. You can use show and show sessions to obtain the variable values for each session that initiates a command.

4. Session & Not Dynamic

Example: proxy_user

? Show the value of this variable:

? Set the variables of the session & Not Dynamic type in a Session:

As you can see, variable values of the Not Dynamic type cannot be set using commands.

Conclusion:

L for variables of the not dynamic type, you cannot directly set the value using commands.

L for Session-type variables, the result cannot be obtained through showglobal. You can use show and show sessions to obtain the variable values for each session that initiates a command.

5. Both & Dynamic

Example: auto_increment_increment

? Show the value of this variable:

We can see that the values of the current session range and global range are both 1.

?Set the Both & Dynamic type variables in a session using the set session Statement:

The global keyword is not used here. Therefore, the set session Statement is used by default to set the variable value.

Ü re-view the value of the variable in the same session:

The global keyword is not used here. Therefore, the showsession statement is used by default to display the variable value. The variable value in this session has changed to the set value.

The keyword global is used to display the value of the variable. The value of the global variable in this session is not changed to the value set through the setsession statement.

Ü view the value of the variable through another session (the session already exists when setting the variable value ):

In another existing session, the global variable value is still the original value. The variable value set by the set session Statement in the previous session is invisible in the session.

Ü view the value of the variable through another session (the session does not exist when the variable value is set, and it is a newly connected session ):

After another variable value is set, the global variable value in the new session is still the original value. The variable value set by the set session Statement in the previous session is invisible in the session.

Ü restart the mysql server and re-view the variable value:

After the mysql server is restarted, the variable value is restored to the default value.

?Set the Both & Dynamic type variables in a session using the set global statement:

Ü re-view the value of the variable in the same session:

The global keyword is not used here, so the showsession variables statement is used by default to display the variable value. In this session, the variable value in the session range is not set.

The keyword global is used to display the value of the variable. In this session, the value of the global variable has changed to the value set by the setglobal statement.

Ü view the value of the variable through another session (the session already exists when setting the variable value ):

The variable value set by the set global statement in the previous session is invisible to the existing session. In this existing session, the global variable has changed to the set value.

Ü view the value of the variable through another session (the session does not exist when the variable value is set, and it is a newly connected session ):

The global variable in the newly created session after the variable value is set is the set value. The variable value set by the set global statement in the previous session is visible to the newly created session.

Ü restart the mysql server and re-view the variable value:

After the mysql server is restarted, the variable value is restored to the default value.

Conclusion:

L if the variable is Both (global/session), set <=> setsession, show <=> show session. To set and display variables in the global range, use setglobal and show global explicitly.

L for Both & Dynamic variables, if you use the set command (by default, the set session command is executed) to set a new value (the variable value in the session range is set at this time ). Then, you can run the show command (show session is executed by default) in the set session to view the variable value and get the set value. The new variable value is invisible to other sessions that already exist or are created later. In addition, because the set session is used to set the variable value, there is no impact on the global range variables, no matter in which sessions, the variable values displayed through the show global statement are the values before the set session command is executed.

L if you use the set global command to set the value of a variable (in this case, the value of a variable in the global range is set ). In the set session, run the show command (the show session is executed by default) to view the variable value. The result is still the value before the set (that is, the value of the session range ). In other previous sessions, the show session command is used to obtain the pre-set value. You can run the show session command in the newly created session to get the set value (because the new session reads the changed value ). Whether it is a modified session or another session that already exists during the change, or a newly created session, all the values in the set global range are obtained by executing the show global command.

L no matter whether it is a global variable or a session range variable, after the server is restarted, the set value will expire and be restored to the default value.

6. Both & Not Dynamic

Example:Gtid_executed

? Show the value of this variable:

? Set the Both & Not Dynamic type variable in a session:

As you can see, variables of the Not Dynamic type, whether in the global range or in the session range, cannot be set through commands.

Conclusion:Variables of the Not Dynamic type cannot be directly set using the set, set session, and set global commands.

To sum up:

? Variables of the Not Dynamic type cannot be set through the set, set session, and set global commands. Only Dynamic variables can be set in these ways.

? For Dynamic variables

Ü if the variable is in a single Global range, you must use the setglobal command to set the variable value. You cannot use the set (set <=> set session) command to set the value, the setting is effective immediately for all sessions. The variables show, show session, and show Global in the global range are equivalent.

Ü if the variable is within a single Session range, you must use the set (set <=> setsession) command to set the variable value. You cannot use the set global command to set the variable value, the setting is only valid for the set session. The show (show <=> show Session) command must be used to display the value of a variable in the session range. You cannot use showglobal to display the variables in the session range. If you use show global, the display is empty.

Ü if the variable is in the Both range, the variable value set by the set (here set <=> setsession) command is only valid for the set session, use the show (show <=> show session) command to get the modified value in the session, in this session, showglobal still obtains the Global range value through set. If the set global command is used to set the value of the variable, the show (show <=> show session) command is used in the set session and other existing sessions to display the variable value before modification, in the later created session, you can run the show (show <=> show session) command to get the modified value. However, whether it is a new or existing session, if you use show global, you can use the set global command to set the value.

? Whether it is a global variable or a session variable, after the server is restarted, the set value becomes invalid and is restored to the default value.