生产环境有很多模块用的数据库是MySQL,一个模块下用的最多得MySQL达到36台（包含主从，主从均参与读）。为了更好的提升项目系统使用的性能，需要将MySQL的慢日志收集起来，按照模块生成到表里。博主是用的pt-query-digest进行慢日志分析，这个工具可以通过将慢SQL输入到表里，且呈现很直观（这里对该工具不做详细说明）
由于要借助pt分析将慢日志导入到表里，因此需要一个MySQL环境进行存储。对线上的任何分析的前提是：不能对线上有任何影响，故决定将线上这些慢日志统一传输到一台机器上，在目标端进行分析。
#!/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
2、在使用group_concat函数借助union all拼接所有表的时候，最后拼接出来的SQL语句就会多出union all字符串，博主是通过reverse函数将字符串从后往前显示，然后再通过substring进行截取
3、因为慢日志是放在以日期名（yyyy-mm-dd，比如2016-06-22)命名的目录里，故此处是通过正则表达式，查找指定目录下，以yyyy-mm-dd格式命名的所有目录，脚本如下：
关于“如何汇总上百台mysql的慢日志”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
