四川盐亭网站制作公司

MySQL在ROW模式下如何通过binlog提取SQL语句

小编给大家分享一下MySQL在ROW模式下如何通过binlog提取SQL语句，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

成都创新互联公司专注于达州企业网站建设,响应式网站设计,商城网站定制开发。达州网站建设公司,为达州等地区提供建站服务。全流程按需设计，专业设计，全程项目跟踪，成都创新互联公司专业和态度为您提供的服务

Linux
基于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进行处理

mysql> select * from yoon;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        2 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        3 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        4 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        5 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        6 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        7 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        8 | HANK       | YOON      | 2006-02-15 04:34:33 |
|        9 | HANK       | YOON      | 2006-02-15 04:34:33 |
|       10 | HANK       | YOON      | 2006-02-15 04:34:33 |
|       11 | HANK       | YOON      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
11 rows in set (0.00 sec)


mysql> delete from yoon;
Query OK, 11 rows affected (1.03 sec)


mysql> select * from yoon;
Empty set (0.00 sec)

命令之间的空格一定要注意，否则就会无法提取SQL语句：
[root@hank-yoon data]# perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/export/data/mysql/data/yoon.sql' -u 'root' -p 'yoon'
Warning: Using a password on the command line interface can be insecure.
[root@hank-yoon data]# ls
auto.cnf            hank     ibdata2      ib_logfile1  modify.pl  mysql-bin.000001  performance_schema  test  yoon.sql
binlog-rollback.pl  ibdata1  ib_logfile0  ib_logfile2  mysql      mysql-bin.index   sakila              yoon
[root@hank-yoon data]# cat yoon.sql 
INSERT INTO `yoon`.`yoon` SET `actor_id`=11, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=10, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=9, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=8, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=7, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=6, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=5, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=4, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=3, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=2, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
INSERT INTO `yoon`.`yoon` SET `actor_id`=1, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);

mysql> INSERT INTO `yoon`.`yoon` SET `actor_id`=11, `first_name`='HANK', `last_name`='YOON', `last_update`=from_unixtime(1139949273);
Query OK, 1 row affected (0.01 sec)


mysql> select * from yoon;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       11 | HANK       | YOON      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+


