mysql show open%_MySQL show status - show open database connections

MySQL "show status" FAQ: Can you demonstrate how to use the MySQL show statuscommand to show MySQL variables and status information, such as the number of open MySQL connections?

I don't have a whole lot of time today to give this a detailed discussion, but here is a quick look at some MySQL work that I did recently to show MySQL open database connections.

MySQL show status - 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

Finally, here are two quick links to MySQL status pages:

I hope this brief look at the MySQL SHOW STATUS command has been helpful. As you can see, you can show information about a lot of MySQL status information, including the number of MySQL database connections, MySQL processes, and much more.