【MySQL】日志

1. 日志基本了解 

常见的MySQL Server日志类型，以及记录的日志信息（场景+通俗理解）

• 错误日志
  • 记录的主要信息由服务器关闭、启动、崩溃事件；MySQL运行过程中出现的错误、警告和严重事件以及与权限、配置相关的问题
  • 使用场景
    • 诊断MySQL服务器启动失败或者运行中崩溃的原因
    • 如果遇到权限问题或者连接错误的时候，可以通过错误日志快速定位的问题
• 一般查询日志
  • 记录信息
    • 记录MySQL中接收到的每一个SQL查询，无论最终查询是否运行
    • 其中是包括连接、断开连接、SQL语句的执行情况等
  • 使用场景
    • 主要用于调试查询和监控SQL语句的执行情况
    • 如果在性能优化场景下，可以分析系统中频繁执行的SQL语句
• 慢查询日志
  • 记录信息
    • 记录哪些执行时间超过设定阈值的SQL语句
    • 一般情况下是和性能紧密相关
  • 使用场景
    • 主要用于找到那些执行时间长、影响数据库性能的查询，从而优化数据库的性能
• 二进制日志
  • 记录信息
    • 记录所有对数据库进行修改的雨具，不记录查询操作，只记录修改操作
  • 使用场景
    • 数据恢复：可以通过二进制日志将数据库恢复到某个时间点的情况
    • 主从复制：主要用于同步服务器和从服务器的数据使用
  • 二进制日志就是“修改历史”，记录了所有对数据库进行的修改的操作，帮助在发生问题的时候还原数据或者数据同步
• 中继日志
  • 记录信息：在主从复制环境中，记录从服务器接收到主服务器传送的二进制日志内容
  • 使用场景：从服务器会读取中继日志，执行主服务器上的数据变更，保持数据同步
  • 中继日志可以理解成邮差中的信件，从服务器通过中继日志来接收并执行主服务器发送过来的操作
• DDL日志
  • 主要记录数据库结构的变更
  • 使用场景：用于记录数据库对象的创建、修改删除操作
• 回滚/撤销日志
  • 主要记录信息：用于事务回滚时保存的数据快照，以便在事务失败时恢复数据到原始状态
  • 使用场景：主要用于事务的处理，特别在当某个事务失败或者需要回滚的时候，通过撤销日志恢复数据库到正确的状态
• 重做日志
  • 主要信息：记录了已经提交事务的所有操作，主要用于数据库崩溃后进行数据恢复
  • 使用场景：数据库崩溃后，用于将尚未写入磁盘的数据重新加载到数据库中，从而确保数据的一致性
  • 重做日志就类似于紧急备份日志，当数据库崩溃的时候，可以恢复那些已经完成但是还没有存储到磁盘的操作

日志使用特性

• 默认情况日志启动：Linux下默认开启错误日志和二进制日志，Windows下是只开启错误日志，不开启其他日志
• 日志写入方式：日志可以写入日志文件、也可以写入数据库中的表，这也就意味着用户可以根据需求选择记录日志的方式
  • 查询日志和慢日志是可以同时写入日志表和文件的
• 日志存储位置：默认情况下，所有启动的日志都会记录在日志目录中
  • 可以通过刷新日志的方式强制关闭当前日志文件并创建新的日志文件，确保日志数据不会过大或者影响系统性能
• 刷新日志的命令
  • 通过 FLUSH LOGS 命令，用户可以强制 MySQL 服务器关闭当前的日志文件并开启一个新的日志文件
  • 另外，命令 mysqladmin flush-logs 或 refresh 参数可以起到类似的效果
  • 在执行数据备份时，可以使用 mysqldump 命令中的 --flush-logs 或 --master-data 选项，确保日志与数据备份的一致性
• 中继日志
  • 主从复制过程中的核心内容之一，主要就是用于主服务器与从服务器之间的数据同步与恢复

2. 一般查询日志与慢查询日志

输出方式

主要有两种方式记录一般查询日志和慢查询日志，可以选择将日志写入到日志文件或者日志表中，也可以同时写入这两个地方。MySQL的log_outut变量专门负责控制输出的具体形式。

• 日志文件（FILE）：日志信息以文件的形式输出
• 日志表（TABLE）：日志信息写入到MySQL系统数据库的日志表中

一般查询日志与慢查询日志位置

