mysql查看binlog日志

mysql查看binlog日志

 

 业内目前推荐使用的是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的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景: 
      其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
      其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
      二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

一、开启binlog

     在[mysqld] 区块设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；

二、通过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日志内容查看

     binlog日志有二种查看方式，具体如下：

    1、mysql查看binlog

1 2 3 4

mysql> show binlog events;   #只查看第一个binlog文件的内容 mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容 mysql> show binary logs;  #获取binlog文件列表 mysql> show master status； #查看当前正在写入的binlog文件

　2、使用mysqlbinlog工具

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

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

      基于开始/结束时间：

1	/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

　

 

 

常识二:怎查看binlog
binlog本身是一类二进制文件。二进制文件更省空间，写入速度更快，是无法直接打开来查看的。
因此mysql提供了命令mysqlbinlog进行查看。
一般的statement格式的二进制文件，用下面命令就可以

mysqlbinlog mysql-bin.000001

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

mysqlbinlog -vv mysql-bin.000001 

常识三:怎么删binlog
删binlog的方法很多，有三种是常见的
(1) 使用reset master,该命令将会删除所有日志，并让日志文件重新从000001开始。
(2) 使用命令

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

例如

purge master logs to "binlog_name.00000X" 

将会清空00000X之前的所有日志文件.
(3) 使用--expire_logs_days=N选项指定过了多少天日志自动过期清空。

常识四:binlog常见参数
常见参数，列举如下，有个印象就好。

 https://www.cnblogs.com/rjzheng/p/9721765.html

参见：http://www.yaoguangkeji.com/a_4brldYkw.html

              http://www.boydwang.com/2014/03/use-mysqlbinlog-to-restore-accidentally-deleted-data/

              http://blog.csdn.net/nuli888/article/details/52106910

              https://www.cnblogs.com/martinzhang/p/3454358.html

https://www.cnblogs.com/moonandstar08/p/8476228.html

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

Binlog日志的两个最重要的使用场景

• MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

• 数据恢复：通过使用 mysqlbinlog工具来使恢复数据

启用 Binlog

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

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

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

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

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

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

常用的Binlog操作命令

# 是否启用binlog日志
show variables like 'log_bin';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position
show master status;


# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容
show binlog events;

# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';

# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3; 

# 删除当前的binlog文件
reset master; 

# 删除slave的中继日志
reset slave;

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件
purge master logs to 'master.000003';

写 Binlog 的时机
对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；

如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。

设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

 

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 的日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

• STATEMENT：基于SQL语句的复制（statement-based replication, SBR）

• ROW：基于行的复制（row-based replication, RBR）

• MIXED：混合模式复制（mixed-based replication, MBR）

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

Statement

每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

Row

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

mysqlbinlog 命令的使用

服务器以二进制格式将binlog日志写入binlog文件，如何要以文本格式显示其内容，可以使用 mysqlbinlog 命令。

# 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"

设置日志格式为ROW时，在我的机器上输出了以下信息

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190308 10:05:03 server id 1  end_log_pos 123 CRC32 0xff02e23d     Start: binlog v 4, server v 5.7.22-log created 190308 10:05:03
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190308 10:05:03 server id 1  end_log_pos 154 CRC32 0xb81da4c5     Previous-GTIDs
# [empty]
# at 154
#190308 10:05:09 server id 1  end_log_pos 219 CRC32 0xfb30d42c     Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
...
...
# 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
/*!*/;
# at 21094
#190308 10:10:09 server id 1  end_log_pos 21161 CRC32 0xdb7a2b35     Table_map: `maxwell`.`positions` mapped to number 110
# at 21161
#190308 10:10:09 server id 1  end_log_pos 21275 CRC32 0xec3be372     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `maxwell`.`positions`
### WHERE
###   @1=1
###   @2='master.000003'
###   @3=20262
###   @4=NULL
###   @5='maxwell'
###   @6=NULL
###   @7=1552011005707
### SET
###   @1=1
###   @2='master.000003'
###   @3=20923
###   @4=NULL
###   @5='maxwell'
###   @6=NULL
###   @7=1552011009790
# at 21275
#190308 10:10:09 server id 1  end_log_pos 21306 CRC32 0xe6c4346d     Xid = 13088
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*/;

截取其中的一段进行分析：

# 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

Binlog 事件类型

binlog 事件的结构主要有3个版本：

• v1: 在 MySQL 3.23 中使用

• v3: 在 MySQL 4.0.2 到 4.1 中使用

• v4: 在 MySQL 5.0 及以上版本中使用

