MySQL system variables are configuration settings that control the operation and behavior of the server . They are fundamental in influencing everything from resource allocation (like memory buffers) to defining SQL modes that control query behavior and enabling or disabling specific features .
When working with them, it's crucial to understand two key concepts: scope and dynamic behavior.
Scope: This defines the range of influence of a variable.
Global Variables: These affect the overall operation of the server and apply to all client connections. A change to a global variable sets the default for new sessions created after the change . For example, max_connections is a global variable that determines the maximum number of simultaneous client connections the server will allow .
Session Variables: These affect the operation for an individual client connection . When a client connects, its session variables are typically initialized from the current values of the global variables . A client can then change its own session variables without affecting other clients. A variable like sql_mode can be set globally to provide a server-wide default, but each session can adjust it for its own needs .
Dynamic vs. Static (Non-Dynamic):
Dynamic System Variables can be modified while the server is running, without requiring a restart . This allows for on-the-fly adjustments to adapt to changing workloads.
Static (Non-Dynamic) Variables are read-only and cannot be changed at runtime . To modify them, you must update the configuration file and restart the MySQL server for the change to take effect .
Before changing any settings, you need to know how to check the current values. MySQL provides several ways to do this.
The most common method is using the SHOW VARIABLES statement . You can use the LIKE clause to filter for specific variables, and the GLOBAL or SESSION modifier to specify the scope.
-- Show all variables
SHOW VARIABLES;
-- Show a specific variable (defaults to SESSION)
SHOW VARIABLES LIKE 'autocommit';
SHOW SESSION VARIABLES LIKE 'autocommit';
-- Show the global value of a variable
SHOW GLOBAL VARIABLES LIKE 'max_connections';
-- Use wildcards to find variables with similar names
SHOW GLOBAL VARIABLES LIKE '%size%';
You can also retrieve variable values directly using SELECT by treating them as a pseudo-table .
-- Retrieve the session value (if exists, otherwise global)
SELECT @@autocommit;
SELECT @@session.autocommit;
-- Retrieve the global value explicitly
SELECT @@global.max_connections;
For a more programmatic approach, especially in MySQL 8.0, you can query the Performance Schema tables . This is very useful for scripts or monitoring tools.
performance_schema.global_variables: For global variables
performance_schema.session_variables: For session variables
performance_schema.variables_info: To see the source and set time for variables
-- Querying the Performance Schema
SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections';
Table: Summary of Commands to View System Variables
Command Scope Usage Example
SHOW VARIABLES Session (default) SHOW VARIABLES LIKE 'autocommit';
SHOW GLOBAL VARIABLES Global SHOW GLOBAL VARIABLES LIKE 'max_connections';
SELECT @@variable_name Session (preferred) SELECT @@autocommit;
SELECT @@global.variable_name Global SELECT @@global.max_connections;
There are multiple ways to set system variables, each with its own use case and persistence level.
The most permanent way to set variables is by specifying them at server startup. This is done either on the command line when starting mysqld or, more commonly, in a configuration file like my.cnf or my.ini .
Using the Configuration File: You declare variables under the [mysqld] section of the file. For Boolean variables, you can use ON/OFF or 1/0. For numeric values with suffixes, you can use K, M, G to denote kilobytes, megabytes, and gigabytes .
# Example my.cnf [mysqld] section
[mysqld]
max_connections = 1000
sort_buffer_size = 256K
max_allowed_packet = 1G
innodb_buffer_pool_size = 2G
To change a variable dynamically without interrupting service, you can use the SET statement .
Setting a Global Variable: This changes the global value, which becomes the default for new sessions. It requires the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege) .
SET GLOBAL max_connections = 500;
SET @@GLOBAL.max_connections = 500;
Setting a Session Variable: This changes the value only for your current session.
SET SESSION sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL'; -- SESSION is default
It's important to note that runtime changes made with SET GLOBAL or SET SESSION are temporary and will be lost if the server is restarted .
MySQL 8.0 introduced a powerful feature to solve the problem of temporary runtime changes: persisted system variables . This allows you to dynamically change a variable and save the change to a configuration file so it persists after a restart, all without manually editing my.cnf.
SET PERSIST: This command updates the runtime global value and saves the setting to a JSON file named mysqld-auto.cnf in the data directory .
SET PERSIST max_connections = 1000;
SET PERSIST_ONLY: This command is for read-only variables that can only be set at startup. It does not change the runtime value but saves the setting to mysqld-auto.cnf so it will be applied on the next restart .
SET PERSIST_ONLY innodb_log_file_size = 50331648*2;
RESET PERSIST: This command removes persisted settings from the mysqld-auto.cnf file .
RESET PERSIST max_connections; -- Remove one variable
RESET PERSIST IF EXISTS unknown_var; -- Safely remove if exists
RESET PERSIST; -- Remove all persisted variables
The mysqld-auto.cnf file takes precedence over the traditional my.cnf file. You can control whether this file is loaded at startup using the persisted_globals_load variable .
Table: Comparison of System Variable Setting Methods
Method Syntax Example Persistence Requires Restart? Best For
Config File max_connections=1000 in my.cnf Permanent Yes Initial setup, static/read-only variables
SET GLOBAL SET GLOBAL max_connections=500; Until server restart No Temporary, emergency adjustments
SET PERSIST SET PERSIST max_connections=1000; Permanent No Making dynamic changes permanent safely
SET PERSIST_ONLY SET PERSIST_ONLY innodb_log_file_size=...;After next restart Yes Configuring read-only variables remotely