13. Mysql之二进制日志(binlog)

1.前言

  二进制日志本来我就像在前面的一小节给简要地概括了,后来想想这个日志还是太重要了,需要细细讲解,因为它关系着mysql的复制和备份恢复等一些非常重要的功能。

2.什么叫二进制日志?

  二进制日志(binary log) 记录了对mysql 数据库执行更改的所有操作,但是不包括select 和show 这类操作,因为这类操作对数据本身没有修改,然而,若操作本身没有导致数据库发生变化,那么该操作可能也会写入二进制日志中。

3.二进制日志的作用?

  • 恢复(recovery):某些数据的恢复需要二进制日志
  • 复制(replication):一般用于主从复制
  • 审计(audit):用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行诸如攻击(这里是否也可以用general log 进行审计呢?) 

4.影响二进制的几个配置参数 

log_bin =/data/3307/binlog/bin.log      ##这个是配置二进制文件的位置
sync_binlog = 1                         
log_slave_updates
binlog_format = row
max_binlog_size
binlog_cache_size
binlog-do-db
binlog-ignore-db

    4.1参数详解:

  sync_binlog=1

  • 设置写了多少个 binlog events 之后把 binlog 日志刷新到磁盘
  • 默认为 0,表示二进制日志的落盘操作由操作系统刷新,性能最好,但也是最不 安全,发生意外崩溃时没有落盘的 binlog 日志将丢失
  • 为了确保 binlog 日志的安全,建议设置为 1,这样在每次事务提交时就会调用 fdatasync()实时把 binlog 日志刷新到磁盘,如果 binlog 支持 group commit,则设置为1表示每个 group commit 提交时就会调用 fdatasync()实时把 binlog 日志刷新到磁盘。注意:虽然设置为 1 是最安全的,但是在极端情况下,发生 crash时还是有可能最后一个事务或者 commit group的binlog日志。且设置为1是性能最差的值。

 log_slave_updates=1

  • 将 master传输过来的变更操作,再次记录成本地 binlog,用于做二次复制,当做中继分发节点,但是要注意,从库中记录的这个 binlog 与主库中的 binlog 内容格式不 一样,从库中的 binlog 包含了主库和从库的 server-id,为了防止复制的循环重复执 行。在 GTID 复制模式下,所有数据库都要开启 log_slave_updates 参数,是因为为了方便快速同步数据,如果涉及到切换,那么可以快速地把二进制日志进行同步(因为所有服务器都有记录完整的 binlog)     

 binlog_format = row

  • 控制二进制日志使用哪种记录格式
  • 有三个有效值(其实只有row和 statement 两种记录格式:mixed 是row和statement 混用):mixed、row、statement
  • * statement:5.1 版本之前都使用这种版本,日志记录中全是 sql 语句,主从复制的时候会将日志解析为 SQL 原文本,并在从库重新执行一次,这种格式的优点 就是日志记录清晰易读,日志量少,对 I/O 影响小,缺点是在某些情况下 slave 的日志复制会报错,比如事务提交顺序的影响。要注意:在 statement 格式下, RU 和 RC 隔离级别不允许执行 load data 语句,在 RR 及其串行隔离级别下,备库在执行 Load data 语句时会把主库上的 load data 加载的临时文件拉取一份到自己的tmpdir目录下执行 load data 语句,执行完成后删除 tempdir 目录下的文件,已达到复制load data语句的目的。 

  • * row:行格式,将每一行的数据改变记录到日志中(row 格式中默认情况下完整的记录了before image 和 after image,before image 代表着数据变更前的整行所有列的数据,after image 记录的是数据变更之后整行所有列的数据,对于 update 语句,记录的是 before image 和 after image,对于 delete 操作,只记录了 before image,对于 insert 语句,记录的是 after image。在 5.6.x 的版本中引入了一个参 数 binlog_row_image=full 参数,控制 Image 中是否记录完整的列还是记录最小匹配需要的列,详见 binlog_row_image 参数解释部分),而不是记录语句,这种记录格式保存在二进制日志中是加密信息,需要使用命令才能查看到内容(如 使用 mysqlbinlog 命令的"-v --base64-output=decode-rows"参数解析 binlog 就可以 打印可读格式),优点是能保存 slave 复制不出现问题,但是缺点是 I/O 影响大, 日志记录无法直接读取,日志记录量大。另外,row 格式记录的是 DML,对于 DDL 或其他管理类型的语句, 记录的仍然是 statement 格式。 如果设置了 binlog_format 为 row,可以将 InnoDB 的事务隔离级别设置为 RC,以获得更好的并发性。

  • mixed:目前 mysql 默认的日志格式,混合模式,混合了 statement 和 row 两种 日志,默认情况下是使用 statement,但是在一些特殊情况下自动采用 row 格式 来记录,比如采用 NDB 存储引擎时、DML 语句全部采用 row 格式、客户端使 用临时表、客户端采用了不确定函数(如 current_user()等,因为这些不确定函数 在主从中得到的值可能不同,可能导致主从数据产生不一致。),混合模式尽量 使用两种格式的优点,避开他们的缺点 

  可能使用Row模式的情况有:

  • 表的存储引擎为 NDB,这时对表的 DML 操作都会以 row 格式记录
  • 使用了 uuid(),user(),current_user(),found_rows(),row_count()等不确定函数
  • 使用了 insert delay 语句
  • 使用了用户自定义函数 UDF
  • 使用了临时表
  • 使用了 load data 语句    

  注意:

  • row 格式下不支持 blackhole 引擎,statement 格式下不支持 NDB 存储引擎
  • 在存储过程、函数或触发器内部,不能动态修改 binlog 格式,否则报错
  • 如果会话当前处于基于行的复制模式并且已打开临时表,不能动态修改 binlog 格式,否则报错
  • 不能在一个事务内部动态修改 binlog 格式,否则报错
  • 二进制日志格式会影响以下服务器选项的行为,除非必须,否则不建议在复 制架构中使用这些参数:

   --replicate-do-db、--replicate-ignore-db、、--binlog-do-db、、--binlog-ignore-db

  • 全局变量,会话变量,动态变量(修改需要有 super 权限),默认值为 STATEMENT (但在 mysql NDB cluster 7.3 及其之后的版本中默认值为 MIXED,因为不支持 STATMENT 格式,另外,在 mysql5.7.7 及其之后的版本中,默认为 ROW 格式

 max_binlog_size

  • 指定单个二进制日志文件的最大值,如果超过这个值则进行日志滚动,关闭当前正 在使用的 binlog 文件并产生一个新的日志文件,在正在使用的 binlog file 后缀编号 上加一命名新的二进制文件,并把新日志名记录到.index 文件中  
  • 事务产生的日志在一个块中写入二进制日志,就算事务产生的日志大小超过了 配置的最大值,不会拆分为多个块来写入多个二进制日志文件。 因此,如果您 有大事务,您可能会在查看文件系统时,看到二进制日志文件大于 max_binlog_size。要避免这种情况,请拆分你的大事务为多个小事务。

  • 如果 max_relay_log_size 为 0,则 max_binlog_size 的值也适用于中继日志文件的 大小控制
  • 全局变量,动态变量,默认值为 1G,最小值为 4K,最大值为 1G,整型值

binlog_cache_size

  • binlog cache 的大小。 binlog cache 每个客户端都会独立分配二进制日志高速缓存 (所有客户端的总的事务语句 binlog cache 大小由变量 max_binlog_cache_size 控制, 如果超过这个大小时会报错)。 如果您经常使用大型事务,则可以增加此缓存大小 以获得更好的性能。观察Binlog_cache_use 和Binlog_cache_disk_use状态变量的值, 如果 Binlog_cache_disk_use 经常被使用到,就需要调整此变量的大小
  • binlog_cache_size 仅设用于事务语句高速缓存的大小; 非事务语句高速缓存的大 小由 binlog_stmt_cache_size 系统变量管理。 
  • 全局变量,动态变量,整型值,默认值为 32K
  • PS:一个事务产生的 binlog 的量超过了 binlog_cache_size 的值时,就会在 tmpdir 参数指定的目录下产生临时文件(类似/tmp/MYdRH1GW,可以使用 lsof 命令 查看, 你经常可能会看到类似"/tmp/MYdRH1GW (deleted)" 的结果)来存放 binlog cache(如果 binlog_format 设置为 row 格式,则临时文件以 ML 开头,其 他的格式则临时文件以 MY 开头),待到事务执行 commit 且 binlog 执行到 flush 阶段时,才会写 binlog file 的文件系统缓冲,binlog 执行到 sync 阶段时,binlog 才会sync到 binlog file 所在的磁盘中。在 sync 阶段执行完成之前,如果一旦数据库异常 crash 或者 tmpdir 空间满了,则会造成 binlog 损坏,进而可能导致 从库复制报错 

binlog-do-dbbinlog-ignore-db表示需要写入或忽略吸入哪些库的日志,默认为空,表示需要同步所有库的日志到二进制日志

expire_logs_days

  • 设置二进制日志的过期天数,过了指定天数的日志将被自动删除,可动态修改
  • 如果设置了非0值,则在 mysqld 启动和日志刷新时,可能执行清理超过定义天数的 binlog file
  • 全局变量,动态变量,默认值为 0(代表不会自动清理 binlog),整型值,取值0-99

     

5. Mysql之binlog操作  

  5.1 查看binlog日志的数量和大小: 

root@localhost 20:48:  [employees]> show binary logs;
+------------+-----------+
| Log_name   | File_size |
+------------+-----------+
| bin.000001 |      8089 |
| bin.000002 |       477 |
| bin.000003 |       217 |
| bin.000004 |      1124 |
| bin.000005 |       217 |
| bin.000006 |       217 |
| bin.000007 |       217 |
| bin.000008 |       217 |
| bin.000009 |       217 |
| bin.000010 |       217 |
| bin.000011 |       217 |
| bin.000012 |       217 |
| bin.000013 |       217 |
| bin.000014 |       217 |
| bin.000015 |      9124 |

  5.2 查看目前正在使用的二进制文件

root@localhost 20:50:  [employees]> show master status;
+------------+----------+--------------+------------------+---------------------------------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------+----------+--------------+------------------+---------------------------------------------+
| bin.000044 |     1665 |              |                  | beb21a31-9a20-11eb-ae5c-000c296098ca:1-2672 |
+------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

  5.3 日志内容查看(这里只截取部分)

13. Mysql之二进制日志(binlog)
root@localhost 20:52:  [employees]> show binlog events in bin.000044;
+------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name   | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                  |
+------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bin.000044 |    4 | Format_desc    |     20107 |         123 | Server ver: 5.7.30-log, Binlog ver: 4                                                                                                                                 |
| bin.000044 |  123 | Previous_gtids |     20107 |         194 | beb21a31-9a20-11eb-ae5c-000c296098ca:1-2666                                                                                                                           |
| bin.000044 |  194 | Gtid           |     20107 |         259 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2667                                                                                                  |
| bin.000044 |  259 | Query          |     20107 |         488 | use `liulin`; create table t1(id int not null auto_increment,a int not null,b int not null,c int not null,primary key(id),key a_b_c(a,b,c))engine=innodb,charset=utf8 |
| bin.000044 |  488 | Gtid           |     20107 |         553 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2668                                                                                                  |
| bin.000044 |  553 | Query          |     20107 |         627 | BEGIN                                                                                                                                                                 |
| bin.000044 |  627 | Table_map      |     20107 |         677 | table_id: 108 (liulin.t1)                                                                                                                                             |
| bin.000044 |  677 | Write_rows     |     20107 |         780 | table_id: 108 flags: STMT_END_F                                                                                                                                       |
| bin.000044 |  780 | Xid            |     20107 |         811 | COMMIT /* xid=13 */                                                                                                                                                   |
| bin.000044 |  811 | Gtid           |     20107 |         876 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2669                                                                                                  |
| bin.000044 |  876 | Query          |     20107 |         950 | BEGIN                                                                                                                                                                 |
| bin.000044 |  950 | Table_map      |     20107 |        1000 | table_id: 108 (liulin.t1)                                                                                                                                             |
| bin.000044 | 1000 | Write_rows     |     20107 |        1052 | table_id: 108 flags: STMT_END_F                                                                                                                                       |
| bin.000044 | 1052 | Xid            |     20107 |        1083 | COMMIT /* xid=16 */                                                                                                                                                   |
| bin.000044 | 1083 | Gtid           |     20107 |        1148 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2670                                                                                                  |
| bin.000044 | 1148 | Query          |     20107 |        1233 | use `liulin`; analyze table t1                                                                                                                                        |
| bin.000044 | 1233 | Gtid           |     20107 |        1298 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2671                                                                                                  |
| bin.000044 | 1298 | Query          |     20107 |        1393 | use `liulin`; optimize table t1                                                                                                                                       |
| bin.000044 | 1393 | Gtid           |     20107 |        1458 | SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2672                                                                                                  |
| bin.000044 | 1458 | Query          |     20107 |        1532 | BEGIN                                                                                                                                                                 |
| bin.000044 | 1532 | Table_map      |     20107 |        1582 | table_id: 115 (liulin.t1)                                                                                                                                             |
| bin.000044 | 1582 | Write_rows     |     20107 |        1634 | table_id: 115 flags: STMT_END_F                                                                                                                                       |
| bin.000044 | 1634 | Xid            |     20107 |        1665 | COMMIT /* xid=44 */
show binlog events in ‘binlog文件名‘

可以看到这个命令也像打印表一样,显示了多个字段(Log_name,Pos,Event_type,server_id,End_log_pos,info)

  • Log_name:列出的文件的名称
  • Pos:事件发生的位置
  • Event_type:描述事件类型的标识符
  • Server_id:发生事件的服务器的服务器 ID
  • End_log_pos:下一个事件开始的位置,等于Pos加上事件的大小
  • Info:有关事件类型的更多详细信息。此信息的格式取决于事件类型

  5.4 这里详解Events_type

  首先在Mysql 5.1.18到5.5.33版本中,一共有27种事件类型(其中有些事件是不能使用的,但是为了向后兼容而保留了),到5.6.12版本已经有了35中事件类型了。

  这里就按照如上显示出的顺序进行介绍:

  1.FORMAT_DESCRIPTION_EVENT

  • FORMAT_DESCRIPTION_EVENT是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件,而且,该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。它通常指定了MySQL Server的版本,binlog的版本,该binlog文件的创建时间 

  2. previous_gtids

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

   3.GTID

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

 4.QUERY

  • QUERY_EVENT以文本的形式来记录事务的操作,QUERY_EVENT类型的事件通常在以下几种情况下使用:1.事务开始时,执行的BEGIN操作,2.STATEMENT格式中的DML操作,3. ROW格式中的DDL操作  

 5.table_map

  • row格式的binlog文件中,每个ROW_EVENT之前都有一个TABLE_MAP_EVENT,用于描述表的内部ID和结构定义  

 6.Row_event

  • 对于ROW格式的binlog,所有的DML语句都是记录在ROWS_EVENT中。
  • ROWS_EVENT分为三种:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT,分别对应insert,update和delete操作。 
  • 对于insert操作,WRITE_ROWS_EVENT包含了要插入的数据,对于update操作,UPDATE_ROWS_EVENT不仅包含了修改后的数据,还包含了修改前的值。对于delete操作,仅仅需要指定删除的主键(在没有主键的情况下,会给定所有列),
  • 对于QUERY_EVENT事件,是以文本形式记录DML操作的。而对于ROWS_EVENT事件,并不是文本形式(*加密文本*),所以在通过mysqlbinlog查看基于ROW格式的binlog时,需要指定-vv --base64-output=decode-rows。     

 7.XID_Event

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

  8.ROTATE_EVENT(补充)

  • 当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时,binlog会发生切换,这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件,用于指定下一个日志的名称和位置。 

  9.STOP_EVENT(补充) 

  • 当MySQL数据库停止时,会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。  

 *以上都是用show binlog events in ‘二进制文件名‘命令查出的,该方式可以很简单地看到各位事件的类型以及对应的起点pos和终点pos位置信息,但是它看不到具体执行的语句。 

  此时我们可以直接用命令mysqlbinlog -vv --base64-output=decode-rows  ‘binlog日志文件名‘ 命令进行查看  

13. Mysql之二进制日志(binlog)
  1 [root@node01 binlog]# mysqlbinlog  -vv --base64-output=decode-rows  binlog.000044;
  2 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4 DELIMITER /*!*/;
  5 mysqlbinlog: File binlog.000044 not found (Errcode: 2 - No such file or directory)
  6 SET @@SESSION.GTID_NEXT= AUTOMATIC /* added by mysqlbinlog */ /*!*/;
  7 DELIMITER ;
  8 # End of log file
  9 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 10 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 11 [root@node01 binlog]# 
 12 [root@node01 binlog]# 
 13 [root@node01 binlog]# 
 14 [root@node01 binlog]# mysqlbinlog  -vv --base64-output=decode-rows  ‘bin.000044‘;
 15 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 16 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 17 DELIMITER /*!*/;
 18 # at 4
 19 #210626 13:20:40 server id 20107  end_log_pos 123 CRC32 0xdaeaa211     Start: binlog v 4, server v 5.7.30-log created 210626 13:20:40 at startup
 20 # Warning: this binlog is either in use or was not closed properly.
 21 ROLLBACK/*!*/;
 22 # at 123
 23 #210626 13:20:40 server id 20107  end_log_pos 194 CRC32 0xad29457a     Previous-GTIDs
 24 # beb21a31-9a20-11eb-ae5c-000c296098ca:1-2666
 25 # at 194
 26 #210626 17:36:06 server id 20107  end_log_pos 259 CRC32 0x7baee8f1     GTID    last_committed=0    sequence_number=1    rbr_only=no
 27 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2667/*!*/;
 28 # at 259
 29 #210626 17:36:06 server id 20107  end_log_pos 488 CRC32 0xd37a862d     Query    thread_id=2    exec_time=0    error_code=0
 30 use `liulin`/*!*/;
 31 SET TIMESTAMP=1624700166/*!*/;
 32 SET @@session.pseudo_thread_id=2/*!*/;
 33 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 34 SET @@session.sql_mode=1436549152/*!*/;
 35 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 36 /*!\C utf8 *//*!*/;
 37 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
 38 SET @@session.lc_time_names=0/*!*/;
 39 SET @@session.collation_database=DEFAULT/*!*/;
 40 create table t1(id int not null auto_increment,a int not null,b int not null,c int not null,primary key(id),key a_b_c(a,b,c))engine=innodb,charset=utf8
 41 /*!*/;
 42 # at 488
 43 #210626 17:39:15 server id 20107  end_log_pos 553 CRC32 0x69473a01     GTID    last_committed=1    sequence_number=2    rbr_only=yes
 44 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 45 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2668/*!*/;
 46 # at 553
 47 #210626 17:39:15 server id 20107  end_log_pos 627 CRC32 0x4d53a149     Query    thread_id=2    exec_time=0    error_code=0
 48 SET TIMESTAMP=1624700355/*!*/;
 49 BEGIN
 50 /*!*/;
 51 # at 627
 52 #210626 17:39:15 server id 20107  end_log_pos 677 CRC32 0x0a198c11     Table_map: `liulin`.`t1` mapped to number 108
 53 # at 677
 54 #210626 17:39:15 server id 20107  end_log_pos 780 CRC32 0x80948986     Write_rows: table id 108 flags: STMT_END_F
 55 ### INSERT INTO `liulin`.`t1`
 56 ### SET
 57 ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 58 ###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
 59 ###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
 60 ###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
 61 ### INSERT INTO `liulin`.`t1`
 62 ### SET
 63 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
 64 ###   @2=2 /* INT meta=0 nullable=0 is_null=0 */
 65 ###   @3=2 /* INT meta=0 nullable=0 is_null=0 */
 66 ###   @4=2 /* INT meta=0 nullable=0 is_null=0 */
 67 ### INSERT INTO `liulin`.`t1`
 68 ### SET
 69 ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
 70 ###   @2=3 /* INT meta=0 nullable=0 is_null=0 */
 71 ###   @3=3 /* INT meta=0 nullable=0 is_null=0 */
 72 ###   @4=3 /* INT meta=0 nullable=0 is_null=0 */
 73 ### INSERT INTO `liulin`.`t1`
 74 ### SET
 75 ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
 76 ###   @2=4 /* INT meta=0 nullable=0 is_null=0 */
 77 ###   @3=4 /* INT meta=0 nullable=0 is_null=0 */
 78 ###   @4=4 /* INT meta=0 nullable=0 is_null=0 */
 79 # at 780
 80 #210626 17:39:15 server id 20107  end_log_pos 811 CRC32 0x836eda3f     Xid = 13
 81 COMMIT/*!*/;
 82 # at 811
 83 #210626 17:43:14 server id 20107  end_log_pos 876 CRC32 0xbf7be942     GTID    last_committed=2    sequence_number=3    rbr_only=yes
 84 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 85 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2669/*!*/;
 86 # at 876
 87 #210626 17:43:14 server id 20107  end_log_pos 950 CRC32 0xfcc1f98d     Query    thread_id=2    exec_time=0    error_code=0
 88 SET TIMESTAMP=1624700594/*!*/;
 89 BEGIN
 90 /*!*/;
 91 # at 950
 92 #210626 17:43:14 server id 20107  end_log_pos 1000 CRC32 0xaba0501f     Table_map: `liulin`.`t1` mapped to number 108
 93 # at 1000
 94 #210626 17:43:14 server id 20107  end_log_pos 1052 CRC32 0xf86511c8     Write_rows: table id 108 flags: STMT_END_F
 95 ### INSERT INTO `liulin`.`t1`
 96 ### SET
 97 ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
 98 ###   @2=4 /* INT meta=0 nullable=0 is_null=0 */
 99 ###   @3=4 /* INT meta=0 nullable=0 is_null=0 */
100 ###   @4=5 /* INT meta=0 nullable=0 is_null=0 */
101 # at 1052
102 #210626 17:43:14 server id 20107  end_log_pos 1083 CRC32 0xa3f464a2     Xid = 16
103 COMMIT/*!*/;
104 # at 1083
105 #210626 18:09:49 server id 20107  end_log_pos 1148 CRC32 0x6344af4c     GTID    last_committed=3    sequence_number=4    rbr_only=no
106 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2670/*!*/;
107 # at 1148
108 #210626 18:09:49 server id 20107  end_log_pos 1233 CRC32 0xfc7e2fab     Query    thread_id=2    exec_time=0    error_code=0
109 SET TIMESTAMP=1624702189/*!*/;
110 analyze table t1
111 /*!*/;
112 # at 1233
113 #210626 18:19:22 server id 20107  end_log_pos 1298 CRC32 0x9dd35230     GTID    last_committed=4    sequence_number=5    rbr_only=no
114 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2671/*!*/;
115 # at 1298
116 #210626 18:19:22 server id 20107  end_log_pos 1393 CRC32 0xa4eb7802     Query    thread_id=2    exec_time=0    error_code=0
117 SET TIMESTAMP=1624702762/*!*/;
118 optimize table t1
119 /*!*/;
120 # at 1393
121 #210626 18:24:00 server id 20107  end_log_pos 1458 CRC32 0x1f17bdae     GTID    last_committed=5    sequence_number=6    rbr_only=yes
122 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
123 SET @@SESSION.GTID_NEXT= beb21a31-9a20-11eb-ae5c-000c296098ca:2672/*!*/;
124 # at 1458
125 #210626 18:24:00 server id 20107  end_log_pos 1532 CRC32 0x2200b1f3     Query    thread_id=2    exec_time=0    error_code=0
126 SET TIMESTAMP=1624703040/*!*/;
127 BEGIN
128 /*!*/;
129 # at 1532
130 #210626 18:24:00 server id 20107  end_log_pos 1582 CRC32 0x7938e8ee     Table_map: `liulin`.`t1` mapped to number 115
131 # at 1582
132 #210626 18:24:00 server id 20107  end_log_pos 1634 CRC32 0x229f6ebf     Write_rows: table id 115 flags: STMT_END_F
133 ### INSERT INTO `liulin`.`t1`
134 ### SET
135 ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
136 ###   @2=4 /* INT meta=0 nullable=0 is_null=0 */
137 ###   @3=5 /* INT meta=0 nullable=0 is_null=0 */
138 ###   @4=5 /* INT meta=0 nullable=0 is_null=0 */
139 # at 1634
140 #210626 18:24:00 server id 20107  end_log_pos 1665 CRC32 0xc185df47     Xid = 44
141 COMMIT/*!*/;
142 SET @@SESSION.GTID_NEXT= AUTOMATIC /* added by mysqlbinlog */ /*!*/;
143 DELIMITER ;
144 # End of log file
145 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
146 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysqlbinlog查看
  • 由以上的内容可知,其中xid事件表示的是一个事务的介绍标志,这个很重要
### INSERT INTO `liulin`.`t1`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2=5 /* INT meta=0 nullable=0 is_null=0 */
###   @3=6 /* INT meta=0 nullable=0 is_null=0 */
###   @4=6 /* INT meta=0 nullable=0 is_null=0 */

-->这里的@1=7表示向表t1的第一列中插入7,@2=5表示向表中第二列中插入5,@3表示向表的第三列中插入6,@4=6表示向表的第四列中插入6

 

6.日志的截取与恢复

  Mysql的日志截取和恢复有两种方法,一种是基于position点,一种是基于GTID事件

  6.1 基于position点(截取与恢复)  

 ---截取数据---- 

 mysqlbinlog --start-position=123 --stop-position=456 mysql-bin.000001 >/tmp/bin_log.sql
  
 --star-position:表示截取时起始位置
--stop-position:表示截取时结束位置
-----恢复数据-----

mysqlbinlog  --start-position=684  --stop-position=1292   mysql-bin.000008  >/tmp/bin_log.sql
mysql> source /tmp/bin_log.sql

或者 mysqlbinlog --start-position="1562" --stop-position="2740" mysql-bin.000003|mysql -uroot -p

  6.2 基于GTID(数据截取与恢复)

 ----截取数据命令----
mysqlbinlog --include-gtids=‘3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12‘ mysql-bin.000004> /tmp/bin.sql

##表示将GTID(7-12)对饮的事务进行截取下来,用来以后的数据恢复
----恢复数据命令-----
mysqlbinlog --include-gtids=‘3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12‘ mysql-bin.000004|mysql -uroot -p

  关于GTID的几个重要参数 

  --include-gtids 截取指定的gtid
  --exclude-gtids 排除指定的gtid
  --skip-gtids 跳过gtid的幂等性机制的检查,即截取日志的时候不带有gtid的信息

 

7.关于二进制日志的清理操作

  7.1自动清理  

-->show variables like %expire%;
-->expire_logs_days  0  ##默认是不清理
建议:自动清理时间,是要按照全备周期+1
set global expire_logs_days=8;
永久生效:在配置文件中条件该参数
my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog

  7.2手动清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO mysql-bin.000010;
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志

 7.3关于binlog日志滚动

  • 当二进制文件的大小高于max_binlog_size时,会发生日志的滚动,开启一个新的二进制日志进行记录事件
  • 可以人工使用Flush log命令进行强制刷新二进制日志。  

  

8.关于table_map_event(用于描述表的内部ID和结构定义) 补充:

13. Mysql之二进制日志(binlog)

  

  

  

    

 

 

  

13. Mysql之二进制日志(binlog)

上一篇:Android平台HTTPS抓包全方案


下一篇:phpstudy mysql5.7无法启动成功