如何汇总上百台mysql的慢日志
发布时间：2021-12-23 11:51:07 所属栏目：MySql教程 来源：互联网
导读：这篇文章将为大家详细讲解有关如何汇总上百台mysql的慢日志，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 汇总上百台mysql的慢日志 【背景说明】 生产环境有很多模块用的数据库是MySQL,一个模块下用的最多得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到指定目录 点击(此处)折叠或打开 #!/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 "yesr" 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 "yesr" 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') 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的慢日志”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。 （编辑：广州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
