3

I need to log SQL to a file so i can check later which SQL run.

so i opened opt/lampp/etc/my.cnf and add these lines 

log_slow_queries  
log_queries_not_using_indexes =1  
long_query_time = 1  
slow_query_log = 1  
slow_query_log_file = "/opt/lampp/logs/query.log"

but it did not logged the queries it even did not created the query.log file, so i created an empty file with the name, but still it's not working.

Edit

[mysqld]
log_slow_queries
log_queries_not_using_indexes =1
long_query_time = 1
slow_query_log = 1
general_log = 1
slow_query_log_file = /opt/lampp/logs/query.log
general_log_file = "/opt/lampp/logs/query.log"
Improve this question
1
  • yup, i restarted & reloaded both Commented Jul 8, 2011 at 17:55

5 Answers 5

Reset to default
10

This will only log slow queries. You need the general log if you want to see all queries.

general_log = 1
general_log_file = "/opt/lampp/logs/query.log"

Note that you'll need to restart the server for this to take effect. Also, you should only use this type of logging during testing as it does cause slowdown.

As other users mentioned, this could be a permissions issue. First, check what user MySQL is running as via ps -u -p $(pgrep mysql). The username will be displayed on the first column under USER. In your case, it seems the user is nobody. You can view the default group of a user via groups nobody. This should print something like nobody : nogroup.

To fix the permissions on the file, just run chown nobody:nogroup /opt/lampp/logs/query.log.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

Check the output of show variables like 'general_log%'; to confirm that MySQL has applied your settings.
how to get the mysql console ?
Typing mysql -hHOST_NAME -uUSER_NAME -DDATABASE_NAME -p at the command line will connect you.
if i type in terminal it shows not installed :( but i'm working with MySQL and have developed site in LAMPP
sudo apt-get install mysql-client
|
3

Be sure to give the correct permission :

chown mysql:mysql filename

also when i last did it , i had to restart the mysql service :

service mysqld restart
Improve this answer

9 Comments

did you restart mysql ? service mysqld restart
yup i restarted but still no result !
are you sure the query is slow to be logged ?
can u edit your question and past the content of your my.cnf file
i've copied my.cnf file, review it
|
2 
log_slow_queries

is deprecated

It now has to look like that:

slow_query_log
log_queries_not_using_indexes =1
long_query_time = 1
slow_query_log = 1
general_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log_file = /var/log/mysql/mysql-slow.log
Improve this answer

Comments

1

The process probably doesn't have permission to write to that directory. Make sure MySQL has permission to write there, or try logging somewhere less restricted.

Improve this answer

1 Comment

i'm new to linux, please guide to do so
1

This will only log slow queries. You need the general log if you want to see all queries.

general_log = 1
general_log_file = "/opt/lampp/logs/query.log"

Note that you'll need to restart the server for this to take effect. Also, you should only use this type of logging during testing as it does cause slowdown.

Also Note that mysql needs permissions over folder too, in my case, I changed:
general_log_file = "/opt/lampp/logs/query.log"
for
general_log_file = "/var/log/mysql/query.log"
But I have mysql installed from software center, without lampp, when I execute ls -l over /var/log/, it shows
drwx------ 8 mysql mysql 4096 sep 25 23:22 mysql

PD:I change the my.cn file and restart mysql, without create the query.log file in the specified path, mysql automatically create it

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.