如何查看 MySQL 中的日志文件？

How to see log files in MySQL?

提问by Arjun

I've read that Mysql server creates a log file where it keeps a record of all activities - like when and what queries execute.

我读过 Mysql 服务器创建了一个日志文件，其中记录了所有活动 - 例如查询执行的时间和内容。

Can anybody tell me where it exists in my system? How can I read it?

谁能告诉我它存在于我的系统中的什么位置？我怎样才能阅读它？

Basically, I need to back up the database with different input [backup between two dates] so I think I need to use log file here, that's why I want to do it...

基本上，我需要用不同的输入[两个日期之间的备份]来备份数据库，所以我想我需要在这里使用日志文件，这就是我想要这样做的原因......

I think this log must be secured somehow because sensitive information such as usernames and password may be logged [if any query require this]; so may it be secured, not easily able to be seen?

我认为必须以某种方式保护此日志，因为可能会记录用户名和密码等敏感信息 [如果有任何查询需要此]；那么它可以被固定，不容易被看到吗？

I have root access to the system, how can I see the log?

我有系统的 root 访问权限，如何查看日志？

When I try to open /var/log/mysql.log it is empty.

当我尝试打开 /var/log/mysql.log 它是空的。

This is my config file:

这是我的配置文件：

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
log = /var/log/mysql/mysql.log 
binlog-do-db=zero



user        = mysql
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
skip-external-locking

bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

采纳答案by loyola

Here is a simple way to enable them. In mysql we need to see often 3 logs which are mostly needed during any project development.

这是启用它们的简单方法。在mysql中，我们经常需要看到3个日志，这是任何项目开发过程中最需要的。

• The Error Log. It contains information about errors that occur while the server is running (also server start and stop)

• The General Query Log. This is a general record of what mysqld is doing (connect, disconnect, queries)

• The Slow Query Log. Ιt consists of "slow" SQL statements (as indicated by its name).

• The Error Log. 它包含有关服务器运行（以及服务器启动和停止）时发生的错误的信息

• The General Query Log. 这是 mysqld 正在做什么（连接、断开连接、查询）的一般记录

• The Slow Query Log. 它由“慢”SQL 语句（如其名称所示）组成。

By default no log files are enabled in MYSQL. All errors will be shown in the syslog (/var/log/syslog).

默认情况下，MYSQL 中没有启用任何日志文件。所有错误都将显示在系统日志 ( /var/log/syslog) 中。

To Enable them just follow below steps:

要启用它们，只需按照以下步骤操作：

step1:Go to this file (/etc/mysql/conf.d/mysqld_safe_syslog.cnf) and remove or comment those line.

步骤 1：转到此文件（/etc/mysql/conf.d/mysqld_safe_syslog.cnf）并删除或注释这些行。

step2:Go to mysql conf file (/etc/mysql/my.cnf) and add following lines

步骤2：转到mysql conf文件（/etc/mysql/my.cnf）并添加以下行

To enable error log add following

要启用错误日志，请添加以下内容

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log

[mysqld]
log_error=/var/log/mysql/mysql_error.log

To enable general query log add following

要启用常规查询日志，请添加以下内容

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

To enable Slow Query Log add following

要启用慢查询日志，请添加以下内容

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

step3:save the file and restart mysql using following commands

step3：保存文件并使用以下命令重新启动mysql

service mysql restart

To enable logs at runtime, login to mysql client (mysql -u root -p) and give:

要在运行时启用日志，请登录到 mysql 客户端 ( mysql -u root -p) 并给出：

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

Finally one thing I would like to mention here is I read this from a blog. Thanks. It works for me.

最后，我想在这里提到的一件事是我从博客中读到的。谢谢。这个对我有用。

Click hereto visit the blog

点击这里访问博客

回答by danidacar

You have to activate the query logging in mysql.

您必须在 mysql 中激活查询日志记录。

1. edit /etc/my.cnf

   [mysqld]
   log=/tmp/mysql.log
   

2. restart the computer or the mysqld service

   service mysqld restart
   

3. open phpmyadmin/any application that uses mysql/mysql console and run a query

4. cat /tmp/mysql.log( you should see the query )

1. 编辑 /etc/my.cnf

   [mysqld]
   log=/tmp/mysql.log
   

2. 重启电脑或mysqld服务

   service mysqld restart
   

3. 打开 phpmyadmin/任何使用 mysql/mysql 控制台的应用程序并运行查询

