相关推荐recommended
Mysql-binlog的查看
作者:mmseoamin日期:2023-12-11

介绍

简介

MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:

其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。

binlog日志概念

binlog全称为:binary,翻译就是二进制文件,主要记录了MySQL在进行DML(数据操作语言)过程中的操作日志。在执行SQL语句的过程中,作为使用者,无需关注程序执行的过程,但是当数据库数据丢失,或者需要搭建数据库主从复制时,则此时的binlog日志文件的重要性就展现出来了。

二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

binlog本身是一类二进制文件。二进制文件更省空间,写入速度更快,是无法直接打开来查看的。

因此mysql提供了命令mysqlbinlog进行查看。

一般的statement格式的二进制文件,用下面命令就可以(后面会讲到什么是statement格式)

mysqlbinlog mysql-bin.000001

如果是row格式,加上-v或者-vv参数就行,如

mysqlbinlog -vv mysql-bin.000001 

使用场景

以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

  • MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
  • 数据恢复:通过使用 mysqlbinlog工具来使恢复数据
  • 查看数据更改记录

    数据库操作类型:

    在对数据库的日常操作中,MySQL的操作语法,经常使用的几种MySQL指令类型如下

    DQL:数据查询语言,例如 where 、group by 、 orderby

    DML:数据操作语言,例如 insert、delete、update等

    DPL:事务处理语言,例如begin transaction、commit、rollback

    DCL:数据控制语言,例如grant 、revoke控制用户访问权限等

    DDL:数据定义语言,例如create、drop删除表、索引添加类等

    binlog三种模式

    format定义优点缺点
    statem ent记录的是修改SQL语句日志文件小,节约I0,提高性能准确性差,对一些系统函数不能准确复制或不能复制,如now0.uuid0等
    row记录的是每行实际数据的变更准确性强,能准确复制数据的变更日志文件大,较大的网络10和磁盘lO
    mixedstatement和row模式的混合准确性强,文件大小适中有可能发生主从不一致问题

    业内目前推荐使用的是row模式,准确性高,虽然说文件大,但是现在有SSD和万兆光纤网络,这些磁盘IO和网络IO都是可以接受的。

    那么,大家一定想问,为什么不推荐使用mixed模式,理由如下

    假设master有两条记录,而slave只有一条记录。

    master的数据为

    +----+------------------------------------------------------+
    | id | n |
    +----+------------------------------------------------------+
    | 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
    | 2 | ddd |
    +----+------------------------------------------------------+
    

    slave的数据为

    +----+-------------------------------------------------------+
    | id | n |
    +----+-------------------------------------------------------+
    | 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
    +----+-------------------------------------------------------+
    

    当在master上更新一条从库不存在的记录时,也就是id=2的记录,你会发现master是可以执行成功的。而slave拿到这个SQL后,也会照常执行,不报任何异常,只是更新操作不影响行数而已。并且你执行命令show slave status,查看输出,你会发现没有异常。但是,如果你是row模式,由于这行根本不存在,是会报1062错误的。

    在 MySQL 5.7.7 之前,默认的格式是 STATEMENT,在 MySQL 5.7.7 及更高版本中,默认值是 ROW。

    开启binlog

    一般来说开启binlog日志大概会有1%的性能损耗。

    需要先输入mysql 进入mysql命令行模式

    mysql> show variables like 'log_%'; #注意有分号
    
    +----------------------------------------+---------------------------------------+
    | Variable_name                          | Value                                 |
    +----------------------------------------+---------------------------------------+
    | log_bin                                | ON                                    | ------> ON表示已经开启binlog日志
    | log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
    | log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
    | log_bin_trust_function_creators        | OFF                                   |
    | log_bin_use_v1_row_events              | OFF                                   |
    | log_error                              | /usr/local/mysql/data/martin.err      |
    | log_output                             | FILE                                  |
    | log_queries_not_using_indexes          | OFF                                   |
    | log_slave_updates                      | OFF                                   |
    | log_slow_admin_statements              | OFF                                   |
    | log_slow_slave_statements              | OFF                                   |
    | log_throttle_queries_not_using_indexes | 0                                     |
    | log_warnings                           | 1                                     |
    +----------------------------------------+---------------------------------------+
    

    如果没有开启

    启用binlog,通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 或mysql\bin\my.ini 配置文件的 log-bin 选项:

    在配置文件中加入 log-bin 配置,表示启用binlog,如果没有给定值,写成 log-bin=,则默认名称为主机名。(注:名称若带有小数点,则只取第一个小数点前的部分作为名称)

    [mysqld]
    log-bin=my-binlog-name
    

    log-bin后面是binlog的文件名,譬如 log-bin=mysql-bin, 则生成的文件名为:

    Mysql-binlog的查看,在这里插入图片描述,第1张

    然后需要重启mysql

    也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog,通过 SET SQL_LOG_BIN=0 命令停用 binlog。启用 binlog 之后须重启MySQL才能生效。

    binlog文件介绍

    binlog日志包括两类文件:

    二进制日志索引文件(文件名后缀为.index)用于记录所有有效的的二进制文件

    二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML语句事件

    binlog是一个二进制文件集合,每个binlog文件以一个4字节的魔数开头,接着是一组Events:

    魔数:0xfe62696e对应的是0xfebin;

    Event:每个Event包含header和data两个部分;header提供了Event的创建时间,哪个服务器等信息,data部分提供的是针对该Event的具体信息,如具体数据的修改;

    第一个Event用于描述binlog文件的格式版本,这个格式就是event写入binlog文件的格式;

    其余的Event按照第一个Event的格式版本写入;

    最后一个Event用于说明下一个binlog文件;

    binlog的索引文件是一个文本文件,其中内容为当前的binlog文件列表

    当遇到以下3种情况时,MySQL会重新生成一个新的日志文件,文件序号递增:

    • MySQL服务器停止或重启时

    • 使用 flush logs 命令;

    • 当 binlog 文件大小超过 max_binlog_size 变量的值时;

      max_binlog_size 的最小值是4096字节,最大值和默认值是 1GB (1073741824字节)。事务被写入到binlog的一个块中,所以它不会在几个二进制日志之间被拆分。

      因此,如果你有很大的事务,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的日志都记录到当前日志文件中,直到事务结束,你可能会看到binlog文件大于 max_binlog_size 的情况。

      binlog日志内容查看

      binlog日志有二种查看方式,具体如下:

      1、mysql查看binlog

      这种方法相当于执行sql语句 在图形化工具中也可直接执行

      mysql> show binlog events; #只查看第一个binlog文件的内容(全部的 怎么只看部分呢)
      mysql> show binlog events ``in 'mysql-bin.000002'``;#查看指定binlog文件的内容 
      mysql> show binary logs; #获取binlog文件列表 
      mysql> show master status; #查看当前正在写入的binlog文件
      
      mysql> show binary logs; #获取binlog文件列表 
      
      参数名含义
      log bin = (onoff base name)
      sql log bin =( onoff )
      expire logs days指定自动删除二进制日志的时间,即日志过期时间
      log bin index指定mysql-bin.index文件的路径
      binlog format = [ mixed | row | statement ]指定二进制日志基于什么模式记录
      max binlog size指定二进制日志文件最大值
      binlog cache size指定事务日志缓存区大小
      max binlog cache size指定二进制日志缓存最大大小
      sync binlog = { 0 | n }指定写缓冲多少次,刷一次盘
      SHOW BINLOG EVENTS
         [IN 'log_name']
         [FROM pos]
         [LIMIT [offset,] row_count]
      在二进制日志中显示事件。如果未指定,则显示第一个二进制日志。需要特权。'log_name'SHOW BINLOG EVENTSREPLICATION SLAVE
      该LIMIT子句与该语句具有相同的语法SELECT。请参见“ SELECT语句”。
      mysql> show binlog events limit 10;
      +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+
      | Log_name         | Pos   | Event_type     | Server_id | End_log_pos | Info |
      +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+
      | mysql-bin.000001 |     4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4               |
      | mysql-bin.000001 |   123 | Previous_gtids |         1 |         154 |      |
      | mysql-bin.000001 |   154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
      | mysql-bin.000001 |   219 | Query          |         1 |         300 | BEGIN|
      | mysql-bin.000001 |   300 | Table_map      |         1 |         412 | table_id: 392 (cactus_000510.360exthost_examinemgr) |
      | mysql-bin.000001 |   412 | Delete_rows    |         1 |        8617 | table_id: 392                                       |
      | mysql-bin.000001 |  8617 | Delete_rows    |         1 |       12732 | table_id: 392 flags: STMT_END_F                     |
      | mysql-bin.000001 | 12732 | Xid            |         1 |       12763 | COMMIT /* xid=530 */                                |
      | mysql-bin.000001 | 12763 | Anonymous_Gtid |         1 |       12828 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
      | mysql-bin.000001 | 12828 | Query          |         1 |       12917 | BEGIN|
      +------------------+-------+----------------+-----------+-------------+-----------------------------------------------------+
      10 rows in set (0.13 sec)
      

      Binlog Event 简要分析

      从一个最简单的实例来分析Event,包括创建表,插入数据,更新数据,删除数据;

      CREATE TABLE `test` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `age` int(11) DEFAULT NULL,
        `name` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      insert into test values(1,22,"小旋锋");
      update test set name='whirly' where id=1;
      delete from test where id=1;
      

      日志格式为STATEMENT,查看所有的Event

      Mysql-binlog的查看,在这里插入图片描述,第2张

      日志格式为ROW时是下面这样,可以发现又有一些不同

      Mysql-binlog的查看,在这里插入图片描述,第3张

      2、使用mysqlbinlog工具

      以下为普通bash命令 非mysql模式下的了

      发出SHOW BINLOG EVENTS with no LIMIT子句可能会启动一个非常耗时和资源消耗的过程,因为服务器将二进制日志的完整内容(包括服务器修改数据的所有语句)返回给客户端。作为替代SHOW BINLOG EVENTS,使用mysqlbinlog实用程序将二进制日志保存到文本文件中,以供以后检查和分析。

      而且 show event命令查看了binlog的简单信息,如何查看执行时间、执行花费的时间等详细信息需要使用工具包中的mysqlbinlog命令。

      mysqlbinlog是一个查看mysql二进制日志的工具,可以把mysql上面的所有操作记录从日志里导出,这个工具默认的安装路径为:/usr/local/mysql/bin/mysqlbinlog

      可以通过find / -name "mysqlbinlog"或者which mysqlbinlog命令查找mysqlbinlog的工具路径。

      至于日志文件,参照前面的开启binlog章

      基于开始/结束时间:

      /usr/local/mysql/bin/mysqlbinlog --start-datetime=``"2013-03-01 00:00:00" --stop-datetime=``"2014-03-21 23:59:59" /usr/local/mysql/``var``/mysql-bin.000007 -r test2.sql
      
      # mysqlbinlog 的执行格式
      mysqlbinlog [options] log_file ...
      # 查看bin-log二进制文件(shell方式)
      mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
      # 查看bin-log二进制文件(带查询条件)
      mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
          --start-datetime="2019-03-01 00:00:00"  \
          --stop-datetime="2019-03-10 00:00:00"   \
          --start-position="5000"    \
          --stop-position="20000"
      

      截取结果其中的一段进行分析:

      # at 21019
      #190308 10:10:09 server id 1  end_log_pos 21094 CRC32 0x7a405abc     Query   thread_id=113   exec_time=0 error_code=0
      SET TIMESTAMP=1552011009/*!*/;
      BEGIN
      /*!*/;
      

      上面输出包括信息:

      position: 位于文件中的位置,即第一行的(# at 21019),说明该事件记录从文件第21019个字节开始
      timestamp: 事件发生的时间戳,即第二行的(#190308 10:10:09)
      server id: 服务器标识(1)
      end_log_pos 表示下一个事件开始的位置(即当前事件的结束位置+1)
      thread_id: 执行该事件的线程id (thread_id=113)
      exec_time: 事件执行的花费时间
      error_code: 错误码,0意味着没有发生错误
      type:事件类型Query
      

      Mysql-binlog的查看,在这里插入图片描述,第4张

      发现BINLOG 处的内容我们看不明白。。。_

      增加 --base64-output=decode-rows –v 选项解析

      (base64-output,可以控制输出语句输出base64编码的BINLOG语句;decode-rows:选项将把基于行的事件解码成一个SQL语句)

      /mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00' mysql-bin.000007 | more
      

      Mysql-binlog的查看,在这里插入图片描述,第5张

      变成人类可以读懂的SQL语句了;

      PS: 可以把binlog解析后的内容放到一个文件后,在分析,利用如下脚本

      /mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00' mysql-bin.000007 >/tmp/binlog007.sql
      
      mysqlbinlog --no-defaults --base64-output=decode-rows -v   mysql-bin.000002 
      

      怎么删binlog

      删binlog的方法很多,有三种是常见的

      (1) 使用reset master,该命令将会删除所有日志,并让日志文件重新从000001开始。

      (2) 使用命令

      PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }

      1

      例如

      purge master logs to “binlog_name.00000X”

      1

      将会清空00000X之前的所有日志文件.

      (3) 使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。