Solved

log an array of database queries, record to txt

Posted on 2014-03-23
6
196 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now