-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathhow-to-easy-collect-mysql-query-log.html
More file actions
180 lines (161 loc) · 11 KB
/
how-to-easy-collect-mysql-query-log.html
File metadata and controls
180 lines (161 loc) · 11 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:og="http://ogp.me/ns#"
xmlns:fb="https://www.facebook.com/2008/fbml">
<head>
<title>"How to easy collect MySQL query log"</title>
<!-- Using the latest rendering mode for IE -->
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- Open Graph tags -->
<meta property="og:type" content="article"/>
<meta property="og:title" content=""How to easy collect MySQL query log""/>
<meta property="og:url" content="http://kevinbin.github.com/how-to-easy-collect-mysql-query-log.html"/>
<meta property="og:description" content="当我们想采集一些查询语句用来分析时，可以利用slow-query-log来记录，与general-log相比它不会记录与查询语句无关的信息，例如：连接信息，登陆等。 我们通过设置long_query_time=0，能够记录到足够多的查询语句样本，但这在繁忙的线上是会有很大的性能影响，会消耗较多的file IO。long_query_time是一个动态变量可以在不重启MySQL数据库的情况下动态修改它的值，可以记录一段时间再把它恢复原值。虽然操作过程并不复杂，但有一个自动化整个过程的脚本会更好。 https://github.com/billkarwin/bk-tools/blob/master/full-slow-log/ $ full-slow-log [ -v ] [ -s seconds ] [ -c config ] $ full-slow-log -v Discovering slow_query_log=1 Discovering slow_query_log_file=mysql-slow.log Discovering long_query_time=60.000000 Setting long_query_time ..."/>
<!-- Bootstrap -->
<link rel="stylesheet" href="http://kevinbin.github.com/theme/css/bootstrap.min.css" type="text/css"/>
<link href="http://kevinbin.github.com/theme/css/font-awesome.min.css" rel="stylesheet">
<link href="http://kevinbin.github.com/theme/css/bootstrap-glyphicons.css" rel="stylesheet">
<link href="http://kevinbin.github.com/theme/css/pygments.css" rel="stylesheet">
<link rel="stylesheet" href="http://kevinbin.github.com/theme/css/style.css" type="text/css"/>
<!-- JavaScript plugins (requires jQuery) -->
<script src="http://code.jquery.com/jquery.js"></script>
</head>
<body>
<nav class="navbar navbar-default navbar-fixed-top" role="navigation">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-ex1-collapse">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a href="http://kevinbin.github.com" class="navbar-brand">DevOps</a>
</div>
<div class="collapse navbar-collapse navbar-ex1-collapse">
<ul class="nav navbar-nav">
<li><a href="http://kevinbin.github.com/pages/about-me.html">About Me</a></li>
<li class="active">
<a href="http://kevinbin.github.com/category/misc.html">misc</a>
</li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li><a href="http://kevinbin.github.com/archives.html"><i class="icon-th-list"></i>Archives</a></li>
</ul>
</div>
<!-- /.navbar-collapse -->
</div>
</nav>
<!-- /.navbar -->
<div class="container">
<div class="row">
<div class="col-lg-9">
<section id="content">
<article>
<header class="page-header">
<h1>
<a href="http://kevinbin.github.com/how-to-easy-collect-mysql-query-log.html"
rel="bookmark"
title="Permalink to "How to easy collect MySQL query log"">
"How to easy collect MySQL query log"
</a>
</h1>
</header>
<div class="entry-content">
<div class="panel">
<div class="panel-body">
<footer class="post-info">
<span class="label label-default">Date</span>
<span class="published">
<i class="icon-calendar"></i>五 23 十一月 2012
</span>
<span class="label label-default">Tags</span>
<a href="http://kevinbin.github.com/tag/mysql.html">mysql</a>
</footer><!-- /.post-info --> </div>
</div>
<p>当我们想采集一些查询语句用来分析时，可以利用slow-query-log来记录，与general-log相比它不会记录与查询语句无关的信息，例如：连接信息，登陆等。</p>
<p>我们通过设置long_query_time=0，能够记录到足够多的查询语句样本，但这在繁忙的线上是会有很大的性能影响，会消耗较多的file IO。long_query_time是一个动态变量可以在不重启MySQL数据库的情况下动态修改它的值，可以记录一段时间再把它恢复原值。虽然操作过程并不复杂，但有一个自动化整个过程的脚本会更好。</p>
<p><a href="https://github.com/billkarwin/bk-tools/blob/master/full-slow-log/">https://github.com/billkarwin/bk-tools/blob/master/full-slow-log/</a></p>
<div class="highlight"><pre><span class="err">$</span> <span class="n">full</span><span class="o">-</span><span class="n">slow</span><span class="o">-</span><span class="n">log</span> <span class="p">[</span> <span class="o">-</span><span class="n">v</span> <span class="p">]</span> <span class="p">[</span> <span class="o">-</span><span class="n">s</span> <span class="n">seconds</span> <span class="p">]</span> <span class="p">[</span> <span class="o">-</span><span class="n">c</span> <span class="n">config</span> <span class="p">]</span>
<span class="err">$</span> <span class="n">full</span><span class="o">-</span><span class="n">slow</span><span class="o">-</span><span class="n">log</span> <span class="o">-</span><span class="n">v</span>
<span class="n">Discovering</span> <span class="n">slow_query_log</span><span class="o">=</span><span class="mi">1</span>
<span class="n">Discovering</span> <span class="n">slow_query_log_file</span><span class="o">=</span><span class="n">mysql</span><span class="o">-</span><span class="n">slow</span><span class="p">.</span><span class="n">log</span>
<span class="n">Discovering</span> <span class="n">long_query_time</span><span class="o">=</span><span class="mf">60.000000</span>
<span class="n">Setting</span> <span class="n">long_query_time</span><span class="o">=</span><span class="mi">0</span>
<span class="n">Setting</span> <span class="n">slow_query_log_file</span><span class="o">=</span><span class="n">mysql</span><span class="o">-</span><span class="n">slow</span><span class="p">.</span><span class="n">log</span><span class="o">-</span><span class="n">full</span><span class="o">-</span><span class="mi">20121122112413</span>
<span class="n">Setting</span> <span class="n">slow_query_log</span><span class="o">=</span><span class="mi">1</span>
<span class="n">Flushing</span> <span class="n">slow</span> <span class="n">query</span> <span class="n">log</span>
<span class="n">Sleeping</span> <span class="mi">5</span> <span class="n">seconds</span><span class="p">...</span> <span class="n">done</span><span class="p">.</span>
<span class="n">Restoring</span> <span class="n">slow_query_log_file</span><span class="o">=</span><span class="n">mysql</span><span class="o">-</span><span class="n">slow</span><span class="p">.</span><span class="n">log</span>
<span class="n">Restoring</span> <span class="n">long_query_time</span><span class="o">=</span><span class="mf">60.000000</span>
<span class="n">Restoring</span> <span class="n">slow_query_log</span><span class="o">=</span><span class="mi">1</span>
<span class="n">Flushing</span> <span class="n">logs</span> <span class="n">during</span> <span class="n">restore</span>
</pre></div>
<p>可以设置采集时间-s，并添加时间戳自动命名日志。非常好用！</p>
</div>
<!-- /.entry-content -->
<hr />
<section class="comments" id="comments">
<h2>Comments</h2>
<div id="disqus_thread"></div>
<script type="text/javascript">
/* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
var disqus_shortname = 'hongbin'; // required: replace example with your forum shortname
var disqus_identifier = 'how-to-easy-collect-mysql-query-log';
var disqus_url = 'http://kevinbin.github.com/how-to-easy-collect-mysql-query-log.html';
/* * * DON'T EDIT BELOW THIS LINE * * */
(function () {
var dsq = document.createElement('script');
dsq.type = 'text/javascript';
dsq.async = true;
dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
})();
</script>
<noscript>Please enable JavaScript to view the <a href="http://disqus.com/?ref_noscript">comments powered by
Disqus.</a></noscript>
<a href="http://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a>
</section>
</article>
</section>
</div>
<div class="col-lg-3 well well-sm" id="sidebar">
<aside>
<section>
<ul class="list-group list-group-flush">
<li class="list-group-item"><a href="http://kevinbin.github.com/tags.html"><h4><i class="icon-tags icon-large"></i>Tags</h4></a></li>
<li class="list-group-item tag-1">
<a href="http://kevinbin.github.com/tag/mysql.html">
mysql
</a>
</li>
<li class="list-group-item tag-4">
<a href="http://kevinbin.github.com/tag/mac.html">
mac
</a>
</li>
</ul>
</section>
</aside> </div>
</div>
</div>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="http://kevinbin.github.com/theme/js/bootstrap.min.js"></script>
<!-- Enable responsive features in IE8 with Respond.js (https://github.com/scottjehl/Respond) -->
<script src="http://kevinbin.github.com/theme/js/respond.min.js"></script>
<script type="text/javascript">
/* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
var disqus_shortname = 'hongbin'; // required: replace example with your forum shortname
/* * * DON'T EDIT BELOW THIS LINE * * */
(function () {
var s = document.createElement('script');
s.async = true;
s.type = 'text/javascript';
s.src = '//' + disqus_shortname + '.disqus.com/count.js';
(document.getElementsByTagName('HEAD')[0] || document.getElementsByTagName('BODY')[0]).appendChild(s);
}());
</script>
</body>
</html>