【MySQL】我在广州学Mysql 系列——Mysql 日志管理详解

ℹ️大家好，我是练小杰，今天又是新的一周了，又该摆好心态迎接美好的明天了！！！😆
本文主要对Mysql数据库中的日志种类以及基本命令进行讨论！！

回顾：👉【MySQL 数据备份与还原】
数据库专栏：👉【数据库专栏】【Mysql练习题】

主页： 【练小杰的CSDN】

日志简介

• MySQL日志主要分为4类

• 错误日志 ，记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。
• 查询日志，记录建立的客户端连接和执行的语句。
• 二进制日志，记录所有更改数据的语句。可以用于数据复制。
• 慢查询日志，记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

MySQL 日志的作用

• 故障排查：用于记录错误信息，帮助定位问题。

• 性能优化：分析慢查询日志，优化繁琐的 SQL 语句。

• 数据恢复：通过二进制日志实现数据恢复。

• 审计与安全：记录用户操作，便于审计和安全分析。

日志的管理与维护

• 日志轮转：定期清理和归档日志文件，避免占用过多磁盘空间。主要使用 logrotate 工具管理日志文件。

• 日志备份：定期备份重要日志（如二进制日志），用于数据恢复。

• 日志分析：使用工具（如 pt-query-digest）分析慢查询日志，优化 SQL 性能。

二进制日志

二进制日志（Binary Log）是MySQL中非常重要的日志类型，用于记录所有更改数据库的操作。它主要用于数据复制和数据恢复。以下是有关二进制日志的基本操作命令。

启动和设置二进制日志

二进制日志记录了所有更改数据的语句，如INSERT、UPDATE、DELETE等。它主要用于数据复制（主从复制）和数据恢复。

• 在MySQL配置文件（my.cnf或my.ini）中，可以通过以下参数启用和设置二进制日志：

[mysqld]
log_bin=/var/log/mysql/binlog
server-id=1

代码说明

• log_bin：指定二进制日志文件的路径和名称。
• server-id：为服务器指定一个唯一的ID，用于主从复制。

查看二进制日志

• 使用 SHOW BINARY LOGS 命令查看当前服务器上的二进制日志文件列表：

  SHOW BINARY LOGS;
  

• 要查看某个二进制日志文件的内容，可以使用 mysqlbinlog 工具：

  mysqlbinlog  /var/log/mysql/binlog.000001
  

删除二进制日志

• 自动删除

我们可以通过设置二进制日志的过期时间来自动删除旧的二进制日志文件。比如以下代码配置，表示二进制日志文件将在7天后自动删除。

[mysqld]
expire_logs_days=7

• 手动删除

使用 PURGE BINARY LOGS 命令手动删除二进制日志文件。

PURGE BINARY LOGS TO 'binlog.000003';

使用二进制日志还原数据库

• 数据恢复

我们可以利用 mysqlbinlog 工具将二进制日志应用到数据库，以恢复数据。例如，恢复到某个时间点：

mysqlbinlog --stop-datetime="2025-02-16 23:45:30" /var/log/mysql/binlog.000001 | mysql -u root -p

以上命令将恢复 binlog.000001 文件中截止到指定时间点的数据。

暂时停止二进制日志功能

• 临时停止：如果需要临时停止二进制日志功能，可以在MySQL会话中执行以下命令。

SET SQL_LOG_BIN=0;

• 若是需要重新启用二进制日志记录，可以执行：

SET SQL_LOG_BIN=1;

⚠️注意：停止二进制日志记录可能会影响数据复制和数据恢复功能，因此应谨慎使用。

错误日志（Error Log）

MySQL中非常重要的日志类型，用于记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。它对于故障排查和系统维护至关重要。

启动和设置错误日志

错误日志记录了MySQL服务器启动、运行或停止过程中出现的关键错误信息。它有助于数据库管理员（DBA）进行故障排查和安全审计。

• 配置：在MySQL配置文件（my.cnf或my.ini）中，可以通过以下参数启用和设置错误日志：

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

其中， log_error 用于指定错误日志文件的路径和名称。

查看错误日志

我们可以使用tail 命令查看错误日志的内容，比如，下列命令 将显示错误日志文件末尾的100行内容，帮助快速定位最近的错误信息。

tail -n 100 /var/log/mysql/error.log

删除错误日志

• 自动删除

可以通过日志轮转工具（比如logrotate）定期轮转和压缩日志文件，防止磁盘空间不足。

• 手动删除

还手动删除旧的错误日志文件，但是建议在删除之前备份重要的日志文件。

mv /var/log/mysql/error.log /var/log/mysql/error.log.bak

通用查询日志

通用查询日志（General Query Log）是MySQL中用于记录所有客户端连接和执行的语句的日志类型。它对于调试和审计非常有用，但是会显著增加日志文件的大小。

启动和设置通用查询日志

在MySQL配置文件（my.ini或my.cnf）中，可以通过以下参数启用和设置通用查询日志。

[mysqld]
general_log=1
general_log_file=/var/log/mysql/general.log

• general_log：启用或禁用通用查询日志（1表示启用，0表示禁用）。
• general_log_file：指定通用查询日志文件的路径和名称。

查看通用查询日志

• 查看方法如下：

通用查询日志是以文本文件的形式存储在文件系统中的。我们可以使用文本编辑器直接打开通用日志文件进行查看，Windows下可以使用记事本，Linux下可以使用vim和gedit等。

