System Variable Scoping

In this lesson, you will learn how to navigate the three distinct layers of configuration scope in MySQL and MariaDB: Global, Session, and Persistent settings.

When you modify a system variable, you aren't just changing a value; you are defining who that change affects and how long it lasts. Understanding these boundaries is critical for avoiding accidental performance degradation or configuration drift across your database ecosystem.

The Three Scopes

1. Global Scope (GLOBAL): Changes made here affect the entire database server. Any new connection initiated after the change will inherit these settings. However, GLOBAL changes do not affect existing connections.
2. Session Scope (SESSION): These changes are private to your current connection. If you set sql_mode or optimizer_switch at the SESSION level, no other user or connection will be affected. This is ideal for testing query optimizations without impacting production traffic.
3. Persistent Scope (PERSIST): Historically, changing a variable at the GLOBAL level would be lost upon a server restart. Using the PERSIST keyword ensures the value is written to a special mysqld-auto.cnf file, making the configuration survive service restarts.

Practical Application

Imagine you want to adjust the max_execution_time for a specific analysis query:

-- Apply only to my current connection
SET SESSION max_execution_time = 1000;

-- Apply globally for all future connections
SET GLOBAL max_execution_time = 5000;

-- Apply globally and ensure it survives a server restart
SET PERSIST max_execution_time = 5000;

Why the Distinction Matters

A common pitfall is changing a variable globally, expecting it to fix an ongoing issue for a long-running transaction. Because GLOBAL changes only propagate to new connections, that long-running transaction will continue to use the old, outdated setting until it finishes.

Furthermore, always check the documentation for a variable before modifying it; some variables are restricted to GLOBAL only (like innodb_buffer_pool_size), while others like foreign_key_checks can be toggled at both SESSION and GLOBAL levels. By mastering these scopes, you transition from simply "running" a database to precisely engineering its runtime environment.

Key Takeaway: Global settings dictate the server's default behavior, Session settings grant you granular control over your current connection, and Persistent settings guarantee your configuration survives a reboot.