How to Monitor MySQL Performance in Real-Time with mysqlstat

Overview

mysqlstat is an open‑source command‑line utility for real‑time monitoring and analysis of MySQL 5.7/8.0 servers. It connects to a MySQL instance using the supplied host, port, user and password, then queries performance_schema, information_schema and SHOW ENGINE INNODB STATUS to collect metrics.

Key Features

Live monitoring of QPS (queries per second), TPS (transactions per second) and network bandwidth.

Top‑N SQL statements by execution count.

Top‑N accessed .ibd table files.

Current lock‑blocking SQL and deadlock information.

Detection of duplicate or redundant indexes.

Connection count per client IP.

Per‑table size statistics.

Binlog analysis to identify high‑traffic tables.

Master‑slave replication status and lag.

Architecture

Usage

After making the binary executable, run the tool with the desired options. If no options are supplied, the default view shows live QPS, TPS and bandwidth.

chmod 755 mysqlstat
./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang'

Common command‑line options:

options:
  -h, --help            show this help message and exit
  -H MYSQL_IP, --mysql_ip MYSQL_IP   MySQL IP address
  -P MYSQL_PORT, --mysql_port MYSQL_PORT   MySQL port
  -u MYSQL_USER, --mysql_user MYSQL_USER   MySQL user name
  -p MYSQL_PASSWORD, --mysql_password MYSQL_PASSWORD   MySQL password
  --top N               Show top N SQL statements by execution count
  --io N                Show top N .ibd files by access count
  --lock                Show currently lock‑blocked SQL statements
  --dead                Show deadlock information
  --index               Show duplicate/redundant indexes
  --conn                Show total connection count per client IP
  --tinfo               Show size of each table in the database
  --binlog              Analyze binlog to find tables with high TPS
  --repl                Show master‑slave replication status
  -v, --version         show program's version number and exit

Example commands:

Top 10 SQL statements:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --top 10

Top 10 accessed tables:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --io 10

Show lock‑blocking SQL:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --lock

Show duplicate indexes:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --index

Show connection counts per IP:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --conn

Show per‑table size information:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --tinfo

Show deadlock information:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --dead

Binlog analysis (default range):

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --binlog

Show replication status:

./mysqlstat -H 192.168.198.239 -P 6666 -u admin -p 'hechunyang' --repl

Compatibility and Setup

The tool runs on CentOS 7 and supports MySQL 5.7 and 8.0. MySQL 8.0 uses the caching_sha2_password authentication plugin by default; if authentication fails, switch to mysql_native_password:

CREATE USER 'rd'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL ON *.* TO 'rd'@'%';

Download

Source code and binary releases are available at the following GitHub repository:

https://github.com/hcymysql/mysqlstat