Introduction

MySQL exposes its runtime configuration through server variables, and the fastest way to inspect them is from SQL. This is essential for troubleshooting, performance tuning, and checking whether a configuration file change actually took effect. The main commands are SHOW VARIABLES, SHOW GLOBAL VARIABLES, and direct reads through the @@ syntax.

Show All Current Variables

To list the current server variables and their values:

SHOW VARIABLES;

That returns both session and global variables as seen by the current connection. On a real server, the output is long, so most of the time you will filter it.

For global scope only:

SHOW GLOBAL VARIABLES;

For session scope only:

SHOW SESSION VARIABLES;

This distinction matters because some variables can differ per connection while others are server-wide.

Filter with LIKE

In practice, you usually want one variable or one family of variables.

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW GLOBAL VARIABLES LIKE 'sql_mode';

This is the quickest day-to-day admin pattern because it avoids scrolling through hundreds of lines.

Read One Variable Directly

If you already know the variable name, the @@ syntax is compact and script-friendly.

SELECT @@GLOBAL.max_connections;
SELECT @@SESSION.sql_mode;
SELECT @@hostname;

This is especially useful in monitoring scripts or one-off checks where you want a single scalar value instead of a two-column result set.

Understand Global Versus Session

A common source of confusion is that some variables exist in both global and session scope.

For example:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

The global value is the server default for new connections. The session value is what your current connection is using right now. If a client or application changes a session variable after connecting, the two can differ.

That is why checking only one scope can lead to false conclusions during debugging.

See What Can Be Changed at Runtime

Many MySQL variables are dynamic, which means they can be changed without restarting the server.

Example:

SET GLOBAL max_connections = 300;

Others are static and only change after editing the config file and restarting MySQL. When a value is unexpected, verify both:

1. what the config file says
2. what the running server currently exposes

The runtime value is what actually matters for live behavior.

Use Information Schema or Performance Schema When Needed

Modern MySQL also exposes variable metadata through schema tables, which can be helpful for tooling.

SELECT *
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'max_connections';

This is less convenient for ad hoc interactive use than SHOW VARIABLES, but it is useful when you want queryable metadata from SQL tables.

Typical Admin Checks

A few common variables worth checking during investigation:

• 'max_connections for connection saturation'
• 'innodb_buffer_pool_size for InnoDB memory tuning'
• 'sql_mode for behavioral differences between environments'
• 'character_set_server and collation_server for encoding issues'
• 'slow_query_log and long_query_time for performance diagnostics'

Example:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

These quick checks are often enough to explain why one environment behaves differently from another.

Common Pitfalls

• Checking session scope when the real question was about global scope.
• Assuming a config file change is active without verifying the running value.
• Searching manually through SHOW VARIABLES instead of filtering with LIKE.
• Forgetting that applications may override session variables after connecting.
• Changing a global variable at runtime and assuming it will persist after restart.

Summary

• 'SHOW VARIABLES is the standard MySQL command for viewing current configuration values.'
• Use SHOW GLOBAL VARIABLES or SHOW SESSION VARIABLES when scope matters.
• Use LIKE to filter by variable name or prefix.
• Use @@GLOBAL.var_name and @@SESSION.var_name for direct one-value reads.
• Always verify the runtime value, not just the config file, when troubleshooting MySQL behavior.