溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

如何匯總上百臺(tái)mysql的慢日志

發(fā)布時(shí)間：2021-11-08 09:17:25 來源：億速云 閱讀：125 作者：小新 欄目：MySQL數(shù)據(jù)庫(kù)

這篇文章將為大家詳細(xì)講解有關(guān)如何匯總上百臺(tái)mysql的慢日志，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

匯總上百臺(tái)mysql的慢日志

【背景說明】

生產(chǎn)環(huán)境有很多模塊用的數(shù)據(jù)庫(kù)是MySQL,一個(gè)模塊下用的最多得MySQL達(dá)到36臺(tái)（包含主從，主從均參與讀）。為了更好的提升項(xiàng)目系統(tǒng)使用的性能，需要將MySQL的慢日志收集起來，按照模塊生成到表里。博主是用的pt-query-digest進(jìn)行慢日志分析，這個(gè)工具可以通過將慢SQL輸入到表里，且呈現(xiàn)很直觀（這里對(duì)該工具不做詳細(xì)說明）

【思路說明】

由于要借助pt分析將慢日志導(dǎo)入到表里，因此需要一個(gè)MySQL環(huán)境進(jìn)行存儲(chǔ)。對(duì)線上的任何分析的前提是：不能對(duì)線上有任何影響，故決定將線上這些慢日志統(tǒng)一傳輸?shù)揭慌_(tái)機(jī)器上，在目標(biāo)端進(jìn)行分析。

鑒于上面思路，需要考慮的問題：

  • 問題一：配置互信可以不用輸入密碼，但是上百臺(tái)服務(wù)器跟目標(biāo)段配置的話，操作麻煩，且不可控，怎么scp不混淆。

  • 問題二：傳輸?shù)侥繕?biāo)端后，怎么能夠一次性分析完所有慢日志，怎么能夠?qū)⒙罩景凑漳K進(jìn)行匯總。 

 問題一解決辦法：
       使用expect交互式，腳本里面放入目標(biāo)端的密碼，以便進(jìn)行遠(yuǎn)程拷貝文件和遠(yuǎn)程創(chuàng)建目錄；
       遠(yuǎn)程創(chuàng)建目錄規(guī)則是：按照模塊名+日期，文件名重命名為主機(jī)名。
       博主的業(yè)務(wù)模塊分別有g(shù)ms、pos等等，主機(jī)名的命名方式里面含有模塊,簡(jiǎn)單列舉見下圖（圖一 模塊跟主機(jī)名映射關(guān)系）。故遠(yuǎn)程需要?jiǎng)?chuàng)建目錄的腳本如下
           dir=`echo $HOSTNAME | cut -d "-" -f 3`      
           remotedir="/data/slow_log/$dir/$DATE/"
           慢日志重命名為 remotefile=slow_"`hostname`".log"
       這樣就可以達(dá)到不混淆的目的。具體處理方式請(qǐng)大家按照線上需求來定！
 問題二解決辦法
       使用for循環(huán)遍歷慢日志進(jìn)行分析：腳本如下，
           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
       用上面方式可能會(huì)重復(fù)分析慢日志，故在for循環(huán)里面加上判斷機(jī)制：腳本如下，
           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
       按照模塊進(jìn)行匯總，可以通過mysql自帶的函數(shù):concat/group_concat拼接SQL實(shí)現(xiàn)滿足需求的語(yǔ)句,詳請(qǐng)請(qǐng)見腳本中function grather_table()函數(shù)

【具體腳本】

腳本一：遠(yuǎn)程創(chuàng)建目錄，將本地文件scp到指定目錄

