Learning MySQL

Checking Server Settings

The SHOW VARIABLES command lists detailed server configuration settings, including things like the server version, paths to the different directories and files used by the server, and maximum concurrent connections. We’ll show only a few of them here; try them on your own server:

mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------------------+
| Variable_name                   | Value                                         |
+---------------------------------+-----------------------------------------------+
| auto_increment_increment        | 1                                             |
| auto_increment_offset           | 1                                             |
| automatic_sp_privileges         | ON                                            |
| back_log                        | 50                                            |
| basedir                         | /                                             |
| binlog_cache_size               | 32768                                         |
| bulk_insert_buffer_size         | 8388608                                       |
| character_set_client            | latin1                                        |
| version_compile_os              | mandriva-linux-gnu                            |
...
| wait_timeout                    | 28800                                         |
+---------------------------------+-----------------------------------------------+
185 rows in set (0.01 sec)

The mysqladmin variables command produces the same result from the command line:

$ mysqladmin --user=root --password=the_mysql_root_password  variables
+---------------------------------+-----------------------------------------------+
| Variable_name                   | Value                                         |
+---------------------------------+-----------------------------------------------+
| auto_increment_increment        | 1                                             |
...
| wait_timeout                    | 28800                                         |
+---------------------------------+-----------------------------------------------+

From the monitor, you can view a subset of the variables by adding a LIKE ...