MySQL 日志类别详解

目录

一. 错误日志

2. 错误日志详细程度

3. 日志输出服务

4. 常见的错误日志内容

二. 二进制日志

1. 启用二进制日志：

2. 二进制日志文件：

3. 使用场景：

4. 二进制日志格式

binlog_format

binlog_row_image

注意事项

5. 二进制日志查看

6. 二进制日志删除

三. 查询日志

查看查询日志状态

开启查询日志

四. 慢查询日志 

启用慢查询日志

与慢查询日志相关的参数表格

---

注: 系统为Centos7-2009mini   MySQL8.0.39

一. 错误日志

mysql>  show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.01 sec)

1. 错误日志路径：通常默认情况下，日志文件位于 /var/log/mysql/error.log。

SHOW VARIABLES LIKE 'log_error'; --查询错误日志路径

2. 错误日志详细程度

log_error_verbosity 参数控制日志中记录的详细信息级别：

• 1：只记录错误。
• 2：记录错误和警告。
• 3：记录错误、警告和通知信息。

SHOW VARIABLES LIKE 'log_error_verbosity';   --查看错误日志详细程度

3. 日志输出服务

log_error_services 参数列出了 MySQL 用来处理和输出错误日志的服务，例如 log_filter_internal 和 log_sink_internal。这些服务控制日志的过滤和写入位置。

SHOW VARIABLES LIKE 'log_error_services';

4. 常见的错误日志内容

MySQL 错误日志文件通常会包含以下类型的信息：

• 服务器启动和关闭的时间戳。
• 发生的严重错误和故障信息。
• 警告和通知消息，例如表的修复或升级建议。
• 二进制日志记录过程中的错误信息。

二. 二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，是以一种紧凑的二进制格式存储的，但 不包括数据查询（SELECT、SHOW）语句。

作用：

①. 灾难时的数据恢复；

②. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，且默认是Row格式。

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

参数解释:

log_bin    ON
                          
/* log_bin：启用或禁用二进制日志记录功能。
   将此参数设置为 ON 表示开启二进制日志。
   二进制日志用于记录所有修改数据库的语句，
   特别用于主从复制和数据恢复。*/

log_bin_basename       /var/lib/mysql/binlog
       
/* log_bin_basename：指定二进制日志文件的基本名称及存储路径。
   该路径和文件名用于生成二进制日志文件，
   如 binlog.000001。日志文件的实际路径由 log_bin_basename 决定。*/

log_bin_index     /var/lib/mysql/binlog.index

/* log_bin_index：指定二进制日志索引文件的路径。
   该文件存储了所有二进制日志文件的列表。*/

log_bin_trust_function_creators   OFF

/* log_bin_trust_function_creators：控制是否允许在二进制日志中记录没有 `DETERMINISTIC` 或 `NO SQL` 标记的存储函数创建。
   如果设置为 OFF，MySQL 要求存储函数和触发器要么声明为确定性的，要么不修改数据。*/

log_bin_use_v1_row_events      OFF
     
/* log_bin_use_v1_row_events：控制二进制日志中使用的行事件格式版本。
   设置为 OFF 时，使用更高效的 V2 行事件格式。 */

sql_log_bin       ON

/* sql_log_bin：控制当前会话是否记录二进制日志。
   设置为 ON 表示会话的所有更新操作都会记录到二进制日志中。*/

1. 启用二进制日志：

在 MySQL 配置文件中,通常是 my.cnf ，启用二进制日志的配置项为 log_bin。例如：

log_bin = mysql-bin

2. 二进制日志文件：

二进制日志以编号的文件名存储在 MySQL 的数据目录中，例如 binlog.000001、binlog.000002 等。

查看二进制日志：

• 使用 MySQL 客户端可以查看二进制日志文件的内容和状态信息。例如，使用以下命令查看当前的二进制日志状态：

  SHOW BINARY LOGS;

• 使用 mysqlbinlog 工具可以查看和分析具体的二进制日志内容。

查看当前的二进制日志文件

SHOW MASTER STATUS;

查看某个二进制文件详细信息

SHOW BINLOG EVENTS IN 'binlog.000001';

3. 使用场景：

• 数据恢复和复制：通过二进制日志可以重放数据库的修改操作，实现主从复制或点播恢复。
• 故障恢复：在故障后，可以使用二进制日志来还原数据库到故障前的状态。

4. 二进制日志格式

二进制日志的格式和配置主要由两个参数控制：binlog_format 和 binlog_row_image。

 查看当前二进制的格式是什么的命令：

SHOW VARIABLES LIKE 'binlog_format';

binlog_format

这个参数决定了二进制日志的记录格式，可以选择的值有：

• Statement：默认格式，记录SQL语句的原始文本。
• Row：记录被修改的每一行数据的变化。
• Mixed：根据具体的SQL语句类型自动选择使用Statement或者Row格式。

一般来说：

• Statement格式：记录SQL语句，适合于简单的SQL操作，但是在涉及到非确定性函数（如NOW()）和存储过程时可能会导致主从不一致。

• Row格式：记录实际数据行的变化，更为精确和安全，但会增加日志的大小和I/O负载。

你可以通过修改MySQL配置文件（如my.cnf）来设置这个参数，例如：

[mysqld]
binlog_format=row

binlog_row_image

这个参数用于在binlog_format为ROW时，控制二进制日志如何记录行的变化。可以选择的值有：

