Monitoring MySQL monitoring transaction hanging (RUNNING) alarm notification in Nagios

An exception occurred while submitting the business alarm order, and the page was not responded. After troubleshooting, the transaction was not committed or rolled back. If the operation status of the transaction is monitored in time

An exception occurred while submitting the business alarm order, and the page was not responded. After troubleshooting, the transaction was not committed or rolled back. If the operation status of the transaction is monitored in time

Preface:
An error occurred while submitting the business alarm order. The page was not responded. After troubleshooting, the transaction was not committed or rolled back. I thought that if the operation status of the transaction was monitored in time, in this way, you can troubleshoot the problem in time to facilitate operation and processing. Therefore, you have created a shell script for nagios to handle transaction alerts.

1. Write a transaction monitoring script
#! /Bin/bash
# Author: tim. man
# Version: 1.0
# Desc: check the running transaction over

ST_ OK = 0
ST_WR = 1
ST_CR = 2
ST_UK = 3

TIME_TRX = 10

# Prompt information
Print_help (){
Echo "$ PROGNAME-w INT-c INT"
Echo "Options :"
Echo "-w/-- warning )"
Echo "Sets a warning number"
Echo "-c/-- critical )"
Echo "Sets a critical level for io"
Exit $ ST_UK
}

While test-n "$1"; do
Case "$1" in
-Help |-h)
Print_help
Exit $ ST_UK
;;
-- Warning |-w)
Warning = $2
Shift
;;
-- Critical |-c)
Critical = $2
Shift
;;
*)
Echo "Unknown argument: $1"
Print_help
Exit $ ST_UK
;;
Esac
Shift
Done

Get_wcdiff (){
If [! -Z "$ warning"-! -Z "$ critical"]
Then
Wclvls = 1

If [$ {warning}-gt $ {critical}]
Then
Wcdiff = 1
Fi
Elif [! -Z "$ warning"-a-z "$ critical"]
Then
Wcdiff = 2
Elif [-z "$ warning"-! -Z "$ critical"]
Then
Wcdiff = 3
Fi
}

# Script judgment
Val_wcdiff (){
If ["$ wcdiff" = 1]
Then
Echo "Please adjust your warning/critical thresholds. The warning must be lower than the critical level! "
Exit $ ST_UK
Elif ["$ wcdiff" = 2]
Then
Echo "Please also set a critical value when you want to use warning/critical thresholds! "
Exit $ ST_UK
Elif ["$ wcdiff" = 3]
Then
Echo "Please also set a warning value when you want to use warning/critical thresholds! "
Exit $ ST_UK
Fi
}

Get_wcdiff
Val_wcdiff

# Count the maximum running time of mysql transactions
Max_over_time = '/usr/local/mysql/bin/mysql -- user = nagios -- password = "nagiosq @ xxx"-NS/usr/local/mysql. sock-e "SELECT TIME_TO_SEC (TIMEDIFF (NOW (), t. trx_started) FROM information_schem
A. INNODB_TRX t WHERE TIME_TO_SEC (TIMEDIFF (NOW (), t. trx_started)> $ TIME_TRX order by TIME_TO_SEC (TIMEDIFF (NOW (), t. trx_started) desc limit 1; "| awk '{print $1 }''

# If there is no RUNNING transaction, the value is 0 to avoid the following if judgment errors
If [! -N "$ max_over_time"]; then max_over_time = 0
Fi

# Obtain the number of currently blocked transactions
Num_trx = '/usr/local/mysql/bin/mysql -- user = nagios -- password = "nagiosq @ xxx"-NS/usr/local/mysql. sock-e "select count (1) FROM information_schema.INNODB_TRX t WHERE TIME_TO_SEC (TIMEDIF
F (NOW (), t. trx_started)> $ TIME_TRX; "| awk '{print $1 }''

If [-n "$ warning"-a-n "$ critical"]
Then
If ['expr $ max_over_time \> $ warning '-eq 1-a' expr $ max_over_time \ <$ critical'-eq 1]
Then
Echo "WARNING-$ num_trx transactions running, go over for $ max_over_time seconds"
Exit $ ST_WR
Elif ['expr $ max_over_time \> $ critical '-eq 1]
Then
Echo "CRITICAL-$ num_trx transactions runnning, go over for $ max_over_time seconds"
Exit $ ST_CR
Else
Echo "OK-TRANSACTIONS RAN successfully ."
Exit $ ST_ OK
Fi
Fi