MySQL 日志

MySQL 日志

Undo Log

Undo Log 介绍

undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。

Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo Log，撤销未提交事务对数据库产生的影响。

Undo Log 的生命周期：

Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 undo log，InnoDB 会将该事务对应的 undo log 放入删除列表中，后面会通过后台线程 purge thread 进行回收处理。

Undo Log 属于逻辑日志，记录一个变化过程。例如执行一个 delete 操作，undo log 会记录一个对应的 insert 语句；执行一个 update 操作，undo log 会记录一个相反的 update。

Undo Log 存储：undo log 采用段的方式管理和记录。在 InnoDB 数据文件中包含一种 rollback segment 回滚段，内部包含 1024 个 undo log segment。可以通过下面一组参数来控制 Undo Log 存储。

show variables likes '%innodb_undo%';

Undo Log 作用

• 实现事务的原子性

  Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 操作，MySQL可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

• 实现多版本并发控制（MVCC）

  Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

Redo Log

Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外是重现操作。

Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

Redo Log 的生成和释放：随着事务操作的执行，就会生成 Redo Log，在事务提交时会将产生 Redo Log 写入 Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log 占用的空间就可以重用（被覆盖写入）。

Redo Log 工作原理

Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo Log 写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

如图所示：

• write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；

• check point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

• write pos 和 check point 之间还空着的部分，可以用来记录新的操作；

• 如果 write pos 追上 check point ，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 check point 推进一下。

Redo Log 相关配置参数

每个 InnoDB 存储引擎至少有一个重做日志文件组（group），每个文件至少有 2 个重做日志文件，默认为 ib_logfile0 和 ib_logfile1 。可以通过以下参数控制 Redo Log 存储：

show variables like '%innodb_log%';

Redo Buffer 持久到 Redo Log 的策略，可以通过 innodb_flush_log_at_trx_commit 设置：

• 0 ： 每秒提交 Redo Buffer => OS cache => 磁盘，可能丢失一秒内的事务数据。由后台 Master 线程每隔 1 秒执行一次操作。
• 1（默认值）：每次事务提交执行 Redo Buffer => OS cache => 磁盘，最安全、性能最差的方式。
• 2：每次事务提交执行 Redo Buffer => OS cache ，然后由后台 Master 线程再每隔 1 秒执行 OS cache => 磁盘

一般建议选择设置为 2 ，因为 MySQL 挂了数据不会丢失，整个服务器挂了才会丢失 1 秒的事务提交数据。

Binlog 日志

Binlog 记录模式

Redo Log 是属于 InnoDB 引擎所特有的日志，而 MySQL Server 也有自己的日志，即 Binary log（二进制日志），简称 Binlog。Binlog 是记录所有数据库表结构变更以及数据表数据修改的二进制日志，不会记录 SELECT 和 SHOW 这类操作。Binlog 日志是以时间形式记录，还包含语句所执行的消耗时间。开启 Binlog 日志有以下两个最重要的使用场景：

• 主从复制：

  在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到 Binlog 后实现数据恢复达到主从数据一致性。

• 数据恢复：

  通过 mysqlbinlog 工具来恢复数据。

Binlog 文件名默认为“主机名_binlog-序列号”格式，例如 xx_mysql-bin.003422 ，也可以在配置文件中指定名称。文件记录模式有 STATEMENT、ROW 和 MIXED 三种，具体含义如下：

• ROW（row-based replication，RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。
  • 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
  • 缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨
• STATMENT（statement-based replication, SBR）：每一条被修改数据的 SQL 都会记录到 master 的 Binlog 中，slave 在复制的时候，会再次执行和 master 一样的 SQL ，简称 SQL 语句复制。
  • 优点：日志量小，减少磁盘 IO，提升存储和恢复速度
  • 缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()，now()等函数，或者从库只有主库的一部分表，主库表更新是基于从库不存在的表。.
• MAXED（mixed-based replication，MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 Binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 Binlog，MySQL 会根据执行的 SQL 语句选择写入模式。

Binlog 文件结构

MySQL 的 Binlog 文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是 Log Event。不同的修改操作对于不同的 Log Event。比较常用的 Log Event 有：Query Event、Row Event、Xid Event 等。Binlog 文件的内容就是各种 Log Event 的集合。

Binlog 文件中 Log Event 结构如下图所示：

Binlog 写入机制

• 根据记录模式和操作触发 Event 事件生成 Log Event（事件触发执行机制）

• 将事务执行过程中产生 Log Event 写入缓冲区，每个事务线程都有一个缓冲区

  Log Event 保存在一个 binlog_cache_mngr 数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的信息；另一个是 trx_cache，用于存放支持事务的信息。

• 事务在提交阶段会将产生的 Log Event 写入到外部 binlog 文件中，不同事务以串行方式将 Log Event写入binlog 文件中，所以一个事务包含的 Log Event 信息在 binlog 文件中是连续的，中间不会插入其他事务的 Log Event。

Binlog 文件操作

• Binlog 状态查看

  show variables like "%log_bin%";
  

• 开启 Binlog 功能

  set global log_bin=mysqllogbin;
  

  需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启 MySQL服务。

  #log-bin=ON
  #log-bin-basename=mysqlbinlog
  binlog-format=ROW
  log-bin=mysqlbinlog
  

• 使用 show binlog events 命令

  # 等价于show master logs;
  show binary logs;
  show master status;
  show binlog events;
  show binlog events in 'mysqlbinlog.000001';
  

• 使用 mysqlbinlog 命令

  mysqlbinlog "文件名"
  mysqlbinlog "文件名" > "test.sql"
  

• 使用 binlog 恢复数据

  # 按指定时间恢复
  mysqlbinlog --start-datetime="2021-01-01 00:00:00" --stopdatetime="2021-01-01 23:59:59" mysqlbinlog.000002 | mysql -uroot -p
  # 按事件位置号恢复
  mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p
  

  mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

• 删除 binlog 文件

  # 删除指定文件
  purge binary logs to 'mysqlbinlog.000001'; 
  # 删除指定时间之前的文件
  purge binary logs before '2021-01-01 00:00:00'; 
  # 清除所有文件
  reset master;
  

  可以通过设置 expire_logs_days 参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

  # BINARY LOGGING #
  log-bin                        = /data/mysql/data/mysql-bin
  # 保留14天的binlog
  expire-logs-days               = 14
  binlog-format                  = mixed
  sync-binlog                    = 1
  

Redo Log和 Binlog 区别

• Redo Log 是属于 InnoDB 引擎功能，Binlog 是属于 MySQL Server 自带功能，并且是以二进制文件记录。
• Redo Log 属于物理日志，记录该数据页更新状态内容，Binlog 是逻辑日志，记录更新过程。
• Redo Log 是循环写，日志空间大小固定，Binlog 是追加写入，写完一个写下一个，不会覆盖使用
• Redo Log 作为服务器异常宕机后事务数据自动恢复使用，Binlog 可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。