Diagnose MySQL Slow Queries on AlmaLinux: One‑Click Shell Monitoring Scripts

After a late‑night overtime session revealed painfully slow SELECT COUNT(*) queries, the article shows how a DBA can monitor MySQL resource usage on AlmaLinux using standard Linux tools and a custom Bash script.

Environment Overview and Component Relationships

Layer

Host / Container

Role

Monitoring Target

Command Execution Scenario

Host

master

AlmaLinux 8.x

Docker engine, overall system resources

Run commands on the master host shell

Host

node1

AlmaLinux 8.x

Docker engine, overall system resources

Run commands on the node1 host shell

Host

node2

AlmaLinux 8.x

Docker engine, overall system resources

Run commands on the node2 host shell

Container

mysql-master

MySQL primary

MySQL process resource usage

docker exec -it mysql-master bash

then run monitoring commands

Container

mysql-node1

MySQL replica 1

MySQL process resource usage

docker exec -it mysql-node1 bash

Container

mysql-node2

MySQL replica 2

MySQL process resource usage

docker exec -it mysql-node2 bash

Overall Relationship

—

Writes go to the primary, reads go to replicas; all MySQL containers run on the hosts

System‑level monitoring (CPU, memory, I/O) on the host; container‑level metrics inside the containers

ss (or netstat): View network connections and port status

When you suspect MySQL connection overload or replication breakage, use ss (socket statistics) or netstat to list listening ports and active connections.

Installation of ss (recommended) or netstat

Run on the host shell:

<code>sudo yum install -y iproute   # installs ss
# or, if you prefer netstat
sudo yum install -y net-tools</code>

Common ss commands

Command

Description

ss -tunlp

List all listening TCP/UDP ports with associated processes

ss -tunp | grep 3306

Show listeners or connections on MySQL's default port 3306

ss -ant "state ESTABLISHED"

Display all established TCP connections

ss -uap

List all UDP ports and processes

ss -xp

Show extra socket information such as reading/writing processes

Example: Check local MySQL listening and connection count <code># Check if port 3306 is listening ss -tunlp | grep 3306 # Find the host PID of the mysql‑master container docker ps | grep mysql-master # Show processes using that PID ss -p | grep <PID></code> This reveals which IPs are connected to the primary and their connection states (ESTABLISHED, TIME‑WAIT, etc.).

netstat common commands (legacy)

<code># Show all TCP listeners
netstat -tlnp
# Show connections related to port 3306
netstat -antp | grep 3306
# Show all established TCP connections
netstat -ant | grep ESTABLISHED</code>

Command not found troubleshooting : If ss is missing, run sudo yum install -y iproute . If netstat is missing, run sudo yum install -y net-tools .

Shell Monitoring Script: One‑Click Snapshot Collection

After mastering individual commands, you can combine top , iotop , and ss into a simple Bash script that captures a snapshot of both host‑level and container‑level metrics whenever performance anomalies occur.

Script Overview

Create a directory named with the current timestamp to store the snapshot.

Collect the following data: top -b -n 1 – system processes, CPU, memory. iotop -b -n 3 -d 1 – three I/O snapshots, 1 s interval (requires sudo). ss -tunp – current TCP/UDP connections. df -h – disk usage. free -h – memory usage.

Store results under /home/dba/monitor-snapshots/YYYY‑MM‑DD_HHMM/ .

Prepare Directory and Permissions

<code>mkdir -p /home/dba/monitor-snapshots
sudo chown -R dba:dba /home/dba</code>

Write the script (collect_monitor.sh)

<code>#!/bin/bash
# collect_monitor.sh – performance snapshot script
# Environment: AlmaLinux + Docker + MySQL (container name: mysql-master)

# Variables
SNAPSHOT_ROOT="/home/dba/monitor-snapshots"
TIMESTAMP=$(date +"%F_%H%M")
SNAPSHOT_DIR="${SNAPSHOT_ROOT}/${TIMESTAMP}"
CONTAINER_NAME="mysql-master"

# Check required commands
for cmd in top iotop ss df free docker; do
  command -v $cmd >/dev/null 2>&1 || {
    echo "[ERROR] Command not found: $cmd. Install it first."
    case $cmd in
      top)   echo "sudo yum install -y procps-ng";;
      iotop) echo "sudo yum install -y iotop";;
      ss)    echo "sudo yum install -y iproute";;
      df|free) echo "Usually installed with procps-ng";;
      docker) echo "sudo yum install -y docker-ce docker-ce-cli containerd.io";;
    esac
    exit 1
  }
