MySQL慢日志客户端安装

安装Percona-Server-shared-56

wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.38-83.0/binary/redhat/7/x86_64/Percona-Server-shared-56-5.6.38-rel83.0.el7.x86_64.rpm
rpm -ivh Percona-Server-shared-56-5.6.38-rel83.0.el7.x86_64.rpm
yum -y install perl-DBD-MySQL perl-Digest-MD5

到官网下载percona-toolkit

https://www.percona.com/software/database-tools/percona-toolkit

安装percona-toolkit

rpm -ivh percona-toolkit-3.2.1-1.el7.x86_64.rpm

编辑文件

#!/bin/bash

DIR="$( cd "$( dirname "$0"  )" && pwd  )"

echo "${DIR}"
cd ${DIR}

#配置数据库的连接地址
db_host="xxx.xxx.xxx.xxx"
db_port=3306
db_user="xxxx"
db_password="xxxxxx"
db_database="slowquery"

#被分析实例的慢日志位置,建议定期清理日志文件,否则会影响分析效率
slowquery_file="/var/lib/mysql/slow.log"

#pt-query-digest可执行文件路径
pt_query_digest="/usr/bin/pt-query-digest"

#被分析实例的连接信息
hostname="xxx.xxx.xxx.xxx:3306" # 需要和实例配置中的内容保持一致,用于筛选,配置错误会导致数据无法展示

#获取上次分析时间,初始化时请删除last_analysis_time_$hostname文件,可分析全部日志数据
if [[ -s last_analysis_time_${hostname} ]]; then
    last_analysis_time=`cat last_analysis_time_${hostname}`
else
    last_analysis_time='1000-01-01 00:00:00'
fi

#收集日志
#RDS需要增加--no-version-check选项
${pt_query_digest} \
--user=${db_user} --password=${db_password} --port=${db_port} \
--review h=${db_host},D=${db_database},t=mysql_slow_query_review  \
--history h=${db_host},D=${db_database},t=mysql_slow_query_review_history  \
--no-report --limit=100% --charset=utf8 \
--since "$last_analysis_time" \
--filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$hostname\"  and \$event->{client}=\$event->{ip} " \
${slowquery_file} > /tmp/analysis_slow_query.log

if [[ $? -ne 0 ]]; then
    echo "failed"
else
    echo `date +"%Y-%m-%d %H:%M:%S"`>last_analysis_time_${hostname}
fi

编辑crontab

0 1 * * *  >/var/lib/mysql/logs/slow.log > /dev/null 2>&1
*/1 * * * * timeout 30 /bin/bash /root/slow_query.sh >/dev/null 2>&1

找一台MySQL,创建库slowquery,然后导入表

https://github.com/hhyo/Archery/blob/master/src/init_sql/mysql_slow_query_review.sql

results matching ""

    No results matching ""