#!/usr/bin/env bash
webhook=‘https://oapi.dingtalk.com/robot/send?access_token=xxxx‘
#dt=`date --date=‘5 min ago‘ "+%Y-%m-%d %H:%M:%S"`
dt=`date -d ‘-543 min‘ +"%Y-%m-%dT%H:%M:%SZ"`
notice=`cat /data/sql_logs/err.log`
#配置数据库的连接地址
monitor_db_host="127.0.0.1"
monitor_db_port=3306
monitor_db_user="xxx"
monitor_db_password="xxx"
monitor_db_database="xx"
#实例慢日志位置
slowquery_file=/data/sql_logs
pt_query_digest="/usr/bin/pt-query-digest"

##钉钉告警
sendmsg() { 
        /usr/bin/curl $webhook -H ‘Content-Type: application/json‘ -d          " {‘msgtype‘: ‘markdown‘,
            ‘markdown‘: {
            ‘title‘: ‘google sql-instance‘,
            ‘text‘: ‘#### <font color=#DC143C>instance_id : $hostname </font> \n<font color=#008230>monitor_metric: sql slowlogs</font> \n\n<font color=#DC143C> alert_notice: $notice</font> \n\n<font color=#DC143C> alert_time: $dt</font>‘
           },
           ‘at‘: {
            ‘isAtAll‘: true
           }
          }"
}
#实例连接信息
hostname="xxxx" # 和实例配置内容保持一致，用于做筛选
#gcloud 拉取日志
/usr/bin/gcloud logging read  "resource.type=cloudsql_database AND logName=projects/xxxxx/logs/cloudsql.googleapis.com%xxxxxx-slow.log AND timestamp>=\"$dt\""  --format=json | /snap/bin/jq -r ‘.[].textPayload‘ |/usr/bin/tac >/data/sql_logs/dcfx-nd-prod-slow.log

if [ $? -ne 0 ]; then
    sendmsg
else
echo "gcloud export failed" > /data/sql_logs/err.log
fi

#收集日志
$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=utf8mb4 --filter="\$event->{add_column}=length(\$event->{arg}) and
 \$event->{hostname}=\"$hostname\" and \$event->{client}=
 \$event->{ip}" $slowquery_file/dcfx-nd-prod-slow.log  > $slowquery_file/dcfx-nd-prod-slow_analyze.log

if [ $? -ne 0 ]; then
    sendmsg
else
echo "slowlog analysis failed" > /data/sql_logs/err.log
fi