Tag Archives: mylogmnr

mylogmnr:MySQL binlog logmnr

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<8;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> 
mysql> delete from tt where id<3;
Query OK, 2 rows affected (0.01 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.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 by  junda@alipay.com
        Just use it for testing or studying
Usage :
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.redo
ROLLBACK; 
BEGIN; 
#140626 16:36:13 server id 1  end_log_pos 244 CRC32 0xd009758c 	Table_map: `test`.`tt` mapped to number 73 
INSERT INTO test.tt  VALUES( 11 , 'test01', '2014-06-26 00:00:00', -555                ); 
COMMIT; 
BEGIN; 
#140626 16:36:28 server id 1  end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 
INSERT INTO test.tt  VALUES( 12 , 'test02', '2014-05-26 00:00:00', 88555               ); 
COMMIT; 
BEGIN; 
#140626 16:37:26 server id 1  end_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+07          WHERE 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 1  end_log_pos 1099 CRC32 0xf0e497d3 	Table_map: `test`.`tt` mapped to number 73 
DELETE FROM test.tt where id=1  and name= 'qqq' and ctime= '2014-07-08 00:00:00' and sary= -222                ; 
DELETE FROM test.tt where id=2  and name= 'ccc' and ctime= '2014-07-08 00:00:00' and sary= -222                ; 
COMMIT; 
ROLLBACK;

undo文件例子:

[root@oel58 ~]# cat 6666666.sql.undo
ROLLBACK; 
BEGIN; 
INSERT INTO test.tt  VALUES( 2 , 'ccc', '2014-07-08 00:00:00', -222                ); 
INSERT INTO test.tt  VALUES( 1 , 'qqq', '2014-07-08 00:00:00', -222                ); 
#140626 16:37:39 server id 1  end_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                 WHERE  id=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+07          WHERE  id=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                 WHERE  id=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                 WHERE  id=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                 WHERE  id=1 and name='qqq' and ctime='2014-07-08 00:00:00' and sary=-222                ; 
#140626 16:37:26 server id 1  end_log_pos 676 CRC32 0x2d429457 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
BEGIN; 
DELETE FROM test.tt where id=12  and name= 'test02' and ctime= '2014-05-26 00:00:00' and sary= 88555               ; 
#140626 16:36:28 server id 1  end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
BEGIN; 
DELETE FROM test.tt where id=11  and name= 'test01' and ctime= '2014-06-26 00:00:00' and sary= -555                ; 
#140626 16:36:13 server id 1  end_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 < 6666666.sql.undo

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