binlog很有用的2个参数binlog_rows_query_log_events和binlog_row_image

最新推荐文章于 2023-07-01 14:03:18 发布
最新推荐文章于 2023-07-01 14:03:18 发布
阅读量2.3k 收藏 1
点赞数
分类专栏： mysql

mysql 5.6.2 引入的两个参数binlog_rows_query_log_events和binlog_row_image

对于使用row格式的binlog,个人觉得很有用;

binlog_rows_query_log_events =1 
    在row模式下..开启该参数,将把sql语句打印到binlog日志里面.默认是0(off);
   虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处.
binlog_row_image='minimal' 
   默认为full,在binlog为row格式下,full将记录update前后所有字段的值,minimal时,只记录更改字段的值和where字段的值,noblob时,记录除了blob和text的所有字段的值,如果update的blob或text字段,也只记录该字段更改后的值,更改前的不记录;

大家都知道row格式下的binlog增长速度太快,对存储空间,主从传输都是一个不小的压力.因为每个event记录了所有更改数据前后的值,不管数据是否有改动都会记录.binlog_row_image的引入就大大减少了binlog记录的数据.在结合binlog_rows_query_log_events,对于dba日常维护binlog是完全没有压力的,而且节省了硬盘空间开销,减小I/O,减少了主从传输压力;


下面通过举例说明:

下面就是一个事务在row格式下的binlog日志,红色部分就是binlog_rows_query_log_events后的效果;黄色部分就是binlog_row_image=full的结果,记录所有字段在更改前后的值;

# at 1134
#161118 17:25:49 server id 3310  end_log_pos 1199 CRC32 0x3d0190ce      GTID    last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= '59194c6e-70db-11e6-b85b-5254002eb131:299'/*!*/;
# at 1199
#161118 17:25:49 server id 3310  end_log_pos 1270 CRC32 0x0bc5f8df      Query   thread_id=26    exec_time=0     error_code=0
SET TIMESTAMP=1479461149/*!*/;
BEGIN
/*!*/;
# at 1270
#161118 17:25:49 server id 3310  end_log_pos 1332 CRC32 0x7533bf59      Rows_query
# update t11 set col7 ='xxx' where id =2     ##############这一行就是启用了binlog_rows_query_log_events后的效果;
# at 1332
#161118 17:25:49 server id 3310  end_log_pos 1396 CRC32 0x75f09d77      Table_map: `tt1`.`t11` mapped to number 137
# at 1396
#161118 17:25:49 server id 3310  end_log_pos 1929 CRC32 0xa6eb390e      Update_rows: table id 137 flags: STMT_END_F
### UPDATE `tt1`.`t11`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='bbbbbbbbbbbbbbbbbbbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4='cccccccccccccccccccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5='dddddddddddddddddddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @6='eeeeeeeeeeeeeeeeeeee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @7='ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @8='gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='bbbbbbbbbbbbbbbbbbbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4='cccccccccccccccccccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5='dddddddddddddddddddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @6='eeeeeeeeeeeeeeeeeeee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @7='ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @8='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 1929
#161118 17:25:49 server id 3310  end_log_pos 1960 CRC32 0x9d147914      Xid = 551
COMMIT/*!*/;


看下面的例子:黄色部分就是binlog_row_image='minimal' 的日志.只记录了更改后的值和where值.跟上面的例子一对比就发现减少了太多记录;

# at 1960
#161118 17:26:25 server id 3310  end_log_pos 2025 CRC32 0x20921004      GTID    last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= '59194c6e-70db-11e6-b85b-5254002eb131:300'/*!*/;
# at 2025
#161118 17:26:25 server id 3310  end_log_pos 2096 CRC32 0x0c129ffb      Query   thread_id=26    exec_time=0     error_code=0
SET TIMESTAMP=1479461185/*!*/;
BEGIN
/*!*/;
# at 2096
#161118 17:26:25 server id 3310  end_log_pos 2158 CRC32 0x6a5031d0      Rows_query
# update t11 set col1 ='xxx' where id =3
# at 2158
#161118 17:26:25 server id 3310  end_log_pos 2222 CRC32 0x34de716f      Table_map: `tt1`.`t11` mapped to number 137
# at 2222
#161118 17:26:25 server id 3310  end_log_pos 2268 CRC32 0x77563057      Update_rows: table id 137 flags: STMT_END_F
### UPDATE `tt1`.`t11`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 2268
#161118 17:26:25 server id 3310  end_log_pos 2299 CRC32 0xe9b5eaa7      Xid = 555
COMMIT/*!*/;


