一、问题描述：

最近有一个数据库实例每天晚上凌晨4点多所有从库都会有延迟的报警，凌晨5点左右就自然恢复。

二、解决办法：

在编写定期延迟追踪脚本之前，走了不少弯路，分别是：1、通过检查主库的binlog，比较凌晨4点与其他时间段是否有大量写入数据，这个怀疑并没有错，但是去监控系统观察了主库与从库的写入情况并没有特别的异常情况，效果不明显。2、使用tcpcollect-master工具抓sql语句，想通过抓取当时所有的sql语句，比较是否有异常情况，按照这个方法去做，效果也不明显，因为基本上没一个时间段的写入和查询基本上是均匀的，没有发现异常情况。

三、最终解决方法：

脚本思路：1、 编写一个脚本在可能出现延迟的时候抓取导致延迟的sql语句。2、将这些sql语句记录下来，并找相应的开发同事检查程序，是否在每天这个点跑了一些脚本处理问题。3、沟通是否能将这样需求打散。

collectBehind.sh

#!/bin/bash

# Name: collectBehind.sh

# Author: qunxing@staff.sina.com.cn

# Version: 1.0

# Date: 2013-08-25

user="myadmin"  #mysql用户名

password="******" #mysql密码

port=3376#端口

mysql="/usr/bin/mysql -S /tmp/mysql${port}.sock -u$user -p$password"  #mysql命令

date=`date '+%Y%m%d%H'` #获取系统时间，主要用于日志

for i in `seq 60` do #考虑到这个脚本是用crontab运行，所以设置循环次数为60次

date_time=`date '+%Y%m%d %H:%M:%S'`  #系统日期和时间（精确到秒），主要用于记录有延迟的具体时间

behind_t=`$mysql -e "show slave status\G;"|grep -i "Seconds_Behind_Master"|awk -F":" '{print $2}'` #获取当前延迟时间

echo -e "$date_time Seconds_Behind_Master:$behind_t" >> /data1/dbatemp/${port}_${date}_behind.log  #以日期时间开头将当前的延迟时间记录下来

if [ $behind_t -gt 10 ];then    #判断延迟时间大于10秒的

echo -e "\n$date_time Seconds_Behind_Master:$behind_t\n" >> /data1/dbatemp/${port}_${date}_processlist.log    #将延迟数与当时的时间记到show processlist抓取的日志文件里面

$mysql -e  "show full processlist;"  >> /data1/dbatemp/${port}_${date}_processlist.log#抓取当时延迟大于10秒的sql并记录到日志里面

fi

sleep 1 #休眠1秒钟，刚刚1秒钟执行一次

done

四、实际例子：

mysql定期延迟追踪脚本_mysql

获取的日志

show_3376_behind.txt


版本升级

随着需求的变化，在新版本中加入了抓包的功能了，由于开发同事看到sql语句之后不能定位到是从那台服务器执行的。

#!/bin/bash

# Name: collectBehind.sh

# Author: qunxing@staff.sina.com.cn

# Version: 2.0

# Date: 2013-10-21

user="****"

password="****"

port=3713

is_dump=1 #是否抓包，1为抓包，其他数字为不抓

mysql="/usr/bin/mysql -S /tmp/mysql${port}.sock -u$user -p$password" date=`date '+%Y%m%d%H'`

for i in `seq 60`

do date_time=`date '+%Y%m%d %H:%M:%S'`

behind_t=`$mysql -e "show slave status\G;"|grep -i "Seconds_Behind_Master"|awk -F":" '{print $2}'`

echo -e "$date_time Seconds_Behind_Master:$behind_t" >> /data1/dbatemp/${port}_${date}_behind.txt

if [ $behind_t -gt 10 ];then  

echo -e "\n$date_time Seconds_Behind_Master:$behind_t\n" >> /data1/dbatemp/${port}_${date}_processlist.txt  

$mysql -e  "show full processlist;"  >> /data1/dbatemp/${port}_${date}_processlist.log  

if [ $is_dump -eq "1" ];then            #判断是否为1，如果是就抓包

echo -e "$date_time\n" >> /data1/dbatemp/${port}_${date}_tcpdump_update.txt            #打印一个时间戳

/etc/dbCluster/extractSQL.pl -i eth1 -p $port -c 100 -t "update" >> /data1/dbatemp/${port}_${date}_tcpdump_update.txt            #抓100包，并将抓到update语句追加到文件

echo -e "$date_time\n" >> /data1/dbatemp/${port}_${date}_tcpdump_insert.txt          

/etc/dbCluster/extractSQL.pl -i eth1 -p $port -c 100 -t "insert" >> /data1/dbatemp/${port}_${date}_tcpdump_insert.txt  

fi

fi

sleep 1

done