MySQL Reference Manual :: 13.5.4.19 SHOW VARIABLES Syntax

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

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

The GLOBAL and SESSION options are new in MySQL 4.0.3. With GLOBAL, you get the values that are used for new connections to MySQL. With SESSION, you get the values that are in effect for the current connection. If you use neither option, the default is SESSION. LOCAL is a synonym for SESSION.

If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts or at runtime with the SET statement. See Section 5.3.1, “mysqld Command-Line Options” and Section 13.5.3, “SET Syntax”.

Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in See Section 5.3.3, “Server System Variables”. Information about tuning them is provided in Section 7.5.2, “Tuning Server Parameters”.

mysql> SHOW VARIABLES;
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------|
| back_log                        | 50                           |
| basedir                         | /usr/local/mysql             |
| bdb_cache_size                  | 8388572                      |
| bdb_log_buffer_size             | 32768                        |
| bdb_home                        | /usr/local/mysql             |
...
| 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                         |
...
| timezone                        | EEST                         |
| tmp_table_size                  | 33554432                     |
| tmpdir                          | /tmp/:/mnt/hd2/tmp/          |
| version                         | 4.0.4-beta                   |
| wait_timeout                    | 28800                        |
+---------------------------------+------------------------------+

With a LIKE clause, the statement displays only those variables that match the pattern:

mysql> SHOW VARIABLES LIKE 'have%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| have_bdb           | YES      |
| have_innodb        | YES      |
| have_isam          | YES      |
| have_raid          | NO       |
| have_symlink       | DISABLED |
| have_openssl       | YES      |
| have_query_cache   | YES      |
+--------------------+----------+