Databases 2 min read

Using ChatGPT to Generate a Shell Script for MySQL Performance Monitoring

This article shares a concise 10‑line Bash script, generated by ChatGPT, that captures MySQL QPS, TPS, and connection counts, timestamps each record, and appends the metrics to a log file for easy performance monitoring.

Advanced AI Application Practice

Mar 29, 2023

Using ChatGPT to Generate a Shell Script for MySQL Performance Monitoring

Inspired by a Zhihu question, the author asked ChatGPT to produce a short shell script for monitoring MySQL database performance. ChatGPT returned a 10‑line Bash script that gathers key metrics and writes them to a log.

The script works as follows:

#!/bin/bash
# Get current timestamp
TIMESTAMP=`date +%s`
# Get MySQL QPS, TPS, and connection count
QPS=`mysqladmin -uroot -ppassword status | awk '{print $5}'`
TPS=`mysqladmin -uroot -ppassword status | awk '{print $8}'`
CONNECTIONS=`mysqladmin -uroot -ppassword status | awk '{print $4}'`
# Append results to log file
echo "$TIMESTAMP,$QPS,$TPS,$CONNECTIONS" >> /var/log/mysql_perf.log

The script uses mysqladmin to query the server status, extracts the fifth, eighth, and fourth fields for QPS, TPS, and connections respectively, and records them together with a Unix timestamp. The output is appended to /var/log/mysql_perf.log, creating a simple time‑series log of performance data.

The author notes that such a script is convenient for technical users and suggests trying it out.