mysql slowlog定期做日志切割并采用pt-query-digest分析，将数据汇报都指定数据库中供开发查看优化

直接上代码

#!/bin/bash 
# 用于切割日志文件 账户必须有超级权限
supuser='root'
suppwds='111111'
# 汇总分析结果的数据库
localuserip='127.0.0.1'
localuser='backup'
localpwds='123456'
localdbname='digestresult'

time=`date +%Y%m%d%H%M%S`
# 分析前先将之前的分析结果表重命名 
newtablename=$"query_review_${time}"
renametablenamesql="ALTER TABLE query_review RENAME TO ${newtablename};";
mysql -h${localuserip} -u${localuser} -p${localpwds} ${localdbname} -e "${renametablenamesql}"
if [ $? -eq 0 ]; then
    echo "rename success"
else
    echo "rename fail"
fi
# 将日志文件mv到新的文件并重新指定mysql的slowlog新文件路径
logpath='/var/lib/mysql/103-53-slow.log'
baklogpath="/var/lib/mysql/slow_${time}.bak.log"
echo "baklogpath:${baklogpath}"

mv ${logpath} ${baklogpath}

if [ $? -eq 0 ]; then
    echo "mv success"
else
    echo "mv fail"
fi

mysql -u$supuser -p$suppwds -e "set global slow_query_log_file='${logpath}'"

if [ $? -eq 0 ]; then
    echo "set success"
else
    echo "set fail"
fi
# 使用pt-query-digest分析并将结果上报到指定数据库内 参数可以参考官方文档
pt-query-digest --user=${localuser} --password=${localpwds} --history h=${localuserip},D=${localdbname},t=query_review --create-review-table ${baklogpath}

if [ $? -eq 0 ]; then
    echo "pt-query-digest success"
else
    echo "pt-query-digest fail"
fi