【Mysql】捕獲線上sql

小亮520cl發表於2016-04-07
MySql

1.基於TCPDUMP的捕獲

    1. [root@bj150 ~]yum install –y tcpdump
      [root@bj150 ~]tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings
      -i：指定網路卡
      -s：0不指定資料包長度，預設1024
      -w - 寫入管道
      dst port 3306 抓取的埠
         
    2.      
    percona官網一種比較好的寫法，只dump需要的
    https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/
    1. [root@bj150 ~]# tcpdump -i em2 -s 0 -l -w - dst port 3306 | strings | perl -e '
      while(<>) { chomp; next if /^[^ ]+[ ]*$/;
        if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
          if (defined $q) { print "$q\n"; }
          $q=$_;
        } else {
          $_ =~ s/^[ \t]+//; $q.=" $_";
        }
      }'

Percona公司的pt-query-digest也能識別tcpdump的抓包並進行分析

  1.     抓取：
  2.     [root@bj150 ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt 
  3.     pt-query-digest分析：
  4.     [root@bj150 ~]# pt-query-digest --type tcpdump mysql.tcp.txt>mysql.log

2基於slowlog的抓取

  1. 將需要觀察的那段時間的slow log調整為0秒，完成後關閉即可
  2. 給個指令碼
    1. #!/bin/bash
      #get full slow log
      slowlog=/u1/mysql/data/slow.log
      archdir=/backup/arch_slow_log

    2. #備份原來的slow log
      cat $slowlog >$archdir/slow_`date '+%Y%m%d'`.log

    3. #清空原來的slow log
      cat /dev/null > $slowlog
    4. mysql -S /tmp/mysql.sock -e "flush logs;"
      fname="slow_"$(date '+%Y%m%d%H%M').log

    5. #記錄全量log
      mysql -S /tmp/mysql.sock e " set global long_query_time=0"
      sleep 5
      mysql -S /tmp/mysql.sock e "set global long_query_time=1"
      cp $slowlog $archdir/$fname

    6. #清空一下
      cat /dev/null >$slowlog
      mysql -S /tmp/mysql.sock -e "flush logs;"

3基於vc-mysql-sniffer利器的抓取（推薦）
  1. 下載連結
  2. 下載解壓即可用
  3. [root@bj150 tooldir]# ./vc-mysql-sniffer --help
    vc-mysql-sniffer is a utility from VividCortex to monitor query activity and write results to a file.
    See --license for the terms governing your usage of this program.


      -binding="[::]:3306"         This is a list of comma separated bind strings as seen in /proc/net/tcp
      -help="false"                Show this usage message
      -license="false"             Print the usage terms of this program
      -output=""                   Filepath to output queries to. Defaults to stdout if none specified.
      -verbose="false"             Enable logging on program startup to stderr
      -version="false"             Show version and exit


      Flag                         Current value
    --------------------------------------------
      -binding                     "[::]:3306"
      -help                        "true"
      -license                     "false"
      -output                      ""
      -verbose                     "false"
      -version                     "false"
  4. 預設使用3306埠，也可指定其它埠
  5. [root@HaoDai_App_DB01 soft]# ./vc-mysql-sniffer -binding="[::]:3307" > log.txt
    [root@HaoDai_App_DB01 soft]# pt-query-digest log.txt > log2.txt                     ---配合pt-query-digest使用
    [root@HaoDai_App_DB01 soft]# more log2.txt 


    # 2.8s user time, 20ms system time, 26.73M rss, 211.18M vsz
    # Current date: Tue Jul 26 17:35:23 2016
    # Hostname: HaoDai_App_DB01
    # Files: log.txt
    # Overall: 17.35k total, 211 unique, 0 QPS, 0x concurrency _______________
    # Time range: 2007-26-16 17:35:03 to 17:35:07
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time            21s       0      1s     1ms     9ms    13ms   152us
    # Query size         1.64M      15     812   99.08  346.17   76.84   84.10


    # Profile
    # Rank Query ID           Response time Calls R/Call V/M   Item
    # ==== ================== ============= ===== ====== ===== ===============
    #    1 0xDB2726892220D869 11.8167 55.6%  1097 0.0108  0.00 SELECT b_user_push_set
    #    2 0xDF26C788A4C72EDE  1.2989  6.1%  6815 0.0002  0.00 SELECT b_orders
    #    3 0x594CE55DB5F268A3  1.2855  6.1%  1537 0.0008  0.00 SHOW COLUMNS
    #    4 0x02DF55AA27B0ADE1  1.2223  5.8%     1 1.2223  0.00 SELECT c_intent_user?
    #    5 0xF26F6FECF39DAFE4  1.1021  5.2%     1 1.1021  0.00 SELECT c_intent_user?



vc-mysql-sniffer統計MySQL的SQL分佈

https://www.cnblogs.com/gomysql/p/8267070.html

3 給予mysql-sniffer抓取（推薦二）
  1. 詳情參考：http://mp.weixin.qq.com/s/jFVHZedaKuG1P9llgEvaGw

  3. [root@HaoDai_App_DB01 bin]# ./mysql-sniffer -i em2 -p 3307 | more
  4. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14729967 ) LIMIT 1
  5. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730075 ) LIMIT 1
  6. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730428 ) LIMIT 1
  7. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730299 ) LIMIT 1
  8. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730476 ) LIMIT 1
  9. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 1 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730507 ) LIMIT 1
  10. 2017-03-03 16:08:56 interface 192.168.1.149 interface_hd_com 0ms 0 use interface_hd_com
  11. 2017-03-03 16:08:56 interface 192.168.1.149 interface_hd_com 0ms 0 SET NAMES 'utf8'
  12. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 1ms 0 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730819 ) LIMIT 1
  13. 2017-03-03 16:08:56 interface 192.168.1.149 interface_hd_com 0ms 0 SET sql_mode=''
  14. 2017-03-03 16:08:56 interface 192.168.1.149 interface_hd_com 0ms 9 SHOW COLUMNS FROM `admin_menu`
  15. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 1 SELECT `id` FROM `b_orders` WHERE ( `cid` = 14730888 ) LIMIT 1
  16. 2017-03-03 16:08:56 NULL 192.168.1.148 NULL 0ms 1 SELECT `id`,`order_id`,`amr_time` FROM `c_intent_user2015` WHERE ( `order_id` = '2017020213818484' )
  17.  LIMIT 1

4 基於genery log的抓取

  1. 思路：開啟一段時間的generlog

5 基於Anemometer平臺來管理Mysql日誌
  1.     開源地址：https://github.com/box/Anemometer


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2076675/，如需轉載，請註明出處，否則將追究法律責任。

相關文章