MySQL系列之二进制日志Binlog学习笔记

谁践踏了优雅 2021-09-02 08:45 450阅读 0赞

学习准备：

*  Mysql服务于端（5.7+版本）
 *  Mysql客户端软件（workbench、sqlyog etc.）

### 1、什么是Binlog？ ###

在[上一篇博客][Link 1]的学习，我们知道了InnoDB存储引擎的两种事务日志，redo log是InnoDB特有的功能，而MySQL也是有自己的日志机制的，也即本文学习的binlog

binlog（二进制日志）：binary log，简称是binlog，binlog记录所有数据库表结构变更以及表数据修改，**而不会记录SELECT和SHOW这类操作**，数据保存的是二进制数据

binlog以事件的形式保存，还包括sql执行所需的时间等等信息，开启Binlog日志有以下两个最重要的使用场景：

>  *  主从复制：binlog的特性可以被应用于主从复制，主库（master）开启binlog功能，从库（salve）通过binlog的事件记录，将数据同步到数据库
>  *  数据恢复：binlog可以用于在数据恢复，因为binlog记录了sql的变更修改操作日志，如果数一不小心删库了，就可以通过binlog进行数据恢复

### 2、Binlog记录模式 ###

Binlog日志的文件名默认是“主机名\_binlog-序列号”的形式，eg：`adminstrator_binlog-000001`，当然也可以在配置文件修改命名为mysqlbinlog：

[mysql]
    log-bin=mysqlbinlog

binlog对log event进行记录，文件记录模式有STATEMENT、ROW和MIXED三种，具体含义：

>  *  Row模式：row-based replication，简称RBR，日志会记录每一行数据被修改的情况，如果用于主从复制，在slave数据库会对相同的数据进行修改
>  *  STATEMENT模式：statement-based replication, 简称SBR，这种模式，没错修改变更的sql都会被记录在日志里
>  *  MIXED模式：mixed-based replication, 简称MB，这种模式，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

> 在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 `binlog-format` 指定，如 `binlog-format=STATEMENT`、`binlog-format=ROW`、`binlog-format=MIXED`。

### 3、Binlog文件结构 ###

