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
How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PMThe article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.
How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PMArticle discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]
How do you handle large datasets in MySQL?Mar 21, 2025 pm 12:15 PMArticle discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.
What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PMArticle discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
How do you drop a table in MySQL using the DROP TABLE statement?Mar 19, 2025 pm 03:52 PMThe article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.
How do you create indexes on JSON columns?Mar 21, 2025 pm 12:13 PMThe article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.
How do you represent relationships using foreign keys?Mar 19, 2025 pm 03:48 PMArticle discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.
How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?Mar 18, 2025 pm 12:00 PMArticle discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
SublimeText3 English version
Recommended: Win version, supports code prompts!
Zend Studio 13.0.1
Powerful PHP integrated development environment
Atom editor mac version download
The most popular open source editor
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
Dreamweaver Mac version
Visual web development tools