点击(此处)折叠或打开

  1. #!/usr/lib/perl -w

  3. use strict;

  4. use warnings;

  6. use Class::Struct;

  7. use Getopt::Long qw(:config no_ignore_case);                    # GetOption

  8. # register handler system signals

  9. use sigtrap 'handler', \&sig_int, 'normal-signals';

  11. # catch signal

  12. sub sig_int(){

  13.     my ($signals) = @_;

  14.     print STDERR "# Caught SIG$signals.\n";

  15.     exit 1;

  16. }

  18. my %opt;

  19. my $srcfile;

  20. my $host = '127.0.0.1';

  21. my $port = 3306;

  22. my ($user,$pwd);

  23. my ($MYSQL, $MYSQLBINLOG, $ROLLBACK_DML);

  24. my $outfile = '/dev/null';

  25. my (%do_dbs,%do_tbs);

  27. # tbname=>tbcol, tbcol: @n=>colname,type

  28. my %tbcol_pos;

  30. my $SPLITER_COL = ',';

  31. my $SQLTYPE_IST = 'INSERT';

  32. my $SQLTYPE_UPD = 'UPDATE';

  33. my $SQLTYPE_DEL = 'DELETE';

  34. my $SQLAREA_WHERE = 'WHERE';

  35. my $SQLAREA_SET = 'SET';

  37. my $PRE_FUNCT = '========================== ';

  39. # =========================================================

  40. # 基于row模式的binlog，生成DML(insert/update/delete)的rollback语句

  41. # 通过mysqlbinlog -v 解析binlog生成可读的sql文件

  42. # 提取需要处理的有效sql

  43. #     "### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误

  44. #

  45. # 将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行

  46. #     INSERT: INSERT INTO => DELETE FROM, SET => WHERE

  47. #     UPDATE: WHERE => SET, SET => WHERE

  48. #     DELETE: DELETE FROM => INSERT INTO, WHERE => SET

  49. # 用列名替换位置@{1,2,3}

  50. #     通过desc table获得列顺序及对应的列名

  51. #     特殊列类型value做特别处理

  52. # 逆序

  53. #

  54. # 注意:

  55. #     表结构与现在的表结构必须相同[谨记]

  56. #     由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT

  57. #     只能对INSERT/UPDATE/DELETE进行处理

  58. # ========================================================

  59. sub main{

  61.     # get input option

  62.     &get_options();

  64.     #

  65.     &init_tbcol();

  67.     #

  68.     &do_binlog_rollback();

  69. }

  71. &main();

  74. # ----------------------------------------------------------------------------------------

  75. # Func : get options and set option flag

  76. # ----------------------------------------------------------------------------------------

  77. sub get_options{

  78.     #Get options info

  79.     GetOptions(\%opt,

  80.         'help',                    # OUT : print help info

  81.         'f|srcfile=s',            # IN : binlog file

  82.         'o|outfile=s',            # out : output sql file

  83.         'h|host=s',                # IN : host

  84.         'u|user=s', # IN : user

  85.         'p|password=s', # IN : password

  86.         'P|port=i',                # IN : port

  87.         'start-datetime=s',        # IN : start datetime

  88.         'stop-datetime=s',        # IN : stop datetime

  89.         'start-position=i',        # IN : start position

  90.         'stop-position=i',        # IN : stop position

  91.         'd|database=s',            # IN : database, split comma

  92.         'T|table=s',            # IN : table, split comma

  93.         'i|ignore',                # IN : ignore binlog check ddl and so on

  94.         'debug',                # IN : print debug information

  95.      ) or print_usage();

  97.     if (!scalar(%opt)) {

  98.         &print_usage();

  99.     }

  101.     # Handle for options

  102.     if ($opt{'f'}){

  103.         $srcfile = $opt{'f'};

  104.     }else{

  105.         &merror("please input binlog file");

  106.     }

  108.     $opt{'h'} and $host = $opt{'h'};

  109.     $opt{'u'} and $user = $opt{'u'};

  110.     $opt{'p'} and $pwd = $opt{'p'};

  111.     $opt{'P'} and $port = $opt{'P'};

  112.     if ($opt{'o'}) {

  113.         $outfile = $opt{'o'};

  114.         # 清空 outfile

  115.         `echo '' > $outfile`;

  116.     }

  118.     #

  119.     $MYSQL = qq{mysql -h$host -u$user -p'$pwd' -P$port};

  120.     &mdebug("get_options::MYSQL\n\t$MYSQL");

  122.     # 提取binlog,不需要显示列定义信息，用-v，而不用-vv

  123.     $MYSQLBINLOG = qq{mysqlbinlog -v};

  124.     $MYSQLBINLOG .= " --start-position=".$opt{'start-position'} if $opt{'start-position'};

  125.     $MYSQLBINLOG .= " --stop-position=".$opt{'stop-position'} if $opt{'stop-postion'};

  126.     $MYSQLBINLOG .= " --start-datetime='".$opt{'start-datetime'}."'" if $opt{'start-datetime'};

  127.     $MYSQLBINLOG .= " --stop-datetime='$opt{'stop-datetime'}'" if $opt{'stop-datetime'};

  128.     $MYSQLBINLOG .= " $srcfile";

  129.     &mdebug("get_options::MYSQLBINLOG\n\t$MYSQLBINLOG");

  131.     # 检查binlog中是否含有 ddl sql: CREATE|ALTER|DROP|RENAME

  132.     &check_binlog() unless ($opt{'i'});

  134.     # 不使用mysqlbinlog过滤，USE dbname;方式可能会漏掉某些sql，所以不在mysqlbinlog过滤

  135.     # 指定数据库

  136.     if ($opt{'d'}){

  137.         my @dbs = split(/,/,$opt{'d'});

  138.         foreach my $db (@dbs){

  139.             $do_dbs{$db}=1;

  140.         }

  141.     }

  143.     # 指定表

  144.     if ($opt{'T'}){

  145.         my @tbs = split(/,/,$opt{'T'});

  146.         foreach my $tb (@tbs){

  147.             $do_tbs{$tb}=1;

  148.         }

  149.     }

  151.     # 提取有效DML SQL

  152.     $ROLLBACK_DML = $MYSQLBINLOG." | grep '^### '";

  153.     # 去掉注释: '### ' -> ''

  154.     # 删除首尾空格

  155.     $ROLLBACK_DML .= " | sed 's/###\\s*//g;s/\\s*\$//g'";

  156.     &mdebug("rollback dml\n\t$ROLLBACK_DML");

  157.     

  158.     # 检查内容是否为空

  159.     my $cmd = "$ROLLBACK_DML | wc -l";

  160.     &mdebug("check contain dml sql\n\t$cmd");

  161.     my $size = `$cmd`;

  162.     chomp($size);

  163.     unless ($size >0){

  164.         &merror("binlog DML is empty:$ROLLBACK_DML");

  165.     };

  167. }    

  170. # ----------------------------------------------------------------------------------------

  171. # Func : check binlog contain DDL

  172. # ----------------------------------------------------------------------------------------

  173. sub check_binlog{

  174.     &mdebug("$PRE_FUNCT check_binlog");

  175.     my $cmd = "$MYSQLBINLOG ";

  176.     $cmd .= " | grep -E -i '^(CREATE|ALTER|DROP|RENAME)' ";

  177.     &mdebug("check binlog has DDL cmd\n\t$cmd");

  178.     my $ddlcnt = `$cmd`;

  179.     chomp($ddlcnt);

  181.     my $ddlnum = `$cmd | wc -l`;

  182.     chomp($ddlnum);

  183.     my $res = 0;

  184.     if ($ddlnum>0){

  185.         # 在ddl sql前面加上前缀

  186.         $ddlcnt = `echo '$ddlcnt' | sed 's/^//g'`;

  187.         &merror("binlog contain $ddlnum DDL:$MYSQLBINLOG. ddl sql:\n$ddlcnt");

  188.     }

  190.     return $res;

  191. }

  194. # ----------------------------------------------------------------------------------------

  195. # Func : init all table column order

  196. #        if input --database --table params, only get set table column order

  197. # ----------------------------------------------------------------------------------------

  198. sub init_tbcol{

  199.     &mdebug("$PRE_FUNCT init_tbcol");

  200.     # 提取DML语句

  201.     my $cmd .= "$ROLLBACK_DML | grep -E '^(INSERT|UPDATE|DELETE)'";

  202.     # 提取表名，并去重

  203.     #$cmd .= " | awk '{if (\$1 ~ \"^UPDATE\") {print \$2}else {print \$3}}' | uniq ";

  204.     $cmd .= " | awk '{if (\$1 ~ \"^UPDATE\") {print \$2}else {print \$3}}' | sort | uniq ";

  205.     &mdebug("get table name cmd\n\t$cmd");

  206.     open ALLTABLE, "$cmd | " or die "can't open file:$cmd\n";

  208.     while (my $tbname = ){

  209.         chomp($tbname);

  210.         #if (exists $tbcol_pos{$tbname}){

  211.         #    next;

  212.         #}

  213.         &init_one_tbcol($tbname) unless (&ignore_tb($tbname));

  214.         

  215.     }

  216.     close ALLTABLE or die "can't close file:$cmd\n";

  218.     # init tb col

  219.     foreach my $tb (keys %tbcol_pos){

  220.         &mdebug("tbname->$tb");

  221.         my %colpos = %{$tbcol_pos{$tb}};

  222.         foreach my $pos (keys %colpos){

  223.             my $col = $colpos{$pos};

  224.             my ($cname,$ctype) = split(/$SPLITER_COL/, $col);

  225.             &mdebug("\tpos->$pos,cname->$cname,ctype->$ctype");

  226.         }

  227.     }

  228. };

  231. # ----------------------------------------------------------------------------------------

  232. # Func : init one table column order

  233. # ----------------------------------------------------------------------------------------

  234. sub init_one_tbcol{

  235.     my $tbname = shift;

  236.     &mdebug("$PRE_FUNCT init_one_tbcol");

  237.     # 获取表结构及列顺序

  238.     my $cmd = $MYSQL." --skip-column-names --silent -e 'desc $tbname'";

  239.     # 提取列名，并拼接

  240.     $cmd .= " | awk -F\'\\t\' \'{print NR\"$SPLITER_COL`\"\$1\"`$SPLITER_COL\"\$2}'";

  241.     &mdebug("get table column infor cmd\n\t$cmd");

  242.     open TBCOL,"$cmd | " or die "can't open desc $tbname;";

  244.     my %colpos;

  245.     while (my $line = ){

  246.         chomp($line);

  247.         my ($pos,$col,$coltype) = split(/$SPLITER_COL/,$line);

  248.         &mdebug("linesss=$line\n\t\tpos=$pos\n\t\tcol=$col\n\t\ttype=$coltype");

  249.         $colpos{$pos} = $col.$SPLITER_COL.$coltype;

  250.     }

  251.     close TBCOL or die "can't colse desc $tbname";

  253.     $tbcol_pos{$tbname} = \%colpos;

  254. }

  257. # ----------------------------------------------------------------------------------------

  258. # Func : rollback sql:    INSERT/UPDATE/DELETE

  259. # ----------------------------------------------------------------------------------------

  260. sub do_binlog_rollback{

  261.     my $binlogfile = "$ROLLBACK_DML ";

  262.     &mdebug("$PRE_FUNCT do_binlog_rollback");

  264.     # INSERT|UPDATE|DELETE

  265.     my $sqltype;

  266.     # WHERE|SET

  267.     my $sqlarea;

  268.     

  269.     my ($tbname, $sqlstr) = ('', '');

  270.     my ($notignore, $isareabegin) = (0,0);

  272.     # output sql file

  273.     open SQLFILE, ">> $outfile" or die "Can't open sql file:$outfile";

  275.     # binlog file

  276.     open BINLOG, "$binlogfile |" or die "Can't open file: $binlogfile";

  277.     while (my $line = ){

  278.         chomp($line);

  279.         if ($line =~ /^(INSERT|UPDATE|DELETE)/){

  280.             # export sql

  281.             if ($sqlstr ne ''){

  282.                 $sqlstr .= ";\n";

  283.                 print SQLFILE $sqlstr;

  284.                 &mdebug("export sql\n\t".$sqlstr);

  285.                 $sqlstr = '';

  286.             }

  288.             if ($line =~ /^INSERT/){

  289.                 $sqltype = $SQLTYPE_IST;

  290.                 $tbname = `echo '$line' | awk '{print \$3}'`;

  291.                 chomp($tbname);

  292.                 $sqlstr = qq{DELETE FROM $tbname};

  293.             }elsif ($line =~ /^UPDATE/){

  294.                 $sqltype = $SQLTYPE_UPD;

  295.                 $tbname = `echo '$line' | awk '{print \$2}'`;

  296.                 chomp($tbname);

  297.                 $sqlstr = qq{UPDATE $tbname};

  298.             }elsif ($line =~ /^DELETE/){

  299.                 $sqltype = $SQLTYPE_DEL;    

  300.                 $tbname = `echo '$line' | awk '{print \$3}'`;

  301.                 chomp($tbname);

  302.                 $sqlstr = qq{INSERT INTO $tbname};

  303.             }

  305.             # check ignore table

  306.             if(&ignore_tb($tbname)){

  307.                 $notignore = 0;

  308.                 &mdebug("#IGNORE#:line:".$line);

  309.                 $sqlstr = '';

  310.             }else{

  311.                 $notignore = 1;

  312.                 &mdebug("#DO#:line:".$line);

  313.             }

  314.         }else {

  315.             if($notignore){

  316.                 &merror("can't get tbname") unless (defined($tbname));

  317.                 if ($line =~ /^WHERE/){

  318.                     $sqlarea = $SQLAREA_WHERE;

  319.                     $sqlstr .= qq{ SET};

  320.                     $isareabegin = 1;

  321.                 }elsif ($line =~ /^SET/){

  322.                     $sqlarea = $SQLAREA_SET;

  323.                     $sqlstr .= qq{ WHERE};

  324.                     $isareabegin = 1;

  325.                 }elsif ($line =~ /^\@/){

  326.                     $sqlstr .= &deal_col_value($tbname, $sqltype, $sqlarea, $isareabegin, $line);

  327.                     $isareabegin = 0;

  328.                 }else{

  329.                     &mdebug("::unknown sql:".$line);

  330.                 }

  331.             }

  332.         }

  333.     }

  334.     # export last sql

  335.     if ($sqlstr ne ''){

  336.         $sqlstr .= ";\n";

  337.         print SQLFILE $sqlstr;

  338.         &mdebug("export sql\n\t".$sqlstr);

  339.     }

  340.     

  341.     close BINLOG or die "Can't close binlog file: $binlogfile";

  343.     close SQLFILE or die "Can't close out sql file: $outfile";

  345.     # 逆序

  346.     # 1!G: 只有第一行不执行G, 将hold space中的内容append回到pattern space

  347.     # h: 将pattern space 拷贝到hold space

  348.     # $!d: 除最后一行都删除

  349.     my $invert = "sed -i '1!G;h;\$!d' $outfile";

  350.     my $res = `$invert`;

  351.     &mdebug("inverter order sqlfile :$invert");

  352. }

  354. # ----------------------------------------------------------------------------------------

  355. # Func : transfer column pos to name

  356. #    deal column value

  357. #

  358. # &deal_col_value($tbname, $sqltype, $sqlarea, $isareabegin, $line);

  359. # ----------------------------------------------------------------------------------------

  360. sub deal_col_value($$$$$){

  361.     my ($tbname, $sqltype, $sqlarea, $isareabegin, $line) = @_;

  362.     &mdebug("$PRE_FUNCT deal_col_value");

  363.     &mdebug("input:tbname->$tbname,type->$sqltype,area->$sqlarea,areabegin->$isareabegin,line->$line");

  364.     my @vals = split(/=/, $line);

  365.     my $pos = substr($vals[0],1);

  366.     my $valstartpos = length($pos)+2;

  367.     my $val = substr($line,$valstartpos);

  368.     my %tbcol = %{$tbcol_pos{$tbname}};

  369.     my ($cname,$ctype) = split(/$SPLITER_COL/,$tbcol{$pos});

  370.     &merror("can't get $tbname column $cname type") unless (defined($cname) || defined($ctype));

  371.     &mdebug("column infor:cname->$cname,type->$ctype");

  373.     # join str

  374.     my $joinstr;

  375.     if ($isareabegin){

  376.         $joinstr = ' ';

  377.     }else{

  378.         # WHERE 被替换为 SET, 使用 , 连接

  379.         if ($sqlarea eq $SQLAREA_WHERE){

  380.             $joinstr = ', ';

  381.         # SET 被替换为 WHERE 使用 AND 连接

  382.         }elsif ($sqlarea eq $SQLAREA_SET){

  383.             $joinstr = ' AND ';

  384.         }else{

  385.             &merror("!!!!!!The scripts error");

  386.         }

  387.     }

  388.     

  389.     #

  390.     my $newline = $joinstr;

  392.     # NULL value

  393.     if (($val eq 'NULL') && ($sqlarea eq $SQLAREA_SET)){

  394.         $newline .= qq{ $cname IS NULL};

  395.     }else{

  396.         # timestamp: record seconds

  397.         if ($ctype eq 'timestamp'){

  398.             $newline .= qq{$cname=from_unixtime($val)};

  399.         # datetime: @n=yyyy-mm-dd hh::ii::ss

  400.         }elsif ($ctype eq 'datetime'){

  401.             $newline .= qq{$cname='$val'};

  402.         }else{

  403.             $newline .= qq{$cname=$val};

  404.         }

  405.     }

  406.     &mdebug("\told>$line\n\tnew>$newline");

  407.     

  408.     return $newline;

  409. }

  411. # ----------------------------------------------------------------------------------------

  412. # Func : check is ignore table

  413. # params: IN table full name #  format:`dbname`.`tbname`

  414. # RETURN：

  415. #        0 not ignore

  416. #        1 ignore

  417. # ----------------------------------------------------------------------------------------

  418. sub ignore_tb($){

  419.     my $fullname = shift;

  420.     # 删除`

  421.     $fullname =~ s/`//g;

  422.     my ($dbname,$tbname) = split(/\./,$fullname);

  423.     my $res = 0;

  424.     

  425.     # 指定了数据库

  426.     if ($opt{'d'}){

  427.         # 与指定库相同

  428.         if ($do_dbs{$dbname}){

  429.             # 指定表

  430.             if ($opt{'T'}){

  431.                 # 与指定表不同

  432.                 unless ($do_tbs{$tbname}){

  433.                     $res = 1;

  434.                 }

  435.             }

  436.         # 与指定库不同

  437.         }else{

  438.             $res = 1;

  439.         }

  440.     }

  441.     #&mdebug("Table check ignore:$fullname->$res");

  442.     return $res;

  443. }

  446. # ----------------------------------------------------------------------------------------

  447. # Func : print debug msg

  448. # ----------------------------------------------------------------------------------------

  449. sub mdebug{

  450.     my (@msg) = @_;

  451.     print "@msg\n" if ($opt{'debug'});

  452. }

  455. # ----------------------------------------------------------------------------------------

  456. # Func : print error msg and exit

  457. # ----------------------------------------------------------------------------------------

  458. sub merror{

  459.     my (@msg) = @_;

  460.     print ":@msg\n";

  461.     &print_usage();

  462.     exit(1);

  463. }

  465. # ----------------------------------------------------------------------------------------

  466. # Func : print usage

  467. # ----------------------------------------------------------------------------------------

  468. sub print_usage{

  469.     print <

  470. ==========================================================================================

  471. Command line options :

  472.     --help                # OUT : print help info

  473.     -f, --srcfile            # IN : binlog file. [required]

  474.     -o, --outfile            # OUT : output sql file. [required]

  475.     -h, --host            # IN : host. default '127.0.0.1'

  476.     -u, --user            # IN : user. [required]

  477.     -p, --password            # IN : password. [required]

  478.     -P, --port            # IN : port. default '3306'

  479.     --start-datetime        # IN : start datetime

  480.     --stop-datetime            # IN : stop datetime

  481.     --start-position        # IN : start position

  482.     --stop-position            # IN : stop position

  483.     -d, --database            # IN : database, split comma

  484.     -T, --table            # IN : table, split comma. [required] set -d

  485.     -i, --ignore            # IN : ignore binlog check contain DDL(CREATE|ALTER|DROP|RENAME)

  486.     --debug                # IN : print debug information

  488. Sample :

  489.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd'

  490.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -i

  491.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --debug

  492.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -h '192.168.1.2' -u 'user' -p 'pwd' -P 3307

  493.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107

  494.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107 --stop-position=10000

  495.    shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2'

  496.    shell> perl binlog-rollback.pl -f 'mysql-bin.0000*' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2' -T 'tb1,tb2'

  497. ==========================================================================================

  498. EOF

  499.     exit;

  500. }

  503. 1;

看完了这篇文章，相信你对“MySQL在ROW模式下如何通过binlog提取SQL语句”有了一定的了解，如果想了解更多相关知识，欢迎关注创新互联行业资讯频道，感谢各位的阅读！


网站标题：MySQL在ROW模式下如何通过binlog提取SQL语句
URL网址：http://scyanting.com/article/ipsgeh.html

其他资讯