When working with production MySQL servers, especially in replication setups, we often need to modify the my.cnf configuration while maintaining continuous operation. The standard restart approach becomes problematic when dealing with:

• High-availability requirements
• Replication topologies
• Heavy query loads (100+ QPS)

Not all my.cnf parameters can be changed dynamically. These replication-related parameters require a restart:

server-id
log-bin
binlog-format
sync-binlog
expire-logs-days

For parameters that can be changed dynamically, we have better options.

MySQL provides several ways to adjust settings without restarting:

Method 1: SET GLOBAL Command

-- For dynamically adjustable variables
SET GLOBAL max_connections = 200;
SET GLOBAL thread_cache_size = 16;

Method 2: Using MySQL Shell

mysql> SET PERSIST max_connections = 200;
mysql> SET PERSIST_ONLY back_log = 150;

PERSIST writes changes to mysqld-auto.cnf while PERSIST_ONLY only updates the file.

For changes that absolutely require my.cnf modification, here's a safe procedure:

# 1. Verify current settings
SHOW GLOBAL VARIABLES LIKE 'server%';

# 2. Prepare modified my.cnf
cp /etc/my.cnf /etc/my.cnf.new
vi /etc/my.cnf.new

# 3. Test configuration (MySQL 5.6+)
mysqld --defaults-file=/etc/my.cnf.new --verbose --help | grep -A1 "Default options"

# 4. Rotate config safely
mv /etc/my.cnf /etc/my.cnf.bak
mv /etc/my.cnf.new /etc/my.cnf

# 5. Apply changes (alternative to restart)
mysqladmin flush-logs
mysqladmin refresh

For zero-downtime schema modifications during replication changes:

pt-online-schema-change \
--alter "ENGINE=InnoDB" \
D=mydatabase,t=mytable \
--execute

Always verify changes with:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW SLAVE STATUS\G
SHOW MASTER STATUS\G

Consider implementing these changes during maintenance windows when possible, even when using dynamic methods.

When configuring MySQL as a replication master, modifying my.cnf to enable binary logging is just the first step. The real challenge comes when you need these changes to take effect without disrupting active connections or losing incoming queries during a service restart.

For many dynamic variables, MySQL provides runtime configuration through the SET GLOBAL command. For binary logging specifically:

SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = 'ROW';  # or 'STATEMENT'/'MIXED'
SET GLOBAL expire_logs_days = 10;
SET GLOBAL max_binlog_size = 100M;

For configurations that can't be changed dynamically, you have these options:

# Flush method (partial reload)
mysql> FLUSH LOGS;
mysql> FLUSH TABLES WITH READ LOCK;  # For replication positions

# Linux signal method
$ kill -SIGHUP pidof mysqld

Be aware that not all parameters can be changed dynamically. These typically require a full restart:

• Changing data directory locations
• Modifying storage engine configurations
• Port number changes

After making changes, verify with:

SHOW GLOBAL VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

For MySQL 8.0+, consider using the AdminAPI:

mysql-js> dba.configureInstance()
mysql-js> dba.configureReplicaSetInstance()