软件开发培训班 >> 编程开发 >> MySQL


Mysql数据库中的各种日志详解

1.binlog（二进制日志）

MySQL中的binlog，全称为Binary Log（二进制日志），是MySQL数据库的一种日志文件，主要记录了数据库中所有修改了数据的SQL语句或者二进制的事件，用来保证数据的一致性与恢复 。具体来说，binlog记录了所有的DDL（Data Definition Language，数据定义语言）和DML（Data Manipulation Language，数据操作语言）语句，但不包括纯查询语句（如SELECT）和一些无需修改数据的操作（如SHOW等） 。binlog是MySQL复制和数据恢复的基础，具有以下几个主要作用 。

（1）数据复制：binlog是MySQL实现主从复制的基础 。在主从复制架构中，主服务器会记录所有修改数据的操作到binlog中，而从服务器通过读取这些binlog记录并在自己的数据库上重新执行这些操作，从而实现与主服务器数据的同步 。

（2）数据恢复：在数据库发生故障时，可以使用binlog来进行点的恢复 。例如，如果数据库在某个时间点之后的数据因为操作错误或者其他原因被破坏，可以通过恢复到最近的备份，然后使用binlog中记录的日志，重新执行从备份时间点到故障时间点之间的所有操作，从而实现数据的恢复 。

（3）审计：通过分析binlog的内容，可以查看在某个时间段内数据库中发生了哪些数据修改操作，这对于跟踪数据变更历史、进行安全审计等都非常有用 。

（4）增量备份：利用binlog可以实现增量备份，即备份自上次备份以来发生的所有数据变更 。这种备份方式比完整备份更高效，尤其是在数据量大的情况下 。

上图表示了binlog 的四种读写作用场景，即数据恢复、数据复制（数据备份）、数据库分布式读写、数据库主从读写分离 。

为了有效地使用binlog，需要在MySQL的配置文件（如my.cnf或my.ini）中开启binlog，并配置相关参数，比如：

  • log_bin：启用binlog，并设置binlog文件的名称 。
  • max_binlog_size：设置binlog文件的最大大小 。
  • binlog_format：设置binlog的格式，可以是STATEMENT（基于SQL语句）、ROW（基于行的变化）或MIXED（混合模式） 。
  • expire_logs_days：管理binlog还需要注意其大小，因为随着时间的推移，binlog可能会占用大量磁盘空间 。因此，需要定期清理或归档旧的binlog文件，以释放空间并保持系统性能 。可以通过设置expire_logs_days参数来自动删除过期的binlog文件 。

Statement每一条会修改数据的sql都会记录在binlog中 。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能 。
Row5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改 。
优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节 。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题 。
Mixed从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合 。

2.错误日志

MySQL的错误日志是MySQL数据库中用于记录数据库启动、运行或停止时出现的问题的日志文件 。这些记录包括MySQL服务器的各种错误、警告和重要的系统信息，对数据库管理员进行故障排查和性能调优非常有用 。错误日志具有以下几个重要作用 。

（1）故障诊断

  • 错误日志提供了关于MySQL服务器运行中发生的错误的详细信息，如启动失败、连接错误、语法错误等 。
  • 管理员可以通过查看错误日志中的错误信息，快速定位问题的原因，进而采取相应的解决措施 。

（2）系统检控

  • 通过定期检查错误日志，可以及时发现并处理潜在的问题，如配置错误、性能瓶颈或安全风险 。
  • 错误日志中的信息可以帮助管理员了解系统的运行状态，从而进行优化和调整 。

（3）恢复参考

  • 在发生系统崩溃或意外中断的情况下，错误日志可以提供崩溃前的最后活动记录，这对于数据恢复和系统重启有重要参考价值 。

启用错误日志

在MySQL的配置文件（通常是my.cnf或my.ini）中，可以设置错误日志的文件路径：

[mysqld]
log_error = /path/to/your/log-file.err

如果没有明确设置log_error选项，MySQL通常会在数据目录中自动创建一个错误日志文件 。错误日志的查看可以直接通过访问其文件路径来进行，或者通过相关的管理工具来查看 。随着运行时间的增长，错误日志的大小可能会增长很快，特别是在出现很多错误或警告的情况下 。定期轮换和备份日志文件是一个好习惯，可以使用如logrotate这样的工具来帮助管理日志文件 。

3.事务日志

mysql的事务有 4 种特性：原子性、一致性、隔离性和持久性 。那么事务的四种特性到底是基于什么机制实现呢？

事务的隔离性由锁机制实现 。

