Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 at 20:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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