下面看看binlog_row_image=noblob时的情况,在noblob下,跟full唯一的区别是没有记录字段类型为text和blob的值,其他的都记录了.而且在例子中特意更改了@8(col7),这是一个text类型的字段,可以看到,在set前是没有记录整个字段的,只是记录该值set后的值;

# at 3691
#161118 17:27:01 server id 3310  end_log_pos 3756 CRC32 0x59fb0d65      GTID    last_committed=6        sequence_number=7
SET @@SESSION.GTID_NEXT= '59194c6e-70db-11e6-b85b-5254002eb131:304'/*!*/;
# at 3756
#161118 17:27:01 server id 3310  end_log_pos 3827 CRC32 0x9943381e      Query   thread_id=26    exec_time=0     error_code=0
SET TIMESTAMP=1479461221/*!*/;
BEGIN
/*!*/;
# at 3827
#161118 17:27:01 server id 3310  end_log_pos 3889 CRC32 0xa617db5e      Rows_query
# update t11 set col7 ='xxx' where id =4
# at 3889
#161118 17:27:01 server id 3310  end_log_pos 3953 CRC32 0x74e30167      Table_map: `tt1`.`t11` mapped to number 137
# at 3953
#161118 17:27:01 server id 3310  end_log_pos 4180 CRC32 0xd92fe8dc      Update_rows: table id 137 flags: STMT_END_F
### UPDATE `tt1`.`t11`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='bbbbbbbbbbbbbbbbbbbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4='cccccccccccccccccccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5='dddddddddddddddddddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @6='eeeeeeeeeeeeeeeeeeee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='bbbbbbbbbbbbbbbbbbbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4='cccccccccccccccccccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5='dddddddddddddddddddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @6='eeeeeeeeeeeeeeeeeeee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @8='xxx' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 4180
#161118 17:27:01 server id 3310  end_log_pos 4211 CRC32 0x87487ec8      Xid = 561
COMMIT/*!*/;
# at 4211


对于delete效果也是一样的,就不一一举例了..

BEGIN
/*!*/;
# at 4347
#161118 18:46:03 server id 3310  end_log_pos 4398 CRC32 0xd76dbd23      Rows_query
# delete from t11 where id =2
# at 4398
#161118 18:46:03 server id 3310  end_log_pos 4462 CRC32 0x75656a14      Table_map: `tt1`.`t11` mapped to number 137
# at 4462
#161118 18:46:03 server id 3310  end_log_pos 4590 CRC32 0xafec2072      Delete_rows: table id 137 flags: STMT_END_F
### DELETE FROM `tt1`.`t11`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xxx' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='bbbbbbbbbbbbbbbbbbbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4='cccccccccccccccccccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5='dddddddddddddddddddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @6='eeeeeeeeeeeeeeeeeeee' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 4590
#161118 18:46:03 server id 3310  end_log_pos 4621 CRC32 0x179ae194      Xid = 562
COMMIT/*!*/;

