汇总上百台mysql的慢日志

最新推荐文章于 2021-02-18 01:27:04 发布

cuanjian2673

最新推荐文章于 2021-02-18 01:27:04 发布

阅读量136

点赞数

汇总上百台mysql的慢日志

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

【背景说明】

生产环境有很多模块用的数据库是MySQL,一个模块下用的最多得MySQL达到36台（包含主从，主从均参与读）。为了更好的提升项目系统使用的性能，需要将MySQL的慢日志收集起来，按照模块生成到表里。博主是用的pt-query-digest进行慢日志分析，这个工具可以通过将慢SQL输入到表里，且呈现很直观（这里对该工具不做详细说明）

【思路说明】

由于要借助pt分析将慢日志导入到表里，因此需要一个MySQL环境进行存储。对线上的任何分析的前提是：不能对线上有任何影响，故决定将线上这些慢日志统一传输到一台机器上，在目标端进行分析。

鉴于上面思路，需要考虑的问题：

问题一：配置互信可以不用输入密码，但是上百台服务器跟目标段配置的话，操作麻烦，且不可控，怎么scp不混淆。
问题二：传输到目标端后，怎么能够一次性分析完所有慢日志，怎么能够将慢日志按照模块进行汇总。

 问题一解决办法：
       使用expect交互式，脚本里面放入目标端的密码，以便进行远程拷贝文件和远程创建目录；
       远程创建目录规则是：按照模块名+日期，文件名重命名为主机名。
       博主的业务模块分别有gms、pos等等，主机名的命名方式里面含有模块,简单列举见下图（图一 模块跟主机名映射关系）。故远程需要创建目录的脚本如下
           dir=`echo $HOSTNAME | cut -d "-" -f 3`      
           remotedir="/data/slow_log/$dir/$DATE/"
           慢日志重命名为 remotefile=slow_"`hostname`".log"
       这样就可以达到不混淆的目的。具体处理方式请大家按照线上需求来定！

 问题二解决办法
       使用for循环遍历慢日志进行分析：脚本如下，
           for e in `find /data/slow_log/ -type d -name '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'`
               do ......
           done
       用上面方式可能会重复分析慢日志，故在for循环里面加上判断机制：脚本如下，
           if [[ `mysql -u$user -p$password -NB -e "select count(*) from information_schema.tables where table_name like '"${tab}"%"${Date}"' and table_schema='slow'"` -le 0 ]] && [[ -n "${Date}" ]];then
       按照模块进行汇总，可以通过mysql自带的函数:concat/group_concat拼接SQL实现满足需求的语句,详请请见脚本中function grather_table()函数

【具体脚本】

脚本一：远程创建目录，将本地文件scp到指定目录

点击(此处)折叠或打开

