MySQL in global, session, and both (Global & session) range

1. Global & Dynamic

Example:slow_query_log

? Display the value of the variable:

Currently set to OFF

? Set the variables of the global &dynamic type in a session:

As you can see, a variable of type global must have its value set by the Global keyword.

 ? Re-view the value of the variable in the same session:

The value of the variable has been found to have changed.

 ? To see the value of the variable by another session:

ü The session already exists when changing the value of the variable

ü The session does not exist when changing the value of the variable, for the newly created session

It is found that the changed variable values can be seen in other sessions that already exist when changing the value of the variable, or later in the new session.

 ? Re-view the value of the variable after restarting the MySQL server:

You can see and revert to the default. (This is true in other sessions, of course)

Conclusion:

For variables of the global& dynamic type, set the variable value through the SET global statement. After the variable is set, the set value is immediately visible in the session and in other sessions (existing and connected). However, the value set after restarting the server is invalidated and reverts to the default value. (The reason is obvious, because the value of the setting is not written into the configuration file.) ）

For variables of the global type show, show session, show global display the result is consistent, because the global variable is visible to all sessions, Setting the value of the variable by Setglobal is equivalent to setting the value of the variable in each session immediately.

2. Global & not Dynamic

Example:bind_address

? Display the value of the variable:

? Set the variable of the global& not dynamic type in a session:

It is found that variable values cannot be set in this way for variables of type not dynamic.

Conclusion:

L A variable of type not dynamic cannot be set directly by command.

Because it is of global type, the results shown in show, show session, and show global are consistent in each session.

3. Session & Dynamic

Example:gtid_next

? Display the value of the variable:

For a variable of session type through show global is not get value, need to show, show session command to view.

? Set the variable of the Session&dynamic type in a session:

? Re-view the value of the variable in the same session:

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

? To see the value of the variable by another session:

ü The session already exists before the value of the variable is changed

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

ü The session does not exist before the value of the variable is changed, the session of the new connection after changing the value of the variable

You can see that the value of the variable is still the value before the setting, whether it is in another existing session or later in a new session.

? Re-view the value of the variable after restarting the MySQL server:

As you can see, the value of the variable reverts to the default value before it is set.

Conclusion:

For variables of type session&dynamic, the value can be set directly through set, set session command, but the value of the set variable is only valid for the session used in the setting, and is not visible in other sessions (existing and later). The variable values set after the MySQL server restart are invalidated.

For variables of session type, the result cannot be obtained by ShowGlobal. The value of the variable in each session of the initiating command can be obtained through show, show session.

4. Session & Not Dynamic

Example:proxy_user

? Display the value of the variable:

? Set the variable of the Session&not dynamic type in a session:

As you can see, a variable of the not dynamic type cannot be set to the value of a variable in the form of a command.

Conclusion:

L A variable of type not dynamic cannot be set directly by command.

For variables of session type, the result cannot be obtained by ShowGlobal. The value of the variable in each session of the initiating command can be obtained through show, show session.

5. Both & Dynamic

Example:auto_increment_increment

? Display the value of the variable:

You can see that the current session range and global range have variable values of 1.

? set the variable of the both& dynamic type in a session by the SET session statement:

The keyword Global is not used, so the value of the variable is set by default using the SET session statement.

ü Re-view the value of the variable in the same session:

The keyword Global is not used, so the value of the variable is displayed by default using the ShowSession statement. In this session, the value of the variable has changed to the set value.

This uses the keyword global to display the value of the variable. In this session, the value of the global range variable is not changed to the value set by the Setsession statement.  

U view the value of the variable by another session (the session already exists when the value of the variable is set):

In another existing session, the variable value of the global type is still the original value. The value of the variable set by the SET session statement in the last session is not visible in this session.

You can see the value of the variable by another session (the session does not exist when the value is set, it is the session after the new connection):

The variable value of the global type in the new session after setting the value of the variable is still the original value. The value of the variable set by the SET session statement in the last session is not visible in this session.                

