Syntax
------

SHOW [GLOBAL | SESSION] VARIABLES
  [LIKE 'pattern' | WHERE expr]

Description
-----------

SHOW VARIABLES shows the values of MariaDB system variables. This information
also can be obtained using the mysqladmin variables command. The LIKE clause,
if present, indicates which variable names to match. The WHERE clause can be
given to select rows using more general conditions.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for
new connections to MariaDB. With SESSION, it displays the values that are in
effect for the current connection. If no modifier is present, the default is
SESSION. LOCAL is a synonym for SESSION. With a LIKE clause, the statement
displays only rows for those variables with names that match the pattern. To
obtain the row for a specific variable, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'maria_group_commit';
SHOW SESSION VARIABLES LIKE 'maria_group_commit';

To get a list of variables whose name match a pattern, use the "%" wildcard
character in a LIKE clause:

SHOW VARIABLES LIKE '%maria%';
SHOW GLOBAL VARIABLES LIKE '%maria%';

Wildcard characters can be used in any position within the pattern to be
matched. Strictly speaking, because "_" is a wildcard that matches any single
character, you should escape it as "\_" to match it literally. In practice,
this is rarely necessary.

The WHERE and LIKE clauses can be given to select rows using more general
conditions, as discussed in Extended SHOW.

See SET for information on setting server system variables.

See Server System Variables for a list of all the variables that can be set.

You can also see the server variables by querying the Information Schema
GLOBAL_VARIABLES and SESSION_VARIABLES tables.

Examples
--------

SHOW VARIABLES LIKE 'aria%';
+------------------------------------------+---------------------+
| Variable_name                            | Value               |
+------------------------------------------+---------------------+
| aria_block_size                          | 8192                |
| aria_checkpoint_interval                 | 30                  |
| aria_checkpoint_log_activity             | 1048576             |
| aria_force_start_after_recovery_failures | 0                   |
| aria_group_commit                        | none                |
| aria_group_commit_interval               | 0                   |
| aria_log_file_size                       | 1073741824          |
| aria_log_purge_type                      | immediate           |
| aria_max_sort_file_size                  | 9223372036853727232 |
| aria_page_checksum                       | ON                  |
| aria_pagecache_age_threshold             | 300                 |
| aria_pagecache_buffer_size               | 134217728           |
| aria_pagecache_division_limit            | 100                 |
| aria_recover                             | NORMAL              |
| aria_repair_threads                      | 1                   |
| aria_sort_buffer_size                    | 134217728           |
| aria_stats_method                        | nulls_unequal       |
| aria_sync_log_dir                        | NEWFILE             |
| aria_used_for_temp_tables                | ON                  |
+------------------------------------------+---------------------+

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
 VARIABLE_NAME LIKE 'max_error_count' OR
 VARIABLE_NAME LIKE 'innodb_sync_spin_loops';
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 64           |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET GLOBAL max_error_count=128;

SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
 INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
 VARIABLE_NAME LIKE 'max_error_count' OR
 VARIABLE_NAME LIKE 'innodb_sync_spin_loops';
+---------------------------+---------------+--------------+
| VARIABLE_NAME             | SESSION_VALUE | GLOBAL_VALUE |
+---------------------------+---------------+--------------+
| MAX_ERROR_COUNT           | 64            | 128          |
| INNODB_SYNC_SPIN_LOOPS    | NULL          | 30           |
+---------------------------+---------------+--------------+

SET GLOBAL max_error_count=128;

SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+

SHOW GLOBAL VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 128   |
+-----------------+-------+

Because the following variable only has a global scope, the global value is
returned even when specifying SESSION (in this case by default):

SHOW VARIABLES LIKE 'innodb_sync_spin_loops';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_sync_spin_loops | 30    |
+------------------------+-------+

URL: https://mariadb.com/kb/en/show-variables/