参数binlog_rows_query_log_events和binlog_row_image 与用 binlog恢复数据

最新推荐文章于 2021-01-27 12:12:00 发布
最新推荐文章于 2021-01-27 12:12:00 发布
阅读量1.8k 收藏 2
点赞数
分类专栏： MYSQL 文章标签： mysql binlog  数据误操作恢复
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/miyatang/article/details/90671154
版权

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,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处.
binlog_row_image=‘minimal’

默认为full,在binlog为row格式下,full将记录update前后所有字段的值,
minimal时,只记录更改字段的值和where字段的值,
noblob时,记录除了blob和text的所有字段的值,如果update的blob或text字段,也只记录该字段更改后的值,更改前的不记录;

row格式下的binlog增长速度太快,对存储空间,主从传输都是一个不小的压力.不管数据是否有改动都会记录.
binlog_row_image=‘minimal’ 时大大减少了binlog记录的数据.
节省了硬盘空间开销,减小I/O,减少了主从传输压力;

有利必有弊。在 binlog_row_image=‘minimal’ 时，因为binlog 中不再记录全字段修改/删除前后的值，如果需要用binlog
来做数据恢复，那就行不通了。

下面示例一下把误删除的数据，怎样使用binlog 日志进行恢复回来。


mysql> show variables like 'binlog_row%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_row_image             | FULL  |
| binlog_rows_query_log_events | ON    |
+------------------------------+-------+
2 rows in set (0.00 sec)


#添加表数据
mysql> create tbl1(id int not null auto_increment,
    -> name varchar(10),
    -> create_time timestamp  default CURRENT_TIMESTAMP,
    -> update_time timestamp  default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> primary key (id));
