mysql logbin row解码_MySQL在ROW模式下通过binlog提取SQL语句

#!/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{

&merror("please input binlog file");

}

$opt{'h'} and $host =