MySQL Longquery Email로 받기

MySQL Long 쿼리를 메일로 Alert 하여 내부 공유 하는 Script

[okayjava@oradb ~]$ cat /home/okayjava/bin/mysql_longquery_detector.sh

#!/bin/bash

#MySQL Long Query Detector Made by Okayjava

ymd=`date +"%Y%m%d"`

hm=`date +"%H%M"`

pid=`echo $$`

mailfile=/tmp/mysql_slowlog_$pid.txt

now_hm=`date +"%H%M"`

logfile=/www/com/log/batch/long_query_$ymd.log

while [ $hm -eq $now_hm ] 

do

#date  >> $logfile

/usr/bin/mysql -urcs -prcs123 -h192.168.1.250 << EOF  > $mailfile

use tvideo;

DROP TEMPORARY TABLE IF exists tmp_processlist;

CREATE TEMPORARY TABLE tmp_processlist

(

  SEQ bigint unsigned NOT NULL  AUTO_INCREMENT,

  ID bigint NOT NULL DEFAULT '0',

  USER varchar(16) NOT NULL DEFAULT '',

  HOST varchar(64) NOT NULL DEFAULT '',

  DB varchar(64) DEFAULT NULL,

  COMMAND varchar(16) NOT NULL DEFAULT '',

  TIME int NOT NULL DEFAULT '0',

  STATE varchar(64) DEFAULT NULL,

  INFO longtext,

  primary key (SEQ)

) engine='MyISAM' ;

# Between 을 이용하여, 수행 시간을 조절 한다.

insert into tmp_processlist ( ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO )

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO

FROM   INFORMATION_SCHEMA.PROCESSLIST

WHERE  info IS NOT NULL 

 AND    INFO    NOT LIKE '%PROCESSLIST%'

 AND    (TIME BETWEEN 10 AND 20 

                 OR TIME BETWEEN 600 AND 610

                 OR TIME > 1800

                 )

;

set @maxleng = 1500000;

set @curdate = sysdate();

SELECT concat('\r\n user | ', user, '\r\n Host : ', Host,'\r\n DB : ', db, '\r\n esp. sec : ',time ,'\r\n', max(mmsStr ) ) as msg

FROM (

        SELECT *

        , Case When @curSeq <> curSeq Then

                        @str := concat ( '\r\n\r\n=======\r\n',SEQ ,'-', ID,'-',@curdate ,'\r\n=========\r\n' , info) 

                Else

                        @str := concat (@str ,'\r\n\=======\r\n',  SEQ ,'-', ID,'-',@curdate ,'\r\n=========\r\n' , info ) 

                End as mmsStr

        , @curSeq := curSeq

        FROM (

                        SELECT  SEQ,ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 

                                , Case When @curSum + length(info) > @maxleng Then

                                                @curSeq := @curSeq + 1

                                        Else 

                                                @curSeq

                                        End as curSeq

                                , Case When @curSum + length(info) > @maxleng Then

                                                @curSum := length(info) 

                                        Else 

                                                @curSum := @curSum + length(info) 

                                        End as curSum

                        FROM tmp_processlist 

                        , (SELECT  @curSum := 0, @curSeq := 1 ) r

        ) as a 

         , (SELECT  @str := '' , @curSeq ) r

) fff

GROUP BY curSeq

HAVING max(length(mmsStr));

DROP TEMPORARY TABLE IF exists tmp_processlist;

EOF

if [ -s $mailfile ]

then

        cat $mailfile | /bin/mail -s "250 MySQL Server Long Query Detector" email1@aa.com,email2@aa.com -c cc1@aa.com, cc2@aa.com

fi

#rm $mailfile

sleep 10

now_hm=`date +"%H%M"`

done