{"id":333,"date":"2006-08-09T08:14:59","date_gmt":"2006-08-09T07:14:59","guid":{"rendered":"http:\/\/wp.devco.net\/?p=333"},"modified":"2009-10-09T14:26:13","modified_gmt":"2009-10-09T13:26:13","slug":"mysql_query_logging","status":"publish","type":"post","link":"https:\/\/www.devco.net\/archives\/2006\/08\/09\/mysql_query_logging.php","title":{"rendered":"MySQL Query Logging"},"content":{"rendered":"<p>MySQL has an option to log all queries to a log file, stupidly though you need to restart the server to start\/stop this log, its a bit lame, it also adds a lot of overhead to your box.  I had the idea some time ago to use libpcap to figure out what gets sent to the server and started the code, I&#8217;ve mostly finished this now.<br \/>\nThe MySQL protocol is undocumented but there is a reverse engineered protocol doc <a href=\"http:\/\/www.redferni.uklinux.net\/mysql\/MySQL-Protocol.html\">here<\/a>.  It is a tad outdated and I&#8217;ve found some queries that does not get parsed perfectly using the info there, I&#8217;ll look into those some more.<br \/>\nHere are some sample outputs from my code:<\/p>\n<blockquote>\n<pre>\n# .\/mysqldump.pl -i mysql.cap\n17:08:44: SET autocommit=1\n17:08:44: SELECT last_insert_id()\n17:08:44: rollback\n<\/pre>\n<\/blockquote>\n<p>It also has a more verbose option:<\/p>\n<blockquote>\n<pre>\n# .\/mysqldump.pl -i mysql.cap -v\nMySQL Packet:\nLength          : 24\nPacket Number   : 0\nPacket Command  : 3\nSQL Command     : SELECT last_insert_id()\nPacket Header:\nSource   : 192.168.1.1\nDest     : 192.168.1.2\nS.Port   : 47241\nD.Port   : 3306\nLength   : 77\nTimestamp: 1150128523.750337\n<\/pre>\n<\/blockquote>\n<p>I could make the script do its own dumping using libpcap it&#8217;s pretty easy but I prefer to only let it read tcpdump files, we get a LOT of queries (> 600 per second) and I don&#8217;t want slow perl code to affect the servers, tcpdump is pretty fast and efficient.<br \/>\nI use <b>Net::Pcap<\/b> and <b>NetPacket::TCP<\/b> to extract the packets from the file, decoding the MySQL commands is pretty easy then:<\/p>\n<blockquote>\n<pre>\n$plen = ord(substr($pkt, 66, 3));\n$pnum = ord(substr($pkt, 69, 1));\n$pcmd = ord(substr($pkt, 70, 1));\n$cmd  = substr($pkt, 71);\n<\/pre>\n<\/blockquote>\n<p>The offsets etc is defined in the documentation linked too above, <i>$cmd<\/i> will now hold the actual query ran against the server, but only where <i>$pcmd == 3<\/i>.<br \/>\nI&#8217;ll upload the code for this in the next few days just letting a few people do some beta testing for me first.<br \/>\n<B>UPDATE:<\/B> You can now get the code <a href=\"http:\/\/www.devco.net\/code\/mysqldump.pl\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL has an option to log all queries to a log file, stupidly though you need to restart the server to start\/stop this log, its a bit lame, it also adds a lot of overhead to your box. I had the idea some time ago to use libpcap to figure out what gets sent to [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","footnotes":""},"categories":[7],"tags":[19,34],"_links":{"self":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/333"}],"collection":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/comments?post=333"}],"version-history":[{"count":1,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/333\/revisions"}],"predecessor-version":[{"id":592,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/333\/revisions\/592"}],"wp:attachment":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/media?parent=333"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/categories?post=333"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/tags?post=333"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}