#!/usr/lib/perl -w
use strict;
use warnings;
use Class::Struct;
use Getopt::Long qw(:config no_ignore_case); # GetOption
# register handler system signals
use sigtrap 'handler', \&sig_int, 'normal-signals';
# catch signal
sub sig_int(){
my ($signals) = @_;
print STDERR "# Caught SIG$signals.\n";
exit 1;
}
my %opt;
my $srcfile;
my $host = '127.0.0.1';
my $port = 3306;
my ($user,$pwd);
my ($MYSQL, $MYSQLBINLOG, $ROLLBACK_DML);
my $outfile = '/dev/null';
my (%do_dbs,%do_tbs);
# tbname=>tbcol, tbcol: @n=>colname,type
my %tbcol_pos;
my $SPLITER_COL = ',';
my $SQLTYPE_IST = 'INSERT';
my $SQLTYPE_UPD = 'UPDATE';
my $SQLTYPE_DEL = 'DELETE';
my $SQLAREA_WHERE = 'WHERE';
my $SQLAREA_SET = 'SET';
my $PRE_FUNCT = '========================== ';
# =========================================================
# 基于row模式的binlog,生成DML(insert/update/delete)的rollback语句
# 通过mysqlbinlog -v 解析binlog生成可读的sql文件
# 提取需要处理的有效sql
# "### "开头的行.如果输入的start-position位于某个event group中间,则会导致"无法识别event"错误
#
# 将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行
# INSERT: INSERT INTO => DELETE FROM, SET => WHERE
# UPDATE: WHERE => SET, SET => WHERE
# DELETE: DELETE FROM => INSERT INTO, WHERE => SET
# 用列名替换位置@{1,2,3}
# 通过desc table获得列顺序及对应的列名
# 特殊列类型value做特别处理
# 逆序
#
# 注意:
# 表结构与现在的表结构必须相同[谨记]
# 由于row模式是幂等的,并且恢复是一次性,所以只提取sql,不提取BEGIN/COMMIT
# 只能对INSERT/UPDATE/DELETE进行处理
# ========================================================
sub main{
# get input option
&get_options();
#
&init_tbcol();
#
&do_binlog_rollback();
}
&main();
# ----------------------------------------------------------------------------------------
# Func : get options and set option flag
# ----------------------------------------------------------------------------------------
sub get_options{
#Get options info
GetOptions(\%opt,
'help', # OUT : print help info
'f|srcfile=s', # IN : binlog file
'o|outfile=s', # out : output sql file
'h|host=s', # IN : host
'u|user=s', # IN : user
'p|password=s', # IN : password
'P|port=i', # IN : port
'start-datetime=s', # IN : start datetime
'stop-datetime=s', # IN : stop datetime
'start-position=i', # IN : start position
'stop-position=i', # IN : stop position
'd|database=s', # IN : database, split comma
'T|table=s', # IN : table, split comma
'i|ignore', # IN : ignore binlog check ddl and so on
'debug', # IN : print debug information
) or print_usage();
if (!scalar(%opt)) {
&print_usage();
}
# Handle for options
if ($opt{'f'}){
$srcfile = $opt{'f'};
}else{