php慢日志显示客户端ip,dba+开源工具：图形化显示MySQL慢日志

#!/bin/bash

#改成你的运维管理机MySQL地址(用户权限最好是管理员)

slowquery_db_host="192.168.148.9"

slowquery_db_port="3306"

slowquery_db_user="admin"

slowquery_db_password="123456"

slowquery_db_database="sql_db"

#改成你的生产MySQL主库地址(用户权限最好是管理员)

mysql_client="/usr/local/mysql/bin/mysql"

mysql_host="192.168.148.1"

mysql_port="3306"

mysql_user="admin"

mysql_password="123456"

#改成你的生产MySQL主库慢查询目录和慢查询执行时间(单位秒)

slowquery_dir="/data/mysql/yourDB/slowlog/"

slowquery_long_time=2

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="/usr/local/bin/pt-query-digest"

#改成你的生产MySQL主库server_id

mysql_server_id=270

#collect mysql slowquery log into slowquery database

$pt_query_digest --user=$slowquery_db_user --password=$slowquery_db_password --port=$slowquery_db_port

--review h=$slowquery_db_host,D=$slowquery_db_database,t=mysql_slow_query_review  --history

h=$slowquery_db_host,D=$slowquery_db_database,t=mysql_slow_query_review_history  --no-report

--limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and

\$event->{serverid}=$mysql_server_id " $slowquery_file > /tmp/slowquery_analysis.log

##### set a new slow query log ###########

tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "select

concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`

#config mysql slowquery

$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;"

$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global

slow_query_log_file = '$tmp_log'; "

#delete log before 7 days

cd $slowquery_dir

/usr/bin/find ./ -name 'slowquery_*' -mtime +7|xargs rm -f ;

####END####