mysql> insert into tbl1(name) values('test1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tbl1(name) values('test2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl1(name) values('test3');
Query OK, 1 row affected (0.00 sec)

mysql> delete from tbl1;
Query OK, 3 rows affected (0.09 sec)

我们来查看一下binlog 日志


[root@m10 mysql]# mysqlbinlog mysql-bin.000011 -vv --base64-output=DECODE-ROWS  >/tmp/mysql-bin.11.log


# at 604
#190529  9:43:16 server id 101  end_log_pos 666 CRC32 0x29a3aebd        Rows_query
# insert into tbl1(name) values('test1')	#只有在binlog_rows_query_log_events =1  时才会打印此内容
# at 666
#190529  9:43:16 server id 101  end_log_pos 720 CRC32 0x7b1a3e0e        Table_map: `test`.`tbl1` mapped to number 70
# at 720
#190529  9:43:16 server id 101  end_log_pos 774 CRC32 0x8f165079        Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`tbl1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test1' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 805
#190529  9:51:37 server id 101  end_log_pos 853 CRC32 0xeb1fde0c        GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '450009fd-3332-11e8-ab5d-005056257272:3'/*!*/;
# at 853
#190529  9:51:37 server id 101  end_log_pos 925 CRC32 0x4b540350        Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1559094697/*!*/;
BEGIN
/*!*/;
# at 925
#190529  9:51:37 server id 101  end_log_pos 987 CRC32 0xe0c0cdd9        Rows_query
# insert into tbl1(name) values('test2')
# at 987
#190529  9:51:37 server id 101  end_log_pos 1041 CRC32 0x7e7cb17f       Table_map: `test`.`tbl1` mapped to number 70
# at 1041
#190529  9:51:37 server id 101  end_log_pos 1095 CRC32 0x107e7821       Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`tbl1`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test2' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 1095
#190529  9:51:37 server id 101  end_log_pos 1126 CRC32 0x27ccc8e1       Xid = 33
COMMIT/*!*/;
# at 1126
#190529  9:51:39 server id 101  end_log_pos 1174 CRC32 0x02a87edc       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '450009fd-3332-11e8-ab5d-005056257272:4'/*!*/;
# at 1174
#190529  9:51:39 server id 101  end_log_pos 1246 CRC32 0x0e82536b       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1559094699/*!*/;
BEGIN
/*!*/;
# at 1246
#190529  9:51:39 server id 101  end_log_pos 1308 CRC32 0xa65a6deb       Rows_query
# insert into tbl1(name) values('test3')
# at 1308
#190529  9:51:39 server id 101  end_log_pos 1362 CRC32 0x31fe1b9c       Table_map: `test`.`tbl1` mapped to number 70
# at 1362
#190529  9:51:39 server id 101  end_log_pos 1416 CRC32 0x26ee5087       Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`tbl1`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test3' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 1416
#190529  9:51:39 server id 101  end_log_pos 1447 CRC32 0xf608d960       Xid = 34
COMMIT/*!*/;
# at 1447
#190529  9:51:49 server id 101  end_log_pos 1495 CRC32 0x4a4d95a7       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '450009fd-3332-11e8-ab5d-005056257272:5'/*!*/;
# at 1495
#190529  9:51:49 server id 101  end_log_pos 1567 CRC32 0x3d5788fb       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1559094709/*!*/;
BEGIN
/*!*/;
# at 1567
#190529  9:51:49 server id 101  end_log_pos 1607 CRC32 0x844332be       Rows_query
# delete from tbl1
# at 1607
#190529  9:51:49 server id 101  end_log_pos 1661 CRC32 0x03342d4e       Table_map: `test`.`tbl1` mapped to number 70
# at 1661
#190529  9:51:49 server id 101  end_log_pos 1753 CRC32 0x3fcb94e0       Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test1' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test2' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test3' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 1753
#190529  9:51:49 server id 101  end_log_pos 1784 CRC32 0xd658ea43       Xid = 35
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

把删除命令的 binlog 读出来后面进行处理

[root@m10 mysql]# cat /tmp/mysql-bin.11.log |sed -n '/### DELETE FROM `test`.`tbl1`/,/COMMIT/p' >/tmp/mysql-bin.del.log

### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test1' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094196 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test2' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094697 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tbl1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test3' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @4=1559094699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 1753
#190529  9:51:49 server id 101  end_log_pos 1784 CRC32 0xd658ea43 	Xid = 35
COMMIT/*!*/;

命令如下：

 cat /tmp/mysql-bin.del.log |sed -n '/###/p'| sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;'
 |sed -r 's/(@4.*),/\1;/g'|sed 's/@[1-2]=//g' |sed 's/@[3-4]=/=/g' |sed 's/=[0-9]\+/from_unixtime(&)/g' >/tmp/insert.sql

#经过多次替换：

#insert.sql 内容如下：


INSERT INTO `test`.`tbl1`
SELECT
  1 ,
  'test1' ,
  from_unixtime(=1559094196) ,
  from_unixtime(=1559094196) ;
INSERT INTO `test`.`tbl1`
SELECT
  2 ,
  'test2' ,
  from_unixtime(=1559094697) ,
  from_unixtime(=1559094697) ;
INSERT INTO `test`.`tbl1`
SELECT
  3 ,
  'test3' ,
  from_unixtime(=1559094699) ,
  from_unixtime(=1559094699) ;

后面可以用 insert.sql 恢复 数据。

source /tmp/insert.sql

mysql> mysql> source /tmp/insert.sql;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from tbl1;
+----+-------+---------------------+---------------------+
| id | name  | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | test1 | 2019-05-29 14:52:36 | 2019-05-29 14:52:36 |
|  2 | test2 | 2019-05-29 14:52:36 | 2019-05-29 14:52:36 |
|  3 | test3 | 2019-05-29 14:52:36 | 2019-05-29 14:52:36 |
+----+-------+---------------------+---------------------+
3 rows in set (0.01 sec)
边城cn
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql binlog events_MySQL中binlog参数:binlog_rows_query_log_events
weixin_34129429的博客
01-19 488
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
阿里巴巴Canal常见问题：重复解析Filter失效消费落后
小熊的博客
08-28 606
阿里巴巴Canal常见问题：重复解析/Filter失效/消费落后 前言 Canal是阿里巴巴开源的数据库Binlog日志解析框架，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费。 在之前我写的文章阿里开源MySQL中间件Canal快速入门中，我已经介绍了Canal的基本原理和基础使用。 在部署到生产环境的过程中，自己作为一个菜鸟，又踩了一些坑，期间做了记录和总结，并再解决后分析了下原因，便有了此文。 本文重点内容 Canal常见的三大问题原因分析及解决方案 Binlog解析错误
MySQL中binlog参数:binlog_rows_query_log_events
weixin_30881367的博客
04-24 332
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
MySQL binlog_rows_query_log_events
weixin_33898876的博客
12-23 212
当binlog_format=statement的时候进制日志只记录的是SQL语句，当binlog_fromat=row的时候记录的是event，如果想要在row模式的情况下 也记录SQL语句；binlog_rows_query_events可以完成这个需求。 [mysqld] bingo_rows_query_log_events=1 注意这个参数支持在线设置的 set @@global....
mysql 参数： binlog_rows_query_log_events
努力不掉队的DBA
09-10 4691
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很有用的2个参数binlog_rows_query_log_events和binlog_row_image
weixin_42575593的博客
04-30 2409
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,但不会执行这个...
MySQL：Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEM
01-19
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 ...
binlog文件解析相关.docx
08-14
MySQL的binlog（Binary Log）是数据库的一种日志记录方式，用于记录所有的数据更改操作，以实现数据的备份、恢复和复制等功能。本篇文章将深入探讨如何查看和解析MySQL的binlog文件，特别是针对两种不同的binlog_...
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_...
MySQL binlog中的事件类型详解
09-10
2. ROWS_EVENT：在ROW格式的binlog中，DML操作对应ROWS_EVENT，它详细记录了每一行的变化，包括插入、更新和删除的具体行数据。ROWS_EVENT分为多个版本，例如V1和V2，以适应不同的MySQL版本。 3. FORMAT_...
mySQL数据库性能优化pdf.pdf
07-17
针对面试经常问的sql优化问题,进行mySQL数据库性能优化
Binlog2Hive:MySQL增量数据实时同步到HDFSHive
04-29
项目背景RDS的数据实时同步到HDFS下，并映射到Hive原理通过解析RDS的binlog将RDS的增量数据同步到HDFS下，并映射加载到Hive外部分区表由于RDS表中的第二个字段都为datetime字段，所以刚才以该字段作为Hive的分区字段...
binlog_rows_query_log_events
cszt85637的博客
04-04 429
The binlog_rows_query_log_events system variable affects row-based logging only. When enabled, it causes a MySQL 5...
mysql binlog events_mysql之参数binlog_rows_query_log_events
weixin_34959044的博客
01-27 499
在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';+-----------------------...
MySQL binlog_rows_query_log_events在线设置无效
T99887766的博客
11-13 357
binlog_rows_query_log_events 对binlog_format=row有效，设为true后可以在binary log中记录原始的语句 官方文档显示binlog_rows_query_log_events是动态参数，可以在线设置生效，但是测试中发现只有重启才可以生效 版本信息 22:25:13[test](;)> select version(); ...
mysql binlog events_MySQL中binlog参数:binlog_rows_query_log_events-记录具体的SQL【转】
weixin_33795542的博客
01-27 281
在使用RBR也就是行格式的时候，去解析binlog，需要逆向才能分析出对应的原始SQL是什么，而且，里面对应的是每一条具体行变更的内容。当然，你可以开启general log,但如果我们需要的只是记录对应的行变更，而不需要记录这些select普通的查询，因为general log 会将线上所有的操作都记录下来，这种功能适合于我们审核统计，但是不适合我们对事务进行判断，故此，我们使用binlog_r...
binlog_rows_query_log_events row模式下记录sql语句
cimeng0072的博客
04-18 300
mysql 5.7后通过设置binlog_rows_query_log_events为on，可以在binlog_format=row中记录执行的SQL语句。 mysql> use mytest; Reading t...
mysql binlog恢复数据
最新发布
05-17
MySQL的二进制日志（binlog）记录了对数据库的所有更改操作，包括增删改等操作。如果你误删了某些数据，可以使用binlog来进行恢复。 以下是使用binlog恢复数据的步骤： 1. 确认binlog是否开启，在MySQL配置文件中确认是否有以下配置： ``` [mysqld] log-bin=mysql-bin ``` 如果没有，则需要在配置文件中添加上述配置，并重新启动MySQL服务。 2. 查看binlog日志文件列表 可以使用以下命令查看binlog日志文件列表： ``` mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v --start-datetime="2022-01-01 00:00:00" --stop-datetime="2022-01-01 23:59:59" mysql-bin.000001 > /tmp/mysql_binlog.sql ``` 上述命令将把2022年1月1日这一天的binlog日志文件转换成文本格式，并保存到/tmp/mysql_binlog.sql文件中。 3. 找到误删的SQL语句 在/binlog.sql文件中查找误删的SQL语句，可以使用grep命令查找。 例如，查找包含“DELETE FROM users WHERE id=100”的SQL语句： ``` grep "DELETE FROM users WHERE id=100" /tmp/mysql_binlog.sql ``` 4. 恢复数据 找到误删的SQL语句后，可以使用mysql客户端连接到数据库，并执行该语句来恢复数据。 例如，执行以下命令来恢复数据： ``` mysql -u root -p mysql> use mydatabase; mysql> DELETE FROM users WHERE id=100; ``` 以上就是使用binlog恢复数据的步骤。需要注意的是，在执行恢复操作前，最好先备份一下数据库，以防止误操作导致数据丢失。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值