undolog 是binlog_MySQL日志之binlog、redo log、undo log

1. binlog(二进制日志)

1.1 binlog介绍

binlog记录了对数据库执行更改的所有操作(不包括查询)，还包括了执行数据库更改操作的时间和执行时间等信息。binlog主要有两个作用：恢复(recovery)。可以通过binlog进行point-in-time的恢复。

复制(replication)。可以用于搭建MySQL主从集群。

默认binlog是没有开启的，可以通过show variables like 'log_bin';查看。通过在my.cnf配置log-bin[=name]可以启动binlog，如果设置name，则默认binlog文件名为主机名，后缀名为binlog的序列号，所在路径为数据库所在目录(datadir)，如：# 配置log-bin，启动bin log

# cat /etc/mysql/my.cnf

...

[mysqld]

log-bin

server-id=1mysql> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.00 sec)

mysql> system ls -lh /var/lib/mysql/;

total 249M

-rw-r----- 1 mysql mysql 154 8月 13 19:35 admin-node-bin.000001

-rw-r----- 1 mysql mysql 24 8月 13 19:35 admin-node-bin.index

-rw-r----- 1 mysql mysql 3.8K 7月 30 00:06 admin-node-slow.log

-rw-r----- 1 mysql mysql 56 6月 1 20:08 auto.cnf

-rw------- 1 mysql mysql 1.7K 6月 1 20:08 ca-key.pem

-rw-r--r-- 1 mysql mysql 1.1K 6月 1 20:08 ca.pem

上面的admin-node-bin.000001就是binlog文件，我们在配置文件中没有指定名称，所有默认用了主机名(admin-node)做了文件名。admin-node-bin.index为binlog的索引文件，用来存储过往产生的binlog序号，通常情况下，不建议手工修改这个文件。

1.2 二进制日志格式

MySQL 5.1开始引入了binlog格式，即binlog_format参数，该参数可设置的值有STATEMENT、ROT、MIXED。使用select @@binlog_format;或者show variables like 'binlog_format';可以查看当前的binlog_format参数设置，如下：mysql> select @@binlog_format;

+-----------------+

| @@binlog_format |

+-----------------+

| ROW |

+-----------------+

1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW |

+---------------+-------+

1 row in set (0.01 sec)STATEMENT格式下，binlog文件记录的是日志的逻辑SQL语句。

在ROW格式下，binlog记录的不再是简单的SQL语句，而是记录表的行更改情况。

MIXED格式下，MySQL会动态自动选择STATEMENT格式或ROW格式。

如下，以STATEMTNT格式为例，看下binlog文件内容。我们先将binlog_format参数设置为STATMENT，然后执行修改语句，查看binlog内容。binlog存储的是二进制，不能直接查看，需要借助命令mysqlbinlog。# 将binlog_format参数设置为STATEMENT

mysql> set @@session.binlog_format='STATEMENT';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@binlog_format;

+-----------------+

| @@binlog_format |

+-----------------+

| STATEMENT |

+-----------------+

1 row in set (0.00 sec)

# 执行插入语句

mysql> insert into t1 select 4, 'dfd';

Query OK, 1 row affected (0.13 sec)

Records: 1 Duplicates: 0 Warnings: 0

查看binlog内容，可以看到记录了刚刚执行的插入语句/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200814 0:07:28 server id 1 end_log_pos 123 CRC32 0x5a8577db Start: binlog v 4, server v 5.7.31-0ubuntu0.18.04.1-log created 200814 0:07:28

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

QGU1Xw8BAAAAdwAAAHsAAAABAAQANS43LjMxLTB1YnVudHUwLjE4LjA0LjEtbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

Adt3hVo=

'/*!*/;

# at 123

#200814 0:07:28 server id 1 end_log_pos 154 CRC32 0x58f58a7a Previous-GTIDs

# [empty]

# at 154

#200814 10:25:14 server id 1 end_log_pos 219 CRC32 0x7e873442 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#200814 10:25:14 server id 1 end_log_pos 302 CRC32 0x5772418a Query thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1597371914/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 302

#200814 10:25:14 server id 1 end_log_pos 410 CRC32 0xd9e9a7b8 Query thread_id=5 exec_time=0 error_code=0

use `mytest`/*!*/;

SET TIMESTAMP=1597371914/*!*/;

insert into t1 select 4, 'dfd'

/*!*/;

# at 410

#200814 10:25:14 server id 1 end_log_pos 441 CRC32 0x3a7bf86d Xid = 40

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2. redo log(重做日志)

2.1 redo log介绍

redo log是InnoDB存储引擎特有的。redo log对InnoDB存储引擎至关重要，它记录了对于InnoDB存储引擎的事务日志。

redo log的主要目的是保证数据的完整性。如数据库由于某种原因突然宕机，InnoDB存储引擎可以使用redo log恢复到宕机之前的时刻。

