千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-11-24 作者：千家信息网编辑
千家信息网最后更新 2024年11月24日，这篇文章将为大家详细讲解有关如何汇总上百台mysql的慢日志，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。汇总上百台mysql的慢日志【背景说明】生产环境有很多模块
千家信息网最后更新 2024年11月24日如何汇总上百台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


  5. function remotecommand()

  6. {

  7. remoteHost=10.240.1.102

  8. remoteUser=root

  9. remotePort=22

  10. #输入异机密码

  11. passwd=123456

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

  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. #将慢日志文件传输到异机

  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，传输到指定服务器，以便慢日志分析；"

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

  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

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

点击(此处)折叠或打开

  1. #!/bin/bash

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

  3. #Auther:cyt


  5. #获取本地内网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. }



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

  27. function create_datbase()

  28. {

  29. local a

  30. #利用存储过程创建按照日期命名的database，比如20160310 ，则创建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. }



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

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

  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. #调用创建数据库的函数

  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'产生的慢日志已分析，如要重新分析，请到DB层删除相关表再执行该脚本';

  61. fi;

  62. done

  63. done

  64. }



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

  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),'序号',char(34),

  92. ',',char(96),'SAMPLE',char(96),' AS ',char(34),'SQL语句',char(34),

  93. ',',char(96),'ts_min',char(96),' AS ',char(34),'最早执行时间',char(34),

  94. ',',char(96),'ts_max',char(96),' AS ',char(34),'最晚执行时间',char(34),

  95. ',',char(96),'ts_cnt',char(96),' AS ',char(34),'总共执行次数',char(34),

  96. ',',char(96),'Query_time_sum',char(96),' AS ',char(34),'总查询时间',char(34),

  97. ',',char(96),'Query_time_min',char(96),' AS ',char(34),'最小查询时间',char(34),

  98. ',',char(96),'Query_time_max',char(96),' AS ',char(34),'最大查询时间',char(34),

  99. ',',char(96),'Query_time_pct_95',char(96),' AS ',char(34),'平均查询时间',char(34),

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

  101. ',',char(96),'Query_time_median',char(96),' AS ',char(34),'查询时间中位数',char(34),

  102. ',',char(96),'Lock_time_sum',char(96),' AS ',char(34),'总锁定时间',char(34),

  103. ',',char(96),'Lock_time_min',char(96),' AS ',char(34),'最小锁定时间',char(34),

  104. ',',char(96),'Lock_time_max',char(96),' AS ',char(34),'最大锁定时间',char(34),

  105. ',',char(96),'Lock_time_pct_95',char(96),' AS ',char(34),'平均锁定时间',char(34),

  106. ',',char(96),'Lock_time_stddev',char(96),' AS ',char(34),'锁定时间标准差',char(34),

  107. ',',char(96),'Lock_time_median',char(96),' AS ',char(34),'锁定时间中位数',char(34),

  108. ',',char(96),'Rows_sent_sum',char(96),' AS ',char(34),'总返回记录行数',char(34),

  109. ',',char(96),'Rows_sent_min',char(96),' AS ',char(34),'最小返回记录数',char(34),

  110. ',',char(96),'Rows_sent_max',char(96),' AS ',char(34),'最大返回记录数',char(34),

  111. ',',char(96),'Rows_sent_pct_95',char(96),' AS ',char(34),'平均返回记录数',char(34),

  112. ',',char(96),'Rows_sent_stddev',char(96),' AS ',char(34),'发送返回数标准差',char(34),

  113. ',',char(96),'Rows_sent_median',char(96),' AS ',char(34),'返回记录数中位数',char(34),

  114. ',',char(96),'Rows_examined_sum',char(96),' AS ',char(34),'参加运算的记录总行数',char(34),

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

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

  117. ',',char(96),'Rows_examined_pct_95',char(96),' AS ',char(34),'平均参加运算的记录行数',char(34),

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

  119. ',',char(96),'Rows_examined_median',char(96),' AS ',char(34),'参加运算的记录行数中位数',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. #创建慢日志所需的数据库

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

  129. #调用拼接SQL，并执行该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. }



  146. #主体

  147. function main()

  148. {

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

  150. user='root'

  151. password='123456'

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

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

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

  155. find_all_slow

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

  157. grather_table

  158. }



  161. #调用主体

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

很赞哦！
日志 时间 模块 分析 目录 脚本 函数 日期 查询 运算 主机 数据 数据库 文件 问题 传输 中位数 密码 标准 环境 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 软件开发公司财税 飞鸽互联网蓝思科技下载 tbt2服务器是什么cpu 数据库的查询实验报告 湖南服务器光模块虚拟主机 电视keep显示与服务器断开 方舟打一个服务器 数据库创建正确方法 软件开发师是什么行业 单招计算机与网络技术这个职业 养鸡数据库 主机安全测评与网络安全测评 网络安全渗透技术学习方法 网络安全威胁有哪些文章 ide软件开发工具 海陵区环保网络技术诚信合作 直播平台服务器搭建 管家婆软件数据库没有了 浦东新区水性网络技术品牌 绑定数据库的方法 数据库bat类型 地理信息软件开发资质申请 互联网数据库2013年4月自考 python服务器程序 事业单位网络安全汇报 计算机网络技术课程表大学 数据库怎么查两个顾客的名字 西电出版社 网络安全 受托软件开发会计处理 数据库加密国内外研究现状

相关文章

0