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

时间：2022-03-16 22:53:59

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
  2. use strict;
  3. use warnings;
  4. use Class::Struct;
  5. use Getopt::Long qw(:config no_ignore_case);                    # GetOption
  6. # register handler system signals
  7. use sigtrap 'handler', \&sig_int, 'normal-signals';
  8. # catch signal
  9. sub sig_int(){
  10. my ($signals) = @_;
  11. print STDERR "# Caught SIG$signals.\n";
  12. exit 1;
  13. }
  14. my %opt;
  15. my $srcfile;
  16. my $host = '127.0.0.1';
  17. my $port = 3306;
  18. my ($user,$pwd);
  19. my ($MYSQL, $MYSQLBINLOG, $ROLLBACK_DML);
  20. my $outfile = '/dev/null';
  21. my (%do_dbs,%do_tbs);
  22. # tbname=>tbcol, tbcol: @n=>colname,type
  23. my %tbcol_pos;
  24. my $SPLITER_COL = ',';
  25. my $SQLTYPE_IST = 'INSERT';
  26. my $SQLTYPE_UPD = 'UPDATE';
  27. my $SQLTYPE_DEL = 'DELETE';
  28. my $SQLAREA_WHERE = 'WHERE';
  29. my $SQLAREA_SET = 'SET';
  30. my $PRE_FUNCT = '========================== ';
  31. # =========================================================
  32. # 基于row模式的binlog，生成DML(insert/update/delete)的rollback语句
  33. # 通过mysqlbinlog -v 解析binlog生成可读的sql文件
  34. # 提取需要处理的有效sql
  35. #     "### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误
  36. #
  37. # 将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行
  38. #     INSERT: INSERT INTO => DELETE FROM, SET => WHERE
  39. #     UPDATE: WHERE => SET, SET => WHERE
  40. #     DELETE: DELETE FROM => INSERT INTO, WHERE => SET
  41. # 用列名替换位置@{1,2,3}
  42. #     通过desc table获得列顺序及对应的列名
  43. #     特殊列类型value做特别处理
  44. # 逆序
  45. #
  46. # 注意:
  47. #     表结构与现在的表结构必须相同[谨记]
  48. #     由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT
  49. #     只能对INSERT/UPDATE/DELETE进行处理
  50. # ========================================================
  51. sub main{
  52. # get input option
  53. &get_options();
  54. #
  55. &init_tbcol();
  56. #
  57. &do_binlog_rollback();
  58. }
  59. &main();
  60. # ----------------------------------------------------------------------------------------
  61. # Func : get options and set option flag
  62. # ----------------------------------------------------------------------------------------
  63. sub get_options{
  64. #Get options info
  65. GetOptions(\%opt,
  66. 'help',                    # OUT : print help info
  67. 'f|srcfile=s',            # IN : binlog file
  68. 'o|outfile=s',            # out : output sql file
  69. 'h|host=s',                # IN : host
  70. 'u|user=s', # IN : user
  71. 'p|password=s', # IN : password
  72. 'P|port=i',                # IN : port
  73. 'start-datetime=s',        # IN : start datetime
  74. 'stop-datetime=s',        # IN : stop datetime
  75. 'start-position=i',        # IN : start position
  76. 'stop-position=i',        # IN : stop position
  77. 'd|database=s',            # IN : database, split comma
  78. 'T|table=s',            # IN : table, split comma
  79. 'i|ignore',                # IN : ignore binlog check ddl and so on
  80. 'debug',                # IN : print debug information
  81. ) or print_usage();
  82. if (!scalar(%opt)) {
  83. &print_usage();
  84. }
  85. # Handle for options
  86. if ($opt{'f'}){
  87. $srcfile = $opt{'f'};
  88. }else{
  89. &merror("please input binlog file");
  90. }
  91. $opt{'h'} and $host = $opt{'h'};
  92. $opt{'u'} and $user = $opt{'u'};
  93. $opt{'p'} and $pwd = $opt{'p'};
  94. $opt{'P'} and $port = $opt{'P'};
  95. if ($opt{'o'}) {
  96. $outfile = $opt{'o'};
  97. # 清空 outfile
  98. `echo '' > $outfile`;
  99. }
  100. #
  101. $MYSQL = qq{mysql -h$host -u$user -p'$pwd' -P$port};
  102. &mdebug("get_options::MYSQL\n\t$MYSQL");
  103. # 提取binlog,不需要显示列定义信息，用-v，而不用-vv
  104. $MYSQLBINLOG = qq{mysqlbinlog -v};
  105. $MYSQLBINLOG .= " --start-position=".$opt{'start-position'} if $opt{'start-position'};
  106. $MYSQLBINLOG .= " --stop-position=".$opt{'stop-position'} if $opt{'stop-postion'};
  107. $MYSQLBINLOG .= " --start-datetime='".$opt{'start-datetime'}."'" if $opt{'start-datetime'};
  108. $MYSQLBINLOG .= " --stop-datetime='$opt{'stop-datetime'}'" if $opt{'stop-datetime'};
  109. $MYSQLBINLOG .= " $srcfile";
  110. &mdebug("get_options::MYSQLBINLOG\n\t$MYSQLBINLOG");
  111. # 检查binlog中是否含有 ddl sql: CREATE|ALTER|DROP|RENAME
  112. &check_binlog() unless ($opt{'i'});
  113. # 不使用mysqlbinlog过滤，USE dbname;方式可能会漏掉某些sql，所以不在mysqlbinlog过滤
  114. # 指定数据库
  115. if ($opt{'d'}){
  116. my @dbs = split(/,/,$opt{'d'});
  117. foreach my $db (@dbs){
  118. $do_dbs{$db}=1;
  119. }
  120. }
  121. # 指定表
  122. if ($opt{'T'}){
  123. my @tbs = split(/,/,$opt{'T'});
  124. foreach my $tb (@tbs){
  125. $do_tbs{$tb}=1;
  126. }
  127. }
  128. # 提取有效DML SQL
  129. $ROLLBACK_DML = $MYSQLBINLOG." | grep '^### '";
  130. # 去掉注释: '### ' -> ''
  131. # 删除首尾空格
  132. $ROLLBACK_DML .= " | sed 's/###\\s*//g;s/\\s*\$//g'";
  133. &mdebug("rollback dml\n\t$ROLLBACK_DML");
  134. # 检查内容是否为空
  135. my $cmd = "$ROLLBACK_DML | wc -l";
  136. &mdebug("check contain dml sql\n\t$cmd");
  137. my $size = `$cmd`;
  138. chomp($size);
  139. unless ($size >0){
  140. &merror("binlog DML is empty:$ROLLBACK_DML");
  141. };
  142. }
  143. # ----------------------------------------------------------------------------------------
  144. # Func : check binlog contain DDL
  145. # ----------------------------------------------------------------------------------------
  146. sub check_binlog{
  147. &mdebug("$PRE_FUNCT check_binlog");
  148. my $cmd = "$MYSQLBINLOG ";
  149. $cmd .= " | grep -E -i '^(CREATE|ALTER|DROP|RENAME)' ";
  150. &mdebug("check binlog has DDL cmd\n\t$cmd");
  151. my $ddlcnt = `$cmd`;
  152. chomp($ddlcnt);
  153. my $ddlnum = `$cmd | wc -l`;
  154. chomp($ddlnum);
  155. my $res = 0;
  156. if ($ddlnum>0){
  157. # 在ddl sql前面加上前缀<DDL>
  158. $ddlcnt = `echo '$ddlcnt' | sed 's/^//g'`;
  159. &merror("binlog contain $ddlnum DDL:$MYSQLBINLOG. ddl sql:\n$ddlcnt");
  160. }
  161. return $res;
  162. }
  163. # ----------------------------------------------------------------------------------------
  164. # Func : init all table column order
  165. #        if input --database --table params, only get set table column order
  166. # ----------------------------------------------------------------------------------------
  167. sub init_tbcol{
  168. &mdebug("$PRE_FUNCT init_tbcol");
  169. # 提取DML语句
  170. my $cmd .= "$ROLLBACK_DML | grep -E '^(INSERT|UPDATE|DELETE)'";
  171. # 提取表名，并去重
  172. #$cmd .= " | awk '{if (\$1 ~ \"^UPDATE\") {print \$2}else {print \$3}}' | uniq ";
  173. $cmd .= " | awk '{if (\$1 ~ \"^UPDATE\") {print \$2}else {print \$3}}' | sort | uniq ";
  174. &mdebug("get table name cmd\n\t$cmd");
  175. open ALLTABLE, "$cmd | " or die "can't open file:$cmd\n";
  176. while (my $tbname = <ALLTABLE>){
  177. chomp($tbname);
  178. #if (exists $tbcol_pos{$tbname}){
  179. #    next;
  180. #}
  181. &init_one_tbcol($tbname) unless (&ignore_tb($tbname));
  182. }
  183. close ALLTABLE or die "can't close file:$cmd\n";
  184. # init tb col
  185. foreach my $tb (keys %tbcol_pos){
  186. &mdebug("tbname->$tb");
  187. my %colpos = %{$tbcol_pos{$tb}};
  188. foreach my $pos (keys %colpos){
  189. my $col = $colpos{$pos};
  190. my ($cname,$ctype) = split(/$SPLITER_COL/, $col);
  191. &mdebug("\tpos->$pos,cname->$cname,ctype->$ctype");
  192. }
  193. }
  194. };
  195. # ----------------------------------------------------------------------------------------
  196. # Func : init one table column order
  197. # ----------------------------------------------------------------------------------------
  198. sub init_one_tbcol{
  199. my $tbname = shift;
  200. &mdebug("$PRE_FUNCT init_one_tbcol");
  201. # 获取表结构及列顺序
  202. my $cmd = $MYSQL." --skip-column-names --silent -e 'desc $tbname'";
  203. # 提取列名，并拼接
  204. $cmd .= " | awk -F\'\\t\' \'{print NR\"$SPLITER_COL`\"\$1\"`$SPLITER_COL\"\$2}'";
  205. &mdebug("get table column infor cmd\n\t$cmd");
  206. open TBCOL,"$cmd | " or die "can't open desc $tbname;";
  207. my %colpos;
  208. while (my $line = <TBCOL>){
  209. chomp($line);
  210. my ($pos,$col,$coltype) = split(/$SPLITER_COL/,$line);
  211. &mdebug("linesss=$line\n\t\tpos=$pos\n\t\tcol=$col\n\t\ttype=$coltype");
  212. $colpos{$pos} = $col.$SPLITER_COL.$coltype;
  213. }
  214. close TBCOL or die "can't colse desc $tbname";
  215. $tbcol_pos{$tbname} = \%colpos;
  216. }
  217. # ----------------------------------------------------------------------------------------
  218. # Func : rollback sql:    INSERT/UPDATE/DELETE
  219. # ----------------------------------------------------------------------------------------
  220. sub do_binlog_rollback{
  221. my $binlogfile = "$ROLLBACK_DML ";
  222. &mdebug("$PRE_FUNCT do_binlog_rollback");
  223. # INSERT|UPDATE|DELETE
  224. my $sqltype;
  225. # WHERE|SET
  226. my $sqlarea;
  227. my ($tbname, $sqlstr) = ('', '');
  228. my ($notignore, $isareabegin) = (0,0);
  229. # output sql file
  230. open SQLFILE, ">> $outfile" or die "Can't open sql file:$outfile";
  231. # binlog file
  232. open BINLOG, "$binlogfile |" or die "Can't open file: $binlogfile";
  233. while (my $line = <BINLOG>){
  234. chomp($line);
  235. if ($line =~ /^(INSERT|UPDATE|DELETE)/){
  236. # export sql
  237. if ($sqlstr ne ''){
  238. $sqlstr .= ";\n";
  239. print SQLFILE $sqlstr;
  240. &mdebug("export sql\n\t".$sqlstr);
  241. $sqlstr = '';
  242. }
  243. if ($line =~ /^INSERT/){
  244. $sqltype = $SQLTYPE_IST;
  245. $tbname = `echo '$line' | awk '{print \$3}'`;
  246. chomp($tbname);
  247. $sqlstr = qq{DELETE FROM $tbname};
  248. }elsif ($line =~ /^UPDATE/){
  249. $sqltype = $SQLTYPE_UPD;
  250. $tbname = `echo '$line' | awk '{print \$2}'`;
  251. chomp($tbname);
  252. $sqlstr = qq{UPDATE $tbname};
  253. }elsif ($line =~ /^DELETE/){
  254. $sqltype = $SQLTYPE_DEL;
  255. $tbname = `echo '$line' | awk '{print \$3}'`;
  256. chomp($tbname);
  257. $sqlstr = qq{INSERT INTO $tbname};
  258. }
  259. # check ignore table
  260. if(&ignore_tb($tbname)){
  261. $notignore = 0;
  262. &mdebug("#IGNORE#:line:".$line);
  263. $sqlstr = '';
  264. }else{
  265. $notignore = 1;
  266. &mdebug("#DO#:line:".$line);
  267. }
  268. }else {
  269. if($notignore){
  270. &merror("can't get tbname") unless (defined($tbname));
  271. if ($line =~ /^WHERE/){
  272. $sqlarea = $SQLAREA_WHERE;
  273. $sqlstr .= qq{ SET};
  274. $isareabegin = 1;
  275. }elsif ($line =~ /^SET/){
  276. $sqlarea = $SQLAREA_SET;
  277. $sqlstr .= qq{ WHERE};
  278. $isareabegin = 1;
  279. }elsif ($line =~ /^\@/){
  280. $sqlstr .= &deal_col_value($tbname, $sqltype, $sqlarea, $isareabegin, $line);
  281. $isareabegin = 0;
  282. }else{
  283. &mdebug("::unknown sql:".$line);
  284. }
  285. }
  286. }
  287. }
  288. # export last sql
  289. if ($sqlstr ne ''){
  290. $sqlstr .= ";\n";
  291. print SQLFILE $sqlstr;
  292. &mdebug("export sql\n\t".$sqlstr);
  293. }
  294. close BINLOG or die "Can't close binlog file: $binlogfile";
  295. close SQLFILE or die "Can't close out sql file: $outfile";
  296. # 逆序
  297. # 1!G: 只有第一行不执行G, 将hold space中的内容append回到pattern space
  298. # h: 将pattern space 拷贝到hold space
  299. # $!d: 除最后一行都删除
  300. my $invert = "sed -i '1!G;h;\$!d' $outfile";
  301. my $res = `$invert`;
  302. &mdebug("inverter order sqlfile :$invert");
  303. }
  304. # ----------------------------------------------------------------------------------------
  305. # Func : transfer column pos to name
  306. #    deal column value
  307. #
  308. # &deal_col_value($tbname, $sqltype, $sqlarea, $isareabegin, $line);
  309. # ----------------------------------------------------------------------------------------
  310. sub deal_col_value($$$$$){
  311. my ($tbname, $sqltype, $sqlarea, $isareabegin, $line) = @_;
  312. &mdebug("$PRE_FUNCT deal_col_value");
  313. &mdebug("input:tbname->$tbname,type->$sqltype,area->$sqlarea,areabegin->$isareabegin,line->$line");
  314. my @vals = split(/=/, $line);
  315. my $pos = substr($vals[0],1);
  316. my $valstartpos = length($pos)+2;
  317. my $val = substr($line,$valstartpos);
  318. my %tbcol = %{$tbcol_pos{$tbname}};
  319. my ($cname,$ctype) = split(/$SPLITER_COL/,$tbcol{$pos});
  320. &merror("can't get $tbname column $cname type") unless (defined($cname) || defined($ctype));
  321. &mdebug("column infor:cname->$cname,type->$ctype");
  322. # join str
  323. my $joinstr;
  324. if ($isareabegin){
  325. $joinstr = ' ';
  326. }else{
  327. # WHERE 被替换为 SET, 使用 , 连接
  328. if ($sqlarea eq $SQLAREA_WHERE){
  329. $joinstr = ', ';
  330. # SET 被替换为 WHERE 使用 AND 连接
  331. }elsif ($sqlarea eq $SQLAREA_SET){
  332. $joinstr = ' AND ';
  333. }else{
  334. &merror("!!!!!!The scripts error");
  335. }
  336. }
  337. #
  338. my $newline = $joinstr;
  339. # NULL value
  340. if (($val eq 'NULL') && ($sqlarea eq $SQLAREA_SET)){
  341. $newline .= qq{ $cname IS NULL};
  342. }else{
  343. # timestamp: record seconds
  344. if ($ctype eq 'timestamp'){
  345. $newline .= qq{$cname=from_unixtime($val)};
  346. # datetime: @n=yyyy-mm-dd hh::ii::ss
  347. }elsif ($ctype eq 'datetime'){
  348. $newline .= qq{$cname='$val'};
  349. }else{
  350. $newline .= qq{$cname=$val};
  351. }
  352. }
  353. &mdebug("\told>$line\n\tnew>$newline");
  354. return $newline;
  355. }
  356. # ----------------------------------------------------------------------------------------
  357. # Func : check is ignore table
  358. # params: IN table full name # format:`dbname`.`tbname`
  359. # RETURN：
  360. #        0 not ignore
  361. #        1 ignore
  362. # ----------------------------------------------------------------------------------------
  363. sub ignore_tb($){
  364. my $fullname = shift;
  365. # 删除`
  366. $fullname =~ s/`//g;
  367. my ($dbname,$tbname) = split(/\./,$fullname);
  368. my $res = 0;
  369. # 指定了数据库
  370. if ($opt{'d'}){
  371. # 与指定库相同
  372. if ($do_dbs{$dbname}){
  373. # 指定表
  374. if ($opt{'T'}){
  375. # 与指定表不同
  376. unless ($do_tbs{$tbname}){
  377. $res = 1;
  378. }
  379. }
  380. # 与指定库不同
  381. }else{
  382. $res = 1;
  383. }
  384. }
  385. #&mdebug("Table check ignore:$fullname->$res");
  386. return $res;
  387. }
  388. # ----------------------------------------------------------------------------------------
  389. # Func : print debug msg
  390. # ----------------------------------------------------------------------------------------
  391. sub mdebug{
  392. my (@msg) = @_;
  393. print "@msg\n" if ($opt{'debug'});
  394. }
  395. # ----------------------------------------------------------------------------------------
  396. # Func : print error msg and exit
  397. # ----------------------------------------------------------------------------------------
  398. sub merror{
  399. my (@msg) = @_;
  400. print ":@msg\n";
  401. &print_usage();
  402. exit(1);
  403. }
  404. # ----------------------------------------------------------------------------------------
  405. # Func : print usage
  406. # ----------------------------------------------------------------------------------------
  407. sub print_usage{
  408. print <<EOF;
  409. ==========================================================================================
  410. Command line options :
  411. --help                # OUT : print help info
  412. -f, --srcfile            # IN : binlog file. [required]
  413. -o, --outfile            # OUT : output sql file. [required]
  414. -h, --host            # IN : host. default '127.0.0.1'
  415. -u, --user            # IN : user. [required]
  416. -p, --password            # IN : password. [required]
  417. -P, --port            # IN : port. default '3306'
  418. --start-datetime        # IN : start datetime
  419. --stop-datetime            # IN : stop datetime
  420. --start-position        # IN : start position
  421. --stop-position            # IN : stop position
  422. -d, --database            # IN : database, split comma
  423. -T, --table            # IN : table, split comma. [required] set -d
  424. -i, --ignore            # IN : ignore binlog check contain DDL(CREATE|ALTER|DROP|RENAME)
  425. --debug                # IN : print debug information
  426. Sample :
  427. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd'
  428. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -i
  429. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --debug
  430. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -h '192.168.1.2' -u 'user' -p 'pwd' -P 3307
  431. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107
  432. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107 --stop-position=10000
  433. shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2'
  434. shell> perl binlog-rollback.pl -f 'mysql-bin.0000*' -o '/tmp/t.sql' -u 'user' -p 'pwd' -d 'db1,db2' -T 'tb1,tb2'
  435. ==========================================================================================
  436. EOF
  437. exit;
  438. }
  439. 1;

参考：http://www.oschina.net/code/snippet_617180_34028

相关文章