Logging MySQL query on Ubuntu

Question

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"

Solution 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.

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.

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions