分为两部分：
第一部分是日常用手动执行脚本，包括troubleshooting
第二部分是放在crontab job里边的定时任务。
一、日常用脚本
使用说明：
脚本执行示例： sh 脚本 IP
client_count.sh 统计客户端数
lock_kill.sh 杀掉locked状态的thread
report.sh 打印出status、variables、engine innodb status并显示稍微重要信息
sqlkill.sh 杀掉超过N秒的查询
slave_status.sh 查看slave列表的状态
#########################################
cat client_count.sh
#!/bin/bash
temp_path=$(dirname "$0")
cd $temp_path
PASSWORD='《password》'
DBNAME='
'
if [ ! -n "$2" ]
then
export PORT=3306
else
export PORT=$2
fi
if [ "$1" = "" ] ; then
echo "请按这种格式使用本脚本: sh ${0} 数据库IP"
echo "例如: sh ${0} 10.19.11.123 "
exit 1
fi
#mysql -u $DBNAME -h $HOST -p -e "show full processlist" |awk '{print $3}'|awk -F':' '{print $1}'|sort|uniq -c
mysql -h$1 -P3306 -u$DBNAME --password=$PASSWORD -e "show full processlist" |awk '{print $3}'|awk -F':' '{print $1}'|sort|uniq -c
###################################################
cat lock_kill.sh
#!/bin/bash
export PASSWORD='《password》'
export DBNAME=''
export HOST=$1
if [ "$1" = "" ] ; then
echo "请按这种格式使用本脚本: sh ${0} 数据库IP"
echo "例如: sh ${0} 10.19.11.123 "
exit 1
fi
if [ ! -n "$2" ]
then
export PORT=3306
else
export PORT=$2
fi
for id in `mysqladmin -u $DBNAME -h $HOST --password=$PASSWORD processlist |grep -i locked |awk '{print $2}'`
do
echo $id
mysqladmin -u $DBNAME -h $HOST --password=$PASSWORD kill $idmysqladmin -u $DBNAME -h $HOST --password=$PASSWORD kill $id
done
############################################
cat report.sh
#!/bin/bash
temp_path=$(dirname "$0")
cd $temp_path
DBNAME=
PASSWORD=《password》
ip=$1
port=3306
if [ "$1" = "" ] ; then
echo "请按这种格式使用本脚本: sh ${0} 数据库IP"
echo "例如: sh ${0} 10.19.11.123 "
exit 1
fi
if [ ! -n "$2" ]
then
export PORT=3306
else
export PORT=$2
fi
mysql -h$1 -P3306 -u$DBNAME --password=$PASSWORD -e 'show global variables;' >$ip-$port.variables
mysql -h$1 -P3306 -u$DBNAME --password=$PASSWORD -e 'show global status;' >$ip-$port.status
#mysql -h$1 -P3306 -u$DBNAME --password=$PASSWORD -e 'show engine innodb status\G' >$ip-$port-innodb.status
Created_tmp_disk_tables=`grep -w Created_tmp_disk_tables $ip-$port.status|awk '{print $2}'`
Created_tmp_tables=`grep -w Created_tmp_tables $ip-$port.status|awk '{print $2}'`
Tmp_disk_percentage=$(echo "scale=4; $Created_tmp_disk_tables / $Created_tmp_tables" | bc)
echo Tmp_disk_percentage 0$Tmp_disk_percentage ----need to less 10%
echo `grep -w Binlog_cache_disk_use $ip-$port.status ` ----target 0
grep Binlog_cache_disk_use $ip-$port.status
grep -w log_bin $ip-$port.variables
grep -w sync_binlog $ip-$port.variables
grep innodb_support_xa $ip-$port.variables
grep -E "max_binlog_size|binlog_cache_size|binlog-do-db|binlog-ingore-db|log_slave_updates|binlog_format" $ip-$port.variables
echo '#############innodb############'
grep -w innodb_flush_log_at_trx_commit $ip-$port.variables
grep -w autocommit $ip-$port.variables
grep -w innodb_buffer_pool_size $ip-$port.variables
grep -w innodb_log_buffer_size $ip-$port.variables
grep innodb_additional_mem_pool_size $ip-$port.variables
grep Innodb_buffer_pool_pages_free $ip-$port.status
grep io_threads $ip-$port.variables
grep srv_master_thread $ip-$port-innodb.status
grep innodb_io_capacity $ip-$port.variables
grep innodb_max_dirty_pages_pct $ip-$port.variables
grep innodb_adaptive_flushing $ip-$port.variables
echo '############# innodb-特性-########'
echo "double write"
grep Innodb_dblwr $ip-$port.status
grep innodb_adaptive_hash_index $ip-$port.variables
grep non-hash $ip-$port-innodb.status
grep innodb_fast_shutdown $ip-$port.variables
grep innodb_force_recovery $ip-$port.variables
grep innodb_log $ip-$port.variables
grep innodb_flush_log_at_trx_commit $ip-$port.variables
########################################
cat sqlkill.sh
#!/bin/bash
#time update 2012-12-14
#ant.li
########
export PASSWORD='《password》'
export DBNAME=''export DBNAME=''
export DBUSE=''
export JKBUSR=jiankongbao
export JKBPASSWORD=《password》
export LOGFILE=/tmp/killlog.`date +%F-%R`
if [ ! -n "$1" ]
then
export HOST=1.1.1.1
else
export HOST=$1
fi
function getid(){
mysql -u $DBNAME -h $HOST --password=$PASSWORD --default-character-set=utf8 -e 'show full processlist;' | grep -i select | gawk
'$6 >= '$SECOND' && $5~/Query/{print $1}'
}
function gettime(){
echo -ne "\033[1;32mYou are going to kill more than how many seconds to execute SQL :\033[0m"
read SECOND
if [[ ! `echo $SECOND | egrep '^[0-9]+$'` || $SECOND -lt 10 ]]
then
echo -e "\033[1;32mWhat also didn't do, have quit\033[0m"
echo -e "\033[1;32mPlease enter the number of more than 10 [ Time > 10s ]\033[0m "
exit
fi
export SECOND
echo -e "\033[1;32mYou are killing the more than $SECOND seconds of SQL,log in /tmp\033[0m "
}
#SHOW SERVER STATUS
echo -e "<<<<<<<<<<<<<<<<<<<\033[1;34m$HOST\033[0m>>>>>>>>>>>>>>>>>>>>"
echo -e "\033[1;32m"
mysql -u $JKBUSR -h $HOST --password=$JKBPASSWORD -e 'show slave status\G;' | egrep 'Seconds_Behind_Master|Running'
echo -e "\033[0m"
echo -e "<<<<<<<<<<<<<<<<<<<\033[1;34m$HOST\033[0m>>>>>>>>>>>>>>>>>>>>"
mysql -u $DBNAME -h $HOST --password=$PASSWORD -e 'show processlist;'
#echo -e `mysql -u $DBNAME -h $HOST --password=$PASSWORD --default-character-set=utf8 -e 'show full processlist;' | gawk '{print
$0}'` >>/tmp/test1
echo -e "\033[1;32m"
#INPU_TTIME
gettime
#GET SQL ID
#ID=${getid}
ID=(`getid`)
###KILL SQL
for id in `echo ${ID[*]}`
do
echo $id
echo "`date +%F-%X`" >>"$LOGFILE"
echo -e `mysql -u $DBNAME -h $HOST --password=$PASSWORD --default-character-set=utf8 -e 'show full processlist;' | gawk
'$1=="'$id'"{print $0}'`>>"$LOGFILE"
echo "<<<<<<<<<<<<<<<<<<<<<<<<<< DB:$1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>" >>"$LOGFILE"
mysql -u $DBNAME -h $HOST --password=$PASSWORD -e "kill $id;"
done
###################################################
[apps@gd6g2s117 dba]$ cat slave_status
cat: slave_status: 没有那个文件或目录
[apps@gd6g2s117 dba]$ cat slave_status.sh
#!/bin/bash
for host in `cat list.txt`
do
echo "***************************************$host**********"
mysql -u root -h $host -p -e "show slave status\G" | egrep "Running|Behind"
donedone
二、crontab job里边的脚本
1、跟昨天的variables对比，有变化则发邮件出来：
[apps@gd6g2s117 sh]$ cat /apps/sh/dba/diff_variables.sh
#!/bin/bash
temp_path=$(dirname "$0")
cd $temp_path
DBNAME=
PASSWORD=《password》
DATE=`date +%Y%m%d`
YESTERDAY=`date -d yesterday +%Y%m%d`
twodayago=`date +%Y%m%d --date="-2 day"`
if [ ! -d $DATE ]; then
mkdir $DATE
fi
for ii in `cat list.txt`;
do
mysql -h$ii -P3306 -u$DBNAME --password=$PASSWORD -e 'show global variables;' | grep -v timestamp >$DATE/$ii.txt
diff $YESTERDAY/$ii.txt $DATE/$ii.txt > log/$ii-$DATE.tmp
if [ -s log/$ii-$DATE.tmp ]; then
echo " -------- 【 $ii 】,its variables is changed below --------" >> log/var_change.log
cat log/$ii-$DATE.tmp >> log/var_change.log
fi
if [ ! -s $DATE/$ii.txt ]; then
rm -rf $DATE/$ii.txt
fi
done
if [ -s log/var_change.log ];then
sed -i /newline/d log/var_change.log
cat log/var_change.log > log/diff-$DATE.log
/apps/sh/tool/sendEmail -f warning_mysql@163.com -t dba@vipshop.com -s smtp.163.com -u "variables is changed" -xu
warning_mysql -xp 《password》 -m "Mysql variables is changed" -a log/diff-$DATE.log
fi
rm -rf $twodayago
find /apps/sh/dba/log -type f -mtime +3 -exec rm {} \;
2、每天慢查询发邮件
[apps@gd6g2s100 ~]$ cat /apps/sh/slow_query.sh
#!/bin/bash
temp_path=$(dirname "$0")
cd $temp_path
DATE=`date +%y%m%d`
MailList=`cat list.txt`
sed -n '/^# Time: '$DATE' 10:/,/^# Time: '$DATE' 12:/p' /apps/logs/mysql/slow3306.log >/apps/logs/mysql/tmp.txt
sleep 1
counter=`grep Query_time /apps/logs/mysql/tmp.txt |wc -l`
Totle_Time=`/apps/sh/tool/percona-toolkit-2.1.8/bin/pt-query-digest /apps/logs/mysql/tmp.txt | grep "Exec time" | head -n 1 | awk
'{print $4}'`
/apps/svr/mysql5/bin/mysqldumpslow -t 30 -s t /apps/logs/mysql/tmp.txt >/apps/logs/mysql/10.19.11.100:3306.txt
sleep 1
his_counter=`sed -n '1p' ./tmp.txt`
his_Time=`sed -n '2p' ./tmp.txt`
/apps/sh/tool/sendEmail -f slow_query@163.com -t $MailList -s smtp.163.com -u "10.19.11.100:3306_slow_query" -xu slow_query -xp
《password》 -m "Today number: $counter,Total_Time $Totle_Time S ; Yesterday number: $his_counter,Total_Time $his_Time S" -a
/apps/logs/mysql/10.19.11.100:3306.txt
echo $counter >./tmp.txt
echo $Totle_Time >>./tmp.txt
3、Threads_connected200则记录下当时的processlist、os summary和mysql summary
[apps@gd6g2s100 perf]$ cat /apps/sh/max_con_status.sh
#!/bin/bash
temp_path=$(dirname "$0")
cd $temp_path
ip=10.19.11.100
port=3306port=3306
DATE_DAY=`date +%Y-%m-%d`
DATE_time=`date +"%Y-%m-%d %H:%M:%S"`
log_dir=/apps/logs/mysql/perf
flag=`ps -ef | grep max_con_status.sh | grep -v grep| wc -l`
echo flag $flag
Threads_connected=`echo "show global status like 'Threads_connected';" |mysql -h $ip -P $port -ujiankongbao
--password='jiankongbaoABCD!@#' | grep Threads_connected | awk '{print $2}'`
if [ $flag -lt 4 ] && [ $Threads_connected -gt 200 ]; then
echo "max connection happend in $DATE_time" >>$log_dir/max_connection.log
echo "select now();show full processlist;" |mysql -h $ip -P $port -ujiankongbao --password='jiankongbaoABCD!@#' >>
$log_dir/processlist.$DATE_DAY
/apps/sh/tool/percona-toolkit-2.1.8/bin/pt-summary >> $log_dir/pt-summary.$DATE_DAY
sleep 1
/apps/sh/tool/percona-toolkit-2.1.8/bin/pt-mysql-summary -- --user=jiankongbao --password='《password》' --host=$ip >>
$log_dir/pt-mysql-summary.$DATE_DAY
sleep 10
/apps/sh/tool/sendEmail -f slow_query@163.com -t $MailList -s smtp.163.com -u "$ip $port connections alarm" -xu slow_query -xp
《password》 -m "Threads_connected is $Threads_connected"
else
exit 0
fi
find $log_dir -type f -name "p*" -mtime +5 -exec rm -f {}