done

# Create snapshot directory
mkdir -p "$SNAPSHOT_DIR" || { echo "[ERROR] Failed to create $SNAPSHOT_DIR"; exit 1; }

# System‑level snapshots
echo "[${TIMESTAMP}] Starting top snapshot"
 top -b -n 1 > "${SNAPSHOT_DIR}/top_${TIMESTAMP}.txt"

 echo "[${TIMESTAMP}] Starting iotop snapshot"
 sudo iotop -b -n 3 -d 1 > "${SNAPSHOT_DIR}/iotop_${TIMESTAMP}.txt"

 echo "[${TIMESTAMP}] Starting ss snapshot"
 ss -tunp > "${SNAPSHOT_DIR}/ss_${TIMESTAMP}.txt"

 echo "[${TIMESTAMP}] Starting df snapshot"
 df -h > "${SNAPSHOT_DIR}/df_${TIMESTAMP}.txt"

 echo "[${TIMESTAMP}] Starting free snapshot"
 free -h > "${SNAPSHOT_DIR}/free_${TIMESTAMP}.txt"

# Container‑level snapshots
if docker ps | grep -q "$CONTAINER_NAME"; then
  echo "[${TIMESTAMP}] Starting container top snapshot"
  docker exec $CONTAINER_NAME top -b -n 1 > "${SNAPSHOT_DIR}/container_top_${TIMESTAMP}.txt"
  echo "[${TIMESTAMP}] Starting container df snapshot"
  docker exec $CONTAINER_NAME df -h > "${SNAPSHOT_DIR}/container_df_${TIMESTAMP}.txt"
  echo "[${TIMESTAMP}] Starting container free snapshot"
  docker exec $CONTAINER_NAME free -h > "${SNAPSHOT_DIR}/container_free_${TIMESTAMP}.txt"
else
  echo "[WARNING] Container $CONTAINER_NAME not running, skipping container snapshots"
fi

echo "[${TIMESTAMP}] Snapshot collection completed, directory: $SNAPSHOT_DIR"
exit 0</code>

Make the script executable and run it

<code>chmod +x collect_monitor.sh
./collect_monitor.sh</code>

Verify that a timestamped directory appears under /home/dba/monitor-snapshots containing the snapshot files.

Schedule the Script with crontab

On each host (master, node1, node2) edit the crontab:

<code>crontab -e   # choose vim or nano
# Run at the top of every hour
0 * * * * /home/dba/monitor-snapshots/collect_monitor.sh >> /home/dba/monitor-snapshots/cron_collect.log 2>&1 &</code>

Check the schedule with crontab -l . For testing, replace the schedule with * * * * * to run every minute.

Practical Cases

Case 1 – CPU spike on the primary host

Alert from application monitoring shows MySQL response time increase.

Log into the master host and locate the snapshot directory closest to the alert time.

Inspect top_*.txt – look for high %CPU usage by mysqld .

Check iotop_*.txt for heavy I/O by the MySQL process.

Review ss_*.txt for a large number of ESTABLISHED connections on port 3306.

Examine df_*.txt and free_*.txt for disk or memory pressure.

Compare with container snapshots ( container_top_*.txt , etc.) to see the MySQL process’s view inside the container.

These snapshots let you quickly decide whether the bottleneck is system‑level CPU contention, MySQL‑internal load, I/O saturation, or connection overload.

Case 2 – Replication lag on a replica due to network jitter

Monitoring reports a sudden increase in replica lag on node1 .

SSH into the node1 host and open the relevant snapshot directory.

In ss_*.txt , search for the master’s IP (e.g., 192.168.1.10:3306 ) and look for many TIME‑WAIT entries, indicating frequent reconnects.

Check iotop_*.txt for spikes in I/O caused by repeated relay‑log writes.

Enter the MySQL container and run SHOW SLAVE STATUS\G to verify Slave_IO_Running and Slave_SQL_Running status and any Last_IO_Error such as “Lost connection to master”.

This workflow pinpoints whether the issue is network instability, I/O overload, or a stopped replication thread.

Interactive Question : If you have doubts about iotop permissions, ss socket filtering, or any other part of the monitoring process, feel free to leave a comment and we’ll discuss it further.