#!/bin/bash
#注释：远程创建目录，scp密码传输文件
#Auther:cyt
function remotecommand()
{
remoteHost=10.240.1.102
remoteUser=root
remotePort=22
#输入异机密码
passwd=123456
#在异机创建跟本地主机名一样的目录
dir=`echo $HOSTNAME | cut -d "-" -f 3`
remotedir="/data/slow_log/$dir/$DATE/"
commands="mkdir -p $remotedir"
expect -c "
set timeout -1
spawn ssh -p $remotePort $remoteUser@$remoteHost \"$commands\"
expect {
\"(yes/no)?\" {
send \"yes\r\"
expect \"password:\"
send \"${passwd}\r\"
}
\"password:\" {
send \"${passwd}\r\"
}
}
expect eof
"
}
function slow_scp()
{
local user=root
local password=123456
local remotefile=$remotedir"\slow_"`hostname`".log"
passwd=123456
slow_log=`mysql -u$user -p$password -NB -e "select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where Variable_name='slow_query_log_file';"`
slow_file="`dirname ${slow_log}`/slow.log.${DATE}"
#将慢日志文件传输到异机
expect -c "
set timeout -1
spawn bash -c \"scp -rp $slow_file $remoteUser@$remoteHost:$remotefile \"
expect {
\"(yes/no)?\" {
send \"yes\r\"
expect \"password:\"
send \"${passwd}\r\"
}
\"password:\" {
send \"${passwd}\r\"
}
}
expect eof
"
}
function usage(){
echo "将" $1 "天前的slow.log，传输到指定服务器，以便慢日志分析；"
echo $"Usage: $0 {numer}(指定整数型) {dirname}(日志输出目录请填写绝对路径)"
exit 1
}
function main()
{
if [ $# -ne 1 ];then
usage
fi
DATE=`date +%Y%m%d --date="$1 days ago"`
remotecommand
slow_scp
}
main $1

脚本二：分析所有的慢日志，并将慢日志按照模块进行汇总，（为了方便给开发人员查看，故此处将列名变成中文，不需要的列就没显示出来，仅供参考）

点击(此处)折叠或打开

#!/bin/bash
#注释：汇总上百服务器的慢日志，按模块形成一个表。
#Auther:cyt
#获取本地内网IP地址
function getLocalInnerIP()
{
ifconfig | grep 'inet addr:' | awk -F"inet addr:" '{print $2}' | awk '{print $1}' | while read theIP; do
A=$(echo $theIP | cut -d '.' -f1)
B=$(echo $theIP | cut -d '.' -f2)
C=$(echo $theIP | cut -d '.' -f3)
D=$(echo $theIP | cut -d '.' -f4)
int_ip=$(($A<<24|$B<<16|$C<<8|$D))
#10.0.0.0(167772160)~10.255.255.255(184549375)
if [ "${int_ip}" -ge 167772160 -a "${int_ip}" -le 184549375 ]; then
echo $theIP
elif [ "${int_ip}" -ge 2886729728 -a "${int_ip}" -le 2887778303 ]; then #172.16.0.0(2886729728)~172.31.255.255(2887778303)
echo $theIP
elif [ "${int_ip}" -ge 3232235520 -a "${int_ip}" -le 3232301055 ]; then #192.168.0.0(3232235520)~192.168.255.255(3232301055)
echo $theIP
fi
done
}
#利用存储过程创建按照日期命名的database，比如20160310 ，则创建slow_20160310
function create_datbase()
{
local a
#利用存储过程创建按照日期命名的database，比如20160310 ，则创建slow_20160310
mysql -u$user -p$password -e "
set @a=date_format('"${Date}"','%Y-%m-%d');
set @sqlstr=CONCAT('CREATE database if not exists ',char(96),'slow_',cast(@a as char),char(96));
select @sqlstr;
PREPARE DD FROM @sqlstr;EXECUTE DD;"
}
#因为零售环境较多，为了便于查看，所有的慢日志存在以 /data/slow_log/[模块名]/[慢日志生成时间]/slow_[主机名].log,比如/data/slow_log/pms/2016-05-11/slow_retail-mysql-pms-slave-01.log
#故所以在/data/slow_log/目录下按照时间来查找，以此for循环；查找慢日志所在的目录下以日期命名的所有目录；这样做是按照日期创建用于分析慢SQL的数据库名，以便跟别的环境区分
function find_all_slow()
{
local e
local f
#下面的正则还可以这样写：find /data/slow_log/ -type d | egrep "[0-9]{4}-[0-9]{2}-[0-9]{2}"
for e in `find /data/slow_log/ -type d -name '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'`
do
cd $e
Date=`basename $e`
local database="slow_${Date}"
for f in `find $e -name 'slow_retail-mysql-*.log' `
do
tab="`basename ${f}| xargs | cut -d '-' -f 3`"
if [[ `mysql -u$user -p$password -NB -e "select count(*) from information_schema.tables where table_name like '"${tab}"%"${Date}"' and table_schema='slow'"` -le 0 ]] && [[ -n "${Date}" ]];then
#调用创建数据库的函数
create_datbase
tablename="`basename ${f}| xargs | cut -d '.' -f 1`"
pt-query-digest --user=$user --password=$password --no-report --history h=${host},D=${database},t=${tablename} --create-history-table $f
else
echo $tab'模块的于'$Date'产生的慢日志已分析，如要重新分析，请到DB层删除相关表再执行该脚本';
fi;
done
done
}
#因为线上使用mycat进行分库分表，故需要将各个分库的慢日志合在一张表里以便我们查看;下面函数通过concat/group_concat拼接SQL实现满足需求的语句
function grather_table()
{
local i
local j
for i in `mysql -u$user -p$password -NB -e "select schema_name from information_schema.schemata where schema_name like 'slow_%'"`
do
echo "开始按模块合并慢日志";
for j in `find /data/slow_log/ -name "\`echo $i | cut -d '_' -f 2\`" | cut -d '/' -f 4`
do
local time=`basename $i`
drop_tab_sql="select concat('drop table if exists ',char(96),'"${j}"','_','"${time}"',char(96))"
echo $drop_tab_sql | \
mysql -u$user -p$password -A ${i} -N | \
mysql -u$user -p$password -A ${i} -N
#拼接SQL
sql="select concat('create table if not exists ',char(96),'"${j}"','_','"${time}"',char(96),'
AS select cyt.*
from ',char(40),substring(t1.b,1,char_length(t1.b)-locate(REVERSE('U'),REVERSE(t1.b),1)),char(41),'cyt') ddl
from
(
select replace(group_concat(t0.a),'UNION ALL ,','UNION ALL ') b
from (
SELECT
concat('SELECT ',char(96),'CHECKSUM',char(96),' AS ',char(34),'序号',char(34),
',',char(96),'SAMPLE',char(96),' AS ',char(34),'SQL语句',char(34),
',',char(96),'ts_min',char(96),' AS ',char(34),'最早执行时间',char(34),
',',char(96),'ts_max',char(96),' AS ',char(34),'最晚执行时间',char(34),
',',char(96),'ts_cnt',char(96),' AS ',char(34),'总共执行次数',char(34),
',',char(96),'Query_time_sum',char(96),' AS ',char(34),'总查询时间',char(34),
',',char(96),'Query_time_min',char(96),' AS ',char(34),'最小查询时间',char(34),
',',char(96),'Query_time_max',char(96),' AS ',char(34),'最大查询时间',char(34),
',',char(96),'Query_time_pct_95',char(96),' AS ',char(34),'平均查询时间',char(34),
',',char(96),'Query_time_stddev',char(96),' AS ',char(34),'查询时间标准差',char(34),
',',char(96),'Query_time_median',char(96),' AS ',char(34),'查询时间中位数',char(34),
',',char(96),'Lock_time_sum',char(96),' AS ',char(34),'总锁定时间',char(34),
',',char(96),'Lock_time_min',char(96),' AS ',char(34),'最小锁定时间',char(34),
',',char(96),'Lock_time_max',char(96),' AS ',char(34),'最大锁定时间',char(34),
',',char(96),'Lock_time_pct_95',char(96),' AS ',char(34),'平均锁定时间',char(34),
',',char(96),'Lock_time_stddev',char(96),' AS ',char(34),'锁定时间标准差',char(34),
',',char(96),'Lock_time_median',char(96),' AS ',char(34),'锁定时间中位数',char(34),
',',char(96),'Rows_sent_sum',char(96),' AS ',char(34),'总返回记录行数',char(34),
',',char(96),'Rows_sent_min',char(96),' AS ',char(34),'最小返回记录数',char(34),
',',char(96),'Rows_sent_max',char(96),' AS ',char(34),'最大返回记录数',char(34),
',',char(96),'Rows_sent_pct_95',char(96),' AS ',char(34),'平均返回记录数',char(34),
',',char(96),'Rows_sent_stddev',char(96),' AS ',char(34),'发送返回数标准差',char(34),
',',char(96),'Rows_sent_median',char(96),' AS ',char(34),'返回记录数中位数',char(34),
',',char(96),'Rows_examined_sum',char(96),' AS ',char(34),'参加运算的记录总行数',char(34),
',',char(96),'Rows_examined_min',char(96),' AS ',char(34),'最少参加运算的记录行数',char(34),
',',char(96),'Rows_examined_max',char(96),' AS ',char(34),'最多参加运算的记录行数',char(34),
',',char(96),'Rows_examined_pct_95',char(96),' AS ',char(34),'平均参加运算的记录行数',char(34),
',',char(96),'Rows_examined_stddev',char(96),' AS ',char(34),'参加运算的记录行数标准差',char(34),
',',char(96),'Rows_examined_median',char(96),' AS ',char(34),'参加运算的记录行数中位数',char(34),
'FROM ',CHAR(96),'"${i}"',char(96),'.',char(96),table_name,CHAR (96),' UNION ALL '
) a
FROM
information_schema. TABLES
WHERE
TABLE_schema = '"${i}"'
AND table_name LIKE 'slow_retail-mysql-"${j}"%' and table_name not like '"${j}"%' ) t0 ) t1 "
#创建慢日志所需的数据库
mysql -u$user -p$password -e "create database if not exists slow;"
#调用拼接SQL，并执行该sql
s=`echo $sql | \
mysql -u$user -p$password -A slow -N `
if [[ "${s}" != "NULL" ]];then
mysql -u$user -p$password -A slow -e "$s";
else echo "没有可以合并的慢日志";
fi
done
#删除分散的慢日志所记录的表
drop_db_sql="select concat('drop database if exists ',char(96),'"${i}"',char(96))"
echo $drop_db_sql | \
mysql -u$user -p$password -N | \
mysql -u$user -p$password -N
done
}
#主体
function main()
{
#用于分析慢SQL的数据库用户名、密码
user='root'
password='123456'
#注意在DB层设置以下参数
#mysql -u$user -p$password -e 'set global group_concat_max_len =1000000000000000000 '
#调用使用pt-query-digest工具分析慢sql的函数
find_all_slow
#调用将各个模块的分库的慢日志分析后的表按照模块名整合在一起
grather_table
}
#调用主体
main

【知识点补充】

1、拼接SQL的时候，一定要调大set global group_concat_max_len =1000000000000000000

2、在使用group_concat函数借助union all拼接所有表的时候，最后拼接出来的SQL语句就会多出union all字符串，博主是通过reverse函数将字符串从后往前显示，然后再通过substring进行截取

 substring(t1.b,1,char_length(t1.b)-locate(REVERSE('U'),REVERSE(t1.b),1)),char(41),'cyt') dd

3、因为慢日志是放在以日期名（yyyy-mm-dd，比如2016-06-22)命名的目录里，故此处是通过正则表达式，查找指定目录下，以yyyy-mm-dd格式命名的所有目录，脚本如下：

 find /data/slow_log/ -type d -name '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324175/viewspace-2120860/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324175/viewspace-2120860/

cuanjian2673

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论