IntroductionMySQL 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 variablesGLOBAL – affect the server as a wholeSESSION – only applicable in a current sessionSyntax for SET:SET variable expr [, variable expr] …variable: {…| {GLOBAL system_var_name| {PERSIST system_var_name| {PERSIST_ONLY system_var_name| [SESSION system_var_name}It is important to know that some system variablesAre dynamic and change behavior of the server immediatelyDo not modify the server at runtime and require a server restartCan modify the server at runtime, but alternatively the DBA can choose to apply only at restartModify the server runtime, but are not persisted once the server is restartedGet applied only to new connection sessions but not to existing sessionsSo 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:ScopeDynamicThe values for Scope can be one of the followingGlobalSessionGlobal, SessionIf 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.max_connectionsCommand-Line Format –max-connections#System Variable max_connections Scope GlobalDynamic YesSET_VAR Hint AppliesNoType IntegerDefault Value 151Minimum Value 1Maximum Value 100000The maximum permitted number of simultaneous client connections. The maximum effective value is the lesser of the effective value of open_files_limit – 810, and the value actually set for max_connections.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 1000Case 1: SET GLOBALIf you performMySQL SET GLOBAL max_connections 1000;All existing sessions are set to 151All new sessions are set to 1000If the server restartsAll sessions are set to 151.In the following illustrationsG: shows the global value:show global variables like ‘max_connections’;S: shows effective session value.However: the value ofshow session variables like ‘max_connections’;Is the value for any new session, NOT the current session.Shown visually you can see the effect of the SET GLOBAL over time and across user sessions and a server restart.Case 2: SET PERSISTIf 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.Shown visually you can see the effect of the SET PERSIST over time and across user sessions and a server restart.Case 3: SET PERSIST_ONLYYou 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 aMySQL SET PERSIST_ONLY max_connections 1000;Both the PERSIST and PERSIST ONLY statements result in writing the new value, in this case writing max_connections1000 to the mysqld-auto.cnf file.Shown visually you can see the effect of the SET PERSIST_ONLY over time and across user sessions and a server restart.CASE 4: RESET/SET to DEFAULTFinally, what if you want to reset the server back to using the MySQL default value.You have 2 choices:Reset the value and persist it. This sets the value in mysqld-auto.cnf to the default.MySQL SET PERSIST max_connections DEFAULT;Removes the setting altogether from mysqld-auto.cnfMySQL 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 errorMySQL SET SESSION max_connections1000;ERROR: 1229 (HY000): Variable ‘max_connections’ is a GLOBAL variable and should be set with SET GLOBALOnly 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 failSESSION 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 isDYNAMIC (Yes) this means SET willApply and reflect the change within the server immediatelyIf session related – only for new sessionsCan be persisted or not – as discussed above.DYNAMIC (No) – means that a variableCan only be changed using SET PERSIST_ONLYChange requires a server restart for it to be appliedKnow your variable settingsSHOW VARIABLES command provides you the active value of your GLOBAL and SESSION variablesshow variables like ‘max_connections’;show global variables like ‘max_connections’;show session variables like ‘max_connections’;Alternatively these variables can be viewed in more detailUsing these 5 Performance Schema tables (for a short demo see – Setting and Viewing MySQL System Variables)global_variables: only global variablesselect * 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.cnfselect * 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 columnsVARIABLE_SOURCE – has a variable was setSET_TIME – when it was setSET_USER, SET_HOST – if DYNAMIC, who made the changeOf most interestselect * from performance_schema.variables_info where VARIABLE_SOURCE ‘COMPILED’;ConclusionHopefully 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!