13.7.5.36 SHOW VARIABLES Syntax

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

SHOW VARIABLES shows the values of MySQL system variables (see Section 5.1.4, “Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

System variable information is also available from the mysqladmin variables command (see Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”).

For SHOW VARIABLES, a LIKE clause, if present, indicates which variable names to match. A WHERE clause can be given to select rows using more general conditions, as discussed in Section 19.18, “Extensions to SHOW Statements”.

SHOW VARIABLES accepts an optional GLOBAL or SESSION variable scope modifier:

  • With a GLOBAL modifier, the statement displays global system variable values. These are the values used to initialize the corresponding session variables for new connections to MySQL. If a variable has no global value, the session value is displayed.

  • With a SESSION modifier, the statement displays the system varaible values that are in effect for the current connection. If a variable has no session value, the global value is displayed. LOCAL is a synonym for SESSION.

  • If no modifier is present, the default is SESSION.

The scope for each system variable is listed at Section 5.1.4, “Server System Variables”.

Most system variables can be set at server startup (read-only variables such as version_comment are exceptions). Many can be changed at runtime with the SET statement. See Section 5.1.5, “Using System Variables”, and Section 13.7.4, “SET Syntax”.

Partial output is shown here. The list of names and values may differ for your server. Section 5.1.4, “Server System Variables”, describes the meaning of each variable, and Section 8.12.2, “Tuning Server Parameters”, provides information about tuning them.

mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
| basedir                         | /                                   |
| bdb_cache_size                  | 8388600                             |
| bdb_home                        | /var/lib/mysql/                     |
| bdb_log_buffer_size             | 32768                               |
...
| max_connections                 | 100                                 |
| max_connect_errors              | 10                                  |
| max_delayed_threads             | 20                                  |
| max_error_count                 | 64                                  |
| max_heap_table_size             | 16777216                            |
| max_join_size                   | 4294967295                          |
| max_relay_log_size              | 0                                   |
| max_sort_length                 | 1024                                |
...
| time_zone                       | SYSTEM                              |
| timed_mutexes                   | OFF                                 |
| tmp_table_size                  | 33554432                            |
| tmpdir                          |                                     |
| transaction_alloc_block_size    | 8192                                |
| transaction_prealloc_size       | 4096                                |
| tx_isolation                    | REPEATABLE-READ                     |
| updatable_views_with_limit      | YES                                 |
| version                         | 5.0.19-Max                          |
| version_comment                 | MySQL Community Edition - Max (GPL) |
| version_compile_machine         | i686                                |
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
+---------------------------------+-------------------------------------+

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 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

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

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

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.


User Comments
  Posted by Abel Braaksma on October 5, 2006
This SHOW command can also be used in conjunction with WHERE. For example, to show all variables that have a numeric setting higher then zero, you can use:

SHOW VARIABLES WHERE VALUE > 0;

Or, if you would like to see all variables, except the storage engine specific ones, you can use the following expression:

SHOW VARIABLES WHERE Variable_Name NOT LIKE '%myisam%' AND Variable_Name NOT LIKE '%innodb%';
Sign Up Login You must be logged in to post a comment.