mylogmnr:MySQL binlog logmnr_MySQL

1.mylogmnr介绍

此脚本主要是用来整理mysqlbinlog解析binlog得到的文本。只针对binlog用ROW模式的update，delete，insert语句。整理后的sql文本可以是易读的整个数据库的，也可以是易读的针对一个表的，同时可以是redo sql或者是undo sql。

注意：此脚本可能存在风险，如mysqlbinlog可能会转义某些字符，以及一些未考虑到的情况。此脚本仅用于测试、诊断问题、学习用途等，不要用于数据恢复等生产环境。使用此脚本产生的问题本人不承担任何责任。

2.mylogmnr所需条件

此脚本是用perl编写，这个一般的Linux都有自带。

另外，需要用到DBD::mysql,DBI模块，这个主要用来查询表的元数据。

还需要一个对所有数据库都有只读查询权限的用户(建议操作是使用slave上的)。

3.mylogmnr使用步骤

3.1 第一步：模拟操作

例如在test库里有个tt表：

mysql> select * from tt;+----+-------+---------------------+----------+| id | name| ctime | sary |+----+-------+---------------------+----------+|1 | qqq | 2014-06-13 14:22:30 | -222 ||2 | ccc | 2014-06-13 14:22:30 | -222 ||3 | ddd | 2014-06-13 14:22:30 | -222 ||4 | eee | 2014-06-13 00:00:00 | 33333300 ||5 | rere| 2014-06-13 00:00:00 | 7777 ||8 | rere| 2014-06-13 00:00:00 | 7777 ||9 | inc01 | 2014-06-16 00:00:00 |999 || 10 | inc02 | 2014-06-16 00:00:00 |11000 |+----+-------+---------------------+----------+8 rows in set (0.01 sec)然后执行一系列操作：mysql> insert into tt values(11,'test01','2014-06-26',-555);Query OK, 1 row affected (0.06 sec)mysql> insert into tt values(12,'test02','2014-05-26',88555);Query OK, 1 row affected (0.00 sec)mysql> mysql> update tt set ctime='2014-07-08' where id mysql> delete from tt where id select * from tt;+----+--------+---------------------+----------+| id | name | ctime | sary |+----+--------+---------------------+----------+|3 | ddd| 2014-07-08 00:00:00 | -222 ||4 | eee| 2014-07-08 00:00:00 | 33333300 ||5 | rere | 2014-07-08 00:00:00 | 7777 ||8 | rere | 2014-06-13 00:00:00 | 7777 ||9 | inc01| 2014-06-16 00:00:00 |999 || 10 | inc02| 2014-06-16 00:00:00 |11000 || 11 | test01 | 2014-06-26 00:00:00 | -555 || 12 | test02 | 2014-05-26 00:00:00 |88555 |+----+--------+---------------------+----------+8 rows in set (0.00 sec)对应的binlog如下：mysql> show master status;+------------------+----------+---------------| File | Position | Binlog_Do_DB |+------------------+----------+---------------| oel58-bin.000006 | 1211 | +------------------+----------+---------------1 row in set (0.00 sec)

3.2 第二步：使用mysqlbinlog解析对应的binlog

mysqlbinlog最好限制好时间段，这个时间段越少越好（不过我遇到过指定启始时间等解析报错的情况）：

mysqlbinlog -v --base64-output=DECODE-ROWS --start-datatime="2014-06-21 09:24:20" --stop-datetime="2014-06-21 09:30:20" mysql-bin.001865 > 001865_2.sql[root@oel58 ~]#mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/oel58-bin.000006 > 6666666.sql[root@oel58 ~]# [root@oel58 ~]# ls -al 6666666.sql -rw-r--r-- 1 root root 4612 Jun 26 16:44 6666666.sql

3.3第三步：mylogmnr.pl使用

可以使用下面的方式获得使用帮助：

