Binlog

⼆进制⽇志（binary log）是 Server 层的⽇志，还被称为归档⽇志。主要记录数据库的变化情况，

内容包括数据库所有的更新操作，属于逻辑上的⽇志。 Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。

Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。

开启Binlog日志有以下两个最重要的使用场景。

主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。数据恢复：通过mysqlbinlog工具来恢复数据。

Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。

格式

binary log包含一系列的事件，每个时间都有固定长度的头部信息。

在日志中的偏移
日期和时间的时间戳
原服务器的ID
end_log_pos,下一个事件偏移的字节
事件类型
源服务器上执行事件的线程ID
exec_time,语句的时间戳与写入日志的时间差
在源服务器上事件产生的错误代码

ROW（row-based replication, RBR）

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：

不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题
能加快从库重放日志的效率，保证从库数据的一致性

缺点：

可能会产生大量的日志内容，干扰内容也较多，尤其是alter table改变了表结构，相当于每一行都进行修改会让日志暴涨。

STATMENT（statement-based replication, SBR）

每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点：日志量小，减少磁盘IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

MIXED（mixed-based replication, MBR）

以上两种模式的混合使用，一般会使用 STATEMENT 模式保存binlog，对于STATEMENT模式无法复制的操作使用 ROW 模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

日志的查看和解析

默认情况下binlog日志是二进制格式，无法直接查看。可使用两种方式进行查看：

a. mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007
        - mysqlbinlog是mysql官方提供的一个binlog查看工具，
        - 也可使用–read-from-remote-server从远程服务器读取二进制日志，
        - 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志
        
        截取位置1190-1352 binlog如下：
        ***************************************************************************************
        # at 1190   //事件的起点
        #171223 21:56:26 server id 123  end_log_pos 1190 CRC32 0xf75c94a7 	Intvar
        SET INSERT_ID=2/*!*/;
        #171223 21:56:26 server id 123  end_log_pos 1352 CRC32 0xefa42fea 	Query	thread_id=4	exec_time=0	error_code=0
        SET TIMESTAMP=1514123786/*!*/;              //开始事务的时间起点 (每个at即为一个event)
        insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"  //sql语句
# /*!*/;
        # at 1352
        #171223 21:56:26 server id 123  end_log_pos 1383 CRC32 0x72c565d3 	Xid = 5 //执行时间，及位置戳，Xid:事件指示提交的XA事务
        ***************************************************************************************
    
    b.直命令行解析
        SHOW BINLOG EVENTS
            [IN 'log_name'] //要查询的binlog文件名
            [FROM pos]  
            [LIMIT [offset,] row_count]  
       
        1190-135如下：mysql> 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 */

日志的安全清除

Binary log需要定期清除过期数据，以防文件过大甚至写满。但如果可以，应该尽可能地保留Binary log文件。

4.1前手动清理命令

但在新版本中禁止使用，使用rm进行删除会导致index状态文件与磁盘文件的不一致，进而导致show master logs等命令的失败。

4.1后支持定期清理

expire_log_days

该配置在服务器启动或者MySQL切换Binary log时生效。因此这个配置是通过日志修改事件决定哪个文件是否被清除。

4.1后的手动清理

redo log

undo log

资料来源

腾讯工程师带你深入解析 MySQL binlog - 知乎 (zhihu.com) MySQL Binlog日志 - SegmentFault 思否

MySQL日志