Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

log an array of database queries, record to txt

Posted on 2014-03-23
6
Medium Priority
?
233 Views
Last Modified: 2014-04-01
http://www.experts-exchange.com/Database/MySQL/Q_28395289.html

part1:
only want to log queries from an array of databases (not db:mysql, because there are too many results)

part2:
want to log queries to .txt file  (do I need to change permissions to 777)
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39959050
Part1: This is not possible at the MySQL level.  The config setting there is for the service as a whole.  If you want to capture only queries to certain databases, you'll need to build an abstraction layer in your application to handle it.

Part2: In the previous question, you stated you are using Windows, so the 777 permission setting does not really apply.  The slow log file you want to use should be owned by the same user used to run the MySQL service, and be writable by that user.
0
 

Author Comment

by:rgb192
ID: 39964934
you stated you are using Windows, so the 777 permission setting does not really appl
I do not understand windows permissions so I use unix based cygwin to set to 777

The slow log file you want to use should be owned by the same user used to run the MySQL service, and be writable by that user.
How
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 39965046
Generally, you can just set the filename to be used in the MySQL configuration, and it will be created by the service.  If the file exists, delete it and it will be recreated.

You can check the status of your slow query log by using this SQL command:
show variables like '%slow_query%';

Open in new window


If your log is enabled, but nothing is being recorded, then you haven't had any executed queries matching the limits.  There are a few limits you can configure.  See https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html for more information on how the engine determines whether or not to log any given query.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:rgb192
ID: 39966812
show variables like '%slow_query%'

slow_query_log      ON
slow_query_log_file      C:\wamp{some-unreadble-character}in\mysql\mysql5.6.12\data\mysql low_query_log_file.txt


the table adds rows but the file stays the same size
C:\wamp\bin\mysql\mysql5.6.12\data\mysql\slow_query_log_file.txt

I think the query is returning incorrect file location

how to change file location

I already have
[mysqld]
port=3306
slow_query_log=1
log-output = TABLE,FILE
slow_query_log_file=C:\wamp\bin\mysql\mysql5.6.12\data\mysql\slow_query_log_file.txt
long_query_time=0
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 39969142
Change your setting as shown below and restart the service:
slow_query_log_file=C:\\wamp\\bin\\mysql\\mysql5.6.12\\data\\mysql\\slow_query_log_file.txt

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 39970871
after restart much data is written to
slow_query_log_file=C:\\wamp\\bin\\mysql\\mysql5.6.12\\data\\mysql\\slow_query_log_file.txt

thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

671 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question