现在一般不会使用MySQL5.0以下版本，所以下面仅介绍v4版本的binlog事件类型。binlog 的事件类型较多，本文在此做一些简单的汇总

事件类型	说明
UNKNOWN_EVENT	此事件从不会被触发，也不会被写入binlog中；发生在当读取binlog时，不能被识别其他任何事件，那被视为UNKNOWN_EVENT
START_EVENT_V3	每个binlog文件开始的时候写入的事件，此事件被用在MySQL3.23 – 4.1，MYSQL5.0以后已经被 FORMAT_DESCRIPTION_EVENT 取代
QUERY_EVENT	执行更新语句时会生成此事件，包括：create，insert，update，delete；
STOP_EVENT	当mysqld停止时生成此事件
ROTATE_EVENT	当mysqld切换到新的binlog文件生成此事件，切换到新的binlog文件可以通过执行flush logs命令或者binlog文件大于 max_binlog_size 参数配置的大小；
INTVAR_EVENT	当sql语句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函数；此事件没有被用在binlog_format为ROW模式的情况下
LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL 3.23版本中使用
SLAVE_EVENT	未使用
CREATE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
APPEND_BLOCK_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
EXEC_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
DELETE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
NEW_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
RAND_EVENT	执行包含RAND()函数的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
USER_VAR_EVENT	执行包含了用户变量的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
FORMAT_DESCRIPTION_EVENT	描述事件，被写在每个binlog文件的开始位置，用在MySQL5.0以后的版本中，代替了START_EVENT_V3
XID_EVENT	支持XA的存储引擎才有，本地测试的数据库存储引擎是innodb，所有上面出现了XID_EVENT；innodb事务提交产生了QUERY_EVENT的BEGIN声明，QUERY_EVENT以及COMMIT声明，如果是myIsam存储引擎也会有BEGIN和COMMIT声明，只是COMMIT类型不是XID_EVENT
BEGIN_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
EXECUTE_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
TABLE_MAP_EVENT	用在binlog_format为ROW模式下，将表的定义映射到一个数字，在行操作事件之前记录（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT）
PRE_GA_WRITE_ROWS_EVENT	已过期，被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT	已过期，被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT	已过期，被 DELETE_ROWS_EVENT 代替
WRITE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 insert 操作
UPDATE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 update 操作
DELETE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 delete 操作
INCIDENT_EVENT	主服务器发生了不正常的事件，通知从服务器并告知可能会导致数据处于不一致的状态
HEARTBEAT_LOG_EVENT	主服务器告诉从服务器，主服务器还活着，不写入到日志文件中

Binlog 事件的结构

一个事件对象分为事件头和事件体，事件的结构如下：

如果事件头的长度是 x 字节，那么事件体的长度为 (event_length - x) 字节；设事件体中 fixed part 的长度为 y 字节，那么 variable part 的长度为 (event_length - (x + y)) 字节

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

STATEMENT格式下create、insert、update、delete操作产生的binlog事件

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

ROW格式下create、insert、update、delete操作产生的binlog事件

关于Event的分析，有需要可以查看参考文档进行推算。

参考文档

• MySQL 5.7参考手册.二进制日志

• MySQL Internals Manual.The Binary Log

• 朱小厮.MySQL Binlog解析

• 七把刀.MySQL binlog格式解析

• 散尽浮华.Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

• MySql Binlog 初识

• MySQL5.7杀手级新特性：GTID原理与实战

• MySQL 5.7 基于 GTID 的主从复制实践

https://mp.weixin.qq.com/s?__biz=MzI1NDU0MTE1NA==&mid=2247483875&idx=1&sn=2cdc232fa3036da52a826964996506a8&chksm=e9c2edeedeb564f891b34ef1e47418bbe6b8cb6dcb7f48b5fa73b15cf1d63172df1a173c75d0&scene=0&xtrack=1&key=e3977f8a79490c6345befb88d0bbf74cbdc6b508a52e61ea076c830a5b64c552def6c6ad848d4bcc7a1d21e53e30eb5c1ead33acdb97df779d0e6fa8a0fbe4bda32c04077ea0d3511bc9f9490ad0b46c&ascene=1&uin=MjI4MTc0ODEwOQ%3D%3D&devicetype=Windows+7&version=62060719&lang=zh_CN&pass_ticket=h8jyrQ71hQc872LxydZS%2F3aU1JXFbp4raQ1KvY908BcKBeSBtXFgBY9IS9ZaLEDi

转载：https://www.cnblogs.com/softidea/p/12624778.html