暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 每个程序员都在讨论的binlog到底是什么

每个程序员都在讨论的binlog到底是什么

程序猿读历史 2021-05-31
4107

前言

几乎每个互联网公司的程序员都会聊MySQL的binlog，每个程序员也都知道binlog很有用。有人说他可以备份、实现高可用、做数据恢复，也有人说他可以分析sql性能，找出slow sql，还有人说他可以做审计。于是乎MySQL的binlog就像金庸笔下的风清扬，重要到无法用语言形容，但是过多的传说又增加了几分神秘色彩，使人看不清他的真实面目。

本文将对binlog做一次刨根问底的解读，希望读完后你有收获。


一、Binlog基础知识

1.1 Binlog是啥log

Binlog是MySQL实例层面的日志，它跟引擎没有关系，即任何引擎都可以产生binlog(redo和undo是innodb引擎特有的日志,redo和undo将在后续文章介绍)。哪些操作会在binlog里记录呢？先看下官方定义：

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data....
The binary log is not used for statements such as SELECT or SHOW that do not modify data.  

根据官方介绍，Binlog只记录对表操作的语句，也就是只记录DML和DDL内容，同时也会记录该语句的耗时，不记录任何select show
操作。其中DML的记录三种格式，binlog格式在【Binlog格式特点】部分详细介绍。对于DDL的语句，只记录语句本身，不会记录语句涉及的具体数据行数。所以对某个表执行了Drop和Truncate操作后，如果需要恢复数据，则需要依据某一个备份文件才能恢复数据。


1.2 Binlog格式特点

Binlog日志有三种格式，分别为Statement、MiXED、ROW，通过下面命令可以查看binlog格式：

root@localhost [(none)]>show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

我们将逐一分析Statement、ROW和MIXED三种格式，首先看Statement格式。

  • Statement

MySQL官方是怎么定义的

Replication capabilities in MySQL originally were based on propagation of SQL statements from source to replica. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

翻译成人话，就是基于binlog是只记录响应了变更语句，而不记录具体的行。这样做的优点，是不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。但是缺点由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)。

