{"id":2253,"date":"2010-03-21T10:45:58","date_gmt":"2010-03-21T08:45:58","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2253"},"modified":"2010-03-21T10:57:35","modified_gmt":"2010-03-21T08:57:35","slug":"oak-hook-general-log-streaming-general-log","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/oak-hook-general-log-streaming-general-log","title":{"rendered":"oak-hook-general-log: streaming general log"},"content":{"rendered":"<p>I&#8217;m seeking input on a new <a href=\"http:\/\/code.openark.org\/forge\/openark-kit\">openark kit<\/a> utility I&#8217;ve started to implement.<\/p>\n<p>The tool, <strong>oak-hook-general-log<\/strong>, will hook up to a MySQL (&gt;= 5.1) server, and stream the general log into standard output. It looks like this:<\/p>\n<blockquote>\n<pre>bash$ python src\/oak\/oak-hook-general-log.py --socket=\/tmp\/mysql.sock --user=root\r\n2010-03-21 10:18:42\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 SELECT COUNT(*) FROM City\r\n2010-03-21 10:18:48\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 DELETE FROM City WHERE id=1000\r\n2010-03-21 10:18:54\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 SHOW PROCESSLIST\r\n2010-03-21 10:19:06\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Quit\r\n2010-03-21 10:19:07\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 93\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Connect root@localhost on\r\n2010-03-21 10:19:07\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 93\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 select @@version_comment limit 1\r\n2010-03-21 10:22:33\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 93\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 SELECT City.Name, Country.Name FROM Country JOIN City ON Country.Capit\r\n2010-03-21 10:22:58\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 93\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Quit\r\n<\/pre>\n<\/blockquote>\n<p>Since output is written to <strong>stdout<\/strong>, one can further:<\/p>\n<blockquote>\n<pre>bash$ python src\/oak\/oak-hook-general-log.py --socket=\/tmp\/mysql.sock --user=root | grep Connect\r\nbash$ python src\/oak\/oak-hook-general-log.py --socket=\/tmp\/mysql.sock --user=root | grep webuser@webhost<\/pre>\n<\/blockquote>\n<p>What the tool does is to enable table logs, and periodically rotate the <strong>mysql.general_log<\/strong> table, read and dump its content.<\/p>\n<p><!--more-->The tool:<\/p>\n<ul>\n<li>Stores and restores the original log state (general log enabled\/disabled, log output).<\/li>\n<li>Disables printing of its own queries to the general log.<\/li>\n<li>Automatically times out (timeout configurable) so as not to enter a situation where the general log is forgotten to be turned on.<\/li>\n<li>Can discard pre-existing data on the <strong>mysql.general_log<\/strong> table.<\/li>\n<li>Will cleanup the <strong>mysql.slow_log<\/strong> table, if it wasn&#8217;t originally used (turning on table logs applies to both general log and slow log).<\/li>\n<\/ul>\n<p>What would you have the tool do further? Should it provide filtering, or should we just use <strong>grep<\/strong>\/<strong>sed<\/strong>\/<strong>awk<\/strong> for that? Any internal aggregation of data?<\/p>\n<p>I would love to hear your thoughts. Meanwhile, <a href=\"http:\/\/code.google.com\/p\/openarkkit\/source\/browse\/trunk\/openarkkit\/src\/oak\/oak-hook-general-log.py\">view or grab the python script file<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m seeking input on a new openark kit utility I&#8217;ve started to implement. The tool, oak-hook-general-log, will hook up to a MySQL (&gt;= 5.1) server, and stream the general log into standard output. It looks like this: bash$ python src\/oak\/oak-hook-general-log.py &#8211;socket=\/tmp\/mysql.sock &#8211;user=root 2010-03-21 10:18:42\u00a0\u00a0\u00a0\u00a0 root[root] @ localhost []\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 79\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Query\u00a0\u00a0 SELECT COUNT(*) FROM City [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[56,34,31,50],"class_list":["post-2253","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-logs","tag-openark-kit","tag-python","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Al","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2253","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2253"}],"version-history":[{"count":8,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2253\/revisions"}],"predecessor-version":[{"id":2260,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2253\/revisions\/2260"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2253"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2253"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2253"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}