Logging MySQL query on Ubuntu
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开发者_Python百科_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"
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
.
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
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
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.
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
精彩评论