0 like 0 dislike
88 views

Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (13.4k points)  

Display MySQL parameters

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%';
0 like 0 dislike
answered by (13.4k points)  

MySQL ‘show status’ and open database connections

You can show MySQL open database connections (and other MySQL database parameters) using the MySQL show status command, like this:

mysql> show status like 'Conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 8     | 
+---------------+-------+
1 row in set (0.00 sec)


mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     | 
+--------------------------+-------+
7 rows in set (0.00 sec)

All those rows and values that are printed out correspond to MySQL variables that you can look at. Notice that I use like 'Conn%'in the first example to show variables that look like "Connection", then got a little wiser in my second MySQL show status query.

MySQL show processlist

Here's what my MySQL processlist looks like when I had my Java application actively running under Tomcat:

mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

And here's what it looked like after I shut Tomcat down:

mysql> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
|  3 | root | localhost | webapp | Query   |    0 | NULL  | show processlist | 
+----+------+-----------+--------+---------+------+-------+------------------+
1 row in set (0.00 sec)

As a final note, you can also look at some MySQL variables using the mysqladmin command at the Unix/Linux command line, like this:

$ mysqladmin status

Uptime: 4661  Threads: 1  Questions: 200  Slow queries: 0  Opens: 16  Flush
tables: 1  Open tables: 6  Queries per second avg: 0.043

MySQL show status - Summary

Server Status Variables
The MySQL server maintains many status variables that provide information about its operation. You can view these variables and their values by using the SHOW [GLOBAL | SESSION] STATUS statement (see Section 13.7.6.35, “SHOW STATUS Syntax”). The optional GLOBAL keyword aggregates the values over all connections, and SESSION shows the values for the current connection.


mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 0          |
| Aborted_connects                  | 0          |
| Bytes_received                    | 155372598  |
| Bytes_sent                        | 1176560426 |
...
| Connections                       | 30023      |
| Created_tmp_disk_tables           | 0          |
| Created_tmp_files                 | 3          |
| Created_tmp_tables                | 2          |
...
| Threads_created                   | 217        |
| Threads_running                   | 88         |
| Uptime                            | 1389872    |
+-----------------------------------+------------+

SHOW STATUS Syntax

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

Related questions

0 like 0 dislike
1 answer
asked Mar 31, 2019 in MySQL Database Forum by nijamutheen j (13.4k points)  
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...