配置文件（将一般查询日志和慢查询日志写入日志文件，并指定自定义查询路径） 

[mysqld]
log_outut = FILE #日志文件
general_log = 1  #开启一般查询
general_log_file = /log/mysql/general.log #指定自定义文件名slow_query_log = 1 #开启慢查询日志
slow_query_log_file = /log/mysql/slow_query.log #指定自定义文件名

配置/val/lib/mysql/my.cnf 

重启数据库查看是否修改成功

MySQL客户端配置前后对比

运行时的日志控制

• 启动查询日志

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';

• 启动错误日志

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

• 使用tail命令实时查看日志文件新增内容

2.1 一般查询日志

重要知识点总结

• 一般查询日志存储内容：客户端连接与断开信息、从客户端接收到的每个SQL语句
• 配置命令：--general_log[={0|1}]
• 默认日志名为host_name.log，但是可以通过general_log_file = file_name来进行修改
• MySQL按照接收到SQL语句顺序，将语句写入查询日志，这个顺序可能与语句的执行顺序不同

分析一般查询日志的创建语句 

• event_time：事件发生时间
• user_host：什么主机发起的请求，记录用户和主机信息
• thread_id：记录线程ID
• server_id：记录服务器ID，主要在主从复制场景下使用功能，用于区别不同服务器所产生的日志
• command_type：命令的类型，标识所执行的SQL命令类型
• argument：记录执行的SQL语句或者相关的详细信息

查看一般查询日志文件内容

启用一般查询日志（通过配置文件生效）

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log//重启生效
sudo systemctl restart mysql

cat /var/log/mysql/general.log

其他日志类似

2.2 慢查询日志

什么是慢查询日志

• 慢查询日志的判断需要满足查询时间和检查的行数是否大于指定数值两个条件
• 记录到的慢查询日志需要进行优化，可以使用mysqldumpslow客户端程序对慢查询日志进行分析汇总
• 获取初始化锁的时间是不计入执行时间的，mysqld在执行完所有的SQL语句并后才会将符合条件的语句写入慢查询日志中
• 补充：一个SQL语句执行经历的阶段
  • 执行线程获取锁，如果没有获取锁则要阻塞等待
  • 执行SQL语句并返回结果
  • 最后将线程获取的锁释放即可
• 慢查询语句计算的时间是以后两个阶段为主

慢查询日志的参数

•  long_query_time参数
  • 一般的默认值是10秒，最小可以设置为0，也就是只要超过这个设定的数值就是开启慢查询
• 默认情况
  • 不记录管理语句，同时也不会记录未使用索引的查询（查询没有利用数据库的索引时不会自动记录）
• 启动慢查询日志：--slow_query_log=[0|1]
• 慢查询默认的日志文件格式是host_name-slow.log，可以通过slow_query_log=file_name来指定新的文件名
• 简单格式记录
  • 使用--log-short-format参数可以启动简化日志记录格式
• 记录未使用索引的查询
  • 没有使用索引的查询可能会导致性能问题，这个参数允许将这样的查询记录下来，同时为了避免日志过大或者过多，可以设置限制每分钟记录的未使用功能索引的查询条数

慢查询日志输出到文件时候的参数

• Query_time：SQL语句的执行事件，也就是查询开始到结束的时间，不包括开始加锁的时间
• Lock_time：MySQL获取表锁的时间
• Rows_sent：查询返回给客户端的行数，这个查询的结果有多个行发送给客户端

开启慢查询日志、指定输出文件、设置查询时间为1秒

慢查询的表结构

• start_time：查询开始执行的时间。
• user_host：执行查询的用户和主机信息。
• query_time：查询的执行时间，单位是 time 类型。
• lock_time：查询过程中获取锁的时间。
• rows_sent：查询返回给客户端的行数。
• rows_examined：查询过程中服务器检查的行数。
• db：查询所在的数据库名称。
• last_insert_id：最后插入的自增ID。
• insert_id：当前查询影响的自增ID。
• server_id：服务器的ID。
• sql_text：执行的实际 SQL 语句。
• thread_id：MySQL 线程ID，用于标识查询所属的线程

具体代码事例

3. 错误日志

错误日志含义

• 错误日志记录的是mysqld程序的操作
• 记录MySQL服务在启动、关闭过程中的诊断信息，以及运行期间发生的错误和警告信息
• 错误日志使用UTF-8编码格式，同时使用英文生成记录

