#!/bin/bash
#########################################################################################################
# SQL 슬립쿼리 자동 킬 스크립트 #
# - 로그 파일 위치 : /var/log/mysql_autometic_kill.log #
# - 타겟아이디를 지정하는 이유는 ssh 접속 mysql 실행하고 있는 유져가 짤릴수도 있기 때문이다. #
# #
# last updated 2015.08.17 made in san0123a@naver.com #
#########################################################################################################
### 대상 아이디 및 슬립 시간에 따라 자동으로 kill 을 한다 ###############################################
sleep_time="500" # 정리 시간 지정 500초
all_user_target="Y" # 모든 유져 대상 (Y/N)
target_id=( account1 account2 account3 account4 NULL ) # 대상 계정 지정 array
#########################################################################################################
mysql_path="/usr/local/mysql/bin/mysql"
dbpa="mysql루트패스워드"
# pid 생성 중복실행을 방지 ########################################################
if [[ -s $0.pid ]];then exist_pid=`cat $0.pid`
if [[ -z `ps -e|grep "^$exist_pid "` ]];then rm -f $0.pid;exec_confirm="Y"
else exec_confirm="N";echo -e "\e[1;32mShell has already running...\e[0m";fi
else exec_confirm="Y";fi
if [[ $exec_confirm == "Y" ]];then echo $$ > $0.pid
###################################################################################
/bin/rm -f /tmp/kill_list.tmp /tmp/kill_list.sql
find /var/log/ -name 'mysql_autometic_kill.log' -size +20000k -type f -exec cp /dev/null {} \;
for i in `$mysql_path -uroot -p$dbpa mysql -s -N -e "SHOW FULL PROCESSLIST;"|awk '{print $1":"$4":"$5":"$6}'`
do
process_id=`echo $i|cut -d: -f 1`
database=`echo $i|cut -d: -f 2`
command=`echo $i|cut -d: -f 3`
work_times=`echo $i|cut -d: -f 4`
if [[ $all_user_target == "Y" ]];then kill_go="Y"
elif [[ $all_user_target == "N" ]];then kill_go="N"
for i2 in ${target_id[@]};do if [[ $i2 == $database ]];then kill_go="Y";fi;done
fi
if [[ $kill_go == "Y" ]];then
if [[ $work_times -gt $sleep_time ]];then
echo "kill $process_id;" >> /tmp/kill_list.sql
echo " -> $process_id / $database / $work_times" >> /tmp/kill_list.tmp
((kills++))
fi;fi
done
if [[ -e /tmp/kill_list.txt ]];then
stime=`date +"%Y/%m/%d %H:%M"`
echo "$stime // $kills - sleep query kill." >> /var/log/mysql_autometic_kill.log
cat /tmp/kill_list.tmp >> /var/log/mysql_autometic_kill.log;rm -f /tmp/kill_list.tmp
echo "" >> /var/log/mysql_autometic_kill.log
$mysql_path -uroot -p$dbpa < /tmp/kill_list.sql;rm -f /tmp/kill_list.sql
fi
###################################################################################
rm -f $0.pid
fi
# pid 생성 중복실행을 방지 - END ##################################################
PDNS 에서의 추가 방법은 아래와 같다.
BIND – zone file 에서의 추가 방법은 아래와 같다.
아래는 적용 테스트 명령어(NSLOOKUP) 이다.