For deadlocks, MySQL does not provide direct variables to represent.For performance_shcema after version 5.5, it can provide detailed information about locks (but we are still 5.0), and for Innodb_lock_monitor, the monitor that comes with InnoDB, its output is always output to the error log, which is inconvenient for comparison.

I use zabbix for monitoring, and use agent to transmit data to zabbix server passively.The script is a shell, redirected by show innodb status

Core code:

Check for new deadlock information:

  New_deadlock() {

new_line_tile=$(grep-n "LATEST DETECTED DEADLOCK" $1 | cut-d ':'-f 1)

new_line_time=$(echo "$new_line_tile + 2" | bc)

last_dect_time="$(head-n $new_line_time $1 | tail-n 1) ###Intercept the timestamp of deadlock occurrence

[-e $2 ] || cp $1 $2 #Take this output information and compare it with the last output information; if it is the first detection, use this output information cp as the last output information

old_line_tile=$(grep-n "LATEST DETECTED DEADLOCK" $2 | cut-d ':'-f 1)

if [-z $old_line_tile ];then

 echo 1

 mv $1   $2   ##Determine whether the last output was a deadlock, if not, return 1 directly to indicate that the last time was a new deadlock.and rename this output

 exit 1

  else      ##Otherwise compare the timestamps twice

 old_line_time=$(echo "$old_line_tile + 2" | bc)  

 old_last_dect_time="$(head-n $old_line_time  $2 | tail-n 1)"

 mv $1   $2 #Output information to prepare for the next test

 if [ "$last_dect_time"="$old_last_dect_time" ];then

    echo 0

 else

    cp $1/tmp/$1_detail #It has been determined to be deadlock, and the crime information needs to be retained

    echo 1

 fi

  fi

}

deadlock_check() {

  case $1 in

  1)

$MYSQL_BIN-u $user-p$password  -S $SOC1-e "show engine innodb statusG" >/tmp/innodb_status_1_$dthm

have_dead_lock=$(grep-c  "LATEST DETECTED DEADLOCK"/tmp/innodb_status_1_$dthm)

#Determine whether this detection contains deadlock information, if so, let New_dead_lock do deadlock comparison, otherwise return 0

if [ $have_dead_lock-gt 0 ];then

  New_deadlock/tmp/innodb_status_1_$dthm/tmp/innodb_status_1_$dt

else

  echo 0

fi;;

 2)

$MYSQL_BIN-u $user-p$password  -S $SOC2-e "show engine innodb statusG" >/tmp/innodb_status_2_$dthm

have_dead_lock=$(grep-c  "LATEST DETECTED DEADLOCK"/tmp/innodb_status_2_$dthm)

if [ $have_dead_lock-gt 0 ];then

  New_deadlock/tmp/innodb_status_2_$dthm/tmp/innodb_status_2_$dt

else

  echo 0

fi;;

 3)

$MYSQL_BIN-u $user-p$password  -S $SOC3-e "show engine innodb statusG" >/tmp/innodb_status_3_$dthm

have_dead_lock=$(grep-c  "LATEST DETECTED DEADLOCK"/tmp/innodb_status_3_$dthm)

if [ $have_dead_lock-gt 0 ];then

  New_deadlock  /tmp/innodb_status_3_$dthm/tmp/innodb_status_3_$dt

else

  echo 0

fi;;

 4)

$MYSQL_BIN-u $user-p$password  -S $SOC4-e "show engine innodb statusG" >/tmp/innodb_status_4_$dthm

have_dead_lock=$(grep-c  "LATEST DETECTED DEADLOCK"/tmp/innodb_status_4_$dthm)

if [ $have_dead_lock-gt 0 ];then

  New_deadlock/tmp/innodb_status_4_$dthm/tmp/innodb_status_4_$dt

else

  echo 0

fi;;

 *)

echo $ERROR

exit 1;;

 esac

}

case  The variable in $1 is for the case of multiple instances on different machines, to transmit the socket parameter.

This article is from the blog "Technology Achieves Dreams", please keep this source http://weipengfei.blog.51cto.com/1511707/1180828