MySQL log

在 MySQL 数据库中，BinLog、RedoLog、UndoLog 都是数据库运行后产生的日志文件，但它们的作用的范围有所不同。其中 RedoLog 和 UndoLog 只适用于 InnoDB 存储引擎，而 BinLog 在 MyISAM 这种存储引擎也可以生效。

BinLog

BinLog 主要用在数据库的数据备份、复制、同步等操作中，它是记录了数据库执行的所有 DDL 语句和 DML 语句的一种二进制日志，BinLog 日志具体记录了对索引对数据库结构和数据操作的日志（例如INSERT、UPDATE、DELETE）。

关于 DDL 和 DML 的说明如下：DDL（Data Definition Language）和DML（Data Manipulation Language）是SQL语言的两个主要组成部分。它们的区别如下：

  • 定义和操作内容不同

    DDL用于定义或改变数据库的结构，如创建数据库、表、索引等；而DML则用于对数据库中的数据进行操作，如插入、更新、删除数据。

  • 影响范围不同

    DDL命令会影响整个数据库或表；而DML命令通常只影响表中的一个或多个记录。

  • 回滚特性不同

    执行DDL命令后，如果需要回滚，通常无法回滚；而执行DML命令后，如果操作涉及的数据存在问题，可以进行回滚。

BinLog 支持三种格式，分别是 statement、row(after 5.1.5)、mixed(after5.1.8)

  • statement

    statement格式的日志在 RR 的事务隔离级别下生效。设置 statement 格式的日志会将 SQL 语句原文原封不动的记录到日志中，但这可能造成主从同步数据不一致的问题。

    例如：DELETE 或者 UPDATE 语句使用了 LIMIT 限制条数，但是并没有使用 ORDER BY 进行排序，语句在主库和从库上执行的最终结果可能是不一样的。

  • row

    row 格式的日志至少在 RC 的事务隔离级别下生效。设置 row 格式的日志会将每个数据更改的具体行的细节记录下来，详细列出发生变更的行的数据以及行变更内容，这意味着日志将要记录更多的内容，这也导致更多的资源占用。

  • mixed

    mixed 格式的日志结合了 statement 和 row 格式，根据执行 SQL 语句的情况，自动切换，在两个格式中选择合适的格式进行记录，节约服务器资源。

    当事务隔离级别为 RC 时，mixed 会强制使用 row 格式记录，当事务隔离级别为 RR 时，两种格式都有可能被记录。

开启BinLog的操作

  1. 修改 MySQL 配置文件 my.ini 或者 my.cnf，添加以下配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    # 配置 server id
     server-id=1
    # 设置日志三种格式：STATEMENT、ROW、MIXED（8.0.36后续版本可能弃用此参数）
    binlog_format = row
    # 设置binlog清理时间（8.0.36的后续版本可能弃用 expire_logs_days 参数）
    #expire_logs_days = 7
    # 用于替代 expire_logs_days 参数的binlog清理时间
    binlog_expire_logs_seconds = 25200
    # 设置日志路径，注意此路经需要运行MySQL的用户有权限读写
    log-bin = ../binlog/mysql-bin.log
    # binlog每个日志文件大小
    max_binlog_size = 500m
    # binlog缓存大小
    binlog_cache_size = 4m
    # 最大binlog缓存大小
    max_binlog_cache_size = 3096m

  2. 重启

重启完成后可以执行 SHOW VARIABLES LIKE 'log_bin'; 语句查询是否开启成功了，返回结果为 ON 或者 1 表示 binlog 已经启用。

RedoLog

RedoLog 是 MySQL 用于实现崩溃恢复和数据持久的一种机制，在执行提交事务时，MySQL 会将事务做的改动记录到 Redo Log 中，当发生异常情况时，MySQL 会利用日志中记录的信息来进行恢复操作，将事务所做的修改进行持久化存储。

RedoLog 的目的是为了保证事务提交的持久化，主要用于崩溃恢复，记录了事务的所有数据修改（包含具体操作、最终结果）。

UndoLog

UndoLog 是 MySQL 用于实现事务回滚或系统崩溃时回滚事务所做的修改（未提交），在执行事务的过程中，MySQL 会将事务修改前的数据记录到日志中，如果事务需要回滚，则会从日志中找到相应的记录来回滚事务所作的操作。

UndoLog 支持 MVCC（多版本并发控制），用于在并发执行事务时提供一定的隔离性。