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

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

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

2. Add script monitoring on the nagios Client
Test the script first.
[Root @ wgq_idc_dbm_3_61 binlog] #/usr/local/nagios/libexec/check_trx-w 30-c 60
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
OK-TRANSACTIONS RAN successfully.
[Root @ wgq_idc_dbm_3_61 binlog] #
Add monitoring command in nrpe. cfg
[Root @ wgq_idc_dbm_3_61 binlog] # vim/usr/local/nagios/etc/nrpe. cfg
Command [check_mysql_trx] =/usr/local/nagios/libexec/check_trx-w 30-c 60

Then restart the nagios client monitoring and service nrpe restart

4. Add configuration options on the main monitoring server of nagios
Check on the nagios server first
[Root @ localhost etc] #/usr/local/nagios/libexec/check_nrpe-H10.254.3.61-c check_mysql_trx
OK-TRANSACTIONS RAN successfully.
[Root @ localhost etc] #

Add the transaction monitoring option in services. cfg:
Define service {
Host_name mysqlserver
Service_description Check mysql transctions
Check_command check_nrpe! Check_mysql_trx
Max_check_attempts 5
Check_command check_nrpe! Check_mysql_trx
Max_check_attempts 5
Normal_check_interval 3
Retry_check_interval 2
Check_period 24x7
Icationication_interval 10
Icationication_period 24x7
Notification_options w, u, c, r
Contact_groups opsweb
}

Add the transaction monitoring command in commands. cnf:
# Add by tim. man on 20141201
Define command {
Command_name check_mysql_trx
Command_line $ USER1 $/check_mysql_trx-w $ ARG1 $-c $ ARG2 $
}

You do not need to reconfigure the alarm function because the alarm function of the email SMS has been added.

Then reload nagios
[Root @ localhost objects] # service nagios reload
Running configuration check...
Reloading nagios configuration...
Done
[Root @ localhost objects] #

5. Go to the main monitoring page of nagios to view the monitoring results.

Normal Monitoring results:

Severe monitoring results:

Configure Mrtg in Ubuntu to monitor Nginx and server system resources

Use snmp + Mrtg to monitor Linux systems

Mrtg server construction (Network Traffic Monitoring)

Network Monitor Nagios Overview

Nagios construction and Configuration

Build a Nagios monitoring platform in the Nginx Environment

Configure the basic Nagios System on RHEL5.3 (using Nagios-3.1.2)

CentOS 5.5 + Nginx + Nagios monitoring and control terminal installation and Configuration Guide

Install Nagios Core for Ubuntu 13.10 Server

Nagios details: click here
Nagios: click here

This article permanently updates the link address: