MySQL日志

774 views

一、MySQL8 日志配置介绍

MySQL8 日志除了用于发现错误之外，它在数据复制、数据恢复、操作审计、以及数据永久性和一致性方面都发挥着重要作用。学会如何查看和配置 MySQL 日志，将有效的提升您的数据库技能和快速解决问题的能力。

1.1、MySQL8 日志分类

MySQL8 数据库主要有六种类型的日志文件，用来存储不同的信息，分别是：

  • 二进制日志：记录更改数据的语句，用于数据同步和无损恢复；
  • 错误日志 ：记录 MySQL 服务异常，用于解决服务器故障；
  • 一般查询日志：记录客户端连接发送给数据库的操作指令；
  • 慢查询日志：记录超过指定时间的查询语句，方便优化 SQL；
  • 中继日志：主从架构，从服务器读取中继日志同步主服务器数据；
  • 数据定义语句日志：记录数据定义执行的元数据操作。

1.二进制日志（Binary Log）：

  • 作用：记录所有对 MySQL 数据库进行的数据更改操作（如 INSERT、UPDATE 和 DELETE），以及某些数据定义操作（如 CREATE 表）。主要用于数据恢复和复制。
  • 文件格式：以 .bin 结尾的文件，如 mysql-bin.000001。
  • 位置：通常在 MySQL 数据目录下。

2.错误日志（Error Log）：

  • 作用：记录 MySQL 服务器的启动、运行和停止过程中的错误信息、警告信息以及一些重要的服务器事件。
  • 文件格式：通常以 .err 结尾的文件，如 mysql-error.log 或 hostname.err。
  • 位置：可以在 MySQL 配置文件中设置，默认位置通常也是数据目录。

3.一般查询日志（General Query Log）：

  • 作用：记录所有对 MySQL 服务器发送的查询和连接信息。这包括正常的 SQL 查询和连接/断开连接事件。
  • 文件格式：以 .log 结尾的文件，如 mysql.log。
  • 位置：可以在 MySQL 配置文件中设置，通常默认不启用，因为它可能会生成大量日志信息。

4.慢查询日志（Slow Query Log）：

  • 作用：记录那些执行时间超过设定阈值的查询。主要用于性能调优，帮助识别执行缓慢的 SQL 查询。
  • 文件格式：以 .log 结尾的文件，如 mysql-slow.log。
  • 位置：可以在 MySQL 配置文件中设置。

5.中继日志（Relay Log）：

  • 作用：在 MySQL 主从复制环境中，从服务器（slave）用来记录从主服务器（master）接收到的二进制日志的副本。用于从服务器执行和同步主服务器上的更改。
  • 文件格式：以 .relay 结尾的文件，如 mysql-relay-bin.000001。
  • 位置：通常在从服务器的数据目录下。

6.数据定义语句日志（Data Definition Log）：

  • 作用：记录对数据库结构的更改操作，例如 CREATE、ALTER 和 DROP 语句。这个日志有时包括在二进制日志中，但也可以单独配置。
  • 文件格式：可以与二进制日志合并或单独记录。

每种日志在 MySQL 的使用和维护中都有其特定的作用，正确配置和管理这些日志对数据库的性能和稳定性至关重要。

小提示：不合理的日志配置会增加服务器开销、降低数据库性能和占用大量磁盘空间，所以学习日志配置很重要。

二、MySQL8 日志配置详细教程

2.1、MySQL8 二进制日志

二进制日志是 MySQL 中比较重要的日志类型，它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句，如数据查询语句等。二进制日志主要用于数据复制和数据恢复。

首先，打开 MySQL8 配置选项：

vim /etc/my.cnf
或：vim /etc/mysql/my.cnf
或：vim /etc/mysql/conf.d/mysqld.cnf

然后，修改日志配置路径：

[mysqld]
log-bin=/data/mysql/bin

最后，重启服务生效：

# 重启服务
systemctl restart mysql;
# 查看状态
show variables like '%log_bin%';

二进制日志格式配置，可选值：STATEMENT / ROW / MIXED。

  • STATEMENT：日志记录是基于语句的；
  • ROW：日志记录基于行，这是默认设置；
  • MIXED：日志记录使用混合格式。
[mysqld]
binlog_format=ROW

二进制日志过期时间配置，单位为秒，默认 30 天。

[mysqld]
binlog_expire_logs_seconds=2592000

二进制日志文件大小配置，单位为字节，最大值和默认值均为 1 GB。

[mysqld]
max_binlog_size=1073741824

2.2、MySQL8 错误日志

MySQL8 错误日志默认是开启的，且无法被禁用，设置错误日志路径示例：

[mysqld]
log-error=/data/mysql/error.log

2.3、MySQL8 一般查询日志

MySQL8 一般查询日志一般需要禁用，只有需要查看更多详细日志的情况开启，否则会导致磁盘暴涨。

查看一般查询日志：

show variables like '%general%';

开启一般查询日志，可选值：ON / OFF（1或0也支持）：

[mysqld]
general_log=ON
general_log_file=/data/mysql/general.log

2.4、MySQL8 慢查询日志

慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句，通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化。

开启慢查询日志，可选值：ON / OFF（1或0也支持），时间单位为秒，默认值10，最大365天：31536000。

[mysqld]
slow_query_log=ON
slow_query_log_file=/data/mysql/slow.log
# 查询时间超过1秒时，记录慢查询日志
long_query_time=1

2.5、MySQL8 中继日志

MySQL8 中继日志由一组编号的文件组成，用于 MySQL 主从架构，从服务器用来存放主服务器二进制日志的一个中间文件，从服务器读取中继日志来同步主服务器上的数据，MySQL8 中继日志默认保存在从服务器的数据目录下。

中继日志格式：从主机名-relay-bin.序号：

# 中继日志文件名示例
slave_try8-relay-bin
# 中继日志索引文件名示例
slave_try8-relay-bin.000001

查看中继日志：

show variables like '%relay%';

中继日志是保存在从服务器上，因此您应该修改从服务器的数据库配置：

[mysqld]
relay-log=/data/mysql/relay.log

2.6、MySQL8 数据定义语句日志

InnoDB 存储引擎将数据定义语句日志写入 mysql.innodb_ddl_log 表中，该表属于隐藏数据字典表，不能在非调试版本的 MySQL 中直接访问，但是您可以手动开启查看。

开启 DDL 日志，创建测试表查看日志输出：

set global innodb_print_ddl_logs=1;
create table try8 (try8_id int) engine = innodb;