[root@oel58 ~]# perl /home/oracle/mylogmnr.pl=====================================================================Info:		Created By noodba (www.noodba.com) .		Modified from parse_binlog.pl byjunda@alipay.com		Just use it for testing or studyingUsage :Command line options :	-h,--help			Print Help Info. 	-P,--port			Port number to use for local mysql connection(default 3306).	-u,--user			user name for local mysql(default qry).	-p,--pswd			user password for local mysql(can't be null).	-lh,--lhost			ip for mysql where info is got(can't be null).	-f,--sqlf			the sql file which will be parsed.	-o,--op				 redo sql or undo sql(default redo sql)	-t,--tbn				table name	Sample : 	shell> perl mylogmnr.pl -u qry -p 123456 -f /tmp/aaa.sql==========================================================================

生成整段日志的redo,输出文件为 输入文件名后加“.redo”：[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql

生成整段日志中某个表的redo,输出文件为 输入文件名后加“.redo”：[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -t test.tt

生成整段日志的undo,输出文件为 输入文件名后加“.undo”：[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -o undo

生成整段日志中某个表的undo,输出文件为 输入文件名后加“.undo”：[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -t test.tt -o undo

redo 文件例子：

[root@oel58 ~]# cat 6666666.sql.redoROLLBACK; BEGIN; #140626 16:36:13 server id 1end_log_pos 244 CRC32 0xd009758c 	Table_map: `test`.`tt` mapped to number 73 INSERT INTO test.ttVALUES( 11 , 'test01', '2014-06-26 00:00:00', -555); COMMIT; BEGIN; #140626 16:36:28 server id 1end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 INSERT INTO test.ttVALUES( 12 , 'test02', '2014-05-26 00:00:00', 88555 ); COMMIT; BEGIN; #140626 16:37:26 server id 1end_log_pos 676 CRC32 0x2d429457 	Table_map: `test`.`tt` mapped to number 73 UPDATE test.tt SET id=1,name='qqq',ctime='2014-07-08 00:00:00',sary=-222 WHERE id=1 and name='qqq' and ctime='2014-06-13 14:22:30' and sary=-222; UPDATE test.tt SET id=2,name='ccc',ctime='2014-07-08 00:00:00',sary=-222 WHERE id=2 and name='ccc' and ctime='2014-06-13 14:22:30' and sary=-222; UPDATE test.tt SET id=3,name='ddd',ctime='2014-07-08 00:00:00',sary=-222 WHERE id=3 and name='ddd' and ctime='2014-06-13 14:22:30' and sary=-222; UPDATE test.tt SET id=4,name='eee',ctime='2014-07-08 00:00:00',sary=3.33333e+07WHERE id=4 and name='eee' and ctime='2014-06-13 00:00:00' and sary=3.33333e+07 ; UPDATE test.tt SET id=5,name='rere',ctime='2014-07-08 00:00:00',sary=7777 WHERE id=5 and name='rere' and ctime='2014-06-13 00:00:00' and sary=7777; COMMIT; BEGIN; #140626 16:37:39 server id 1end_log_pos 1099 CRC32 0xf0e497d3 	Table_map: `test`.`tt` mapped to number 73 DELETE FROM test.tt where id=1and name= 'qqq' and ctime= '2014-07-08 00:00:00' and sary= -222; DELETE FROM test.tt where id=2and name= 'ccc' and ctime= '2014-07-08 00:00:00' and sary= -222; COMMIT; ROLLBACK;

undo文件例子：

[root@oel58 ~]# cat 6666666.sql.undoROLLBACK; BEGIN; INSERT INTO test.ttVALUES( 2 , 'ccc', '2014-07-08 00:00:00', -222); INSERT INTO test.ttVALUES( 1 , 'qqq', '2014-07-08 00:00:00', -222); #140626 16:37:39 server id 1end_log_pos 1099 CRC32 0xf0e497d3 	Table_map: `test`.`tt` mapped to number 73 COMMIT; BEGIN; UPDATE test.tt SET id=5 ,name='rere' ,ctime='2014-06-13 00:00:00' ,sary=7777 WHEREid=5 and name='rere' and ctime='2014-07-08 00:00:00' and sary=7777; UPDATE test.tt SET id=4 ,name='eee' ,ctime='2014-06-13 00:00:00' ,sary=3.33333e+07WHEREid=4 and name='eee' and ctime='2014-07-08 00:00:00' and sary=3.33333e+07 ; UPDATE test.tt SET id=3 ,name='ddd' ,ctime='2014-06-13 14:22:30' ,sary=-222 WHEREid=3 and name='ddd' and ctime='2014-07-08 00:00:00' and sary=-222; UPDATE test.tt SET id=2 ,name='ccc' ,ctime='2014-06-13 14:22:30' ,sary=-222 WHEREid=2 and name='ccc' and ctime='2014-07-08 00:00:00' and sary=-222; UPDATE test.tt SET id=1 ,name='qqq' ,ctime='2014-06-13 14:22:30' ,sary=-222 WHEREid=1 and name='qqq' and ctime='2014-07-08 00:00:00' and sary=-222; #140626 16:37:26 server id 1end_log_pos 676 CRC32 0x2d429457 	Table_map: `test`.`tt` mapped to number 73 COMMIT; BEGIN; DELETE FROM test.tt where id=12and name= 'test02' and ctime= '2014-05-26 00:00:00' and sary= 88555 ; #140626 16:36:28 server id 1end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 COMMIT; BEGIN; DELETE FROM test.tt where id=11and name= 'test01' and ctime= '2014-06-26 00:00:00' and sary= -555; #140626 16:36:13 server id 1end_log_pos 244 CRC32 0xd009758c 	Table_map: `test`.`tt` mapped to number 73 COMMIT; ROLLBACK;

3.4 第四步：回滚数据（请在测试机上进行）

初始数据情况如下：

mysql> select sysdate();+---------------------+| sysdate() |+---------------------+| 2014-06-26 16:55:56 |+---------------------+1 row in set (0.00 sec)mysql> select * from tt;+----+--------+---------------------+----------+| id | name | ctime | sary |+----+--------+---------------------+----------+|3 | ddd| 2014-07-08 00:00:00 | -222 ||4 | eee| 2014-07-08 00:00:00 | 33333300 ||5 | rere | 2014-07-08 00:00:00 | 7777 ||8 | rere | 2014-06-13 00:00:00 | 7777 ||9 | inc01| 2014-06-16 00:00:00 |999 || 10 | inc02| 2014-06-16 00:00:00 |11000 || 11 | test01 | 2014-06-26 00:00:00 | -555 || 12 | test02 | 2014-05-26 00:00:00 |88555 |+----+--------+---------------------+----------+8 rows in set (0.01 sec)执行回滚脚本：[root@oel58 ~]# mysql  select sysdate();+---------------------+| sysdate() |+---------------------+| 2014-06-26 16:56:44 |+---------------------+1 row in set (0.00 sec)mysql> mysql> select * from tt;+----+-------+---------------------+----------+| id | name| ctime | sary |+----+-------+---------------------+----------+|1 | qqq | 2014-06-13 14:22:30 | -222 ||2 | ccc | 2014-06-13 14:22:30 | -222 ||3 | ddd | 2014-06-13 14:22:30 | -222 ||4 | eee | 2014-06-13 00:00:00 | 33333300 ||5 | rere| 2014-06-13 00:00:00 | 7777 ||8 | rere| 2014-06-13 00:00:00 | 7777 ||9 | inc01 | 2014-06-16 00:00:00 |999 || 10 | inc02 | 2014-06-16 00:00:00 |11000 |+----+-------+---------------------+----------+8 rows in set (0.01 sec)

4. 联系方式

EMAIL:qiuwsh@gmail.com

Q Q : 570182914

Phone: 13817963180

Weibo: weibo.com/noodba

5.参考资料

1 改造自parse_binlog.pl by junda@alipay.com