如何汇总上百台mysql的慢日志

这篇文章将为大家详细讲解有关如何汇总上百台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到指定目录

点击(此处)折叠或打开

  1. #!/bin/bash

  2. #注释:远程创建目录,scp密码传输文件

  3. #Auther:cyt

  4. function remotecommand()

  5. {

  6.       remoteHost=10.240.1.102

  7.       remoteUser=root

  8.       remotePort=22

  9.       #输入异机密码

  10.       passwd=123456

  11.       #在异机创建跟本地主机名一样的目录

  12.       dir=`echo $HOSTNAME | cut -d “-” -f 3`

  13.       remotedir=”/data/slow_log/$dir/$DATE/”

  14.       commands=”mkdir -p $remotedir”

  15.       expect -c “

  16.       set timeout -1

  17.       spawn  ssh -p $remotePort $remoteUser@$remoteHost \\”$commands\\”

  18.       expect {

  19.       \\”(yes/no)?\\” {

  20.       send \\”yes\\r\\”

  21.       expect \\”password:\\”

  22.       send \\”${passwd}\\r\\”

  23.       }

  24.       \\”password:\\” {

  25.       send \\”${passwd}\\r\\”

  26.       }

  27.       }

  28.       expect eof

  29.       “

  30. }

  31. function slow_scp()

  32. {

  33.       local user=root

  34.       local password=123456

  35.       local remotefile=$remotedir”\\slow_”`hostname`”.log”

  36.       passwd=123456

  37.       slow_log=`mysql -u$user -p$password -NB -e “select VARIABLE_VALUE  from information_schema.GLOBAL_VARIABLES  where Variable_name=’slow_query_log_file’;”`

  38.       slow_file=”`dirname ${slow_log}`/slow.log.${DATE}”

  39.       #将慢日志文件传输到异机

  40.       expect -c “

  41.       set timeout -1

  42.       spawn bash -c \\”scp -rp $slow_file   $remoteUser@$remoteHost:$remotefile \\”

  43.       expect {

  44.       \\”(yes/no)?\\” {

  45.       send \\”yes\\r\\”

  46.       expect \\”password:\\”

  47.       send \\”${passwd}\\r\\”

  48.       }

  49.       \\”password:\\” {

  50.       send \\”${passwd}\\r\\”

  51.       }

  52.       }

  53.       expect eof

  54.       “

  55. }

  56. function usage(){

  57.       echo “将” $1 “天前的slow.log,传输到指定服务器,以便慢日志分析;”

  58.      echo $”Usage: $0  {numer}(指定整数型) {dirname}(日志输出目录请填写绝对路径)”

  59.         exit 1

  60. }

  61. function main()

  62. {

  63.       if [ $# -ne 1 ];then

  64.           usage

  65.       fi

  66.       DATE=`date +%Y%m%d –date=”$1 days ago”`

  67.       remotecommand

  68.       slow_scp

  69. }

  70. main $1

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

点击(此处)折叠或打开

  1. #!/bin/bash

  2. #注释:汇总上百服务器的慢日志,按模块形成一个表。

  3. #Auther:cyt

  4. #获取本地内网IP地址

  5. function getLocalInnerIP()

  6. {

  7.       ifconfig | grep ‘inet addr:’ | awk -F”inet addr:” ‘{print $2}’ | awk ‘{print $1}’ | while read theIP; do

  8.           A=$(echo $theIP | cut -d ‘.’ -f1)

  9.           B=$(echo $theIP | cut -d ‘.’ -f2)

  10.           C=$(echo $theIP | cut -d ‘.’ -f3)

  11.           D=$(echo $theIP | cut -d ‘.’ -f4)

  12.           int_ip=$(($A<<24|$B<<16|$C<<8|$D))

  13.           #10.0.0.0(167772160)~10.255.255.255(184549375)

  14.           if [ “${int_ip}” -ge 167772160 -a “${int_ip}” -le 184549375 ]; then

  15.               echo $theIP

  16.           elif [ “${int_ip}” -ge 2886729728 -a “${int_ip}” -le 2887778303 ]; then     #172.16.0.0(2886729728)~172.31.255.255(2887778303)

  17.               echo $theIP

  18.           elif [ “${int_ip}” -ge 3232235520 -a “${int_ip}” -le 3232301055 ]; then   #192.168.0.0(3232235520)~192.168.255.255(3232301055)

  19.               echo $theIP

  20.           fi

  21.       done

  22. }

  23.         

  24.         

  25. #利用存储过程创建按照日期命名的database,比如20160310 ,则创建slow_20160310

  26. function create_datbase()

  27. {

  28.       local a

  29.       #利用存储过程创建按照日期命名的database,比如20160310 ,则创建slow_20160310

  30.       mysql -u$user -p$password -e “

  31.       set @a=date_format(‘”${Date}”‘,’%Y-%m-%d’);

  32.       set @sqlstr=CONCAT(‘CREATE database  if not exists ‘,char(96),’slow_’,cast(@a as char),char(96));

  33.       select @sqlstr;

  34.       PREPARE DD FROM @sqlstr;EXECUTE DD;”

  35. }

  36.       

  37. #因为零售环境较多,为了便于查看,所有的慢日志存在以 /data/slow_log/[模块名]/[慢日志生成时间]/slow_[主机名].log,比如/data/slow_log/pms/2016-05-11/slow_retail-mysql-pms-slave-01.log

  38. #故所以在/data/slow_log/目录下按照时间来查找,以此for循环;查找慢日志所在的目录下以日期命名的所有目录;这样做是按照日期创建用于分析慢SQL的数据库名,以便跟别的环境区分

  39. function find_all_slow()

  40. {

  41.       local e

  42.       local f

  43.       #下面的正则还可以这样写:find /data/slow_log/ -type d | egrep “[0-9]{4}-[0-9]{2}-[0-9]{2}”

  44.        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]’`

  45.            do

  46.                cd $e

  47.                Date=`basename $e`

  48.                local database=”slow_${Date}”

  49.                for f in `find $e -name ‘slow_retail-mysql-*.log’ `

  50.                    do

  51.                       tab=”`basename ${f}| xargs |  cut -d ‘-‘ -f 3`”

  52.                       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

  53.                          #调用创建数据库的函数

  54.                          create_datbase

  55.                          tablename=”`basename ${f}| xargs |  cut -d ‘.’ -f  1`”

  56.                          pt-query-digest –user=$user –password=$password –no-report –history h=${host},D=${database},t=${tablename} –create-history-table   $f

  57.                       else

  58.                          echo $tab’模块的于’$Date’产生的慢日志已分析,如要重新分析,请到DB层删除相关表再执行该脚本’;

  59.                       fi;

  60.                    done

  61.        done

  62. }

  63. #因为线上使用mycat进行分库分表,故需要将各个分库的慢日志合在一张表里以便我们查看;下面函数通过concat/group_concat拼接SQL实现满足需求的语句

  64. function grather_table()

  65. {

  66.       local i

  67.       local j

  68.       for i in `mysql -u$user -p$password -NB -e “select schema_name from information_schema.schemata where schema_name like ‘slow_%'”`

  69.           do

  70.             echo “开始按模块合并慢日志”;

  71.               for j in `find /data/slow_log/ -name “\\`echo $i | cut -d ‘_’ -f 2\\`” | cut -d ‘/’ -f 4`

  72.                   do

  73.                     local time=`basename $i`

  74.                     drop_tab_sql=”select concat(‘drop table if exists ‘,char(96),'”${j}”‘,’_’,'”${time}”‘,char(96))”

  75.                     echo $drop_tab_sql | \\

  76.                     mysql -u$user -p$password -A ${i} -N | \\

  77.                     mysql -u$user -p$password -A ${i} -N

  78.                     #拼接SQL

  79.                     sql=”select concat(‘create table if not exists ‘,char(96),'”${j}”‘,’_’,'”${time}”‘,char(96),’

  80.                            AS select cyt.*

  81.                            from  ‘,char(40),substring(t1.b,1,char_length(t1.b)-locate(REVERSE(‘U’),REVERSE(t1.b),1)),char(41),’cyt’) ddl

  82.                          from

  83.                          (

  84.                           select replace(group_concat(t0.a),’UNION ALL ,’,’UNION ALL  ‘) b

  85.                             from (

  86.                               SELECT

  87.                                   concat(‘SELECT ‘,char(96),’CHECKSUM’,char(96),’ AS ‘,char(34),’序号’,char(34),

  88.                                           ‘,’,char(96),’SAMPLE’,char(96),’ AS ‘,char(34),’SQL语句’,char(34),

  89.                                           ‘,’,char(96),’ts_min’,char(96),’ AS ‘,char(34),’最早执行时间’,char(34),

  90.                                           ‘,’,char(96),’ts_max’,char(96),’ AS ‘,char(34),’最晚执行时间’,char(34),

  91.                                           ‘,’,char(96),’ts_cnt’,char(96),’ AS ‘,char(34),’总共执行次数’,char(34),

  92.                                           ‘,’,char(96),’Query_time_sum’,char(96),’ AS ‘,char(34),’总查询时间’,char(34),

  93.                                           ‘,’,char(96),’Query_time_min’,char(96),’ AS ‘,char(34),’最小查询时间’,char(34),

  94.                                           ‘,’,char(96),’Query_time_max’,char(96),’ AS ‘,char(34),’最大查询时间’,char(34),

  95.                                           ‘,’,char(96),’Query_time_pct_95′,char(96),’ AS ‘,char(34),’平均查询时间’,char(34),

  96.                                           ‘,’,char(96),’Query_time_stddev’,char(96),’ AS ‘,char(34),’查询时间标准差’,char(34),

  97.                                           ‘,’,char(96),’Query_time_median’,char(96),’ AS ‘,char(34),’查询时间中位数’,char(34),

  98.                                           ‘,’,char(96),’Lock_time_sum’,char(96),’ AS ‘,char(34),’总锁定时间’,char(34),

  99.                                           ‘,’,char(96),’Lock_time_min’,char(96),’ AS ‘,char(34),’最小锁定时间’,char(34),

  100.                                           ‘,’,char(96),’Lock_time_max’,char(96),’ AS ‘,char(34),’最大锁定时间’,char(34),

  101.                                           ‘,’,char(96),’Lock_time_pct_95′,char(96),’ AS ‘,char(34),’平均锁定时间’,char(34),

  102.                                           ‘,’,char(96),’Lock_time_stddev’,char(96),’ AS ‘,char(34),’锁定时间标准差’,char(34),

  103.                                           ‘,’,char(96),’Lock_time_median’,char(96),’ AS ‘,char(34),’锁定时间中位数’,char(34),

  104.                                           ‘,’,char(96),’Rows_sent_sum’,char(96),’ AS ‘,char(34),’总返回记录行数’,char(34),

  105.                                           ‘,’,char(96),’Rows_sent_min’,char(96),’ AS ‘,char(34),’最小返回记录数’,char(34),

  106.                                           ‘,’,char(96),’Rows_sent_max’,char(96),’ AS ‘,char(34),’最大返回记录数’,char(34),

  107.                                           ‘,’,char(96),’Rows_sent_pct_95′,char(96),’ AS ‘,char(34),’平均返回记录数’,char(34),

  108.                                           ‘,’,char(96),’Rows_sent_stddev’,char(96),’ AS ‘,char(34),’发送返回数标准差’,char(34),

  109.                                           ‘,’,char(96),’Rows_sent_median’,char(96),’ AS ‘,char(34),’返回记录数中位数’,char(34),

  110.                                           ‘,’,char(96),’Rows_examined_sum’,char(96),’ AS ‘,char(34),’参加运算的记录总行数’,char(34),

  111.                                           ‘,’,char(96),’Rows_examined_min’,char(96),’ AS ‘,char(34),’最少参加运算的记录行数’,char(34),

  112.                                           ‘,’,char(96),’Rows_examined_max’,char(96),’ AS ‘,char(34),’最多参加运算的记录行数’,char(34),

  113.                                           ‘,’,char(96),’Rows_examined_pct_95′,char(96),’ AS ‘,char(34),’平均参加运算的记录行数’,char(34),

  114.                                           ‘,’,char(96),’Rows_examined_stddev’,char(96),’ AS ‘,char(34),’参加运算的记录行数标准差’,char(34),

  115.                                           ‘,’,char(96),’Rows_examined_median’,char(96),’ AS ‘,char(34),’参加运算的记录行数中位数’,char(34),

  116.                                          ‘FROM ‘,CHAR(96),'”${i}”‘,char(96),’.’,char(96),table_name,CHAR (96),’ UNION ALL ‘

  117.                                           ) a

  118.                               FROM

  119.                                 information_schema. TABLES

  120.                                  WHERE

  121.                                 TABLE_schema = ‘”${i}”‘

  122.                                 AND table_name LIKE ‘slow_retail-mysql-“${j}”%’ and table_name not like ‘”${j}”%’ ) t0 ) t1  ”

  123.                      #创建慢日志所需的数据库

  124.                      mysql -u$user -p$password -e “create  database if not  exists  slow;”

  125.                      #调用拼接SQL,并执行该sql

  126.                      s=`echo $sql | \\

  127.                      mysql -u$user -p$password -A slow -N `

  128.                      if [[ “${s}” != “NULL” ]];then

  129.                        mysql -u$user -p$password -A slow -e “$s”;

  130.                        else echo “没有可以合并的慢日志”;

  131.                      fi

  132.                    done

  133.                 #删除分散的慢日志所记录的表

  134.                 drop_db_sql=”select concat(‘drop database if exists ‘,char(96),'”${i}”‘,char(96))”

  135.                 echo $drop_db_sql | \\

  136.                 mysql -u$user -p$password -N | \\

  137.                 mysql -u$user -p$password -N

  138.           done

  139. }

  140.                                                             

  141. #主体

  142. function main()

  143. {

  144.       #用于分析慢SQL的数据库用户名、密码

  145.       user=’root’

  146.       password=’123456′

  147.       #注意在DB层设置以下参数

  148.       #mysql -u$user -p$password -e ‘set global group_concat_max_len =1000000000000000000 ‘

  149.       #调用使用pt-query-digest工具分析慢sql的函数

  150.       find_all_slow

  151.       #调用将各个模块的分库的慢日志分析后的表按照模块名整合在一起

  152.       grather_table

  153. }

  154. #调用主体

  155. 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的慢日志”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工

【AD】炭云:36元/年/1GB内存/20GB SSD空间/500GB流量/5Gbps端口/KVM/香港/国际线路LUMEN