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

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. 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.

If the default system variable values are unsuitable, you can set them using command options when mysqld starts, and most can be changed at runtime with the SET statement. See Section 5.2.3, “Using System Variables”, and Section 13.5.3, “SET Syntax”.

Partial output is shown here. The list of names and values may be different for your server. Section 5.2.2, “Server System Variables”, describes the meaning of each variable, and Section 7.5.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                         | /home/jon/bin/mysql-5.1/  |
| binlog_cache_size               | 32768                     |
| bulk_insert_buffer_size         | 8388608                   |
| character_set_client            | latin1                    |
| character_set_connection        | latin1                    |
...
| max_user_connections            | 0                         |
| max_write_lock_count            | 4294967295                |
| multi_range_count               | 256                       |
| myisam_data_pointer_size        | 6                         |
| myisam_max_sort_file_size       | 2147483647                |
| myisam_recover_options          | OFF                       |
| myisam_repair_threads           | 1                         |
| myisam_sort_buffer_size         | 8388608                   |
| ndb_autoincrement_prefetch_sz   | 32                        |
| ndb_cache_check_time            | 0                         |
| ndb_force_send                  | ON                        |
...
| 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.1.6-alpha-log           |
| version_comment                 | Source distribution       |
| version_compile_machine         | i686                      |
| version_compile_os              | suse-linux                |
| wait_timeout                    | 28800                     |
+---------------------------------+---------------------------+

With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, 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.