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

时间：2021-06-28 10:16:16 收藏：0 阅读：22

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-db和 binlog-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 日志内容查看(这里只截取部分)

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日志文件名‘ 命令进行查看　　

  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和结构定义) 补充:

技术分享图片

原文：https://www.cnblogs.com/zmc60/p/14942593.html