A snapshot monitor for MySQL

I recently created a snapshop monitor for Mysql.

It gives nice snapshots every 15 minutes of Mysql (server) activities.

It is a useful tool for finding problems especially from night run as you can go back in time. 

The output consists of:

1 Memory stats of the server

2 Number of SQL queries since last snap

3 Processes running when snap is taken

A snapshot of the snapshop monitor output:

Fri Aug 19 03:31:10 CEST 2022              total       used       free     shared    buffers     cached Mem:         64383      63785        597          2          0      45306 -/+ buffers/cache:      18478      45905 Swap:        32767         75      32692 SQL queries since last snap 1011240 Id User Host db Command Time State Info 36790 KalleAnka toossedwvetl3-i:52859 TRANSFORM Query 1461 Sending data create temporary table LKLP_Selection (primary key(VBELN, POSNR)) as (\n selec 36798 KalleAnka toossedwvetl3-i:52868 RAW Query 4193 updating DELETE FROM TRANSFORM.Finance_Pricing WHERE Valid_From_Date >=  36848 KalleAnka toossedwvetl3-i:52930 TRANSFORM Query 56 copy to tmp table ALTER TABLE PTD_DTR_OL ADD INDEX (KUNNR, VKORG) 36967 manager %:53112 TRANSFORM Query 124 Creating sort index INSERT INTO TRANSFORM.Logistics_MaterialMovements_RollingQuantity_Periodic(\nSELECT\nA.W 36977 KalleAnka toossedwvetl3-i:53122 TRANSFORM Query 4175 Sending data INSERT INTO TRANSFORM.Sourcing_PurchaseOrders\nSELECT\nA.AFNAM as  36992 KalleAnka toossedwvetl3-i:53137 TRANSFORM Query 4170 Sending data INSERT INTO PPV_INVOICED_Temp (\n\t SELECT \n\t A.BELNR as  38395 KalleAnka toossedwvetl3-i:55708 TRANSFORM Query 2857 Sending data INSERT INTO TRANSFORM.PKBridgeMaterialCost  38554 KalleAnka toossedwvetl3-i:55976 ACTADW Query 2449 executing LOAD DATA LOCAL INFILE 'AFRU.CSV' replace INTO TABLE ZDTSQRYAFRU FIELDS B 38566 KalleAnka toossedwvetl3-i:55981 A…

 The bash code:

#!/bin/bash mv /home/tooljn/mymonlog/0mymon.txt "/home/tooljn/mymonlog/$(date '+%Y%m%d%H%M')-mymon.txt" find /home/tooljn/mymonlog -type f  -mtime +32 -delete while true do    date >> /home/tooljn/mymonlog/0mymon.txt    free -m >> /home/tooljn/mymonlog/0mymon.txt    mysql -e "show global status like 'Queries'" | grep Queries >> /home/tooljn/mymonlog/mymonQ.txt    topl="$(head -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    botl="$(tail -n 1 /home/tooljn/mymonlog/mymonQ.txt | grep -oE '[^[:space:]]+$')"    queries=$((botl-topl))    echo "SQL queries since last snap $queries" >> /home/tooljn/mymonlog/0mymon.txt    (mapfile -n 2 <  /home/tooljn/mymonlog/mymonQ.txt;    if ((${#MAPFILE[@]}>1)); then     echo "This file has more than 1 line."    fi)    mysql -e "show processlist" | sed '/Sleep/d'  | sed '/root/d' |  sed -e "s/[[:space:]]\+/ /g" | cut -c -150  >> /home/tooljn/mymonlog/0mymon.txt    echo "- - - - - - - - - - - - - - - - - - - - - - \n" >> /home/tooljn/mymonlog/0mymon.txt    sleep 15m done