Exploring the MySQL Server System Variables and SET Statements

Introduction  

MySQL system variables configure the server’s operation, and the SET statement is used to change system variables. The MySQL SET statements have various options for specifying how and when changes are made to system variables. It’s important to understand how these changes are reflected in current sessions (connections), later sessions, and after database server restarts.  

The variables that a specific user can change using SET are determined by their level of permissions.  This blog will focus on GLOBAL, PERSIST, and PERSIST_ONLY.
 
There are different scopes for system variables 

• GLOBAL – affect the server as a whole
• SESSION – only applicable in a current session

Syntax for SET:

It is important to know that some system variables 

• Are dynamic and change behavior of the server immediately
• Do not modify the server at runtime and require a server restart
• Can modify the server at runtime, but alternatively the DBA can choose to apply only at restart 
• Modify the server runtime, but are not persisted once the server is restarted
• Get applied only to new connection sessions but not to existing sessions

So given the above possibilities and to better understand the various scenarios, next we will review how things work in practice via examples.

MySQL reference documentation shows our system variables and their details:  

Each system variable includes various attributes. The 2 which are relevant here are: 

• Scope
• Dynamic

The values for Scope can be one of the following

• Global
• Session
• Global, Session
   

If you change a global system variable and if the value has implications for sessions (connections) that value is used at the initialization of a session.  Thus, any existing connections prior to setting a GLOBAL value will not “inherit” this value, only new sessions will have this setting change. Additionally based on the SET command, a change in a GLOBAL could either be persisted or not persisted when the server is restarted.  

This example demonstrates this using the max_connections variable.

Say you want to change the value of max_connections and max_connections is currently set to 151 (the default) and you want to change it to 1000

Case 1: SET GLOBAL
If you perform
MySQL> SET GLOBAL max_connections = 1000;
All existing sessions are set to 151
All new sessions are set to 1000
If the server restarts
All sessions are set to 151.

Case 2: SET PERSIST

If you instead want to change max_connections for new sessions and additionally after a restart have all sessions set to 1000 you need to PERSIST the change.
MySQL> SET PERSIST max_connections = 1000;

This locks in the change for both the currently running server and for the server after a restart.

Case 3: SET PERSIST_ONLY

You have yet another option. You may not want to change the value for the currently running server – in this case leaving the value as 151, however upon restart you want the value to be changed to 1000. In this case you would instead perform a 
MySQL> SET PERSIST_ONLY max_connections = 1000;

Both the PERSIST and PERSIST ONLY statements result in writing the new value, in this case writing max_connections=1000 to the mysqld-auto.cnf file. 

CASE 4: RESET/SET to DEFAULT

Finally, what if you want to reset the server back to using the MySQL default value. 

You have 2 choices:

1. Reset the value and persist it. This sets the value in mysqld-auto.cnf to the default.
   • MySQL> SET PERSIST max_connections = DEFAULT;
2. Removes the setting altogether from mysqld-auto.cnf
   • MySQL> RESET PERSIST max_connections;

In the case of max connections each command will result in setting the variable to the default of 151.

What if you wanted to change the max_connections at the session level?

If a variable is GLOBAL then SET SESSION will error

MySQL> SET SESSION max_connections=1000;
ERROR: 1229 (HY000): Variable ‘max_connections’ is a GLOBAL variable and should be set with SET GLOBAL

Only variables that have a scope of SESSION or GLOBAL,SESSION can be set for a session.
If a variable is GLOBAL then SET SESSION will fail

SESSION variables are only active in the current session where they were set.

Now let’s consider the difference between a variable that is DYNAMIC (Yes) or DYNAMIC (No).

If a variable is 
DYNAMIC (Yes) this means SET will 

• Apply and reflect the change within the server immediately
  • If session related – only for new sessions
• Can be persisted or not – as discussed above.

DYNAMIC (No) – means that a variable 

• Can only be changed using SET PERSIST_ONLY
• Change requires a server restart for it to be applied 

Know your variable settings

 SHOW VARIABLES command provides you the active value of your GLOBAL and SESSION variables

show variables like ‘max_connections’;
show global variables like ‘max_connections’;
show session  variables like ‘max_connections’;

Alternatively these variables can be viewed in more detail

Using these 5 Performance Schema tables (for a short demo see – Setting and Viewing MySQL System Variables)

• global_variables:  only global variables
  • select * from performance_schema.global_variables;
• session_variables:  the session variables a session, as well as the values of global variables that have no session counterpart. 
  • select * from performance_schema.session_variables;
• variables_by_thread: Includes session variables only, identified by thread ID. 
  • select * from performance_schema.variables_by_thread;
• persisted_variables: View the values set in the  mysqld-auto.cnf 
  • select * from performance_schema.persisted_variables;
• variables_info: Shows, for each system variable, the source from which it was most recently set, and its range of values.
  • See “Performance Schema variables_info Table”. 
  • select * from performance_schema.variables_info;
  • Of special interest are the columns
    • VARIABLE_SOURCE – has a variable was set 
    • SET_TIME – when it was set
    • SET_USER, SET_HOST – if DYNAMIC, who made the change
    • Of most interest 
      • select * from performance_schema.variables_info where VARIABLE_SOURCE <> ‘COMPILED’;

Conclusion

Hopefully you now better understand how the various SET options can be leveraged to meet your expected outcome when changing system variables.  That improved understanding should help you avoid missteps and errors in managing your MySQL Database Server. 

As always, thank you for running MySQL!