而事务的原子性、一致性和持久性由事务的 redo 日志和 undo 日志来保证 。

  • Redo Log：又称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性 。记录的是物理级别上的页修改操作，比如页号xxx、偏移量 yyy 写入了 ‘zzz’ 数据 。主要为了保证数据的可靠性；
  • Undo Log：又称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性 。记录的是逻辑操作日志，比如对某一行数据进行了 INSERT 语句操作，那么 undo log 就记录一条与之相反的 DELETE 操作 。主要用于事务的回滚（undo log 记录的是每个修改操作的逆操作）和一致性非锁定读（undo log 回滚行记录到某种特定的版本—MVCC，即多版本并发控制）

3.1Redo日志

InnoDB 存储引擎是以页为单位来管理存储空间的 。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问 。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘（checkPoint 机制），通过缓冲池来优化 CPU 和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快 。

InnoDB 引擎的事务采用了 WAL 技术 (Write-Ahead Logging )，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是 redo log 。当发生宕机且数据未刷到磁盘的时候，可以通过 redo log 来恢复，保证 ACID 中的 D，这就是 redo log 的作用 。

redo日志的组成

Redo log 可以简单分为以下两个部分：

（1）重做日志缓冲 (redo log buffer)，保存在内存中，是易失的 。在服务器启动时就向操作系统申请了一大片称之为 redo log buffer 的连续内存空间，翻译成中文就是 redo 日志缓冲区 。这片内存空间被划分成若干个连续的 redo log block 。一个 redo log block 占用 512 字节大小 。

（2）重做日志文件 (redo log file)，保存在硬盘中，是持久的 。

redo的整体流程如下图

第 1 步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝；

第 2 步：生成一条重做日志并写入 redo log buffer，记录的是数据被修改后的值；

第 3 步：当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file，对 redo log file采用追加写的方式；

第 4 步：定期将内存中修改的数据刷新到磁盘中；

3.2Undo日志

undo log 是事务原子性的保证 。在事务中更新数据的前置操作其实是要先写入一个 undo log 。

（1）事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做 。但有时候事务执行到一半会出现一些情况，比如：

情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误， 操作系统错误 ，甚至是突然断电导致的错误 。

情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行 。

（2）以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合原子性要求 。

（3）每当我们要对一条记录做改动时（这里的改动可以指 INSERT、DELETE、UPDATE，不包括 SELECT），都需要"留一手"——即把回滚时所需的东西记下来 。比如：

你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录融掉就好了 。(对于每个INSERT，InnoDB 存储引擎会完成一个DELETE）

你删除了一·条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了 。（对于每个DELETE，InnoDB 存储引擎会执行一个 INSERT）你修改了一个记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了 。（对于每个UPDATE，InnoDB 存储引擎会执行一个相反的 UPDATE，将修改前的行放回去）

（4）MySQL 把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志（即 undo log） 。注意，由于查询操作 (SELECT) 并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的 undo 日志 。此外，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护 。

undo+redo日志生成过程（简要）

（1）假设有 2 个数值，分别为 A = 1 和 B = 2，然后将 A 修改为3 ，B 修改为 4 。

1. start transaction;
2. 记录 A = 1 到 undo log;
3. update A = 3;
4. 记录 A = 3 到 redo log;
5. 记录 B = 2 到 undo log;
6. update B =4;
7. 记录 B = 4 到 redo log;
8. 将 redo log 刷新到磁盘;
9. commit;

在 1 ' 8 步骤的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响 。
如果在 8 ' 9 之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交
因为此时 redo log 已经持久化 。若在 9 之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据 redo log 把数据刷回磁盘 。

4.慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志类型，用于记录执行时间超过特定阈值的SQL查询 。这个功能主要用来识别数据库操作中的性能瓶颈，从而帮助数据库管理员优化查询语句和改进数据库性能，其主要作用如下：

（1）性能分析

  • 慢查询日志提供了一个记录所有执行时间超过预设阈值的SQL语句的机制 。这使得开发者和数据库管理员能够识别和优化那些对性能影响最大的查询 。

（2）优化数据库

  • 通过分析慢查询日志中的信息，可以确定需要添加索引的地方、需要改写的查询、可能存在的表设计问题等 。这有助于减少数据库的响应时间和提升应用的整体性能 。

（3）监控与警报

  • 在一些高级的监控系统中，慢查询日志可以被用作生成性能警报的依据，及时通知管理员采取行动 。

如何启用慢查询日志及其相关参数

（1）启用慢查询日志

在MySQL的配置文件（通常是my.cnf或my.ini）中设置以下参数以启用慢查询日志：

[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/your/log-file
long_query_time = 2

其中，slow_query_log用于启用慢查询日志，slow_query_log_file指定日志文件的存储位置，long_query_time设置查询执行的时间阈值（单位是秒），超过这个时间的查询会被记录 。

总结

到此这篇关于Mysql数据库中的各种日志的文章就介绍到这了,更多相关Mysql各种日志内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！