轉：通過pt-query-digest分析收集MySQL慢查詢日誌到指定位置

http://blog.csdn.net/dbaxiaosa/article/details/51392821
說明：本指令碼是基於開源工具Lepus監控

           需要安裝percona-toolkit工具包

慢查詢時間：主庫1s，只讀庫3s

一臺伺服器上單例項或者多例項都可執行，本例子是單例項和雙例項的環境

可將本指令碼通過OS定時任務執行，便於慢查詢的分析彙總。

#!/bin/bash
#config lepus database server
lepus_db_host="xxx"
lepus_db_port=xxx
lepus_db_user="xxx"
lepus_db_password="xxx"
lepus_db_database="lepus"

#config MySQL server
mysql_client="/usr/bin/mysql"
mysql_host="localhost"
mysql_port=xxx
mysql_user="xxx"
mysql_password="xxx"
mysql_socket="xxx"

#pt-query-digest
pt_query_digest="/usr/local/bin/pt-query-digest"

#mysqlslow
slowquery_long_time=1

#define mysql slowquery dir
if [ -d "/data/lib/mysql" ]; then
    arr_slowdir=("/data/lib/mysql/")
elif [ -d "/data/lib/mysqla/" ]; then
    arr_slowdir=("/data/lib/mysqla/" "/data/lib/mysqlb/")
fi

for (( i=0; i<${#arr_slowdir[@]}; i++ ))
do
    slowquery_dir=${arr_slowdir[i]}
    if [ "$slowquery_dir" = "/data/lib/mysqlb/" ]; then
        mysql_port=3307
        mysql_socket="/data/lib/mysqlb/mysql.sock"
    elif [ "$slowquery_dir" = "/data/lib/mysqla/" ]; then
        mysql_port=3306
        mysql_socket="/data/lib/mysqla/mysql.sock"
    fi
    
    #mysql readonly is on or off
    read_only=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --socket=$mysql_socket -e "show variables like 'read_only'"|grep O|awk '{print $2}'`    
    if [ "$read_only" = "ON" ]; then
        slowquery_long_time=3
    fi
    
    #mysql slowquery file
    slowquery_file=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password  --socket=$mysql_socket -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
    
    #config server_id
    host_ip="`/sbin/ifconfig|grep "inet addr:"|cut -d ":" -f2|awk '{print $1}'|head -1`"
    lepus_server_id=`$mysql_client -h$lepus_db_host -P$lepus_db_port -u$lepus_db_user -p$lepus_db_password -D$lepus_db_database -e "SELECT server_id FROM db_status WHERE host='$host_ip'"|sed 
-n -e '2p'`

    #collect mysql slowquery log into lepus database
    $pt_query_digest --user=$lepus_db_user --password=$lepus_db_password --port=$lepus_db_port --charset=utf8 --review h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review  --histo
ry h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review_history  --no-report --limit=100% --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{serverid}=$lepus_se
rver_id" $slowquery_file > /data/mysqlslow/lepus_slowquery.log   

    ##### set a new slow query log ###########
    tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --socket=$mysql_socket -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H%i'),'
.log');"|grep log|sed -n -e '2p'`

    #config mysql slowquery
    $mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --socket=$mysql_socket -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;"
    $mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --socket=$mysql_socket -e "set global slow_query_log_file = '$tmp_log'; "
 
    #delete log before 5 days
    cd $slowquery_dir
    /usr/bin/find ./ -name 'slowquery_*' -mtime +5|xargs rm -rf
done

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-2130621/，如需轉載，請註明出處，否則將追究法律責任。