點(diǎn)擊(此處)折疊或打開

  1. #!/bin/bash

  2. #注釋：遠(yuǎn)程創(chuàng)建目錄，scp密碼傳輸文件

  3. #Auther:cyt


  5. function remotecommand()

  6. {

  7.       remoteHost=10.240.1.102

  8.       remoteUser=root

  9.       remotePort=22

  10.       #輸入異機(jī)密碼

  11.       passwd=123456

  12.       #在異機(jī)創(chuàng)建跟本地主機(jī)名一樣的目錄

  13.       dir=`echo $HOSTNAME | cut -d "-" -f 3`

  14.       remotedir="/data/slow_log/$dir/$DATE/"

  15.       commands="mkdir -p $remotedir"

  16.       expect -c "

  17.       set timeout -1

  18.       spawn  ssh -p $remotePort $remoteUser@$remoteHost \"$commands\"

  19.       expect {

  20.       \"(yes/no)?\" {

  21.       send \"yes\r\"

  22.       expect \"password:\"

  23.       send \"${passwd}\r\"

  24.       }

  25.       \"password:\" {

  26.       send \"${passwd}\r\"

  27.       }

  28.       }

  29.       expect eof

  30.       "

  31. }



  34. function slow_scp()

  35. {

  36.       local user=root

  37.       local password=123456

  38.       local remotefile=$remotedir"\slow_"`hostname`".log"

  39.       passwd=123456

  40.       slow_log=`mysql -u$user -p$password -NB -e "select VARIABLE_VALUE  from information_schema.GLOBAL_VARIABLES  where Variable_name='slow_query_log_file';"`

  41.       slow_file="`dirname ${slow_log}`/slow.log.${DATE}"

  42.       #將慢日志文件傳輸?shù)疆悪C(jī)

  43.       expect -c "

  44.       set timeout -1

  45.       spawn bash -c \"scp -rp $slow_file   $remoteUser@$remoteHost:$remotefile \"

  46.       expect {

  47.       \"(yes/no)?\" {

  48.       send \"yes\r\"

  49.       expect \"password:\"

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

  51.       }

  52.       \"password:\" {

  53.       send \"${passwd}\r\"

  54.       }

  55.       }

  56.       expect eof

  57.       "

  58. }



  61. function usage(){

  62.       echo "將" $1 "天前的slow.log，傳輸?shù)街付ǚ?wù)器，以便慢日志分析；"

  63.      echo $"Usage: $0  {numer}(指定整數(shù)型) {dirname}(日志輸出目錄請(qǐng)?zhí)顚懡^對(duì)路徑)"

  64.         exit 1

  65. }



  68. function main()

  69. {

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

  71.           usage

  72.       fi

  73.       DATE=`date +%Y%m%d --date="$1 days ago"`

  74.       remotecommand

  75.       slow_scp

  76. }



  79. main $1

腳本二：分析所有的慢日志，并將慢日志按照模塊進(jìn)行匯總，（為了方便給開發(fā)人員查看，故此處將列名變成中文，不需要的列就沒顯示出來，僅供參考）

