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-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 */
这里可以用pager less 或者 pager grep "关键词"|less 进行查看
可以看到这个命令也像打印表一样，显示了多个字段(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*/;
- 由以上的内容可知，其中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.日志的截取与恢复(注意这里只能配合全备+binlog日志才能恢复数据)
binlog日志只能配合全备(mysqldump或xtrabackup全备数据)才能恢复到某个时间点的位置，具体操作会在Mysql之备份与恢复这一小节中进行细说。
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和结构定义) 补充:
