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

2021-11-20 00:00:00 logging mysql

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

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

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

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?

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

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

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

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

这是我的配置文件:

[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

推荐答案

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

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.

  • 错误日志.它包含有关发生的错误的信息服务器正在运行(也是服务器启动和停止)

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

一般查询日志.这是mysqld是什么的一般记录做(连接、断开、查询)

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

慢查询日志.它由慢"的 SQL 语句组成(如由其名称指示).

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

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

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

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

To Enable them just follow below steps:

步骤 1: 转到此文件 (/etc/mysql/conf.d/mysqld_safe_syslog.cnf) 并删除或注释这些行.

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

step2: 转到 mysql conf 文件 (/etc/mysql/my.cnf) 并添加以下行

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

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

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

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

要启用一般查询日志添加以下内容

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

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

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

step3:保存文件并使用以下命令重启mysql

step3: save the file and restart mysql using following commands

service mysql restart

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

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

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.

点击此处访问博客

相关文章