Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to collect hundreds of slow logs of mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Summarize the slow logs of hundreds of mysql [background note]

The database used by many modules in the production environment is MySQL, and a maximum of 36 MySQL are used under one module (including master and slave, both master and slave participate in reading). In order to better improve the performance of the project system, the slow logs of MySQL need to be collected and generated into the table according to the module. Bloggers use pt-query-digest for slow log analysis. This tool can be used to input slow SQL into the table, and the presentation is very intuitive (this tool is not detailed here)

[train of thought]

Because slow logs are to be imported into the table with the help of pt analysis, a MySQL environment is needed for storage. The premise of any online analysis is that it can not have any impact on the online, so it is decided to transfer these slow logs online to a single machine for analysis on the target side.

In view of the above ideas, the following questions need to be considered:

Question 1: you don't have to enter a password to configure mutual trust, but if hundreds of servers are configured with the target segment, the operation is troublesome and uncontrollable, so how can scp not be confused?

Question 2: after being transferred to the target end, how can we analyze all the slow logs at once, and how can we summarize the slow logs according to the module?

The first solution to the problem: using expect interactive, put the password of the target side in the script to remotely copy files and create directories remotely; the rule of remote directory creation is: according to the module name + date, the file name is renamed to the host name. The blogger's business modules include gms, pos, etc. The naming method of the host name contains modules, which are briefly listed in the following figure (figure 1, the mapping relationship between the module and the host name). Therefore, the remote script that needs to create a directory is as follows: dir= `echo $HOSTNAME | cut-d "-"-f 3` remotedir= "/ data/slow_log/$dir/$DATE/" slow log is renamed to remotefile=slow_ "`hostname`" .log "so as to avoid confusion. The specific way to deal with please according to the online demand to decide! The second solution to the problem is analyzed by for cycling through slow logs: the script is as follows: 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 may repeatedly analyze slow logs in the above way, so add a judgment mechanism to the for loop: the script is as follows: 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 is summarized by module. You can use the function included in mysql: concat/group_concat stitching SQL to achieve statements that meet the requirements. For more information, please see the function grather_table () function in the script [specific script].

Script 1: create a directory remotely and scp the local files to the specified directory

Click (here) to collapse or open

#! / bin/bash

# Note: create directories remotely and transfer files with scp password

# Auther:cyt

Function remotecommand ()

{

RemoteHost=10.240.1.102

RemoteUser=root

RemotePort=22

# enter a different computer password

Passwd=123456

# create a directory with the same host name on a different machine

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}"

# transfer slow log files to different machines

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 "transfers the slow.log of" $1 "days ago to the designated server for slow log analysis;"

Echo $"Usage: $0 {numer} (specify integer type) {dirname} (log output directory please fill in the absolute path)"

Exit 1

}

Function main ()

{

If [$#-ne 1]; then

Usage

Fi

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

Remotecommand

Slow_scp

}

Main $1

Script 2: analyze all the slow logs and summarize the slow logs according to the module. (in order to make it easy for developers to view, the column names are changed into Chinese here, and the unnecessary columns are not displayed for reference only.)

Click (here) to collapse or open

#! / bin/bash

# Note: summarize the slow logs of hundreds of servers and form a table by module.

# Auther:cyt

# obtain the local private network IP address

Function getLocalInnerIP ()

{

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

Average $(echo $theIP | cut-d'.'- F1)

Bounded $(echo $theIP | cut-d'.'- f2)

Paid $(echo $theIP | cut-d'.'- f3)

Dumped $(echo $theIP | cut-d'.'- f4)

Int_ip=$ ($A

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.