Hi_Shook
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mySQL数据库性能优化pdf.pdf
07-17
针对面试经常问的sql优化问题,进行mySQL数据库性能优化
[MySQL binlog]mysql如何彻底解析Mixed日志格式的binlog
12-15
mysql binlog3种格式，row,mixed,statement. 解析工作 mysqlbinlog –base64-output=DECODE-ROWS -v mysql-bin.000144 |more –base64-output=DECODE-ROWS： 会显示出row模式带来的sql变更。 -v ：显示statement模式带来的sql语句 代码如下:[mysql@002tmp]$ mysqlbinlog –base64-output=DECODE-ROWS -v mysql-bin.000144 |more/*!40019 SET @@session.max_in
MySQL binlog中的事件类型详解
01-19
MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型。 首先，看看源码中定义的事件类型 源码位置：mysql-5.7.14/libbinlogevents/include/binlog_event.h enum Log_event_type { /** Every time you update this enum (when you add a type), you have to fix Format_description_event:
MySQL：Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEM
01-19
1：错误日志大量错误 150602 14:40:02 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT… SELECT… ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order
mysql问题之slow log中出现大量的binlog dump记录的解决方法
01-21
线上有个数据库，在slow log中，存在大量类似下面的记录： 代码如下: # Time: 130823 13:56:08 # User@Host: repl[repl] @ slave [10.x.x.x] # Query_time: 9.000833 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1377237368; # administrator command: Binlog Dump; 每完成一次binlog dump都会被记录下来，看着非常不爽（我有强迫症，O(∩_∩)O哈哈~），得想着法子搞掉
mysql 参数： binlog_rows_query_log_events
努力不掉队的DBA
09-10 4586
binlog_rows_query_log_events 参数 随笔 mysql设置这个参数=1，可以在binlog日志中看到语句的原始sql 但是网上很多没说清楚，开了之后也看不到，因为在使用mysqlbinlog查看时，需要加上-vv参数才行，如下 mysqlbinlog -vv mysql-bin.00000x 如果你还想看到binlog日志中那些乱码所代表的的含义可以加上另一个参数 mysqlbinlog --base64-output=decode-rows -vv mysql-bin.00
参数binlog_rows_query_log_events和binlog_row_image 与用 binlog恢复数据
好记忆也需烂博客
05-29 1758
binlog_rows_query_log_events和binlog_row_image 参数作用 (mysql 5.6.2 引入) 当binlog使用row格式时 binlog_rows_query_log_events =1 在row模式下开启该参数,将把sql语句打印到binlog日志里面.默认是0(off); 虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于d...
mysql binlog events_MySQL中binlog参数:binlog_rows_query_log_events
weixin_34129429的博客
01-19 474
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
mysql binlog events_MySQL中binlog参数:binlog_rows_query_log_events-记录具体的SQL【转】
weixin_33795542的博客
01-27 269
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
MySQL中binlog参数:binlog_rows_query_log_events
weixin_30881367的博客
04-24 321
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
Binlog2Hive:MySQL增量数据实时同步到HDFSHive
04-29
项目背景 RDS的数据实时同步到HDFS下，并映射到Hive 原理 通过解析RDS的binlog将RDS的增量数据同步到HDFS下，并映射加载到Hive外部分区表 由于RDS表中的第二个字段都为datetime字段，所以刚才以该字段作为Hive的分区字段 配置文件介绍 doc/creat table.sql：Hive表的建表语句，除了静态表外，其他全部为天级别外部分区表 binglog2Hive_conf.properties:里面为所有全部需要同步到HDFS的表 mysql.properties:Mysql druid连接池配置 程序说明 binlog解析框架： 核心类为BinlogClient 程序主要序列化以下几个事件 TABLE_MAP：包括表名，数据库名 WRITE_ROWS：包含增量的业务记录 程序启动时会先从t_position表中获取上次的同步状态，根据上次的同步状态来确定
mysql之参数binlog_rows_query_log_events
weixin_33938733的博客
10-16 1149
在mysql5.6版本将设置binlog_rows_query_log_events=1，在binlog格式为row的模式下，也可通过SHOW BINLOG EVENTS in 'binlog.000001';查看完整的sql语句 mysql> show variables like 'binlog_rows_query_log_even...
mysql binlog events_mysql之参数binlog_rows_query_log_events
weixin_34959044的博客
01-27 492
在mysql5.6版本将设置binlog_rows_query_log_events=1，在binlog格式为row的模式下，也可通过SHOW BINLOG EVENTS in 'binlog.000001';查看完整的sql语句mysql> show variables like 'binlog_rows_query_log_events';+-----------------------...
binlog_rows_query_log_events
justsomebody126的专栏
12-03 849
show variables like ‘binlog_rows_query_log_events’; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | binlog_rows_query_log_events | OFF | +------------------------------+-------+
mysql的binlog_rows_query_log_events和binlog_row_image
陈小珂
11-19 752
1 . binlog_rows_query_log_events binlog_rows_query_log_events 对binlog_format=row有效，设为true后可以在binary log中记录原始的语句 2 . binlog_row_image 默认为full,在binlog为row格式下, full将记录update前后所有字段的值. minimal时,只记录更改字段的值和...
binlog工具_MySQL5.6新增的参数binlog_row_image到底怎么设置-爱可生
weixin_39862697的博客
12-23 433
背景binlog_row_image这个参数是MySQL5.6新增的参数，默认值是FULL，在5.7版本默认值也是FULL，但今天我看到有客户的 MySQL5.7版本参数模板采用的是MINIMAL而不是FULL，我对这个修改表示疑惑。一般来说，对一个参数默认值作出修改，我们都应该考虑清楚影响范围，所以我准备做一次测试，并得出结论哪个参数值才是最佳设置。术语解释前提：binlog格式必须为row格式...
binlog解析技巧
easydb.net
07-08 2870
MySQL binlog解析技巧
关于MySQL Binlog日志的二三事
最新发布
Sebastien23的博客
07-01 515
🐬数据库版本：MySQL 8.0.30。
用python代码写一个用mysql-replication监控MySQL表变动的功能，表的名字为task_name
05-26
好的，以下是一个使用 mysql-replication 包监控 MySQL 表变动的 Python 代码示例： ```python from mysql_replication import BinLogStreamReader from mysql.connector import connect # 连接 MySQL 数据库 conn = connect(host='localhost', user='root', password='password', database='test') # 创建 BinLogStreamReader 对象 stream = BinLogStreamReader( connection_settings={ "host": "localhost", "port": 3306, "user": "root", "password": "password" }, server_id=100, blocking=True, only_events=["tablemap", "writerows", "updaterows", "deleterows"], only_schemas=["test"], only_tables=["task_name"] ) # 监听 MySQL 表变动 for binlogevent in stream: for row in binlogevent.rows: event = {"schema": binlogevent.schema, "table": binlogevent.table} if binlogevent.event_type == "tablemap": event["column_schemas"] = binlogevent.column_schemas elif binlogevent.event_type == "writerows": event["action"] = "insert" event["rows"] = row["values"] elif binlogevent.event_type == "updaterows": event["action"] = "update" event["rows"] = row["after_values"] event["old_rows"] = row["before_values"] elif binlogevent.event_type == "deleterows": event["action"] = "delete" event["rows"] = row["values"] # 打印表变动信息 print(event) # 关闭 BinLogStreamReader 对象 stream.close() # 关闭数据库连接 conn.close() ``` 在上述代码中，我们首先使用 mysql.connector 包连接到 MySQL 数据库。然后，我们创建一个 BinLogStreamReader 对象，该对象可以监听 MySQL 数据库的二进制日志（binlog），并实时获取表变动信息。在创建 BinLogStreamReader 对象时，我们指定了需要监听的表名为 task_name，只监听 tablemap、writerows、updaterows 和 deleterows 事件，只监听 test 数据库。接着，我们使用 for 循环遍历 BinLogStreamReader 对象，处理每个表变动事件，并打印表变动信息。最后，我们在循环结束后关闭 BinLogStreamReader 对象和数据库连接。 需要注意的是，上述代码仅演示了如何使用 mysql-replication 包监控 MySQL 表变动，实际使用时还需要根据自己的需求进行修改。例如，我们可以将表变动信息保存到文件、发送邮件或调用其他 API 进行处理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值