错误日志输出位置

• 默认输出目标：

  • 如果没有指定--log-error选项，默认情况下，错误日志会输出到控制台

• 默认文件路径：

  • 当使用--log-error但未指定具体文件时，MySQL会默认在数据目录中生成名为host_name.err的日志文件

• 指定日志文件路径：

  • 如果明确指定了--log-error的文件名，MySQL会在数据目录下根据该名称创建错误日志文件。如果未提供后缀名，系统会默认使用.err作为后缀
  • 也可以通过指定绝对路径来更改日志的存储位置，以便于集中管理和备份

• 控制台输出与文件记录的选择：

  • 如果设置了错误日志的输出目标为控制台（stderr），则MySQL会将日志信息显示在控制台窗口
  • 否则，log_error的值会被解析为文件路径，并按照该路径将日志记录为文件

[mysqld]
log-error=/var/log/mysql/error_log.err  # 自定义错误日志的路径

• 总结
  • 为方便系统日志的集中管理，最好还是使用绝对路径的错误日志文件，然后将其保存到/var/log目录下

 错误日志核心字段

• time（时间戳）

  • 记录事件发生的精确时间，通常精确到微秒级。这在排查问题的先后顺序时尤为关键。

• msg（消息内容）

  • 记录错误事件的详细信息，即具体的错误消息字符串，用于描述问题的类型或原因。

• prio（事件优先级）

  • 表示该事件的优先级，常见分类有：
    • System event（系统事件）：优先级最低（0），一般用于系统级通知。
    • Error event（错误事件）：优先级为1，需要紧急处理。
    • Warning（警告）：优先级为2。
    • Note/information event（信息提示）：优先级为3，记录一些状态变化或通知，提示意义较多。

• err_code（错误代码）

  • 每个错误都会分配一个错误代码，用于系统内部识别和匹配。

• err_symbol（错误符号）

  • 使用字符串来标识具体错误，例如 'ER_DUP_KEY' 表示主键重复错误。

• SQL_state（SQL状态码）

  • 与err_symbol对应的SQLSTATE值，例如 'ER_DUP_KEY' 对应的SQLSTATE为23000。这些状态码是标准化的，用于数据库系统之间的兼容。

• subsystem（子系统）

  • 记录错误的子系统来源，常见的值包括：
    • InnoDB（存储引擎）
    • Repl（复制系统）
    • Server（其他通用系统错误）

实践理解

• 修改my.cnf配置文件，设置错误日志路径

sudo nano /etc/mysql/my.cnf// 添加内容
[mysqld]
log-error=/var/log/mysql/error_log.err  # 指定错误日志路径

 

•  重新启动MySQL服务配置
•  查看错误日志中内容

4. 二进制日志

二进制日志含义理解

• 二进制日志是MySQL数据库中记录对数据更改操作的重要日志文件
• 该日志保存着所有对数据库状态产生变更的雨具，例如INSERT、UPDATE等操作，注意不会记录查询数据的语句（例如select / show）
• 主要作用就是数据复制和数据恢复，从而保证数据库在发生故障或者数据同步的时候可以有效回滚或者同步

二进制日志特点

• 影响数据库状态的事件记录：其中包括每个更新操作的执行时间信息，从而可以更好的分析数据库性能
• 不记录查询语句
• 启动二进制可能会对数据库的性能有轻微影响，因为每次数据更改都会被记录下来

二进制日志的作用

• 主从复制：从库服务器通过读取主库二进制日志并执行相应的操作，实现数据同步，最终保证了主从数据库的一致性
• 数据恢复：数据库管理员可以利用二进制日志，将数据库恢复到某个时间点状态
• 加密与安全：如果日志中涉及到敏感信息，那么就需要对二进制日志进行加密，确保不会以明文形式泄漏

二进制日志核心变量

log_bin

• 启动或者禁用二进制日志，一般默认是OFF

// 启动并设置二进制日志路径[mysqld]
log-bin = /var/log/mysql/mysql-bin.log

binlog_format

• 用于控制二进制日志格式，主要有三种格式
• ROW：记录每一行的数据变化，保证精确同步。
• STATEMENT：记录SQL语句，性能更高但可能导致数据不一致。
• MIXED：两者结合，常用于性能与数据一致性的平衡。

SET GLOBAL binlog_format = 'ROW';

expire_logs_days

