[Mysql] redo log 与 binlog 的区别

迷南。 2022-04-16 06:15 139阅读 0赞

redo log（重做日志）和 binlog（归档日志）

# MySQL redo log 与 binlog 的区别 #

*  **什么是redo log**
 *  **什么是binlog**
 *  **redo log与binlog的区别**

## 1. 什么是redo log？ ##

redo log又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

### 1.1 redo日志文件名 ###

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，如默认的`ib_logfile0`和`ib_logfile1`。  
![wbDzJFm.png][]

### 1.2 影响redo log参数 ###

*  `innodb_log_file_size`：指定每个redo日志大小，默认值48MB

*  `innodb_log_files_in_group`：指定日志文件组中redo日志文件数量，默认为2
 *  `innodb_log_group_home_dir`：指定日志文件组所在路劲，默认值`./`，指mysql的数据目录`datadir`
 *  `innodb_mirrored_log_groups`：指定日志镜像文件组的数量，默认为1，此功能属于未实现的功能，在5.6版本中废弃，在5.7版本中删除了。
 *  以下显示了一个关于redo日志组的配置：
    
    ![复制代码][Image 1]
    
        mysql> show variables like 'innodb%log%';
        
        +----------------------------------+------------+
        | Variable_name                    | Value      |
        +----------------------------------+------------+
        ...     
        | innodb_log_file_size             | 2147483648 |
        | innodb_log_files_in_group        | 2          |
        | innodb_log_group_home_dir        | ./         |
        ...
        +----------------------------------+------------+
        15 rows in set (0.00 sec)
    
    ![复制代码][Image 1]
    
     
    
    ### 1.3 redo log大小怎么设置？ ###
    
    redo log文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。
    
     *  **设置的太大**  
        设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。
 *  **设置的太小**  
    当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。  
    ![Y2m7CCA.png][]
 *  **怎么设置？**  
    参考官方文档'[Optimizing InnoDB Redo Logging][]'章节
    
     *  把重做日志文件设置很大，甚至与缓冲池一样大。当InnoDB将重做日志文件写满时，它会触发数据库的检查点，把缓冲池的脏数据写入磁盘。小的重做日志文件会导致许多不必要的磁盘写入。虽然在以前版本中，大的重做日志文件导致冗长的恢复时间，但现在恢复速度更快，可以放心地使用大型重做日志文件。
 *  考虑增加日志缓冲区的大小。 大型日志缓冲区可以在事务提交之前运行大型事务，而无需将日志写入磁盘。 因此，如果您有更新，插入或删除许多行的事务，则使日志缓冲区更大可以节省磁盘I/O. 使用[`innodb_log_buffer_size`][innodb_log_buffer_size]配置选项配置日志缓冲区大小。
 *  设置[`innodb_log_write_ahead_size`][innodb_log_write_ahead_size]参数，表示redo log写前的块大小。InnoDB以512字节一个block的方式对齐写入ib\_logfile文件，但文件系统一般以4096字节为一个block单位。如果即将写入的日志文件块不在OS Cache时，就需要将对应的4096字节的block读入内存，修改其中的512字节，然后再把该block写回磁盘。当 当前写入文件的偏移量不能整除该值时，则补0，多写一部分数据。这样当写入的数据是以磁盘block size对齐时，就可以直接write磁盘，而无需read-modify-write这三步了。
 *  ## [2. 什么是binlog][2. _binlog]  ##
    
    binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制日志。例如：
    
    ![复制代码][Image 1]
    
        root@localhost [(none)] 08:30:14>set binlog_format = 'STATEMENT';
    
    root@localhost \[(none)\] 08:30:26>use test;  
    Database changed  
    root@localhost \[test\] 08:30:33>select \* from account;  
    \+–--------±--------+  
    | acct\_num | amount |  
    \+–--------±--------+  
    | 138 | 14.98 |  
    | 141 | 1937.50 |  
    | 97 | -100.00 |  
    \+–--------±--------+  
    3 rows in set (0.00 sec)
    
    root@localhost \[test\] 08:30:53>show master status;  
    \+–--------------------±---------±-------------±-----------------±-------------------------------------------+  
    | File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
    \+–--------------------±---------±-------------±-----------------±-------------------------------------------+  
    | my3306\_binlog.000052 | 471 | | | e4382832-949d-11e8-97ba-080027793430:1-205 |  
    \+–--------------------±---------±-------------±-----------------±-------------------------------------------+  
    
    
    root@localhost \[test\] 08:31:04>update account set acct\_num=139 where amount=14;  
    Query OK, 0 rows affected (0.01 sec)  
    Rows matched: 0 Changed: 0 Warnings: 0
    
    root@localhost \[test\] 08:31:35>show binlog events in ‘my3306\_binlog.000052’;  
    \+–--------------------±----±---------------±----------±------------±--------------------------------------------------------------------+  
    | Log\_name | Pos | Event\_type | Server\_id | End\_log\_pos | Info |  
    \+–--------------------±----±---------------±----------±------------±--------------------------------------------------------------------+  
    | my3306\_binlog.000052 | 4 | Format\_desc | 1003306 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |  
    | my3306\_binlog.000052 | 123 | Previous\_gtids | 1003306 | 194 | e4382832-949d-11e8-97ba-080027793430:1-204 |  
    | my3306\_binlog.000052 | 194 | Gtid | 1003306 | 259 | SET @@SESSION.GTID\_NEXT= ‘e4382832-949d-11e8-97ba-080027793430:205’ |  
    | my3306\_binlog.000052 | 259 | Query | 1003306 | 331 | BEGIN |  
    | my3306\_binlog.000052 | 331 | Table\_map | 1003306 | 384 | table\_id: 108 (test.account) |  
    | my3306\_binlog.000052 | 384 | Update\_rows | 1003306 | 440 | table\_id: 108 flags: STMT\_END\_F |  
    | my3306\_binlog.000052 | 440 | Xid | 1003306 | 471 | COMMIT / xid=14 / |  
    | my3306\_binlog.000052 | 471 | Gtid | 1003306 | 536 | SET @@SESSION.GTID\_NEXT= ‘e4382832-949d-11e8-97ba-080027793430:206’ |  
    | my3306\_binlog.000052 | 536 | Query | 1003306 | 615 | BEGIN |  
    | my3306\_binlog.000052 | 615 | Query | 1003306 | 736 | use `test`; update account set acct\_num=139 where amount=14 |  
    | my3306\_binlog.000052 | 736 | Query | 1003306 | 816 | COMMIT |  
    \+–--------------------±----±---------------±----------±------------±--------------------------------------------------------------------+  
    11 rows in set (0.01 sec)
    
    ![复制代码][Image 1]
    
     
    
    从上述例子中可以看到，MySQL数据库首先进行update操作，从返回的结果看到Changed为0，这意味着该操作并没有导致数据库的变化。但是通过`show binlog events in '...'`可以看出在二进制日志中的确进行了记录。
    
    如果想记录SELECT和SHOW操作，那只能使用查询日志`--general_log[={0|1}]`(1为启用)
    
    ### 2.1 binlog文件名 ###
    
    通过配置参数`--log-bin[=name]`可以启动二进制日志。如果不指定那么，则默认binlog日志文件名为主机名，后缀名为binlog的序列号，默认路劲为数据目录(datadir).你也可以指定绝对路径，如：
    
    ![复制代码][Image 1]
    
        # cat /etc/my.cnf|grep log-bin
        log-bin = /data/mysql/mysql3306/logs/my3306_binlog
    
    # cd /data/mysql/mysql3306/logs #
    
    # ls -l #
    
    total 60  
    \-rw-r–— 1 mysql mysql 194 Aug 15 10:04 my3306\_binlog.000045  
    \-rw-r–— 1 mysql mysql 1552 Aug 16 10:01 my3306\_binlog.000046  
    \-rw-r–— 1 mysql mysql 2953 Aug 17 09:56 my3306\_binlog.000047  
    \-rw-r–— 1 mysql mysql 1239 Aug 20 10:29 my3306\_binlog.000048  
    \-rw-r–— 1 mysql mysql 217 Aug 20 10:29 my3306\_binlog.000049  
    \-rw-r–— 1 mysql mysql 19567 Aug 21 10:24 my3306\_binlog.000050  
    \-rw-r–— 1 mysql mysql 194 Aug 22 08:01 my3306\_binlog.000051  
    \-rw-r–— 1 mysql mysql 816 Aug 22 08:31 my3306\_binlog.000052  
    \-rw-r–— 1 mysql mysql 384 Aug 22 08:01 my3306\_binlog.index
    
    ![复制代码][Image 1]
    
     
    
    ### [2.2 影响binlog的参数][2.2 _binlog] ###
    
     *  **max\_binlog\_size**:指定单个binlog文件最大值。默认值为1g，最大值1g，如果超过该值，则产生新的binlog文件，后缀名+1，并记录到`.index`文件。
     *  **binlog\_cache\_size**：使用事务表存储引擎（如innodb存储引擎）时，所有未提交的binlog日志会被记录到一个缓存中去，等事务提交时再将缓存中的binlog写入到binlog文件中。缓存的大小由binlog\_cache\_size决定，默认大小为32K。
        
        binlog\_cache\_size是基于session的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog\_cache\_size的缓存，因此该值的设置需要非常小心，不能设置过大。  
        当一个事务的记录大于设定的binlog\_cache\_size时，MySQL会把缓存中的日志写入一个临时文件中，因此该值又不能设的太小。
        
        **那怎么设置呢？**
        
        ![复制代码][Image 1]
        
            通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态，以判断当前binlog_cache_size设置是否合适。
    
    binlog\_cache\_use：记录了使用缓存写binlog次数  
    binlog\_cache\_disk\_use：记录了使用临时文件写binlog次数。
    
    示例：
    
    root@localhost \[(none)\] 09:55:48>show variables like ‘binlog\_cache\_size’;  
    \+–-----------------±--------+  
    | Variable\_name | Value |  
    \+–-----------------±--------+  
    | binlog\_cache\_size | 32768 |  
    \+–-----------------±--------+  
    1 row in set (0.00 sec)
    
    root@localhost \[(none)\] 09:53:26>show global status like ‘binlog\_cache%’;  
    \+–---------------------±------+  
    | Variable\_name | Value |  
    \+–---------------------±------+  
    | Binlog\_cache\_disk\_use | 0 |  
    | Binlog\_cache\_use | 33553 |  
    \+–---------------------±------+  
    2 rows in set (0.00 sec)
    
    使用缓存次数为33553，临时文件使用次数为0。说明32KB的缓存大小对于当前MySQL数据库是够用的。
    
    ![复制代码][Image 1]
 *  **max\_binlog\_cache\_size**:如果事务需要的内存超过很多字节，则服务器会生成多于“max\_binlog\_cache\_size”字节的存储错误所需的并发事务。 最小值为4096字节，最大可能值为16EB（exabytes）。 **建议的最大值为4GB**; 这是因为MySQL目前无法使用大于4GB的二进制日志位置。
 *  **expire\_logs\_days**：表示binlog文件自动删除N天前的文件。默认值为0，表示不自动删除，最大值99.要手动删除binlog文件，可以使用`purge binary logs`语句。例如：
    
        PURGE { BINARY | MASTER } LOGS
          { TO 'log_name' | BEFORE datetime_expr }
 *  PURGE BINARY LOGS TO ‘mysql-bin.010’;  
    PURGE BINARY LOGS BEFORE ‘2008-04-02 22:46:26’;  
    PURGE BINARY LOGS BEFORE now() - interval 3 days;
    
     
    
     *  **binlog\_rows\_query\_log\_events**：默认为不启用，启用binlog\_rows\_query\_log\_events时，会在binlog日志中记录原始SQL语句。
        
        ![复制代码][Image 1]
        
            root@localhost [test] 08:07:52>show binlog events in 'my3306_binlog.000056';
            +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
            | Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                                |
            +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
            | my3306_binlog.000056 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.23-log, Binlog ver: 4                               |
            | my3306_binlog.000056 | 123 | Previous_gtids |   1003306 |         194 | e4382832-949d-11e8-97ba-080027793430:1-206                          |
            | my3306_binlog.000056 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:207' |
            | my3306_binlog.000056 | 259 | Query          |   1003306 |         331 | BEGIN                                                               |
            | my3306_binlog.000056 | 331 | Table_map      |   1003306 |         375 | table_id: 108 (test.t)                                              |
            | my3306_binlog.000056 | 375 | Update_rows    |   1003306 |         421 | table_id: 108 flags: STMT_END_F                                     |
            | my3306_binlog.000056 | 421 | Xid            |   1003306 |         452 | COMMIT /* xid=16 */                                                 |
            | my3306_binlog.000056 | 452 | Gtid           |   1003306 |         517 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:208' |
            | my3306_binlog.000056 | 517 | Query          |   1003306 |         589 | BEGIN                                                               |
            | my3306_binlog.000056 | 589 | Table_map      |   1003306 |         633 | table_id: 108 (test.t)                                              |
            | my3306_binlog.000056 | 633 | Write_rows     |   1003306 |         673 | table_id: 108 flags: STMT_END_F                                     |
            | my3306_binlog.000056 | 673 | Xid            |   1003306 |         704 | COMMIT /* xid=18 */                                                 |
            | my3306_binlog.000056 | 704 | Gtid           |   1003306 |         769 | SET @@SESSION.GTID_NEXT= 'e4382832-949d-11e8-97ba-080027793430:209' |
            | my3306_binlog.000056 | 769 | Query          |   1003306 |         841 | BEGIN                                                               |
            | my3306_binlog.000056 | 841 | Rows_query     |   1003306 |         887 | # insert into t select 3                                            |
            | my3306_binlog.000056 | 887 | Table_map      |   1003306 |         931 | table_id: 108 (test.t)                                              |
            | my3306_binlog.000056 | 931 | Write_rows     |   1003306 |         971 | table_id: 108 flags: STMT_END_F                                     |
            | my3306_binlog.000056 | 971 | Xid            |   1003306 |        1002 | COMMIT /* xid=24 */                                                 |
            +----------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------+
            
            #黄颜色标记的就是，开启binlog_rows_query_log_events选项时，显示的原始SQL语句。
        
        ![复制代码][Image 1]
    
     
    
     *  **sync\_binlog**：sync\_binlog=\[N\]表示没写缓冲N次就同步到磁盘，如果将N设为1，即sync\_binlog表示采用同步写磁盘的方式来写二进制日志，在MySQL5.7.7后，默认为1。会对数据库的IO系统带来一定影响，但可以得到最大的高可用行。
     *  **binlog\_checksum**：该参数目的就是写入binlog进行校验，有两个值\[crc32|none\],默认为crc32
     *  **binlog-do-db**：表示需要写入日志的数据库，默认为空，表示同步所有库
     *  **binlog-ignore-db**：表示忽略写入日志的数据库，默认为空，表示同步所有库
     *  **log-slave-update**:表示从master端取得并执行的binlog，写入自己的binlog文件中，一般应用在master=>slave=>slave架构
     *  [**binlog\_format**][binlog_format]：记录binlog的格式。\[statement,row,mixed\],在MySQL5.7.7之后，默认为row。
        
        **存储引起对binlog格式的支持情况：**  
        ![nFgK19H.png][]
    
    ### 2.3 查看binlog ###
    
    使用`mysqlbinlog`程序进行查看，例如：
    
    ![复制代码][Image 1]
    
        [root@mysqldb1 10:58:18 /data/mysql/mysql3306/logs]
        # mysqlbinlog -v --base64-output=decode-rows my3306_binlog.000052|more
    
    /!50530 SET @@SESSION.PSEUDO\_SLAVE\_MODE=1/;  
    /!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0/;  
    DELIMITER /!/;
    
    # at 4 #
    
    \#180822 8:01:00 server id 1003306 end\_log\_pos 123 CRC32 0xcbe20047 Start: binlog v 4, server v 5.7.23-log created 180822 8:01:00 at startup
    
    # Warning: this binlog is either in use or was not closed properly. #
    
    ROLLBACK/!/;
    
    # at 123 #
    
    \#180822 8:01:00 server id 1003306 end\_log\_pos 194 CRC32 0xb1bda518 Previous-GTIDs
    
    # e4382832-949d-11e8-97ba-080027793430:1-204 #
    
    # at 194 #
    
    \#180822 8:10:59 server id 1003306 end\_log\_pos 259 CRC32 0xeced9ada GTID last\_committed=0 sequence\_number=1 rbr\_only=yes  
    /!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;  
    SET @@SESSION.GTID\_NEXT= ‘e4382832-949d-11e8-97ba-080027793430:205’/!/;
    
    # at 259 #
    
    \#180822 8:10:59 server id 1003306 end\_log\_pos 331 CRC32 0x6da7802a Query thread\_id=2 exec\_time=0 error\_code=0  
    SET TIMESTAMP=1534896659/!/;  
    SET @@session.pseudo\_thread\_id=2/!/;  
    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=45/!/;  
    SET @@session.lc\_time\_names=0/!/;  
    SET @@session.collation\_database=DEFAULT/!/;  
    BEGIN  
    /!/;
    
    # at 331 #
    
    \#180822 8:10:59 server id 1003306 end\_log\_pos 384 CRC32 0xf239dd79 Table\_map: `test`.`account` mapped to number 108
    
    # at 384 #
    
    \#180822 8:10:59 server id 1003306 end\_log\_pos 440 CRC32 0xef6460fe Update\_rows: table id 108 flags: STMT\_END\_F
    
    ### UPDATE `test`.`account` ###
    
    ### WHERE ###
    
    ### @1=137 ###
    
    ### @2=14.98 ###
    
    ### SET ###
    
    ### @1=138 ###
    
    ### @2=14.98 ###
    
    # at 440 #
    
    \#180822 8:10:59 server id 1003306 end\_log\_pos 471 CRC32 0x360f05d0 Xid = 14  
    COMMIT/!/;
    
    # at 471 #
    
    \#180822 8:31:35 server id 1003306 end\_log\_pos 536 CRC32 0x662c8f17 GTID last\_committed=1 sequence\_number=2 rbr\_only=no  
    SET @@SESSION.GTID\_NEXT= ‘e4382832-949d-11e8-97ba-080027793430:206’/!/;
    
    # at 536 #
    
    \#180822 8:31:35 server id 1003306 end\_log\_pos 615 CRC32 0xa728a60a Query thread\_id=3 exec\_time=0 error\_code=0  
    SET TIMESTAMP=1534897895/!/;  
    BEGIN  
    /!/;
    
    # at 615 #
    
    \#180822 8:31:35 server id 1003306 end\_log\_pos 736 CRC32 0x7513aa73 Query thread\_id=3 exec\_time=0 error\_code=0  
    use `test`/!/;  
    SET TIMESTAMP=1534897895/!/;  
    update account set acct\_num=139 where amount=14  
    /!/;
    
    # at 736 #
    
    \#180822 8:31:35 server id 1003306 end\_log\_pos 816 CRC32 0x1cd7f41c Query thread\_id=3 exec\_time=0 error\_code=0  
    SET TIMESTAMP=1534897895/!/;  
    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/;
    
    ![复制代码][Image 1]
    
     
    
    ## 3. redo log与binlog的区别 ##
    
    **第一**:redo log是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层产生的，并且二进制日志不仅仅针对INNODB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
    
    **第二**：两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。
    
    **第三**：两种日志与记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
    
    二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于innodb存储引擎的重做日志，由于其记录是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，其在文件中记录的顺序并非是事务开始的顺序。
    
    **第四**：binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redo log是循环使用。
    
    **第五**：binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

[wbDzJFm.png]: https://i.imgur.com/wbDzJFm.png
[Image 1]: 
[Y2m7CCA.png]: https://i.imgur.com/Y2m7CCA.png
[Optimizing InnoDB Redo Logging]: https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-logging.html
[innodb_log_buffer_size]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size
[innodb_log_write_ahead_size]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_write_ahead_size
[2. _binlog]: https://dev.mysql.com/doc/refman/5.7/en/binary-log.html
[2.2 _binlog]: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html
[binlog_format]: https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
[nFgK19H.png]: https://i.imgur.com/nFgK19H.png