这篇文章将为大家详细讲解有关如何汇总上百台mysql的慢日志，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
汇总上百台mysql的慢日志
【背景说明】
生产环境有很多模块用的数据库是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]'
关于“如何汇总上百台mysql的慢日志”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。