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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.