• 设置二进制日志的自动清理时间，以天为单位

[mysqld]
expire_logs_days = 7  # 二进制日志保留7天

max_binlog_size

• 限制单个二进制日志文件的大小，一般默认是1G

[mysqld]
max_binlog_size = 500M  # 设置最大为500MB

sync_binlog

• 控制二进制日志在写入磁盘时间的同步频率
• 0：系统自行决定同步频率，性能高但可能丢失数据。
• 1：每次提交后都同步，确保数据一致性

[mysqld]
sync_binlog = 1

实践：启动二进制日志并进行数据恢复 

• 编写配置文件 

• 重启服务，创建数据库写入数据

• 查看该二进制日志中的内容 
• 补充最终二进制文件名为什么是mysql-bin.000001
  • MySQL对二进制日志文件的管理不是统一放在一个文件中，而是根据设定前缀名，后续就是按照顺序递增的后缀名

• 查看二进制日志的系统变量

• 查看二进制日志的状态变量 

 

 5. Redo Log 和 Undo Log

这两个日志是确保数据一致性和可靠性的关键日志机制。其在事务处理、崩溃恢复、回滚操作中起到重要作用。

Redo Log（重做日志）

• 作用
  • 崩溃恢复：当数据库发生故障后，重启Redo Log会重新应用尚未写入磁盘的数据变更，确保数据完整性
  • 记录已经提交的事务，确保数据在崩溃后能够完整地反映这些变更
• 运行机制
  • 写入机制：事务提交的时候，先将修改I的数据写入到Redo Log 中，然后再写入磁盘的数据文件
  • 循环写入：Redo Log采用循环日志文件机制，日志写满后会覆盖掉最早的日志，从而保证性能和存储的高效性
• 刷新日志的选项
  • innodb_flush_log_at_trx_commit = 1：每次事务提交时都刷盘（默认值，最安全）。
  • innodb_flush_log_at_trx_commit = 2：每秒刷盘一次。
  • innodb_flush_log_at_trx_commit = 0：不立即刷盘，性能高但有丢失风险。

[mysqld]
innodb_flush_log_at_trx_commit = 1  # 每次事务提交时都将Redo Log刷盘，确保数据安全。

Undo Log（撤销日志）

• 作用
  • 用于回滚事务：当需要撤销或者回滚一个事务的时候，Undo Log会撤销已经应用的操作，恢复数据到事务开始之前的状态
  • 支持多版本并发控制：Undo Log的历史版本数据也被用于实现多版本并发控制，从而支持读未提交或者快照隔离的事务
• 工作机制
  • 事务开始的时候就会创建一份原始数据的快照，如果事务发生了回滚，就会从Undo Log中读取快照进行恢复
  • Undo Log会存放在InnoDB表空间中，并在事务完成后逐步清理

START TRANSACTION;
INSERT INTO users (id, name) VALUES (1, 'Alice');
ROLLBACK;  -- 使用Undo Log撤销插入操作

总结 

• 这两个日志主要用来保证了MySQL事务的ACID特性
• Redo Log主要用于确保崩溃后的数据一致性，Undo Log则是用于回滚未完成的事务，并实现多版本控制

6. 服务器日志维护

基于Ubuntu系统，学习部分日志管理维护方法

常见日志文件及其路径

Ubuntu使用rsyslog和journald来管理日志，主要日志文件位于**/var/log/**目录下：

• 系统日志：/var/log/syslog（系统事件、服务信息）
• 内核日志：/var/log/kern.log（内核事件）
• 用户登录日志：/var/log/auth.log（登录认证相关）
• MySQL日志：/var/log/mysql/error.log（数据库运行错误）
• Nginx/Apache日志：/var/log/nginx/access.log，/var/log/nginx/error.log

 

日志轮换与压缩

使用Logrotate工具实现日志的自动轮换与压缩，避免日志文件占用大量磁盘文件

• 配置

• 测试

 使用journalctl管理系统日志

• 查看所有系统日志 

 

• 查看实时的日志

• 查看特定时间的日志

 

管理日志大小和清理旧日志 

• 限制journald日志的大小，限制其总大小为500MB每个单日志最大100MB

• 重启该服务然后应用配置

 

 实时监控与告警

 安装logwatch，然后设置每天发送邮件通知

sudo apt install logwatchsudo logwatch --detail high --mailto [e-mail] --service all --range today