在Statement格式下，执行INSERT INTO test.t_1207_03 select * from test.t_1207 limit 10;
，bin如log记录内容如下：

    #201208 14:41:32 server id 111  end_log_pos 1164 CRC32 0x3e516e1a   Query  thread_id=18  exec_time=0  error_code=0
    SET TIMESTAMP=1607409692/*!*/;
    BEGIN
    /*!*/;
    # at 1164
    #201208 14:41:32 server id 111  end_log_pos 1291 CRC32 0xf3b94ca4   Query  thread_id=18  exec_time=0  error_code=0
    SET TIMESTAMP=1607409692/*!*/;
    insert into t_1207_03 select * from t_1207 limit 10
    /*!*/;
    # at 1291
    #201208 14:41:32 server id 111  end_log_pos 1322 CRC32 0x6c657421   Xid = 2361
    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*/;

    在这种格式情况下，如果要做数据同步，则无法实现只同步部分表的需求，所以目前一般业界都不在使用该格式。

    • Row

    仍然先看官方定义：

    In row-based logging (the default), the source writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

    翻译成人话，该格式下只记录涉及的行，不记录具体的sql。这样做的优点， 是不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。ROW的日志内容清楚的记录每一行数据变更内容，也不会出现某些特定情况下的存储过程、函数、触发器等的调用和触发无法被正确复制的问题。缺点是m这样可能会产生大量的日志内容，比如一条DML语句更新百万行记录，则binlog会记录相当多的内容，且有可能该binlog文件会非常大(一个事务的产生的binlog是一定写在一个文件里的)，这样造成binlog日志量会很大。

    在ROW格式下，执行INSERT INTO test.t_1207_03 select * from test.t_1207 limit 5;
    ，binlog记录内容如下：

      BEGIN
      /*!*/;
      # at 511
      #201208 14:35:58 server id 111 end_log_pos 564 CRC32 0x6414de64 Table_map: `test`.`t_1207_03` mapped to number 109
      # at 564
      #201208 14:35:58 server id 111 end_log_pos 641 CRC32 0x1dee2314 Write_rows: table id 109 flags: STMT_END_F
      ### INSERT INTO `test`.`t_1207_03`
      ### SET
      ###   @1='2020-12-07 20:52:53' * DATETIME(0) meta=0 nullable=1 is_null=0 */
      ### INSERT INTO `test`.`t_1207_03`
      ### SET
      ###   @1='2020-12-07 20:53:57' * DATETIME(0) meta=0 nullable=1 is_null=0 */
      ### INSERT INTO `test`.`t_1207_03`
      ### SET
      ###   @1='2020-12-08 14:07:37' * DATETIME(0) meta=0 nullable=1 is_null=0 */
      ### INSERT INTO `test`.`t_1207_03`
      ### SET
      ###   @1='2020-12-08 14:07:38' * DATETIME(0) meta=0 nullable=1 is_null=0 */
      ### INSERT INTO `test`.`t_1207_03`
      ### SET
      ###   @1='2020-12-08 14:07:39' * DATETIME(0) meta=0 nullable=1 is_null=0 */
      # at 641
      #201208 14:35:58 server id 111 end_log_pos 672 CRC32 0xb15c67ea Xid = 2317
      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*/;

      从上可以看出row格式下，DML语句是记录了详细的行，且有执行时间及XID等相关信息。

      • Mixed

      A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

      MIXED是以上两种的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。


      1.3 BInlog写入方法

      事务执行过程中，binlog 首先会被写到 binlog cache 中。事务提交的时候，再将binlog cache 写到 binlog 文件中。一个事务的 binlog 是原子的，无论多大都需要保证完整性。

      系统为每个客户端线程分配一个 binlog cache，其大小由 binlog_cache_size
      参数 控制。如果binlog cache 超过阀值，就会临时持久化到磁盘。当事务提交的时候，再将 binlog cache 中完整的事务持久化到磁盘中，并清空 binlog cache。

      从上面可以看出，每个客户端线程都有自己独立的 binlog cache，但是会共享一份 binlog files。

      注意：上面的 write 是指把binlog cache 写到文件系统的 page cache，并没有写入到磁盘中，因此速度较快。

      fsync 是实际的写盘操作，占用磁盘的 IOPS。

      write 和 fsync 的写入时机，是由sync_binlog
      控制的：

      1. sync_binlog=0：每次事务提交都只 write，不 fsync；

      2. sync_binlog=1：每次事务提交都会fsync；

      3. sync_binlog=N（N>1）：每次提交事务都会 write，累计N 个后再执行 fsync。

      所以在出现 IO 瓶颈的情况下，可以考虑将sync_binlog
      设置成一个大的值。比较常见的是将 N设置为 100~1000。但是存在的风险是，当主机异常重启时会丢失 N 个最近提交的事务 binlog。


      1.4 BInlog相关参数和操作

        log_bin_basename
        表示二进制文件的路径和名称
        log_bin
        表示启用binlog功能


        log_bin_index
        表示二进制索引文件的路径与名称


        binlog_format
        表示二进制文件的格式


        binlog_cache_size
        binlog使用的内存大小


        binlog_cache_use
        表示二进制日志缓存的事务数量


        binlog_cache_disk_use
        表示二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量


        max_binlog_cache_size
        表示binlog使用的内存最大的尺寸


        max_binlog_size
        Binlog文件最大值，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束


        expire_logs_days
        设置binlog过期时间


        sync_binlog
        文件系统将Binlog文件缓存刷新到磁盘的频率，该值为0、1和N。详细作用见【Binlog写入方法】部分


        删除binlog
        mysql> reset master; #删除master的binlog，即手动删除所有的binlog日志
        mysql> reset slave;  #删除slave的中继日志
        mysql> purge master logs before '2020-12-30 17:20:00';  # 删除指定日期以前的日志索引中binlog日志文件
        mysql> purge master logs to 'binlog.000002';   # 删除指定日志文件的日志索引中binlog日志文件


        二、Binlog文件解析

        Binlog其实是有一个个events组成，每个event对应一个具体的DDL/DML操作。如STATEMENT格式下的DML操作对应的是QUERY_EVENT类型，而ROW格式下的DML操作对应的是ROWS_EVENT类型，其中ROWS_EVENT又分成三大块：WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT，分别对应I、U、D操作。

        注：由于目前binlog格式一般都设置为ROW，所以以下我们只讨论row格式下的内容。


        2.1 Binlog事件

        每个Event包含header和data两个部分，其中header提供了Event的基本信息，比如创建时间、服务器等信息。而data部分是该Event的具体信息，如具体数据的修改。每个binlog的第一个Event用于描述binlog文件的格式版本，其余的Event按照第一个Event的格式版本写入，最后一个Event用于说明下一个binlog文件。可以通过show binlog events in '具体binlog文件名'
        查看binlog的详细evnets。下图是ON.000013的binlog的所有信息，每一行表示一个Event。

        第一列表示binlog文件名

        第二列是events开启位点

        第三列是events类型

        第四列是mysql实例id

        第五列是该事件的结束位点

        第六列是事件具体信息

        注意：生产环境 往往一个binlog包含大量events，全部打开看意义不大，mysql支持show binlog events in '具体binlog文件名' from postion limit N;

        第三列和第六列是要我们重点关注的，官方介绍events类型有30多种，但是常见的主要有以下几种：FORMAT_DESCRIPTION、PREVIOUS_GTIDS、GTID、QUERY、ROWS(包括TBALE_MAP和[IUD]_ROWS两部分，下面会详细介绍)、XID、ROTATE，我们将逐个介绍这些类型含义。

        • FORMAT_DESCRIPTION

        该EVENT是binlog文件中的第一个事件，而且只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。它通常指定了MySQL Server的版本，binlog的版本，该binlog文件的创建时间。


        • PREVIOUS_GTIDS_LOG_EVENT

        开启GTID模式后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT事件，它的值是上一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT，实际上，在数据库重启的时候，需要重新填充gtid_executed的值，该值即是最新一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT。


        • QUERY

        QUERY_EVENT类型的事件通常在以下几种情况下使用：

        1.事务开始时，执行的BEGIN操作。

        2.STATEMENT格式中的DML操作

        3.ROW格式中的DDL操作


        • GTID

        在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID


        • ROWS

        在row格式的DML操作，binlog里面记录的是：TABLE_MAP_EVENT+ ROWS_LOG_EVENT，ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应Insert，Update和Delete操作。为什么一个DML在ROW模式下需要分解成两个event：一个Table_map，一个Row_log？我们想象一下，假设一个delete如果更新了1000条数据，那么对应的表结构信息是否需要记录1000次？我们知道其实是对同一个表的操作，所以这里binlog只是记录了一个Table_map用于记录表结构相关信息，而后面的Delete_rows记录了更新数据的行信息。他们之间是通过table_id来联系的。


        • XID

        在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。


        • ROTATE

        该event是每一个binlog的最后一个event。当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时或者mysql服务重启，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置。


        2.2 BInlog查看

        Binlog本身是一个二进制文件，因为二进制文件更省空间，写入速度更快。由于二进制是无法直接打开来查看，所以MySQl提供了命令mysqlbinlog
        进行查看。针对ROW格式，加上-vv
        参数就行。下面就是执行mysqlbinlog --base64-output=decode-rows -vv on.000014
        完整结果.

          [root@mysql-mha mysql]# 
          [root@mysql-mha mysql]# mysqlbinlog --base64-output=decode-rows -vv on.000014
          /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
          /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
          DELIMITER /*!*/;
          # at 4
          #201209 16:56:07 server id 111 end_log_pos 125 CRC32 0x80e3ad30 Start: binlog v 4, server v 8.0.22 created 201209 16:56:07
          # at 125
          #201209 16:56:07 server id 111 end_log_pos 196 CRC32 0x2c3dc4df Previous-GTIDs
          # bad6b8fe-3888-11eb-bf85-005056a00f24:1-19
          # at 196
          #201209 18:41:10 server id 111 end_log_pos 275 CRC32 0x5369b9fe GTIDlast_committed=0sequence_number=1rbr_only=yesoriginal_committed_timestamp=1607510470288763immediate_commit_timestamp=1607510470288763transaction_length=295
          /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
          # original_commit_timestamp=1607510470288763 (2020-12-09 18:41:10.288763 CST)
          # immediate_commit_timestamp=1607510470288763 (2020-12-09 18:41:10.288763 CST)
          /*!80001 SET @@session.original_commit_timestamp=1607510470288763*//*!*/;
          /*!80014 SET @@session.original_server_version=80022*//*!*/;
          /*!80014 SET @@session.immediate_server_version=80022*//*!*/;
          SET @@SESSION.GTID_NEXT= 'bad6b8fe-3888-11eb-bf85-005056a00f24:20'/*!*/;
          # at 275
          #201209 18:41:10 server id 111 end_log_pos 350 CRC32 0x6b733c94 Querythread_id=11exec_time=0error_code=0
          SET TIMESTAMP=1607510470/*!*/;
          SET @@session.pseudo_thread_id=11/*!*/;
          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 utf8mb4 *//*!*/;
          SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
          SET @@session.lc_time_names=0/*!*/;
          SET @@session.collation_database=DEFAULT/*!*/;
          /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
          BEGIN
          /*!*/;
          # at 350
          #201209 18:41:10 server id 111 end_log_pos 416 CRC32 0x9f8c49ad Table_map: `test`.`t_test_03` mapped to number 102
          # at 416
          #201209 18:41:10 server id 111 end_log_pos 460 CRC32 0xced8e0b9 Write_rows: table id 102 flags: STMT_END_F
          ### INSERT INTO `test`.`t_test_03`
          ### SET
          ###   @1=6 * INT meta=0 nullable=0 is_null=0 */
          ###   @2='fgg' * VARSTRING(40) meta=40 nullable=1 is_null=0 */
          ###   @3=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
          # at 460
          #201209 18:41:10 server id 111 end_log_pos 491 CRC32 0xbd22b63b Xid = 161
          COMMIT/*!*/;
          # at 491
          #201209 18:41:20 server id 111 end_log_pos 531 CRC32 0xbcdd624f Rotate to on.000015 pos: 4
          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*/;
          [root@mysql-mha mysql]#

          mysqlbinlog详细参数可以通过help获取，本文只介绍几个常用的参数：

            --start-datetime=  binlog文件读取的起始时间点，可接受datetime和timestamp类型，格式2020-12-12 11:25:56
            --stop-datetime=   binlog文件的结束的时间点。
            --start-position=  binlog文件的位置信息
            --stop-position=  binlog文件的结束的位置信息
            -d, --database=name  列出数据库的名称(仅限binlog文件存储在本地)
            -R, --read-from-remote-server 从MySQL远程服务器读取二进制日志，是read-from-remote-master = BINLOG-DUMP-NON-GTIDS的别名。
            -h, --host=name   获取binlog的服务器IP地址或者域名


            三、Binlog用途

            3.1 复制

            目前业界mysql的主从复制主要是基于binlog的逻辑sql实现，他的原理如下图：


            通过上图我们知道复制主要涉及到3个线程：Binlog Dump线程跑在主库上，I/0线程和 SQL线程跑在从库上。当在从库上启动复制时，首先创建I/0程连接主库，主库随后创建 Binlog Dump线程读取数据库事件并发送给 I/0线程， I/0线程获取到事件数据后更新到从库的中继日志 Relay Log中去，之后从库上的 SQL线程读取中继日志RelayLog中更新的数据库事件并应用。

            目前mysql主要有异步和半同步两种复制方式。

            • 异步复制

            通过主从复制的实现原理，主从可以知道slave是主动发起是读maser的binlog，也就是slave节点apply master节点的binlog是必然延迟的，而且连接master节点的等待从节点read或者apply binlog之后才会告知应用，所以这种mysql的主从是异步方式，这也就是mysql目前默认的实现方式。所以这种异步方式天然从库会有落后主库。

            • 半同步复制

            Mysql在5.5版本后推出了半同步复制的实现方式。所谓半同步复制是在一主一从，一主多从情况下，Master节点只要确认至少有一个slave接受到了事务，即可向发起请求的客户端返回执行成功的操作，master节点是不需要等待slave节点成功执行完这个事务。slave节点接受到这个事务，并成功写入到本地relay日志中，就算是成功了。

            相比异步复制，半同步复制提高了数据完整性，因为很明确知道，在一个事务提交成功之后，这个事务就至少会存在于两个地方。即在master的dumper线程通知slave后，增加了一个ack（消息确认），即是否成功收到的标志码，也就是dumper线程除了发送到slave，还承担了接收slave的ack工作。如果出现异常，没有收到ack，那么将自动降级为普通的复制，直到异常修复后又会自动变为半同步复制。

            另外，目前MySQL业界也有基于redo log实现的物理主从复制，但是MySQL官方至今没有推出社区版本的物理复制功能。如果推出该功能，必然对官方的Oracle DataGuard有影响，但是MySQL物理复制应该是未来趋势。


            3.2 备份

            通过前面的介绍我们知道，binlog记录了所有DML和DDL操作，其中DML是详细的具体行，DDL只记录语句。所以在对DDL恢复时，要依赖于某一个备份文件，然后再重放所有的binlog。而恢复DML操作只需要重返binlog即可。所以数据库的备份是极其重要的，鲁迅说：有备无患是真的。这话送给自己也送给所有DBA同学。


            四、FAQ

            4.1 Binlog切换存储文件。

            1、重启mysql服务。

            2、执行flush logs。

            3、超过binlog文件的最大值，默认是500M。


            4.2 大事务的DML语句影响

            1、一个事务产生的binlog只能在一个file里，如果一个binlog文件最大是500M，当到了490M时，执行一个涉及几百万的大事务sql时。则这个binlog文件会异常大。

            2、因为binlog在刷磁盘之前，会写入到binlog cache的步骤。如果该语句过大则binlog cache超过了内存，则会用disk来缓存。就会降低效率。


            4.3 BInlog和redo log如何保持一致性

            是通过二阶段提交实现的，两阶段提交原理是这样的。第1阶段：InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。第2阶段：如果前面prepare成功，binlog 写盘，那么再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB事务则进入 commit 状态，实际是在redo log里面写上一个commit记录。

            因为这个问题涉及到redo log，本文只对binlog介绍，故在此只说答案，详细介绍将在redo log一文做说明。


            五、参考文章

            • 美团回滚binlog工具

            https://github.com/Meituan-Dianping/MyFlash

            • 最早将的binlog 转换为sql的开源工具

            https://github.com/danfengcao/binlog2sql

            • MySQL官方地址

            https://dev.mysql.com/doc/refman/8.0/en/

            • 阿里巴巴基于binlog同步工具

            https://github.com/alibaba/canal/wiki/Docker-QuickStart


            数据库
            文章转载自程序猿读历史，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

            评论