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
   

2. 
   

3. use strict;
   

4. use warnings;
   

5. 
   

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';
   

10. 
    

11. # catch signal
    

12. sub sig_int(){
    

13. my ($signals) = @_;
    

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

15. exit 1;
    

16. }
    

17. 
    

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);
    

26. 
    

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

28. my %tbcol_pos;
    

29. 
    

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';
    

36. 
    

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

38. 
    

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{
    

60. 
    

61. # get input option
    

62. &get_options();
    

63. 
    

64. #
    

65. &init_tbcol();
    

66. 
    

67. #
    

68. &do_binlog_rollback();
    

69. }
    

70. 
    

71. &main();
    

72. 
    

73. 
    

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();
    

96. 
    

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

98. &print_usage();
    

99. }
    

100. 
     

101. # Handle for options
     

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

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

104. }else{
     

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

106. }
     

107. 
     

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. }
     

117. 
     

118. #
     

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

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

121. 
     

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");
     

130. 
     

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

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

133. 
     

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. }
     

142. 
     

143. # 指定表
     

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

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

146. foreach my $tb (@tbs){
     

147. $do_tbs{$tb}=1;
     

148. }
     

149. }
     

150. 
     

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. };
     

166. 
     

167. }
     

168. 
     

169. 
     

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);
     

180. 
     

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. }
     

189. 
     

190. return $res;
     

191. }
     

192. 
     

193. 
     

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";
     

207. 
     

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";
     

217. 
     

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. };
     

229. 
     

230. 
     

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;";
     

243. 
     

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";
     

252. 
     

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

254. }
     

255. 
     

256. 
     

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");
     

263. 
     

264. # INSERT|UPDATE|DELETE
     

265. my $sqltype;
     

266. # WHERE|SET
     

267. my $sqlarea;
     

268. 
     

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

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

271. 
     

272. # output sql file
     

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

274. 
     

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. }
     

287. 
     

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. }
     

304. 
     

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";
     

342. 
     

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

344. 
     

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. }
     

353. 
     

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");
     

372. 
     

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;
     

391. 
     

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. }
     

410. 
     

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. }
     

444. 
     

445. 
     

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

447. # Func : print debug msg
     

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

449. sub mdebug{
     

450. my (@msg) = @_;
     

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

452. }
     

453. 
     

454. 
     

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. }
     

464. 
     

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
     

487. 
     

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. }
     

501. 
     

502. 
     

503. 1;

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