MySQL Session vs Global Variables Explained

MySQL utilizes two distinct scopes for its configuration variables—global and session—to balance overall server performance with individual client connection requirements. This article explains the operational differences between session-level and global-level variables in MySQL, how they interact, and how database administrators can manage them to optimize database behavior.

Global Variables

Global variables control the overall operation of the MySQL server. When the MySQL server starts, it initializes these variables using default values or configurations specified in the option files (such as my.cnf or my.ini).

Scope: Global variables affect the entire server and apply to all connections.
Privileges: Changing a global variable requires administrative privileges (typically the SUPER or SYSTEM_VARIABLES_ADMIN privilege).

Syntax: To modify a global variable at runtime, use the GLOBAL keyword:

SET GLOBAL max_connections = 500;
-- Or using the double-at syntax
SET @@global.max_connections = 500;

Persistence: Standard SET GLOBAL commands only change the variable in memory. If the server restarts, these changes are lost unless they are also updated in the configuration file. In MySQL 8.0 and later, you can use SET PERSIST to write the change to a mysqld-auto.cnf file, ensuring the setting survives a restart:
```
SET PERSIST max_connections = 500;
```

Session Variables

Session variables apply strictly to individual client connections. They allow developers and administrators to customize the database behavior for a specific task or query session without affecting other connected users.

Scope: Session variables are active only for the duration of the current connection. Once the client disconnects, the session-specific settings are destroyed.
Privileges: Most session variables can be modified by any user without administrative privileges.
Syntax: To modify a session variable, use the SESSION keyword, or simply omit the scope keyword, as SESSION is the default:
```
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- Or simply
SET sql_mode = 'STRICT_TRANS_TABLES';
```

How Global and Session Variables Interact

When a new client connects to MySQL, the server automatically initializes the client’s session variables by copying the values from the currently active global variables.

Once the session is established, changes made to the global variables will not affect any currently active sessions. The new global values will only apply to connections created after the change was made. Conversely, changing a session variable only impacts the current connection and has zero effect on other active sessions or the global defaults.

Not All Variables Exist in Both Scopes

It is important to note that not all MySQL system variables can be set at both levels:

Global-Only: Some variables regulate resources that belong to the entire server, such as max_connections, innodb_buffer_pool_size, or port. These cannot be set at the session level.
Session-Only: Some variables are strictly context-dependent for a specific execution, such as last_insert_id or pseudo_thread_id.
Both Scopes: Many variables exist in both scopes, such as sql_mode, character_set_client, and join_buffer_size. This allows a global baseline to be set while letting individual sessions override the setting when executing specific queries.

To check the current value of a variable at either scope, use the SHOW VARIABLES statement with the appropriate modifier:

SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';
SHOW SESSION VARIABLES LIKE 'join_buffer_size';