Linux 在 Ubuntu 上记录 MySQL 查询

Logging MySQL query on Ubuntu

提问by Sourav

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

我需要将 SQL 记录到文件中，以便稍后检查运行的是哪个 SQL。

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

所以我打开opt/lampp/etc/my.cnf并添加这些行

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.

但它没有记录查询，它甚至没有创建 query.log 文件，所以我用这个名字创建了一个空文件，但它仍然不起作用。

Edit

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"

采纳答案by Michael Mior

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.

正如其他用户所提到的，这可能是权限问题。首先，检查 MySQL 正在运行的用户是 via ps -u -p $(pgrep mysql)。用户名将显示在 下的第一列中USER。在您的情况下，用户似乎是nobody. 您可以通过 来查看用户的默认组groups nobody。这应该打印类似nobody : nogroup.

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

要修复文件的权限，只需运行chown nobody:nogroup /opt/lampp/logs/query.log.

回答by hughes

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.

该进程可能没有写入该目录的权限。确保 MySQL 有权在那里写入，或尝试在不受限制的地方进行日志记录。

回答by Tarek

Be sure to give the correct permission :

请务必给予正确的许可：

chown mysql:mysql filename 

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

同样，当我上次这样做时，我不得不重新启动 mysql 服务：

service mysqld restart

回答by William Ardila

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

另请注意，mysql 也需要对文件夹的权限，在我的情况下，我更改了：
general_log_file = "/opt/lampp/logs/query.log"
for
general_log_file = "/var/log/mysql/query.log"
But I have mysql install from software center, without lampp，当我在 /var/log/ 上执行 ls -l 时，它显示
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

PD：我更改了my.cn文件并重启mysql，没有在指定路径创建query.log文件，mysql自动创建

回答by sahib

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