溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關如何匯總上百臺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') dd3、因為慢日志是放在以日期名（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的慢日志”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
