mysql binlog 查找

2010-08-25 09:27:21 157 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【binlog】【logminer】如何确定mysql binlog及oracle logminer scn 如何确定mysql的binlog position和oracle的logminer各类scn，以便自定义位置开启cdc同步阅读详情

接着昨天的mysql delete还原，我现在大致的思路是，从还原一个最近备份的表，然后再从日志文件里面找，可是如果日志文件非常大的情况下找起来非常的麻烦，我想能不能通过linux sed 或者grep 找出和表相关的操作， grep 'xxx' 1.sql 值能列出和xxx相关的操作，但是列不出时间等等。下面是日志文件的部分，我现在想得到和xxx相关的东西包括时间等，例如：其他的不想要，这个正则表达式怎么写？？
BEGIN
/*!*/;
# at 170
#100824 11:26:27 server id 1 end_log_pos 277 Query thread_id=1 exec_time=0 error_code=0
use test_dbo/*!*/;
SET TIMESTAMP=1282620387/*!*/;
update xxx set ChineseName='abc'
/*!*/;
# at 277
#100824 11:26:27 server id 1 end_log_pos 304 Xid = 38
COMMIT/*!*/;
# at 304
#100824 11:26:27 server id 1 end_log_pos 376 Query thread_id=3 exec_time=658 error_code=0
SET TIMESTAMP=1282620387/*!*/;
BEGIN
/*!*/;
# at 376
#100824 11:26:27 server id 1 end_log_pos 483 Query thread_id=3 exec_time=658 error_code=0
SET TIMESTAMP=1282620387/*!*/;
update xxx set ChineseName='abc'
/*!*/;
# at 483
#100824 11:26:27 server id 1 end_log_pos 510 Xid = 54
COMMIT/*!*/;

所有的日志文件
BEGIN
/*!*/;
# at 170
#100824 11:26:27 server id 1 end_log_pos 277 Query thread_id=1 exec_time=0 error_code=0
use test_dbo/*!*/;
SET TIMESTAMP=1282620387/*!*/;
update xxx set ChineseName='abc'
/*!*/;
# at 277
#100824 11:26:27 server id 1 end_log_pos 304 Xid = 38
COMMIT/*!*/;
# at 304
#100824 11:26:27 server id 1 end_log_pos 376 Query thread_id=3 exec_time=658 error_code=0
SET TIMESTAMP=1282620387/*!*/;
BEGIN
/*!*/;
# at 376
#100824 11:26:27 server id 1 end_log_pos 483 Query thread_id=3 exec_time=658 error_code=0
SET TIMESTAMP=1282620387/*!*/;
update xxx set ChineseName='abc'
/*!*/;
# at 483
#100824 11:26:27 server id 1 end_log_pos 510 Xid = 54
COMMIT/*!*/;
# at 510
#100824 11:40:13 server id 1 end_log_pos 582 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1282621213/*!*/;
BEGIN
/*!*/;
# at 2353
#100825 9:13:49 server id 1 end_log_pos 2443 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1282698829/*!*/;
delete from ddd
/*!*/;
# at 2443
#100825 9:13:49 server id 1 end_log_pos 2470 Xid = 202
COMMIT/*!*/;
# at 2470
#100825 9:13:53 server id 1 end_log_pos 2542 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1282698833/*!*/;
BEGIN
/*!*/;
# at 2542
#100825 9:13:53 server id 1 end_log_pos 2639 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1282698833/*!*/;
delete from zzz
/*!*/;
# at 2639
#100825 9:13:53 server id 1 end_log_pos 2666 Xid = 206
COMMIT/*!*/;
# at 2666
#100825 9:13:54 server id 1 end_log_pos 2738 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1282698834/*!*/;
BEGIN
/*!*/;
# at 2738
#100825 9:13:54 server id 1 end_log_pos 2835 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1282698834/*!*/;
delete from zzz
/*!*/;
# at 2835
#100825 9:13:54 server id 1 end_log_pos 2862 Xid = 207
COMMIT/*!*/;
# at 2862
#100825 9:15:34 server id 1 end_log_pos 2954 Query thread_id=8 exec_time=1 error_code=0
SET TIMESTAMP=1282698934/*!*/;
DROP TABLE `qqq`
/*!*/;
DELIMITER ;

...全文