乐趣区

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

作者：

jiezi

在

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。也就是说回滚不是物理上的复原，而是逻辑上的复原，保障回滚的事务没有更改数据库数据。

评论

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。

更多文章