删除通用查询日志

• 删除方法

可以用直接删除日志文件的方式删除通用查询日志。 即找到目标文件，直接删除即可。

• 重新建立新的通用查询日志文件

mysqladmin -u root -p flush-logs

慢查询日志（Slow Query Log）

慢查询日志（Slow Query Log）是MySQL中用于记录所有执行时间超过指定阈值的查询语句的日志类型。它对于识别和优化性能瓶颈非常有帮助。

启动和设置慢查询日志

MySQL中慢查询日志默认是关闭的，可通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开。
我们也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name]启动慢查询日志。

[mysqld]
log-slow-queries[=path/[filename]]

查看慢查询日志

MySQL的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。比如，window中可以用记事本打开文件。

删除慢查询日志

慢查询日志也可以直接删除。删除后在不重启服务器的情况下。

• 重新生成慢查询日志文件

mysqladmin -u root -p flush-logs
flush logs

综合示例——有关MySQL日志的综合管理

步骤1：设置启动二进制日志，并指定二进制日志文件名为binlog.log

打开my.ini（或者my.cnf）在[mysqld]组下添加如下内容：

[mysqld]
log-bin=binlog.log
net stop mysql 
net start mysql

步骤2：将二进制日志文件存储路径改为d:/log

• 首先打开my.ini（或者my.cnf）在[mysqld]组下修改如下内容：

[mysqld]
log-bin=d:/log/binlog.log
net stop mysql 
net start mysql

步骤3： 查看flush logs对二进制日志的影响。

mysqladmin flush-logs
FLUSH LOGS;

• 接下来，二进制日志会发生以下变化。

1. 当前正在使用的二进制日志文件（d:/log/binlog.log）会被关闭。
2. 创建新的二进制日志文件：系统会创建一个新的二进制日志文件，文件名通常是当前文件名加上一个递增的序号。当前文件是binlog.log，新的文件可能会是binlog.000001
3. 文件序号递增：如果二进制日志文件的命名方式包含序号（如binlog.000001），那么新文件的序号会比当前文件大1。

步骤4：查看二进制日志。

mysqlbinlog d:\log\binlog.000001

• 执行结果示例：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190328 10:45:00 server id 1 end_log_pos 107 CRC32 0x5e2f5a3d  Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1553754300/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 107
#190328 10:45:00 server id 1 end_log_pos 194 CRC32 0x8e890f1a  Query   thread_id=1 exec_time=0 error_code=0
use `test_db`/*!*/;
SET TIMESTAMP=1553754300/*!*/;
INSERT INTO `test_table` VALUES (1, 'test')
/*!*/;
# at 194
#190328 10:45:00 server id 1 end_log_pos 225 CRC32 0xafc9c3f1  Xid = 42
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

• 头部信息：包含了一些设置和分隔符。
• BEGIN：表示一个事务的开始。
• use test_db：切换到test_db数据库。
• INSERT INTO test_table VALUES (1, 'test')：执行的SQL语句。
• COMMIT：提交事务。
• 尾部信息：包含了一些清理操作。

步骤5：使用二进制日志还原数据

USE test
CREATE TABLE member(id INT AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(50));   /*创建数据库*/
INSERT INTO member VALUES(NULL, 'Playboy1');
INSERT INTO member VALUES(NULL, 'Playboy2');
SET SQL_LOG_BIN=0;    /*暂停二进制日志功能*/
drop table member;    /*删除数据表*/
SELECT * FROM member;
SET SQL_LOG_BIN=1;   /*打开二进制日志功能*/
mysqlbinlog d:/log/binlog.0000001 | mysql -u root -p   /*还原数据表*/
SELECT * FROM member;

步骤6：删除二进制日志

RESET MASTER;

步骤7：暂停和重新启动二进制日志

SET sql_log_bin = 0;   /*暂停日志*/
SET sql_log_bin =1;    /*打开日志*/

步骤8：设置启动错误日志。

[mysqld]
log-error

步骤9：设置错误日志的文件为d:/log/error_log.err

[mysqld]
log-error=d:/log/error_log.err

步骤10：查看错误日志

打开d:/log/error_log.err文件，可以使用任何文本编辑器，如记事本（Notepad）、Notepad++、Sublime Text等。

• 还可以通过MySQL客户端查看

SHOW VARIABLES LIKE 'log_error';

• 这将返回错误日志文件的路径，例如：

+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| log_error     | d:/log/error_log.err |
+---------------+----------------------+

步骤11：设置启动通用查询日志

设置通用查询日志文件为：d:/log/ gneral_query.log

[mysqld]
log =d:/log/general_query.log

步骤13：设置启动慢查询日志

设置慢查询日志的文件为d:/log/ slow_query.log，并设置记录查询时间超过3秒的语句。

[mysqld]
log-slow-queries=d:/log/slow_query.log
long_query_time=3

步骤14：查看慢查询日志。

查询方法和查询错误日志一样，都可以使用任何文本编辑器。

• 通过MySQL客户端查看慢查询日志的位置：

SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log_file | d:/log/slow_query.log |
+---------------------+---------------------+

• 使用以下命令查看慢查询日志的状态：

SHOW STATUS LIKE 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 10    |
+---------------+-------+

本文有关Mysql数据库的日志相关内容已讲完了，明天再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！