mysql binlog知识

今天药忘吃喽~ 2023-06-27 05:51 11阅读 0赞

### 前言 ###

说实话惭愧，平常接触到的都是已经从binlog解析好的消息体，不清楚binlog的内容，所以这里讲解一下把。

### binlog 作用 ###

binlog是Mysql sever层维护的一种二进制日志，主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中；

作用主要有：

*  复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
 *  数据恢复：通过mysqlbinlog工具恢复数据
 *  增量备份

### binlog 日志内容 ###

首先可以明确的是binlog的内容就是二进制，二进制直接看，看不懂，但利用mysqlbinlog命令将 MySQL 的binlog日志转换成MySQL 文本SQL语句，但是转换后你看得懂吗？参考这篇博客：[mysql 的binlog 是二进制日志？][mysql _binlog]，我们从篇博客里拿一些数据来吧。

//  直接用xxd命令查看（二进制）
    $ xxd -l 1024 mysql-bin.002149
    
    00000000: fe62 696e 562a 5f5d 0f01 0000 0077 0000  .binV*_].....w..
    00000010: 007b 0000 0000 0004 0035 2e37 2e32 372d  .{.......5.7.27-
    00000020: 3075 6275 6e74 7530 2e31 362e 3034 2e31  0ubuntu0.16.04.1
    00000030: 2d6c 6f67 0000 0000 0000 0000 0000 0000  -log............
    00000040: 0000 0000 0000 0000 0000 0000 0000 0013  ................
    
    // 用 mysqlbinlog  命令 转换日志成文本的sql看一下
    $ mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > ~/test.sql

详细的内容如下：

# at 417750
    #181010  9:50:38 server id 1630000  end_log_pos 417844 CRC32 0x9fc3e3cd     Query   thread_id=440109962 exec_time=0 error_code=0
    SET TIMESTAMP=1539136238/*!*/;
    BEGIN
    /*!*/;
    # at 417844
    #181010  9:50:38 server id 1630000  end_log_pos 417930 CRC32 0xce36551b     Table_map: `goods`.`good_info` mapped to number 129411
    # at 417930
    #181010  9:50:38 server id 1630000  end_log_pos 418030 CRC32 0x5827674a     Update_rows: table id 129411 flags: STMT_END_F
    ### UPDATE `goods`.`good_info`
    ### WHERE
    ###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
    ###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
    ###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
    ###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
    ###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
    ###   @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
    ###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
    ### SET
    ###   @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
    ###   @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
    ###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
    ###   @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
    ###   @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
    ###   @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
    ###   @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
    # at 418030
    #181010  9:50:38 server id 1630000  end_log_pos 418061 CRC32 0x468fb30e     Xid = 212760460521
    COMMIT/*!*/;
    # at 418061

*  \#at 417750  
    指明的当前位置相对文件开始的偏移位置，这个在mysqlbinlog命令中可以作为–start-position的参数
 *  \#181007 1:50:38 server id 1630000 end\_log\_pos 417844 CRC32 0x9fc3e3cd Query thread\_id=440109962 exec\_time=0 error\_code=0  
    181007 1:50:38 ：指明时间为18年10月7号1:50:38  
    serverid：也就是你在配置文件中的配置的  
    end\_log\_pos ：417844，这个块在417844结束  
    thread\_id：执行的线程id  
    exec\_time：执行时间  
    error\_code：错误码
 *  SET TIMESTAMP=1538877038/!/; BEGIN  
    SET TIMESTAMP：开始事物的时间，从 BEGIN 到 COMMIT 结束，这中间的数据都是操作数据的记录，上文给出的就是一个update 操作。

这就是一个binlog 解析后的情况，mysqlbinlog 可以通过时间及at 点来获取、恢复等操作，mysqlbinlog可以基于server\_id，以及基于数据库级别提取日志，不支持表级别。这一块就不做详细展示，具体有需求可以去了解。

看到另一篇博客中提到利用show binlog events 也可以看到，这里直接引用吧：

show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
    
    *************************** 13. row ***************************
               Log_name: mysql-bin.000007
                    Pos: 1190
             Event_type: Query  //事件类型
              Server_id: 123
            End_log_pos: 1352   //结束pose点，下个事件的起点
                   Info: use `test`; insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"
            *************************** 14. row ***************************
               Log_name: mysql-bin.000007
                    Pos: 1352
             Event_type: Xid
              Server_id: 123
            End_log_pos: 1383
                   Info: COMMIT /* xid=51 */

### binlog 格式 ###

这个问题很奇怪，格式？这个是什么，其实格式就是存储情况，存那些信息，想上段那样有update sql 语句，不同格式存储的内容不一样。主要有三类格式。

*  Row level:
 *  Statement level: 基于SQL语句的复制(statement-based replication, SBR)  
    每一条会修改数据的 sql 都会记录在 binlog 中。
 *  Mixed level：以上两种 leve 的结合。

仔细来看下吧：

#### Row level: 基于行的复制(row-based replication, RBR） ####

仅保存记录被修改细节，不记录 sql 语句上下文相关信息，如果一个update 、insert 语句多条，则会有多行出现在binlog中。

执行命令：

mysql> insert into user(username) select * from aa;

查看 binlog :

'/*!*/;
    ### INSERT INTO test.user
    ### SET
    ###   @1=4 /* SHORTINT meta=0 nullable=0 is_null=0 */
    ###   @2='baoyu' /* VARSTRING(45) meta=45 nullable=0 is_null=0 */
    # at 565
    #120806  0:27:39 server id 80  end_log_pos 592     Xid = 20
    COMMIT/*!*/;
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

这是1行数据的记录，如果是insert 多行，则会显示多行，

*  优点  
    1、能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的 procedure、function、及 trigger 的调用触发无法被正确复制的问题，任何情况都可以被复制，  
    2、且能加快从库重放日志的效率，保证从库数据的一致性。
 *  缺点  
    由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多；特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。  
    5.6 版本后新增了一个参数：binlog\_row\_image；把 binlog\_row\_image 设置为 minimal 以后，binlog 记录的就只是影响的列，大大减少了日志内容。

#### Statement level: 基于SQL语句的复制(statement-based replication, SBR) ####

每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。

执行命令：

mysql> insert into user(username) values('xuebinbin');

查看日志:

BEGIN
    /*!*/;
    # at 174
    #120806 14:47:35 server id 80  end_log_pos 202     Intvar
    SET INSERT_ID=2/*!*/;
    # at 202
    #120806 14:47:35 server id 80  end_log_pos 311     Query    thread_id=5    exec_time=0    error_code=0
    use test/*!*/;
    SET TIMESTAMP=1344235655/*!*/;
    insert into user(username) values('xuebinbin')
    /*!*/;
    # at 311
    #120806 14:47:35 server id 80  end_log_pos 338     Xid = 20
    COMMIT/*!*/;
    # at 338
    #120806 14:53:18 server id 80  end_log_pos 357     Stop
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

上面可以看到执行的语句，其实不太懂得这上下文信息具体是指什么，查了网上的内容，也没注意到有什么，看上面的也看不出来那些是上下文信息，有朋友清楚可以评论给出。

*  优点：  
    1、只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比 ROW level 能大大减少 binlog 日志量，节约 IO，提高性能；  
    2、还可以用于实时的还原；  
    3、同时主从版本可以不一样，从服务器版本可以比主服务器版本高。
 *  缺点：  
    1、为了保证 sql 语句能在 slave 上正确执行，必须记录上下文信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果；  
    2、另外，主从复制时，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：在存储过程中使用了last\_insert\_id()函数，可能会使slave和master上得到不一致的id等等。

#### Mixed level：混合模式复制(mixed-based replication, MBR) ####

Statement level:、Row level 都互有优点，如能根据 sql 语句取舍可能会有更好地性能和效果；Mixed level 便是以上两种 leve的结合。不过，新版本的 MySQL 对 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录，如果sql语句确实就是 update或者 delete 等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用 row level 即可。

SET TIMESTAMP=1350355892/*!*/;
    
    BEGIN
    
    /*!*/;
    
    # at 1643330
    
    #121016 10:51:32 server id 1  end_log_pos 1643885        Query     thread_id=272571   exec_time=0   error_code=0
    
    SET TIMESTAMP=1350355892/*!*/;
    
    Insert into T_test….)
    
    /*!*/;
    
    # at 1643885
    
    #121016 10:51:32 server id 1  end_log_pos 1643912        Xid = 0
    
    COMMIT/*!*/;

