Practical MySQL Server Administration - Part 1: Server Configuration and System Variables
Part 1 starts a 7-part series on practical MySQL server administration.
Learn MySQL administration quickly by understanding how the server reads configuration, exposes runtime settings, and responds to live operational changes. Before touching replication, performance tuning, or backup strategy, I verify the basics: what mysqld starts with, what remains active now, and what I can change safely without a restart.
In this article, I focus on server configuration and system variables.
Start With the Server Defaults
The mysqld server binary can display its default options and system variables directly.
mysqld --verbose --help
Use this command to confirm server defaults on a host before reviewing local configuration files.
Check Active Variables at Runtime
To inspect the current runtime configuration, connect to MySQL and review the active variables.
SHOW VARIABLES;
You can also check the same information from the shell:
/bin/mysqladmin -uroot -p variables
/bin/mysqladmin -uroot -p extended-status
I typically compare server defaults, runtime values, and configuration-file entries together. That makes it much easier to spot drift.
Where MySQL Reads Configuration
MySQL variables can be set in several places.
At startup from the command line:
mysqld --data-dir=/my_data_dir --wait-timeout=10000
From the configuration file:
[mysqld]
datadir=/my_data_dir
wait_timeout=10000
MySQL also lets you change many variables dynamically while the server runs.
Changing Variables Without Restarting the Server
For operational work, MySQL stays particularly flexible here.
Set a global variable at runtime:
SET GLOBAL max_connections = 500;
SET @@GLOBAL.max_connections = 500;
Persist the value at runtime and across restarts:
SET PERSIST max_connections = 500;
SET @@PERSIST.max_connections = 500;
Persist the value without applying it immediately:
SET PERSIST_ONLY super_read_only = TRUE;
SET @@PERSIST_ONLY.super_read_only = TRUE;
Set a session-only value:
SET SESSION sql_mode = 'ANSI';
SET @@SESSION.sql_mode = 'ANSI';
SET PERSISTwrites the change intomysqld-auto.cnf, which makes it especially useful for planned operational changes that should survive a restart.
Inspect Running Sessions
On a busy server, I inspect active sessions early.
SHOW PROCESSLIST;
Example terminal output:
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 8 | event_scheduler | localhost | NULL | Daemon | 1403 | Waiting on empty queue | NULL |
| 26 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.01 sec)
This output quickly distinguishes server background threads from active client work.
If a session must be terminated, use the connection id:
KILL 26;
Limit Per-User Resource Consumption
Resource controls become important when a single account starts consuming more server capacity than expected.
Check the current setting:
SHOW VARIABLES LIKE '%max_user%';
Example terminal output:
mysql> show variables like '%max_user%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_user_connections | 0 |
+----------------------+-------+
1 row in set (0.10 sec)
A value of
0means MySQL does not enforce a per-user connection limit.
To apply account-level limits:
ALTER USER 'test'@'localhost'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 20
MAX_CONNECTIONS_PER_HOUR 30
MAX_USER_CONNECTIONS 5;
These controls help in shared environments, batch users, and integration accounts that should not overwhelm the server.
Final Thoughts
Good MySQL administration starts with configuration discipline. If you can identify where a value comes from, decide whether it should be runtime-only or persistent, and inspect live sessions with confidence, the rest of the operational stack becomes much easier to manage.
In Part 2, I walk through the MySQL data directory and the physical layout of the files the server maintains.