SHELL脚本提取binlog delete语句 供恢复数据

binlog日志 如下：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 120
#160111 16:37:29 server id 333336  end_log_pos 195 CRC32 0x5759daa6     Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1452587849/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#160111 16:37:29 server id 333336  end_log_pos 250 CRC32 0x6c642807     Table_map: `gaoquan`.`t1` mapped to number 70
# at 250
#160111 16:37:29 server id 333336  end_log_pos 348 CRC32 0xde1ef5e7     Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=1
###   @2='gaoquan'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=2
###   @2='son'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=3
###   @2='gaoquan1'
###   @3=32
###   @4='male'
# at 348
#160111 16:37:29 server id 333336  end_log_pos 379 CRC32 0x05ee85fb     Xid = 52
COMMIT/*!*/;
# at 379
#160111 16:37:32 server id 333336  end_log_pos 426 CRC32 0x8649f4ea     Rotate to mysql-bin.000006  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 120
#160112 16:37:29 server id 333336  end_log_pos 195 CRC32 0x5759daa6     Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1452587849/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#160112 16:37:29 server id 333336  end_log_pos 250 CRC32 0x6c642807     Table_map: `gaoquan`.`t1` mapped to number 70
# at 250
#160112 16:37:29 server id 333336  end_log_pos 348 CRC32 0xde1ef5e7     Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=1
###   @2='gaoquan'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=2
###   @2='son'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=3
###   @2='gaoquan1'
###   @3=32
###   @4='male'
# at 348
#160112 16:37:29 server id 333336  end_log_pos 379 CRC32 0x05ee85fb     Xid = 52
COMMIT/*!*/;
# at 379
#160112 16:37:32 server id 333336  end_log_pos 426 CRC32 0x8649f4ea     Rotate to mysql-bin.000006  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 120
#160113 16:37:29 server id 333336  end_log_pos 195 CRC32 0x5759daa6     Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1452587849/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#160113 16:37:29 server id 333336  end_log_pos 250 CRC32 0x6c642807     Table_map: `gaoquan`.`t1` mapped to number 70
# at 250
#160113 16:37:29 server id 333336  end_log_pos 348 CRC32 0xde1ef5e7     Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=1
###   @2='gaoquan'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=2
###   @2='son'
###   @3=22
###   @4='male'
### DELETE FROM `gaoquan`.`t1`
### WHERE
###   @1=3
###   @2='gaoquan1'
###   @3=32
###   @4='male'
# at 348
#160113 16:37:29 server id 333336  end_log_pos 379 CRC32 0x05ee85fb     Xid = 52
COMMIT/*!*/;
# at 379
#160113 16:37:32 server id 333336  end_log_pos 426 CRC32 0x8649f4ea     Rotate to mysql-bin.000006  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 
要求：
把binlog日志 中2016年1月12日的delete语句提取出来 供恢复数据
 
脚本如下：
#!/bin/bash

#bl表列数 #语句yj
bl=4
yj=DELETE
zs1=`awk '/#160112/,/#160113/ {print $0}' /tmp/delete|awk '"/$yj/",/# at/ {print $0}'|grep ^###|grep "@"|cut -d"=" -f2`
zs2=`echo $zs1|awk '{print NF}'`
zt=`echo "$zs2/$bl"|bc`
hs=0

databa=gz
tab=wubx
ztt=$(($zt+0))
ii=0

a1=1
a2=2
a3=3
a4=4
  while [[ $ii -lt $ztt ]];do
l1=`echo $zs1|awk '{print $'"$a1"'}'`
l2=`echo $zs1|awk '{print $'"$a2"'}'`
l3=`echo $zs1|awk '{print $'"$a3"'}'`
l4=`echo $zs1|awk '{print $'"$a4"'}'`
echo "use $databa;insert into $tab values($l1,$l2,$l3,$l4)" >>hf
  
a1=$(($a1+$bl))
a2=$(($a2+$bl))
a3=$(($a3+$bl))
a4=$(($a4+$bl))

ii=$(($ii+1));
  done
 
运行结果：
oracle@gmcc-oracle:~> sh mysql.sh && cat hf
use gz;insert into wubx values(1,'gaoquan',22,'male')
use gz;insert into wubx values(2,'son',22,'male')
use gz;insert into wubx values(3,'gaoquan1',32,'male')

 

总结：初步写一个提取delete语句脚本，还有需要改进的地方，欢迎提出意见，共同探讨。

 #@_@ 欢迎交流！联系人 广西宾阳 阿桂 qq149951292