binlog机制中用来表示保存修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等，可以获取一份log来看看：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #201012 11:26:13 server id 1 end_log_pos 124 CRC32 0x253ff34e Start: binlog v 4, server v 8.0.13 created 201012 11:26:13 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG ' 1cyDXw8BAAAAeAAAAHwAAAABAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADVzINfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgFO8z8l '/*!*/;
    # at 124
    #201012 11:26:14 server id 1 end_log_pos 155 CRC32 0xf80d0acf Previous-GTIDs
    # [empty]
    # at 155
    #201012 14:04:52 server id 1 end_log_pos 228 CRC32 0xe767e3f3 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1602482692499600 immediate_commit_timestamp=1602482692499600 transaction_length=183
    # original_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
    # immediate_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
    /*!80001 SET @@session.original_commit_timestamp=1602482692499600*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 228
    #201012 14:04:52 server id 1 end_log_pos 338 CRC32 0x8f981f33 Query thread_id=9 exec_time=0 error_code=0 Xid = 16
    SET TIMESTAMP=1602482692/*!*/;
    SET @@session.pseudo_thread_id=9/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1168113696/*!*/;
    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=33/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
    /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
    create database `test`
    /*!*/;
    # at 338
    #201012 14:31:46 server id 1 end_log_pos 411 CRC32 0xc6dbb42e Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1602484306791600 immediate_commit_timestamp=1602484306791600 transaction_length=181
    # original_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
    # immediate_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
    /*!80001 SET @@session.original_commit_timestamp=1602484306791600*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

画表格表示其主要信息：

<table> 
 <thead> 
  <tr> 
   <th>信息</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>position</td> 
   <td>位于文件中的位置，表示事件记录从文件第?个字节开始</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>事件开始的时间戳</td> 
  </tr> 
  <tr> 
   <td>server_id</td> 
   <td>服务器的server id</td> 
  </tr> 
  <tr> 
   <td>end_log_pos</td> 
   <td>表示下一个事件开始的位置</td> 
  </tr> 
  <tr> 
   <td>thread_id</td> 
   <td>执行该事件的线程id</td> 
  </tr> 
  <tr> 
   <td>exec_time</td> 
   <td>事件执行的花费时间</td> 
  </tr> 
  <tr> 
   <td>error_code</td> 
   <td>错误码，0意味着没有发生错误</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>事件类型Query</td> 
  </tr> 
 </tbody> 
</table>

### 4、Binlog写入机制 ###

binlog 什么时候刷新到磁盘？binlog刷数据到磁盘跟参数 `sync_binlog` 相关

*  如果设置为0：则表示MySQL不控制binlog的刷新，由文件系统去控制日志的刷新；
 *  如果设置为不为0的值：则表示每 `sync_binlog` 次事务，MySQL调用文件系统刷新binlog到磁盘中

然后binlog具体是怎么刷日志的？画图表示：  
![在这里插入图片描述][20201016105621498.png_pic_center]

*  根据记录模式和操作触发log事件从而生成log event
 *  将事务执行过程的log event写入缓存区，缓存区是一种binlog\_cache\_mngr数据结构，该结构中有两个缓冲区，一个是stmt\_cache，用于存放不支持事务的信息；另一个是trx\_cache，用于存放支持事务的信息。
 *  事务在提交阶段会将产生的log event写入到磁盘binlog文件中

### 5、Binlog命令操作 ###

*  启用Binlog  
    通过命令`set global log_bin=1;`启动，也可以在my.ini配置文件加上配置：

[mysql]
    #log-bin=ON
    binlog-format=ROW
    log-bin=mysqlbinlog

不能通过命令配置，只能修改my.ini配置文件  
![在这里插入图片描述][20201012112641340.png_pic_center]

*  确定binlog是否启动  
    ![在这里插入图片描述][20201012135944639.png_pic_center]
 *  查看binlog相关参数  
    ![在这里插入图片描述][20201012114627797.png_pic_center]
 *  设置binlog\_format模式

set global binlog_format='ROW';

*  binlog events命令

//等价于show master logs;
    show binary logs; 
    //查看最新一个binlog日志文件名称和Position
    show master status;
    //查看 binlog 内容
    show binlog events;
    //查看 指定binlog 内容
    show binlog events in 'mysqlbinlog.000001';

*  mysqlbinlog 命令

// 查看指定的binlog日志文件
    mysqlbinlog "文件名"
    // 查看指定binlog文件并保存到test.sql文件
    mysqlbinlog "文件名" > "test.sql"

要到对应目录，不然就需要指定路径：  
![在这里插入图片描述][20201012150447334.png_pic_center]  
查看test.sql：  
![在这里插入图片描述][20201012161155865.png_pic_center]

*  使用 binlog 恢复数据

// 按指定时间恢复
    mysqlbinlog --start-datetime="2020-10-25 18:00:00" --stop-
    datetime="2020-10-26 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123

// 按事件位置号恢复
    mysqlbinlog --start-position=592 --stop-position=708 mysqlbinlog.000001 | mysql -u root -p

*  清除binlog文件

指定日志文件

purge binary logs to 'mysqlbinlog.000001';

按时间清日志

purge binary logs before '2020-10-10 00:00:00';

清除所有日志

reset master; //清除所有文件

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

mysql> show binary logs;
    +--------------------+-----------+
    | Log_name           | File_size |
    +--------------------+-----------+
    | mysqlbinlog.000001 |       519 |
    +--------------------+-----------+
    1 row in set (0.00 sec)
    
    mysql> show master status\G;
    *************************** 1. row ***************************
                 File: mysqlbinlog.000001
             Position: 519
         Binlog_Do_DB:
     Binlog_Ignore_DB:
    Executed_Gtid_Set:
    1 row in set (0.00 sec)
    
    mysql> show binlog events \G;
    ... 
    mysql> show binlog events in 'mysqlbinlog.000001'\G;
    *************************** 1. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 4
     Event_type: Format_desc
      Server_id: 1
    End_log_pos: 124
           Info: Server ver: 8.0.13, Binlog ver: 4
    *************************** 2. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 124
     Event_type: Previous_gtids
      Server_id: 1
    End_log_pos: 155
           Info:
    *************************** 3. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 155
     Event_type: Anonymous_Gtid
      Server_id: 1
    End_log_pos: 228
           Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
    *************************** 4. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 228
     Event_type: Query
      Server_id: 1
    End_log_pos: 338
           Info: create database `test` /* xid=16 */
    *************************** 5. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 338
     Event_type: Anonymous_Gtid
      Server_id: 1
    End_log_pos: 411
           Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
    *************************** 6. row ***************************
       Log_name: mysqlbinlog.000001
            Pos: 411
     Event_type: Query
      Server_id: 1
    End_log_pos: 519
           Info: drop database `test` /* xid=40 */
    6 rows in set (0.00 sec)

### 6、Redo Log和Binlog区别 ###

*  Redo log是**InnoDB**特有的功能，属于事务日志；Binlog是MySQL的功能
 *  Redo log不是二进制文件，而Binlog是一种二进制的日志文件
 *  **Redo log是物理日志，记录的是数据页更新的状态内容，Binlog是逻辑日志，记录的是更新过程**
 *  Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用
 *  Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，不会覆盖写

对于redo log可以参考我之前博客：[MySQL系列之事务日志redo log学习笔记][Link 1]

### 附录：参考资料 ###

*  [MySQL Binlog 介绍][MySQL Binlog]
 *  [详细分析MySQL事务日志(redo log和undo log)][MySQL_redo log_undo log]

[Link 1]: https://smilenicky.blog.csdn.net/article/details/109077688
[20201016105621498.png_pic_center]: /images/20210813/7cab5294f15d4457bbe8cffcd458b9a0.png
[20201012112641340.png_pic_center]: /images/20210813/a69138e8908640dd9b01b9185a9fdb12.png
[20201012135944639.png_pic_center]: /images/20210813/2869fa4ca9b6442a8fe0cbdf7a231fe5.png
[20201012114627797.png_pic_center]: /images/20210813/e849d8195ba1488a80b96e15f80c11da.png
[20201012150447334.png_pic_center]: /images/20210813/af827d9314b84c6db459cc9bfe616e82.png
[20201012161155865.png_pic_center]: /images/20210813/d52ee187e842457d89eef29116def816.png
[MySQL Binlog]: https://juejin.im/post/6844903794073960455
[MySQL_redo log_undo log]: https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html