Get MySQL status information from the command line ~ Tech Blog

This post shows how to get some MySQL status information from the command line on a Linux/Unix machine using either the mysqladmin command or the mysql init script. Information returned includes the version, connection type, socket file location, uptime and some other stats.

Using mysqladmin

Using mysqladmin is the only way to get the same sort of information across distros. The example provided in the "using the init script" section below works on Debian based distros and may or may not yield the same information for other distros. For example, on Red Hat / CentOS based distros the init script will only return the pid number and whether MySQL is running.

To get a basic set of stats run this:

mysqladmin -u root -p status

This will show something like this:

Uptime: 1588357  Threads: 1  Questions: 52438664  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 384  Queries per second avg: 33.014

To get more comprehensive information run this:

mysqladmin -u root -p version

which will show something like this:

Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.22, for redhat-linux-gnu on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.22
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 18 days 9 hours 14 min 17 sec

Threads: 1  Questions: 52441939  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 384  Queries per second avg: 33.014

These first two examples are from MySQL running on a CentOS 5 server.

Using the init script

The init script at /etc/init.d/mysql on Debian based distros will return the same sort of status information as "mysqladmin status" when passed the "status" parameter. Run this:

sudo /etc/init.d/mysql status

This will show something like this:

/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a-24
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 202 days 18 hours 35 min 10 sec

Threads: 1  Questions: 93883881  Slow queries: 26  Opens: 145610  Flush tables: 1  Open tables: 256  Queries per second avg: 5.359.

This example is from a Debian 5 server.

Using the init script instead of mysqladmin means it can be run as the root user or using sudo without needing to know the mysql root user's password.

Remember this init script example on a Red Hat or CentOS based system will not show the same information but instead just that MySQL is running along with the pid number.

Tech Blog

Monday, 10 September 2018