关于mysql:MySQL日志之bin-logredo-logundo-log

43次阅读

共计 6374 个字符，预计需要花费 16 分钟才能阅读完成。

1. bin log（二进制日志）

1.1 bin log 介绍

  bin log 记录了对数据库执行 更改 的所有操作（不包含查问），还包含了执行数据库更改操作的工夫和执行工夫等信息。bin log 次要有两个作用：

  • 复原（recovery）。能够通过 bin log 进行 point-in-time 的复原。
  • 复制（replication）。能够用于搭建 MySQL 主从集群。

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

# 配置 log-bin，启动 bin log
# cat /etc/mysql/my.cnf
...
[mysqld]
log-bin
server-id=1
mysql> 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)
  1. STATEMENT 格局下，bin log 文件记录的是日志的逻辑 SQL 语句。
  2. 在 ROW 格局下，bin log 记录的不再是简略的 SQL 语句，而是记录表的行更改状况。
  3. MIXED 格局下，MySQL 会动静主动抉择 STATEMENT 格局或 ROW 格局。

  如下，以 STATEMTNT 格局为例，看下 bin log 文件内容。咱们先将 binlog_format 参数设置为 STATMENT，而后执行批改语句，查看 binlog 内容。bin log 存储的是二进制，不能间接查看，须要借助命令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

查看 bin log 内容，能够看到记录了刚刚执行的插入语句

/*!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 的区别

  1. bin log 会记录所有与 MySQL 无关的日志记录，包含不同存储引擎的日志。而 redo log 是 InnoDB 存储引擎特有的，只记录无关 InnoDB 自身的事务日志。
  2. 记录的内容不同。bin log 记录的是对于一个事务的具体操作内容，次要用于 人工复原数据。而 redo log 记录的是对于每个页（Page）的更改的物理状况，如 Spaceid，PageNo，OpCode，Data，它是 InnoDB 用于保障 crash-safe 能力地，也就是事务提交后 MySQL 解体地话，能够保障事务的持久性，从而保证数据的完整性。也就是说，bin log 是用作人工复原的。redo log 是 MySQL 本人应用的，用于保障在数据库解体是的数据完整性。
  3. 写入的工夫不同。bin log 文件是在事务提交前记录的，而在事务进行的过程中，一直有 redo log 被写入日志文件中。

4. undo log

  在执行事务的过程中可能会失败，这个时候就能够利用 undo log 将数据回滚到批改之前的样子。与 redo 不同的是，redo 放在文件中，而 undo 寄存在数据库外部的一个非凡段中，称为 undo 段，undo 段位于共享表空间内（ibdata1）。
  利用 undo 回滚并不是间接将数据库复原到执行事务之前的样子，因为事务是反对并发的，如果间接复原到某个事务执行之前的样子，就可能会影响到其余事务的后果。实际上利用 undo 回滚采取的是相似 弥补 的形式，比方对每个 insert，InnoDB 存储引擎会实现一个 delete；对每个 delete，会实现一个 insert；对每个 update，会执行一个相同的 update。也就是说回滚不是物理上的复原，而是逻辑上的复原，保障回滚的事务没有更改数据库数据。

正文完
mysql
发表至： mysql
2020-08-14
 0
还在划水这个SQL你能写出来吗
关于mysql:技术分享-MySQL从库复制半个事务会怎么样
关于mysql:一条Sql查询MySql数据存储状况
关于mysql:mysql-的这个痛点用-elasticsearch-轻松解决