Skip to content
DBAExperts
MySQL Operations

How to change configuration variables in MySQL safely

MySQL has dynamic variables (changed live) and variables that only apply at startup. The classic mistake is changing something with SET GLOBAL, seeing that it works, and losing it on the next restart. MySQL 8.0 solves this with SET PERSIST.

  1. 1

    Check the current value before touching anything

    Note the original value so you can revert. Never change a variable without knowing what it was.

    SHOW GLOBAL VARIABLES LIKE 'max_connections';
  2. 2

    Change a dynamic variable live

    SET GLOBAL takes effect immediately without a restart, but the change is lost when the server restarts. Good for testing.

    SET GLOBAL max_connections = 500;
  3. 3

    Make the change persistent

    SET PERSIST applies the value and saves it so it survives restarts, writing it to mysqld-auto.cnf. This is the recommended approach in MySQL 8.0.

    SET PERSIST max_connections = 500;
  4. 4

    For variables that only apply at startup, use my.cnf

    Some variables (like innodb_buffer_pool_size in older versions, or startup parameters) go in the configuration file and require a service restart.

    [mysqld]
max_connections = 500

# Luego reinicia el servicio:
# sudo systemctl restart mysql

// common mistake

Raising values like innodb_buffer_pool_size or max_connections without accounting for available RAM can keep MySQL from starting or make the operating system kill the process for running out of memory. Change one variable at a time, measure the effect, and keep the previous value handy to revert.

// when it's worth an expert

Configuration tuning depends on the hardware, the load, and the query pattern: copying a my.cnf off the internet usually does more harm than good. At dba.mx we adjust parameters based on real metrics from your server, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.