4. cat /tmp/mysql.log（您应该会看到查询）

回答by kenorb

The MySQL logs are determined by the global variables such as:

MySQL 日志由全局变量决定，例如：

• log_errorfor the error message log;
• general_log_filefor the general query log file (if enabled by general_log);
• slow_query_log_filefor the slow query log file (if enabled by slow_query_log);

• log_error对于错误消息日志；
• general_log_file用于一般查询日志文件（如果由 启用general_log）；
• slow_query_log_file对于慢查询日志文件（如果由 启用slow_query_log）；

To see the settings and their location, run this shell command:

要查看设置及其位置，请运行以下 shell 命令：

mysql -se "SHOW VARIABLES" | grep -e log_error -e general_log -e slow_query_log

To print the value of error log, run this command in the terminal:

要打印错误日志的值，请在终端中运行以下命令：

mysql -e "SELECT @@GLOBAL.log_error"

To read content of the error log file in real time, run:

要实时读取错误日志文件的内容，请运行：

sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")

^{Note: Hit Control-Cwhen finish}

^{注意：点击Control-C完成时}

When general log is enabled, try:

启用常规日志后，请尝试：

sudo tail -f $(mysql -Nse "SELECT CONCAT(@@datadir, @@general_log_file)")

---

---

To use mysqlwith the password access, add -por -pMYPASSparameter. To to keep it remembered, you can configure it in your ~/.my.cnf, e.g.

要mysql与密码访问一起使用，请添加-p或-pMYPASS参数。为了记住它，你可以在你的~/.my.cnf，例如

[client]
user=root
password=root

So it'll be remembered for the next time.

所以下次会记住的。

回答by Line

In my (I have LAMP installed) /etc/mysql/my.cnf file I found following, commented lines in [mysqld] section:

在我的（I安装LAMP）的/ etc / MySQL的/my.cnf文件我发现以下，在的[mysqld]节注释行：

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

I had to open this file as super user, with terminal:

我必须以超级用户身份使用终端打开此文件：

sudo geany /etc/mysql/my.cnf

(I prefer to use Geany instead of gedit or VI, it doesn't matter)

（我更喜欢用 Geany 而不是 gedit 或 VI，没关系）

I just uncommented them & save the file then restatt mysql with

我只是取消注释它们并保存文件然后用

sudo service mysql restart

Run several queries, open above file (/var/log/mysql/mysql.log) and the log was there :)

运行几个查询，打开上面的文件（/var/log/mysql/mysql.log），日志就在那里:)

回答by Mark Nenadov

From the MySQL reference manual:

来自 MySQL 参考手册：

By default, all log files are created in the data directory.

默认情况下，所有日志文件都在数据目录中创建。

Check /var/lib/mysqlfolder.

检查/var/lib/mysql文件夹。

回答by Oussama Romdhane

To complement loyola's answer it is worth mentioning that as of MySQL 5.1 log_slow_queriesis deprecated and is replaced with slow-query-log

为了补充loyola的回答，值得一提的是，自 MySQL 5.1 起log_slow_queries已弃用并替换为slow-query-log

Using log_slow_querieswill cause your service mysql restartor service mysql startto fail

使用log_slow_queries将导致您service mysql restart或service mysql start失败

回答by br3w5

In addition to the answers above you can pass in command line parameters to the mysqld process for logging options instead of manually editing your conf file. For example, to enable general logging and specifiy a file:

除了上面的答案之外，您还可以将命令行参数传递给 mysqld 进程以进行日志记录选项，而不是手动编辑您的 conf 文件。例如，要启用常规日志记录并指定文件：

mysqld --general-log --general-log-file=/var/log/mysql.general.log

Confirming other answers above, mysqld --help --verbosegives you the values from the conf file (so running with command line options general-log is FALSE); whereas mysql -se "SHOW VARIABLES" | grep -e log_error -e general_loggives:

确认上面的其他答案，mysqld --help --verbose为您提供 conf 文件中的值（因此使用命令行选项运行 general-log 为 FALSE）；而mysql -se "SHOW VARIABLES" | grep -e log_error -e general_log给出：

general_log     ON
general_log_file        /var/log/mysql.general.log

Use slightly more compact syntax for the error log:

对错误日志使用稍微紧凑的语法：

mysqld --general-log --general-log-file=/var/log/mysql.general.log --log-error=/var/log/mysql.error.log

回答by Test

shell> mysqladmin flush-logs


shell> mv host_name.err-old backup-directory