Skip to content

关于慢查询获取不到信息 #83

@yidazhi

Description

@yidazhi

在两台虚拟机上跑脚本,均无法抓取慢日志里的sql记录到数据库,我的目标数据库和archer所在数据库,均有archer用户,并且all privileges;
慢日志中有内容,并且我也会删除last_analysis_time_$hostname这个文件,但还是抓取不到sql,但是很奇怪的抓取到了慢日志中的show语句:SHOW VARIABLES LIKE 'wait_timeout';

还请大神们帮忙看看

我的脚本配置:
#!/bin/bash
DIR="$( cd "$( dirname "$0" )" && pwd )"
cd $DIR

#config monitor database server 收集日志 配置archer数据库的连接地址
monitor_db_host="192.168.1.159"
monitor_db_port=3306
monitor_db_user="archer"
monitor_db_password="111111"
monitor_db_database="archer"

#config mysql server 被监控机
mysql_host="192.168.1.159" # 和archer主库配置保持一致
mysql_port=3306 # 和archer主库配置保持一致

#config slowqury 被监控机慢日志位置
slowquery_file="/data/mysql/slow.log"
pt_query_digest="/usr/local/bin/pt-query-digest"

#config server_id
hostname="${mysql_host}:${mysql_port}" # 用于archer做筛选

#获取上次分析时间,初始化时请删除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

#collect mysql slowquery log into monitor database
#RDS需要增加--no-version-check选项
$pt_query_digest
--user=$monitor_db_user --password=$monitor_db_password --port=$monitor_db_port
--review h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review
--history h=$monitor_db_host,D=$monitor_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

echo date +"%Y-%m-%d %H:%M:%S">last_analysis_time_$hostname

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions