mysqlbinlog解析binlog为可执行sql mysql binlog类型

转载

技术博主 2023-08-02 10:28:42

文章标签 mysql 学习数据库服务器数据 文章分类 MySQL 数据库

Mysql数据库的日志可以分为服务层和存储引擎层日志。服务层日志主要包含二进制日志（binlog）、慢查询日志和通用日志；存储引擎层日志（以InnoDB为例）包含重做日志（redolog）和回滚日志（undolog）。

binlog中只存储执行成功的日志。

binlog的三种格式

设置binlog日志格式的参数：

set binlog_format=[statement|row|mixed];

查看binlog文件：

show binary logs;

刷新log文件：

flush logs;

查看binlog内容：

mysqlbinlog -vv binlog.000154

一、STATEMENT

按段存储，mysql 5.7 之前的版本默认使用的日志格式。binlog中只记录数据库修改时使用的sql语句。

优点：

日志较小，节省磁盘和网络IO，只记录sql语句

缺点：

必须记录上下文信息，以保证在从服务器上能得到相同的结果
特定函数无法复制，如UUID(), user()，可能造成主从服务器数据不一致

BEGIN
/*!*/;
# at 1351
# at 1383
#210216 23:44:22 server id 1  end_log_pos 1383 CRC32 0x3a8b5df0 	Intvar
SET INSERT_ID=3/*!*/;
#210216 23:44:22 server id 1  end_log_pos 1492 CRC32 0x2503c540 	Query	thread_id=15	exec_time=0	error_code=0
SET TIMESTAMP=1613490262/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
insert into t (num) values (3)
/*!*/;
# at 1492
#210216 23:44:22 server id 1  end_log_pos 1523 CRC32 0xe14ff0b0 	Xid = 297
COMMIT/*!*/;

二、ROW

基于行的binlog日志格式，Mysql 5.7 版本之后默认的binlog日志格式

优点：

主从复制更加安全
对每一行的复制效率高于基于段的复制效率
如果对数据进行了误操作，可以通过ROW格式的binlog进行反向处理，恢复数据

缺点：

记录的日志量较大，尤其是对于批量处理数据，每一行变动都会记录

优化：

mysql通过binlog_row_image参数对ROW格式的binlog进行了优化

set binlog_row_image=[FULL|MANIMAL|NOBLOG]

**FULL：**默认，记录被修改行的数据的所有列的内容
### UPDATE `test`.`t` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=4 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=5 /* INT meta=0 nullable=1 is_null=0 */
**MINIMAL：**只记录被修改的列的变化
### UPDATE `test`.`t` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @2=4 /* INT meta=0 nullable=1 is_null=0 */ # at 1035 #210217 0:14:33 server id 1 end_log_pos 1066 CRC32 0x37eac0bb Xid = 309 COMMIT/*!*/;
**NOBLOG：**与FULL类似，但是对于blog和text的值没有修改的话，就不记录（这里我先在表中添加了一个text字段）
### UPDATE `test`.`t` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=4 /* INT meta=0 nullable=1 is_null=0 */ # at 1522 #210217 0:22:11 server id 1 end_log_pos 1553 CRC32 0x853a53a6 Xid = 315 COMMIT/*!*/;
上面的日志中省略了text字段：
mysql> select * from t; +----+------+------+ | id | num | tt | +----+------+------+ | 1 | 1 | NULL | | 3 | 4 | NULL | +----+------+------+