In this lesson, you will learn how to modify server configurations in real-time to adjust database behavior without the downtime associated with a service restart.
The Power of Dynamic Variables
In a production environment, restarting a database to apply a configuration change is often a luxury you cannot afford. Fortunately, MySQL and MariaDB classify many configuration parameters as dynamic. These flags can be modified at runtime, allowing you to tune performance, adjust memory limits, or toggle diagnostic logging on the fly.
When you modify these settings, you are interacting with the GLOBAL scope. Changes made here apply to all existing sessions and affect the state of the server immediately. However, it is crucial to remember that unless you also update your configuration file (like my.cnf or my.ini), these settings will revert to their original values the next time the database service restarts.
Modifying Flags via SQL
To update a global flag, you use the SET GLOBAL command. For example, if you need to adjust the innodb_buffer_pool_instances or temporarily disable the slow_query_log, you can do so directly within your SQL client:
-- Change the setting for the current running instance
SET GLOBAL slow_query_log = 'OFF';
-- Verify the change took effect
SELECT @@GLOBAL.slow_query_log;
Safety and Persistence
Before executing a SET GLOBAL command, always verify if the variable is indeed dynamic. Attempting to change a static variable—one that can only be set during initialization—will result in an error. Furthermore, when tuning critical parameters like memory buffers or thread caches, use your knowledge from previous lessons on Performance Baseline Profiling to ensure the change is actually solving the contention you are targeting rather than creating new bottlenecks.
To ensure your changes survive a reboot, always mirror your SET GLOBAL command by manually editing the configuration file on your server. This "two-step" process—applying it live, then persisting it in the configuration file—is the professional standard for maintaining database uptime while optimizing performance.
Key Takeaway: Use SET GLOBAL to apply configuration changes instantly, but remember to update your server’s configuration file to ensure those changes persist after a restart.