How to view and set server variables in MySQL or MariaDB

Server variables in MySQL and MariaDB control runtime behavior such as connection limits, timeouts, memory usage, and SQL modes, making them a fast lever for troubleshooting and performance tuning without changing application code.

Most variables exist at two scopes: a GLOBAL value owned by the server (used as the default for new connections) and a SESSION value owned by each client connection (overriding the global default for that connection). The SHOW VARIABLES family reads the current values from the running server, while SET SESSION and SET GLOBAL change values in memory for the current session or server-wide defaults.

Some variables are dynamic (changeable at runtime) while others are read-only or require a restart after editing an on-disk option file (typically a my.cnf or *.cnf file on Linux). Changing global values typically requires elevated privileges and can impact connected applications, so changes should be made deliberately and verified immediately.

Steps to view and set server variables:

Connect to the server using the mysql command-line client.
```
$ mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password:
mysql>
```
Adjust root, host, and port to match the intended administrative account and endpoint.

Display the current session value of a single variable using SHOW VARIABLES LIKE.

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES defaults to SESSION scope on both MySQL and MariaDB.

Search for related settings using a wildcard pattern.

mysql> SHOW VARIABLES LIKE '%timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| connect_timeout     | 10    |
| interactive_timeout | 28800 |
| net_read_timeout    | 30    |
| net_write_timeout   | 60    |
| wait_timeout        | 28800 |
+---------------------+-------+
5 rows in set (0.00 sec)

Display the global value of a variable using SHOW GLOBAL VARIABLES LIKE.

mysql> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

Display the session value of the same variable using SHOW SESSION VARIABLES LIKE.

mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

Session values can differ from global values when a client or proxy applies per-connection overrides.

Read a variable with explicit scope using @@GLOBAL and @@SESSION.

mysql> SELECT
    @@GLOBAL.wait_timeout  AS global_wait_timeout,
    @@SESSION.wait_timeout AS session_wait_timeout;
+---------------------+----------------------+
| global_wait_timeout | session_wait_timeout |
+---------------------+----------------------+
| 28800               | 28800                |
+---------------------+----------------------+
1 row in set (0.00 sec)

Set a per-connection value using SET SESSION.
```
mysql> SET SESSION wait_timeout = 600;
Query OK, 0 rows affected (0.00 sec)
```
wait_timeout is measured in seconds, and a session change applies only to the current connection.

Verify the session change using @@SESSION.

mysql> SELECT @@SESSION.wait_timeout AS session_wait_timeout;
+----------------------+
| session_wait_timeout |
+----------------------+
| 600                  |
+----------------------+
1 row in set (0.00 sec)

Set a server-wide default for new connections using SET GLOBAL.
```
mysql> SET GLOBAL wait_timeout = 600;
Query OK, 0 rows affected (0.00 sec)
```
Changing wait_timeout can disconnect clients that stay idle longer than the new timeout after reconnecting with the updated default.

Verify the global change using @@GLOBAL.

mysql> SELECT @@GLOBAL.wait_timeout AS global_wait_timeout;
+---------------------+
| global_wait_timeout |
+---------------------+
| 600                 |
+---------------------+
1 row in set (0.00 sec)

SET GLOBAL changes the default for new sessions, and existing sessions keep their current session value until changed or disconnected.

Recognize missing-privilege failures when changing global variables.
```
mysql> SET GLOBAL wait_timeout = 600;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
```
Privilege names differ between MySQL and MariaDB, but the fix is the same: use an administrative account permitted to change global system variables.
Recognize a non-dynamic or read-only variable by its error message.
```
mysql> SET GLOBAL server_uuid = '00000000-0000-0000-0000-000000000000';
ERROR 1238 (HY000): Variable 'server_uuid' is a read only variable
```
Some variables require an option-file change and a restart, and some cannot be changed at all.

Print the option-file search order used by mysqld on Linux before persisting changes.

$ mysqld --verbose --help 2>/dev/null | sed -n '/Default options are read from the following files in the given order:/,/^$/p'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Persisted settings belong under the [mysqld] section of an option file in the server’s read order.

Persist the desired value by adding it under [mysqld] in the chosen option file.
```
[mysqld]
wait_timeout = 600
```
Invalid syntax in an option file can prevent mysqld from starting, so changes should be small and reviewed carefully.
Restart the database service to apply option-file changes.
```
$ sudo systemctl restart mysql
```
The systemd unit name may be mysql, mysqld, or mariadb depending on packaging.

Reconnect after the restart to run verification queries.

$ mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password:
mysql>

Verify the persisted value after restart using SHOW VARIABLES LIKE.

mysql> SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 600   |
+---------------+-------+
1 row in set (0.00 sec)

Reset a variable back to its default using SET GLOBAL … = DEFAULT.
```
mysql> SET GLOBAL wait_timeout = DEFAULT;
Query OK, 0 rows affected (0.00 sec)
```
If the variable is set in an option file, the configured value returns after the next restart unless the option-file entry is removed.

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.

Discuss the article:

Comment anonymously. Login not required.