二进制日志本来我就像在前面的一小节给简要地概括了，后来想想这个日志还是太重要了，需要细细讲解，因为它关系着mysql的复制和备份恢复等一些非常重要的功能。
二进制日志(binary log) 记录了对mysql 数据库执行更改的所有操作，但是不包括select 和show 这类操作，因为这类操作对数据本身没有修改，然而，若操作本身没有导致数据库发生变化，那么该操作可能也会写入二进制日志中。
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
sync_binlog=1
log_slave_updates=1
binlog_format = row
* 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，以获得更好的并发性。
--replicate-do-db、--replicate-ignore-db、、--binlog-do-db、、--binlog-ignore-db
事务产生的日志在一个块中写入二进制日志，就算事务产生的日志大小超过了 配置的最大值，不会拆分为多个块来写入多个二进制日志文件。 因此，如果您 有大事务，您可能会在查看文件系统时，看到二进制日志文件大于 max_binlog_size。要避免这种情况，请拆分你的大事务为多个小事务。
binlog_cache_size
binlog-do-db和 binlog-ignore-db表示需要写入或忽略吸入哪些库的日志，默认为空，表示需要同步所有库的日志到二进制日志
expire_logs_days
全局变量,动态变量，默认值为 0（代表不会自动清理 binlog），整型值，取值0-99
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 |
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)
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 */
可以看到这个命令也像打印表一样，显示了多个字段(Log_name，Pos，Event_type，server_id，End_log_pos，info)
Pos加上事件的大小首先在Mysql 5.1.18到5.5.33版本中，一共有27种事件类型(其中有些事件是不能使用的，但是为了向后兼容而保留了)，到5.6.12版本已经有了35中事件类型了。
这里就按照如上显示出的顺序进行介绍：
1.FORMAT_DESCRIPTION_EVENT
2. previous_gtids
3.GTID
4.QUERY
5.table_map
6.Row_event
7.XID_Event
8.ROTATE_EVENT(补充)
9.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*/;
### 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
Mysql的日志截取和恢复有两种方法，一种是基于position点，一种是基于GTID事件
---截取数据----
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
----截取数据命令----
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的信息
-->show variables like ‘%expire%‘; -->expire_logs_days 0 ##默认是不清理 建议：自动清理时间,是要按照全备周期+1 set global expire_logs_days=8; 永久生效:在配置文件中条件该参数 my.cnf expire_logs_days=15; 企业建议,至少保留两个全备周期+1的binlog
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开始重新记录日志
原文：https://www.cnblogs.com/zmc60/p/14942593.html