ü Re-view the value of the variable after restarting the MySQL server:

After restarting the MySQL server, the value of the variable reverts to the default value.

? set the variable of the both & dynamic type in a session by the SET GLOBAL statement:

ü Re-view the value of the variable in the same session:

The keyword Global is not used here, so the value of the variable is displayed by default using the ShowSession variables statement. The variable value of the session range in this session is still not set before the value.

This uses the keyword global to display the value of the variable. In this session, the value of the global scope variable has changed to the value set by the Setglobal statement.

U view the value of the variable by another session (the session already exists when the value of the variable is set):

The value of the variable set by the SET global statement in the previous session is not visible in the previously existing session. In this existing session, the global scope variable has changed to the set value.

You can see the value of the variable by another session (the session does not exist when the value is set, it is the session after the new connection):

in the new session, after setting the value of the variable, the global range variable is the set value. The value of the variable set by the SET global statement in the last session is visible after this new session.                 

ü Re-view the value of the variable after restarting the MySQL server:

After restarting the MySQL server, the value of the variable reverts to the default value.

Conclusion:

L If the variable is both (global/session), then Set<=>setsession, Show<=>show session. To set and display variables of the global scope, use Setglobal and show global explicitly.

For variables of type both&dynamic, if the new value is set by the SET command (the default execution of the set session command), the value of the variable at the session range is set. Then the set value can be obtained by viewing the value of the variable in the session with the show command (default execution show session). The value of the newly set variable is not visible to any other session that was previously present or created afterwards. In addition, because it is a variable value set by setting session, there is no effect on variables of global scope, regardless of which session the value of the variable seen through the show global statement is the value before the set session command is executed.

If the value of the variable is set by the set global command (at this point the variable value of the global range is set). In the session where the setting is performed, the value of the variable is viewed by the show command (by default, show session), and the resulting value is still pre-set (that is, the value of the session range). Using the show session command in other previously existing sessions is the same as the pre-set value. In the later session, the set value is obtained by the show session command (because the new session reads the new value after the change). Whether it is a session that has been changed or another session that already existed or a new session that was created later, the show Global command is the value of the set global range.

L Regardless of the global scope variable or the session range variable, after restarting the server, the value of the set will be invalidated and revert to the default value.

6. both& not Dynamic

Example:gtid_executed

? Display the value of the variable:

? Set the variable of the both& not dynamic type in a session:

As you can see, a variable of the not dynamic type cannot be set by command, whether it is a global scope or a session range.

Conclusion: A variable of not dynamic type cannot be set directly by set, set session, set global command.

Sum up:

? Variables of type not dynamic cannot be set by set, set session, set global command. Only variables of type dynamic can be set in these ways.

? For dynamic variables

ü If the variable is a single global range, you must use the Setglobal command to set the value of the variable, you cannot use the set (here set<=>set session) command setting, and it will be valid for all sessions immediately after setting. For global range variables show, show session, show global are equivalent.

ü If the variable is a single session range, you need to set the variable value using the set (here set<=>setsession) command, cannot be set using the set global command, and is only valid for the session setting. For variables with session range, use Show (here show<=> Show session) command to display variable values, you cannot use ShowGlobal to display session range variables, and show as empty if you use show global.

ü If the variable is of the both range, then the variable value set using the set (here set<=>setsession) command is only valid for the session that is set, using show (Here Show<=>show session) command to get the modified value in the session, and the value obtained by ShowGlobal in the session is still the global range before the set setting, if the value of the variable is set using the SET global command, In the session that is set up and in the other saved sessions, the show (here show<=>show session) command shows the variable value before the change, and in the later session it is shown by show (show <=>show session) command to get the modified value, however, whether it is a new or existing session, if you use show global to set the value through the set global command.

? Regardless of the global scope variable or the session range variable, after restarting the server, the set value is invalidated and reverts to the default value.

MySQL in global, session, and both (Global & session) range