點(diǎn)擊(此處)折疊或打開

  1. #!/bin/bash

  2. #注釋：匯總上百服務(wù)器的慢日志，按模塊形成一個(gè)表。

  3. #Auther:cyt


  5. #獲取本地內(nèi)網(wǎng)IP地址

  6. function getLocalInnerIP()

  7. {

  8.       ifconfig | grep 'inet addr:' | awk -F"inet addr:" '{print $2}' | awk '{print $1}' | while read theIP; do

  9.           A=$(echo $theIP | cut -d '.' -f1)

  10.           B=$(echo $theIP | cut -d '.' -f2)

  11.           C=$(echo $theIP | cut -d '.' -f3)

  12.           D=$(echo $theIP | cut -d '.' -f4)

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

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

  15.           if [ "${int_ip}" -ge 167772160 -a "${int_ip}" -le 184549375 ]; then

  16.               echo $theIP

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

  18.               echo $theIP

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

  20.               echo $theIP

  21.           fi

  22.       done

  23. }

  24.         

  25.         

  26. #利用存儲(chǔ)過程創(chuàng)建按照日期命名的database，比如20160310 ，則創(chuàng)建slow_20160310

  27. function create_datbase()

  28. {

  29.       local a

  30.       #利用存儲(chǔ)過程創(chuàng)建按照日期命名的database，比如20160310 ，則創(chuàng)建slow_20160310

  31.       mysql -u$user -p$password -e "

  32.       set @a=date_format('"${Date}"','%Y-%m-%d');

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

  34.       select @sqlstr;

  35.       PREPARE DD FROM @sqlstr;EXECUTE DD;"

  36. }

  37.       


  39. #因?yàn)榱闶郗h(huán)境較多，為了便于查看，所有的慢日志存在以 /data/slow_log/[模塊名]/[慢日志生成時(shí)間]/slow_[主機(jī)名].log,比如/data/slow_log/pms/2016-05-11/slow_retail-mysql-pms-slave-01.log

  40. #故所以在/data/slow_log/目錄下按照時(shí)間來查找，以此for循環(huán)；查找慢日志所在的目錄下以日期命名的所有目錄；這樣做是按照日期創(chuàng)建用于分析慢SQL的數(shù)據(jù)庫(kù)名，以便跟別的環(huán)境區(qū)分

  41. function find_all_slow()

  42. {

  43.       local e

  44.       local f

  45.       #下面的正則還可以這樣寫：find /data/slow_log/ -type d | egrep "[0-9]{4}-[0-9]{2}-[0-9]{2}"

  46.        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]'`

  47.            do

  48.                cd $e

  49.                Date=`basename $e`

  50.                local database="slow_${Date}"

  51.                for f in `find $e -name 'slow_retail-mysql-*.log' `

  52.                    do

  53.                       tab="`basename ${f}| xargs |  cut -d '-' -f 3`"

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

  55.                          #調(diào)用創(chuàng)建數(shù)據(jù)庫(kù)的函數(shù)

  56.                          create_datbase

  57.                          tablename="`basename ${f}| xargs |  cut -d '.' -f  1`"

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

  59.                       else

  60.                          echo $tab'模塊的于'$Date'產(chǎn)生的慢日志已分析，如要重新分析，請(qǐng)到DB層刪除相關(guān)表再執(zhí)行該腳本';

  61.                       fi;

  62.                    done

  63.        done

  64. }



  67. #因?yàn)榫€上使用mycat進(jìn)行分庫(kù)分表，故需要將各個(gè)分庫(kù)的慢日志合在一張表里以便我們查看;下面函數(shù)通過concat/group_concat拼接SQL實(shí)現(xiàn)滿足需求的語(yǔ)句

  68. function grather_table()

  69. {

  70.       local i

  71.       local j

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

  73.           do

  74.             echo "開始按模塊合并慢日志";

  75.               for j in `find /data/slow_log/ -name "\`echo $i | cut -d '_' -f 2\`" | cut -d '/' -f 4`

  76.                   do

  77.                     local time=`basename $i`

  78.                     drop_tab_sql="select concat('drop table if exists ',char(96),'"${j}"','_','"${time}"',char(96))"

  79.                     echo $drop_tab_sql | \

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

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

  82.                     #拼接SQL

  83.                     sql="select concat('create table if not exists ',char(96),'"${j}"','_','"${time}"',char(96),'

  84.                            AS select cyt.*

  85.                            from  ',char(40),substring(t1.b,1,char_length(t1.b)-locate(REVERSE('U'),REVERSE(t1.b),1)),char(41),'cyt') ddl

  86.                          from

  87.                          (

  88.                           select replace(group_concat(t0.a),'UNION ALL ,','UNION ALL  ') b

  89.                             from (

  90.                               SELECT

  91.                                   concat('SELECT ',char(96),'CHECKSUM',char(96),' AS ',char(34),'序號(hào)',char(34),

  92.                                           ',',char(96),'SAMPLE',char(96),' AS ',char(34),'SQL語(yǔ)句',char(34),

  93.                                           ',',char(96),'ts_min',char(96),' AS ',char(34),'最早執(zhí)行時(shí)間',char(34),

  94.                                           ',',char(96),'ts_max',char(96),' AS ',char(34),'最晚執(zhí)行時(shí)間',char(34),

  95.                                           ',',char(96),'ts_cnt',char(96),' AS ',char(34),'總共執(zhí)行次數(shù)',char(34),

  96.                                           ',',char(96),'Query_time_sum',char(96),' AS ',char(34),'總查詢時(shí)間',char(34),

  97.                                           ',',char(96),'Query_time_min',char(96),' AS ',char(34),'最小查詢時(shí)間',char(34),

  98.                                           ',',char(96),'Query_time_max',char(96),' AS ',char(34),'最大查詢時(shí)間',char(34),

  99.                                           ',',char(96),'Query_time_pct_95',char(96),' AS ',char(34),'平均查詢時(shí)間',char(34),

  100.                                           ',',char(96),'Query_time_stddev',char(96),' AS ',char(34),'查詢時(shí)間標(biāo)準(zhǔn)差',char(34),

  101.                                           ',',char(96),'Query_time_median',char(96),' AS ',char(34),'查詢時(shí)間中位數(shù)',char(34),

  102.                                           ',',char(96),'Lock_time_sum',char(96),' AS ',char(34),'總鎖定時(shí)間',char(34),

  103.                                           ',',char(96),'Lock_time_min',char(96),' AS ',char(34),'最小鎖定時(shí)間',char(34),

  104.                                           ',',char(96),'Lock_time_max',char(96),' AS ',char(34),'最大鎖定時(shí)間',char(34),

  105.                                           ',',char(96),'Lock_time_pct_95',char(96),' AS ',char(34),'平均鎖定時(shí)間',char(34),

  106.                                           ',',char(96),'Lock_time_stddev',char(96),' AS ',char(34),'鎖定時(shí)間標(biāo)準(zhǔn)差',char(34),

  107.                                           ',',char(96),'Lock_time_median',char(96),' AS ',char(34),'鎖定時(shí)間中位數(shù)',char(34),

  108.                                           ',',char(96),'Rows_sent_sum',char(96),' AS ',char(34),'總返回記錄行數(shù)',char(34),

  109.                                           ',',char(96),'Rows_sent_min',char(96),' AS ',char(34),'最小返回記錄數(shù)',char(34),

  110.                                           ',',char(96),'Rows_sent_max',char(96),' AS ',char(34),'最大返回記錄數(shù)',char(34),

  111.                                           ',',char(96),'Rows_sent_pct_95',char(96),' AS ',char(34),'平均返回記錄數(shù)',char(34),

  112.                                           ',',char(96),'Rows_sent_stddev',char(96),' AS ',char(34),'發(fā)送返回?cái)?shù)標(biāo)準(zhǔn)差',char(34),

  113.                                           ',',char(96),'Rows_sent_median',char(96),' AS ',char(34),'返回記錄數(shù)中位數(shù)',char(34),

  114.                                           ',',char(96),'Rows_examined_sum',char(96),' AS ',char(34),'參加運(yùn)算的記錄總行數(shù)',char(34),

  115.                                           ',',char(96),'Rows_examined_min',char(96),' AS ',char(34),'最少參加運(yùn)算的記錄行數(shù)',char(34),

  116.                                           ',',char(96),'Rows_examined_max',char(96),' AS ',char(34),'最多參加運(yùn)算的記錄行數(shù)',char(34),

  117.                                           ',',char(96),'Rows_examined_pct_95',char(96),' AS ',char(34),'平均參加運(yùn)算的記錄行數(shù)',char(34),

  118.                                           ',',char(96),'Rows_examined_stddev',char(96),' AS ',char(34),'參加運(yùn)算的記錄行數(shù)標(biāo)準(zhǔn)差',char(34),

  119.                                           ',',char(96),'Rows_examined_median',char(96),' AS ',char(34),'參加運(yùn)算的記錄行數(shù)中位數(shù)',char(34),

  120.                                          'FROM ',CHAR(96),'"${i}"',char(96),'.',char(96),table_name,CHAR (96),' UNION ALL '

  121.                                           ) a

  122.                               FROM

  123.                                 information_schema. TABLES

  124.                                  WHERE

  125.                                 TABLE_schema = '"${i}"'

  126.                                 AND table_name LIKE 'slow_retail-mysql-"${j}"%' and table_name not like '"${j}"%' ) t0 ) t1  "

  127.                      #創(chuàng)建慢日志所需的數(shù)據(jù)庫(kù)

  128.                      mysql -u$user -p$password -e "create  database if not  exists  slow;"

  129.                      #調(diào)用拼接SQL，并執(zhí)行該sql

  130.                      s=`echo $sql | \

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

  132.                      if [[ "${s}" != "NULL" ]];then

  133.                        mysql -u$user -p$password -A slow -e "$s";

  134.                        else echo "沒有可以合并的慢日志";

  135.                      fi

  136.                    done

  137.                 #刪除分散的慢日志所記錄的表

  138.                 drop_db_sql="select concat('drop database if exists ',char(96),'"${i}"',char(96))"

  139.                 echo $drop_db_sql | \

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

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

  142.           done

  143. }


  145.                                                             

  146. #主體

  147. function main()

  148. {

  149.       #用于分析慢SQL的數(shù)據(jù)庫(kù)用戶名、密碼

  150.       user='root'

  151.       password='123456'

  152.       #注意在DB層設(shè)置以下參數(shù)

  153.       #mysql -u$user -p$password -e 'set global group_concat_max_len =1000000000000000000 '

  154.       #調(diào)用使用pt-query-digest工具分析慢sql的函數(shù)

  155.       find_all_slow

  156.       #調(diào)用將各個(gè)模塊的分庫(kù)的慢日志分析后的表按照模塊名整合在一起

  157.       grather_table

  158. }



  161. #調(diào)用主體

  162. main

【知識(shí)點(diǎn)補(bǔ)充】

1、拼接SQL的時(shí)候，一定要調(diào)大set global group_concat_max_len =1000000000000000000

2、在使用group_concat函數(shù)借助union all拼接所有表的時(shí)候，最后拼接出來的SQL語(yǔ)句就會(huì)多出union all字符串，博主是通過reverse函數(shù)將字符串從后往前顯示，然后再通過substring進(jìn)行截取 

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

3、因?yàn)槁罩臼欠旁谝匀掌诿▂yyy-mm-dd，比如2016-06-22)命名的目錄里，故此處是通過正則表達(dá)式，查找指定目錄下，以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]'

關(guān)于“如何匯總上百臺(tái)mysql的慢日志”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手