2

Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track down the offending app server.

I googled around, and checked documentation but most of the discussion surrounded using IP Addresses as data in tables.

1 Answer 1

4

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out

Then, after you are done with your packet capture, do this:

pt-query-digest tcpdump.out --type=tcpdump --group-by=fingerprint --print --no-report

That will give you the raw digest of all queries and the hosts from which they originated. More information can be found at: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html

4
  • Thanks for the suggestion. Mostly out of curiosity, I'm wondering why this is not a core feature in MySQL. Seems like it would be useful. WDYT?
    – KM.
    Jan 17, 2013 at 14:52
  • While MySQL is much more feature-rich than it was in its pre-5.x days, it is still a pretty bare-bones RDBMS compared to Oracle, MS SQL, DB2, etc. Generally, the skill-sets of a senior-level MySQL DBA include Linux and networking. Generally, the first thing to do when looking trying to do something with MySQL is search the Internet for solutions (ie: Open source or proprietary software, or one-off scripts). You'll find that a basic knowledge of Bash-scripting is beneficial.
    – potto
    Jan 17, 2013 at 17:57
  • What then might be the proper avenue for requesting this as a feature? It is to report a "bug" at bugs.mysql.com ... ?
    – KM.
    Jan 17, 2013 at 18:22
  • Yeah - [bugs.mysql.com/] (bugs.mysql.com) is where you'd put in such a request. They're not the most responsive -- I've watched hardcore bugs linger for 2-3 years... :)
    – potto
    Jan 17, 2013 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.