• FULL：记录完整的新旧数据，适用于任何行变更情况，但会增加日志的大小。
• MINIMAL：只记录变化的数据，适用于大多数情况，可以减少日志大小。

例如，设置binlog_row_image为FULL：

[mysqld]
binlog_format=row
binlog_row_image=FULL

注意事项

• 修改这些参数后，需要重启MySQL服务才能生效。
• 在设置二进制日志格式时，需要考虑数据库的复制需求和性能影响。
• 不同的日志格式会对数据库的性能和主从同步的正确性产生影响，需要根据具体场景进行选择和优化。

5. 二进制日志查看

--help：显示帮助信息。
mysqlbinlog --help

--start-datetime：指定开始时间，格式为YYYY-MM-DD HH:MM:SS。
mysqlbinlog --start-datetime="2024-08-19 00:00:00" /path/to/binlog-file

--stop-datetime：指定结束时间，格式为YYYY-MM-DD HH:MM:SS。
mysqlbinlog --stop-datetime="2024-08-19 23:59:59" /path/to/binlog-file

--start-position：指定开始位置。
mysqlbinlog --start-position=107 /path/to/binlog-file

--stop-position：指定结束位置。
mysqlbinlog --stop-position=200 /path/to/binlog-file

-d（或 --database）：只显示与指定数据库相关的事件。
mysqlbinlog --database=test_db /path/to/binlog-file

--tables：只显示指定表的事件。
mysqlbinlog --tables=employees /path/to/binlog-file

--verbose：显示详细信息。
mysqlbinlog --verbose /path/to/binlog-file

--no-defaults：不读取默认配置文件。
mysqlbinlog --no-defaults /path/to/binlog-file

--skip-gtids：忽略 GTID 事件。
mysqlbinlog --skip-gtids /path/to/binlog-file

--execute：将二进制日志的内容直接应用到 MySQL 实例中（需要超级权限）。使用时需谨慎。
mysqlbinlog --execute /path/to/binlog-file | mysql -u username -p

-o（或 --offset）：忽略掉日志中的前 n 行命令。
mysqlbinlog -o 100 /path/to/binlog-file

-v（或 --verbose）：将行事件（数据变更）重构为 SQL 语句。
mysqlbinlog -v /path/to/binlog-file

-vv（或 --verbose --verbose）：将行事件（数据变更）重构为 SQL 语句，并输出注释信息。
mysqlbinlog -vv /path/to/binlog-file

6. 二进制日志删除

删除指定日志文件

PURGE BINARY LOGS TO 'mysql-bin.000012';

这将删除 mysql-bin.000012 之前的所有二进制日志文件。

或者

删除指定日期之前的所有日志：

PURGE BINARY LOGS BEFORE '2024-08-18 00:00:00';

这将删除在 2024 年 8 月 18 日 00:00:00 之前的所有二进制日志文件。

删除除所有不再需要的旧日志文件，只保留当前正在使用的日志文件 

PURGE BINARY LOGS BEFORE NOW();

删除当前所有的二进制日志文件，并重新初始化二进制日志文件的序列号。

RESET MASTER;

三. 查询日志

查询日志记录所有发送到 MySQL 服务器的查询（包括对数据库的所有操作语句等）。默认情况下， 查询日志是未开启的。

查看查询日志状态

SHOW VARIABLES LIKE '%general%';

开启查询日志

临时开启:

SET GLOBAL general_log = 'ON';

 永久开启: 在 MySQL 配置文件（my.cnf ）中添加以下行

[mysqld]
#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启
general_log = 1
#设置日志的文件名 ， 如果没有指定， 默认的文件名为 localhost.log
general_log_file = /var/lib/mysql/localhost.log

开启了查询日志之后，在MySQL的数据存放目录，也就是 /var/lib/mysql/ 目录下就会出现localhost.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。

四. 慢查询日志 

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

查看慢查询状态

SHOW VARIABLES LIKE 'slow_query%';

启用慢查询日志

编辑配置文件 /etc/my.cnf，编辑配置文件 /etc/my.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

注释：
slow_query_log = 1：启用慢查询日志。
slow_query_log_file：指定慢查询日志文件的路径。
long_query_time：设置慢查询的时间阈值（以秒为单位）。在这个时间内执行的查询将被记录。
log_queries_not_using_indexes：记录没有使用索引的查询（可选）。

与慢查询日志相关的参数表格

参数名	默认值	说明
slow_query_log	OFF	是否开启慢查询日志
slow_query_log_file		慢查询日志文件路径
long_query_time	10.000000	慢查询的时间阈值（秒），超过该时间的查询会被记录到慢查询日志中
log_queries_not_using_indexes	OFF	是否记录未使用索引的查询到慢查询日志中
log_throttle_queries_not_using_indexes	0	限制每分钟记录未使用索引的查询次数，0表示不限制。该参数可用于防止日志文件过大。
min_examined_row_limit	0	查询至少扫描的行数阈值，低于该阈值的查询不会被记录到慢查询日志中，即使查询时间超过了long_query_time
log_slow_admin_statements	OFF	是否记录管理语句（如ALTER TABLE、ANALYZE TABLE等）到慢查询日志中
log_slow_slave_statements	OFF	在从服务器上，是否记录慢查询日志。仅在从服务器上有效。
expire_logs_days	0	慢查询日志文件保留天数，0表示不自动删除。