MySQL is a very popular database, you can get your site particular setting in /etc/my.cnf, but most of system variable are not shown in the configuration file, how to get list and check their values?

System variable information is also available from the mysqladmin variables command, here just show you how to get it via mysql loging session.

List all MySQL parameters

Partial output is shown here

mysql> show variables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    34
Current database: *** NONE ***

+---------------------------------------------------+---------------------+
| Variable_name                                     | Value               |
+---------------------------------------------------+---------------------+
| auto_increment_increment                          | 1                   |
| auto_increment_offset                             | 1                   |
| autocommit                                        | ON      |
| automatic_sp_privileges                           | ON          |
| back_log                                          | 50                  |
| basedir                                           | /usr                |
| big_tables                                        | OFF                 |
| binlog_cache_size                                 | 32768               |
| binlog_direct_non_transactional_updates           | OFF                 |
| binlog_format                                     | STATEMENT           |
| binlog_stmt_cache_size                            | 32768               |
| bulk_insert_buffer_size                           | 8388608             |
| character_set_client                              | utf8                |
| character_set_connection                          | utf8                |
| character_set_database                            | latin1              |
| character_set_filesystem                          | binary  
...
...

SHOW VARIABLES Syntax

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

SHOW VARIABLES shows the values of MySQL system variables. This statement does not require any privilege. It requires only the ability to connect to the server.


There are two optional modifiers:

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. 
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.

With a LIKE clause

The statement displays only rows for those variables with names that match the pattern. acting like a filter.

To obtain the row for a specific variable, use a LIKE clause as shown:


mysql> SHOW VARIABLES LIKE 'max_join_size';
Connection id:    161
Current database: i381684_jos1

+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.03 sec)

mysql> SHOW SESSION VARIABLES LIKE 'max_join_size';
Connection id:    162
Current database: i381684_jos1

+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.03 sec)

To get a list of variables whose name match a pattern, use the “%” wildcard character in a LIKE clause, Wildcard characters can be used in any position within the pattern to be matched.  “_” is a wildcard that matches any single character, to match the wildcrd caracter, you should escape it like “\_” to match it literally.

mysql> SHOW VARIABLES LIKE '%buffer%';
Connection id:    164
Current database: i381684_jos1

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| bulk_insert_buffer_size      | 8388608   |
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 37748736  |
| innodb_change_buffering      | all       |
| innodb_log_buffer_size       | 8388608   |
| join_buffer_size             | 4194304   |
| key_buffer_size              | 536870912 |
| myisam_sort_buffer_size      | 67108864  |
| net_buffer_length            | 16384     |
| preload_buffer_size          | 32768     |
| read_buffer_size             | 2097152   |
| read_rnd_buffer_size         | 8388608   |
| sort_buffer_size             | 2097152   |
| sql_buffer_result            | OFF       |
+------------------------------+-----------+
14 rows in set (0.03 sec)

With WHERE clause

To show all variables that have a numeric setting higher then zero, you can use:

mysql> SHOW VARIABLES where value='on';
Connection id:    168
Current database: i381684_jos1

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| autocommit                 | ON    |
| automatic_sp_privileges    | ON    |
| delay_key_write            | ON    |
| engine_condition_pushdown  | ON    |
| foreign_key_checks         | ON    |
| innodb_adaptive_flushing   | ON    |
| innodb_adaptive_hash_index | ON    |
...

More complicated example:

SHOW VARIABLES WHERE Variable_Name NOT LIKE '%myisam%' AND Variable_Name NOT LIKE '%innodb%';