这其实是看不出什么的，因为这区分具体的语句，做了优化，不同的语句也是不一样的binlog。如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式：

*  当DML语句更新一个NDB表时
 *  当函数中包含 UUID() 时
 *  2个及以上包含 AUTO\_INCREMENT 字段的表被更新时
 *  行任何 INSERT DELAYED 语句时
 *  用 UDF 时
 *  视图中必须要求运用 RBR 时，例如建立视图是运用了 UUID() 函数

一些处理技巧：

*  主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写疑问  
    UDF 产生的大 BLOB 值会导致复制变慢
 *  当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生  
    另外，针对系统库 mysql 里面的表发生变化时的处理准则如下：  
    （1）如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog\_format 的设定而记录  
    （2）如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何 都采用 SBR 模式记录。  
    注：采用 RBR 模式后，能处理很多原先出现的主键重复问题。

### 参考博客 ###

[mysql 的binlog 是二进制日志？][mysql _binlog]  
[使用mysqlbinlog提取二进制日志][mysqlbinlog]  
[(转)Mysql数据库之Binlog日志使用总结][Mysql_Binlog]  
[MySQL binlog 格式（Mixed，Statement，Row Level）][MySQL binlog _Mixed_Statement_Row Level]  
[腾讯工程师带你深入解析 MySQL binlog  
][MySQL binlog]

[mysql _binlog]: https://segmentfault.com/q/1010000020255674
[mysqlbinlog]: https://blog.csdn.net/leshami/article/details/41962243
[Mysql_Binlog]: https://www.cnblogs.com/liujiacai/p/7669058.html
[MySQL binlog _Mixed_Statement_Row Level]: https://www.iteye.com/blog/whxhz-1118614
[MySQL binlog]: https://zhuanlan.zhihu.com/p/33504555