13.5.4.27. SHOW VARIABLES Syntax
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.
