Q. How to find out more information about MySQL server status?
A. The MySQL server maintains many status variables that provide information about its operation. You can see upto date variables lists and explanation of its usages online. For example you can connect to MySQL server and execute SHOW STATUS
$ mysql -u vivek -p
mysql> show status;
Output:
+--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ | Aborted_clients | 0 | | Aborted_connects | 4 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 208711192 | | Bytes_sent | 2331972589 | | Com_admin_commands | 1 | | Com_alter_db | 0 | | Com_alter_table | 0 | .... ..... . | Threads_connected | 1 | | Threads_created | 53 | | Threads_running | 1 | | Uptime | 143182 | +--------------------------------+------------+ 186 rows in set (0.00 sec)
However it takes some time to understand these variables. I recommend using mysqlreport which makes a friendly report of important status variables.
From the project home page:
It makes a friendly report of nearly every status value from SHOW STATUS. Unlike SHOW STATUS which simply dumps over 100 values to screen in one long list, mysqlreport interprets and formats the values and presents the basic values and many more inferred values in a human-readable format. Numerous example reports are available at the mysqlreport web page.
The benefit of mysqlreport is that it allows you to very quickly see a wide array of performance indicators for your MySQL server which would otherwise need to be calculated by hand from all the various SHOW STATUS values
How do I use mysqlreport?
First, download mysqlreport perl script:
$ wget http://hackmysql.com/scripts/mysqlreport
Now, execute mysqlreport
$ ./mysqlreport --user vivek --host localhost --port 3306 --socket /var/lib/mysql/mysql.sock --password
Output:
Password for database user vivek: MySQL 4.1.20 uptime 1 15:51:22 Fri Oct 20 00:39:46 2006 __ Key _________________________________________________________________ Buffer used 2.92M of 500.00M %Used: 0.58 Current 94.22M %Usage: 18.84 Write ratio 0.83 Read ratio 0.01 __ Questions ___________________________________________________________ Total 575.29k 4.01/s Slow 0 0.00/s %Total: 0.00 %DMS: 0.00 DMS 165.78k 1.16/s 28.82 __ Table Locks _________________________________________________________ Waited 5 0.00/s %Total: 0.00 Immediate 174.21k 1.21/s __ Tables ______________________________________________________________ Open 99 of 4000 %Cache: 2.48 Opened 1.82k 0.01/s __ Connections _________________________________________________________ Max used 15 of 400 %Max: 3.75 Total 76.37k 0.53/s __ Created Temp ________________________________________________________ Disk table 6.21k 0.04/s Table 14.24k 0.10/s File 22 0.00/s
=> Download Link
=> Documentation
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 0 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |