Solved

log an array of database queries, record to txt

Posted on 2014-03-23
6
208 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
  • 3
  • 3
6 Comments
 
LVL 50

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 50

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 50

Accepted Solution

by:
Steve Bink earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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