• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

MySQL: Tracking Queries in mysql database

Hello, i'm using windows based server (xampp / zend server windows) and i want to know on how i can trace queries that happen in mysql database inc. Insert, Update, Delete, and Select.

Can i do that in mysql ? i saw lots of tutorial but in ubuntu not windows...
0
veematics
Asked:
veematics
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Mark BradyCommented:
Try this:
1:   Send this command to the server: set global general_log=1;
2:   Find the log file (the default on Win7 it resides in C:\ProgramData\MySQL\MySQL Server 5.1\data) and has a name just like the computer.
3:  Examine the log :)
4:  Set the general_log back to 0 to return the DB server performance back to normal.

Or you could download some software to track all queries like http://www.webyog.com/en/
They have a 30 day trial of their software.

0
 
raysonleeCommented:
You can do that in MySQL by executing the commands:

  set global log_output='table';
  set global general_log=1;

That will log all commands to the table 'mysql.general_log'
  select argument from mysql.general_log
to view the logged commands
0
 
johanntagleCommented:
The thing with the general log is it also logs client connects and disconnects.  If you need that too then the general log is the one you should use.  If you want queries only, I use the slow query log then set long_query_time=0 to log all queries.  See http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Then I guess you might want to generate statistics on the queries (how many each time is run, average run time, maximum run time, etc).  Google "mysql slow query log parser" - it's Perl-based though and although there's a Perl version for Windows, I've never tried it.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
veematicsAuthor Commented:
@elvin66 , i'm trying to use monyog at the moment, where can i trace the query ?

i already in Query Analyzer, but i don't find the way to track query per database ? could you help ?
0
 
veematicsAuthor Commented:
@johanntagle can it filter per database ?
0
 
Mark BradyCommented:
I'm not sure I have not used it before - I just posted the link to see if it was a help.
0
 
veematicsAuthor Commented:
@raysonlee how to clear the log ? it had tons of logged command at the moment...

- also, like i ask other contributor... is there any way to logs command per database (not general) ?
0
 
johanntagleCommented:
No the slow query log is for all databases.  Maybe you can make sure your queries reference the tables as dbname.tablename then have a parser check what's after the FROM keyword in the query so to determine databases.
0
 
raysonleeCommented:
Stop the logging with
  set global general_log=0;
  TRUNCATE table mysql.general_log;

You can filter the records of database 'mydb' by:
  SELECT arguments FROM mysql.general_log WHERE arguments LIKE '%mydb.%';
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now