每个InnoDB存储引擎至少有一个redo log文件组(重做日志文件组，MySQL 5.7只能有一个redo log文件组)，每个redo log组下至少有两个redo log文件。一个redo log文件组下的每个redo log文件大小一致，并以循环方式使用。InnoDB存储引擎先写redolog1，当达到文件的最后时，会切换至redolog2，当redolog2也被写满时，会再切回到redo1。下图显示了一个拥有3个redolog文件的redo log组。

redo log的写入是先写入到日志缓冲区，然后按照一定的策略刷入磁盘。如下图所示：

2.2 redo log重要参数介绍

有几个和redo log相关的比较重要的参数，这里作下介绍。innodb_log_file_size：指定了重做日志文件的大小。

innodb_log_files_in_group：指定了redo log组中redo log文件的数量，默认为2。

innodb_mirrored_log_groups：指定了日志镜像文件组的数量，默认为1。MySQL 5.7没有这个参数了，只能由一个组。

innodb_log_group_home_dir：指定了redo log文件组所在路径，默认在数据库路径下。

innodb_flush_log_at_trx_commit：用来控制redo log刷盘的策略，可设的值有0、1、2，默认是1。0代表redo log由mysql主线程定时刷新到磁盘；1表示在事务提交时，将redo log从缓冲区同步刷新到磁盘；2表示事务提交后，异步刷新到磁盘。

2.3 redo log在事务中的作用

在InnoDB存储引擎中，事务日志通过redo log文件和InnoDB存储引擎的日志缓冲(InnoDB Log Buffer，是InnoDB存储引擎将redo log的缓冲区)来实现。当开始一个事务时，会记录该事务的一个LSN(Log Sequence Number，日志序列号)，当事务执行时，会往InnoDB存储引擎的日志缓冲里插入事务日志，当事务提交时，必须将InnoDB存储引擎的日志缓冲写入磁盘(默认的实现即innodb_flush_log_at_trx_commit=1)。也就是在数据落盘之前，需要先写日志。这种方式成为预写日志方式(Write-Ahead Logging， WAL)。

InnoDB存储引擎通过预写日志的方式来保证事务的完整性。也就是说磁盘上存储的数据页和内存缓冲池中的页是不同步的，对于内存缓冲池中页的修改，是先写入redo log文件，再写入磁盘，是一种异步的方式。可以通过show engine innodb status看着当前磁盘数据和redo log数据的状态，其中Log sequence number表示内存中当前的LSN，Log flushed up to表示刷新到redo log文件的LSN，Last checkpoint at表示最后一次检查点的LSN，即已经刷新到磁盘的LSN。因为数据是异步写入redo log和刷新的磁盘的，所以在有数据库有数据变化时上面的三个值可能是不同的。mysql> show engine innodb status\G

*************************** 1. row ***************************

......

---

LOG

---

Log sequence number 937484896

Log flushed up to 937484896

Pages flushed up to 937484896

Last checkpoint at 937484887

0 pending log flushes, 0 pending chkp writes

10 log i/o's done, 0.00 log i/o's/second

----------------------

......

3. bin log和redo log的区别bin log会记录所有与MySQL有关的日志记录，包括不同存储引擎的日志。而redo log是InnoDB存储引擎特有的，只记录有关InnoDB本身的事务日志。

记录的内容不同。bin log记录的是关于一个事务的具体操作内容，主要用于人工恢复数据。而redo log记录的是关于每个页(Page)的更改的物理情况，如Spaceid，PageNo，OpCode，Data，它是InnoDB用于保证crash-safe能力地，也就是事务提交后MySQL崩溃地话，可以保证事务的持久性，从而保证数据的完整性。也就是说，bin log是用作人工恢复的。redo log是MySQL自己使用的，用于保证在数据库崩溃是的数据完整性。

写入的时间不同。bin log文件是在事务提交前记录的，而在事务进行的过程中，不断有redo log被写入日志文件中。

4. undo log

在执行事务的过程中可能会失败，这个时候就可以利用undo log将数据回滚到修改之前的样子。与redo不同的是，redo放在文件中，而undo存放在数据库内部的一个特殊段中，称为undo段，undo段位于共享表空间内(ibdata1)。

利用undo回滚并不是直接将数据库恢复到执行事务之前的样子，因为事务是支持并发的，如果直接恢复到某个事务执行之前的样子，就可能会影响到其他事务的结果。实际上利用undo回滚采取的是类似补偿的方式，比如对每个insert，InnoDB存储引擎会完成一个delete；对每个delete，会完成一个insert；对每个update，会执行一个相反的update。也就是说回滚不是物理上的恢复，而是逻辑上的恢复，保证回滚的事务没有更改数据库数据。