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

How to setup MySQL logging of queries

Hi-

I want to log all queries to MySQL table that shows me metrics (when the query was submitted, how long it took, how many records, user who requested it)

I have added the following to my.ini

slow-query-log

log-output = TABLE

general_log_file = host_name.log

slow_query_log_file = slow_query.log

log-queries-not-using-indexes

Open in new window


In Server Logs (MySQL Workbench), I now see:
Error Log File
Slow Query Log Table

Error Log File was there before I updated my.ini; Slow Query Log Table is new

What do I need to do to get the info I want (preferably using Workbench)?

Thanks--
0
SAbboushi
Asked:
SAbboushi
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
Steve BinkCommented:
If you want this information for all queries, the general log is what you want, but I don't think it shows the execution time.  The slow query log will, but only for queries that take longer than indicated in the long_query_time variable.

You can also see here: http://lserinol.blogspot.com/2009/02/mysql-general-query-log.html.  It explains how more recent versions of MySQL can write the log to a table for easier manipulation.
0
 
johanntagleCommented:
Use the slow query log then set long_query_time=0 (use GLOBAL option).  What's the structure of the slow query log table, btw?  Honestly, I've never tried logging to tables - I always log to disk.  Then to process them you can use the Query Digest of the Percona Toolkit.  See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html
0
 
Steve BinkCommented:
I don't know much more than I posted...I've never used tables to hold the logs.  Here's a screenshot of a select from it: http://lserinol.blogspot.com/2009/02/mysql-general-query-log.html.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Steve BinkCommented:
Should have posted this as well...always nice to have a first-hand source.

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
0
 
gr8gonzoConsultantCommented:
johanntagle's suggestion should work, but you REALLY should not leave this on for any length of time. Trying to log every query and its metadata is going to severely degrade performance. Unless you have some spectacularly good reason for logging everything (e.g. you suspect that you have hackers in your system), you really should not do this.
0
 
johanntagleCommented:
Agree.  I forgot to mention I only do this to get do query profiling, so I leave it on for an hour maximum.
0
 
SAbboushiAuthor Commented:
Thanks for all the posts.

I had previously read the MySQL doc and added the settings (see original post) but this did not seem to set up general log table.

I am looking for help on what steps I need to take next

This is for debugging purposes in development environment.
Thanks--
0
 
gr8gonzoConsultantCommented:
Assuming you're on MySQL 5.1.21 or later, just add this to your my.ini file:
long_query_time=0
0
 
SAbboushiAuthor Commented:
Thanks - I'll try that.  But why isn't the General Log listed under Server Logs?
0
 
gr8gonzoConsultantCommented:
I'm not sure - you should have a General tab for it, according to how your my.ini is set up.
0
 
johanntagleCommented:
long_query_time is for the slow query log, not the general log.  And you can do a "set global long_query_time=0" within MySQL without having to edit my.ini and restart mysql.

As for the missing general log, just a guess but can you remove the " = host_name.log" in your my.ini?  It might be causing MySQL to write the logs to file even if you already set log_output to TABLE.  Then restart mysql (and maybe reload workbench?), of course.
0
 
SAbboushiAuthor Commented:
Change the line from
general_log_file = host_name.log

Open in new window

 to
general_log_file

Open in new window

 or remove the line?
0
 
johanntagleCommented:
Change the line
0
 
SAbboushiAuthor Commented:
When the line is
general_log_file

Open in new window

, MySQL server won't start
0
 
johanntagleCommented:
Sorry, it should just be "general-log".  See http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

I just realized that not having that in your my.ini should be the reason why the general log is not enabled in the first place.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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