#!/bin/bash
#****************************************************************#
# ScriptName:/usr/local/sbin/lepus_slowquery.sh
# Create Date:2016-03-25
#***************************************************************#
# configlepus database server监控机;
lepus_db_host="10.106.33.2"
lepus_db_port=3306
lepus_db_user="lepus_user"
lepus_db_password="123456"
lepus_db_database="lepus"
# configmysql server被监控机;
mysql_client=`which mysql`
mysql_host="10.0.8.134"
mysql_port=3306
mysql_user="mysql_zabbix"
mysql_password="123456"
# configslowqury慢日志位置;
slowquery_dir="/var/lib/mysql/"
slowquery_long_time=1
slowquery_file=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "show variables like 'slow_query_log_file';" | grep log | awk '{print $2}'`
pt_query_digest=`which pt-query-digest`
# config server_id被监控机id从lepus图形界面看;
lepus_server_id=1
# collect mysqlslowquery 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 --history 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_server_id " $slowquery_file 2> /dev/null
# set a new slowquery log;
tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "select concat('$slowquery_dir','slow_',date_format(now(),'%Y%m%d%H'),'.log');" | grep log | sed -n -e '2p'`
# config mysqlslowquery;
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;" 2> /dev/null
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log_file = '$tmp_log';" 2> /dev/null
# delete log before 7days;
cd $slowquery_dir
/usr/bin/find ./ -name 'slow_*' -mtime +7 | xargs rm -rf