MySQL binlog日期解析

由于业务环境中MySQL 二进制日志复制是基于行的，昨天开发跑过来让查询有没有人对库进行过插入操作
用mysqlbinlog 工具查询出来的日志全是base-64编码的信息。
这是因为从MySQL 5.1开始，binlog支持row-based的格式，默认情况下只能看到一些经过base-64编码的信息

点击(此处)折叠或打开

1. #151224 23:29:48 server id 1 end_log_pos 13376153 CRC32 0x974f9a2e Query thread_id=164727 exec_time=0 error_code=0
   
2. SET TIMESTAMP=1450970988/*!*/;
3. BEGIN
4. /*!*/;
5. # at 13376153
6. #151224 23:29:48 server id 1 end_log_pos 13376236 CRC32 0x08e3e7fe Table_map: `guoqing`.`test` mapped to number 255
7. # at 13376236
8. #151224 23:29:48 server id 1 end_log_pos 13376532 CRC32 0xbb7ed638 Update_rows: table id 255 flags: STMT_END_F
10. BINLOG ‘
    
11. bA98VhMBAAAAUwAAAOwazAAAAP8AAAAAAAEAB25pcnZhbmEACHRlcm1pbmFsAAwIDw8PDwgREREQ
12. Dw8RwADAAP0C/QIAAAABAJYAlgDcD/7n4wg=
13. bA98Vh8BAAAAKAEAABQczAAAAP8AAAAAAAEAAgAM/////wDw5RUAAAAAAAAkRDVFRUJCNDYtRDI5
14. RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBiODlmMTVjMDk2NmY0Mzdl
15. BwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPKVZv1ydWfA8oAAANQ29mZmVlLzIuMC4xIADw5RUAAAAA
16. AAAkRDVFRUJCNDYtRDI5RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBi
17. ODlmMTVjMDk2NmY0MzdlBwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPbVZv1ydWfA9sAAANQ29mZmVl
18. LzIuMC4xIDjWfrs=
19. ‘/*!*/;
20. # at 13376532
21. #151224 23:29:48 server id 1 end_log_pos 13376563 CRC32 0xa58e318d Xid = 486691
22. COMMIT/*!*/;
23. # at 13376563
24. #151224 23:30:00 server id 1 end_log_pos 13376647 CRC32 0xd718f5ce Query thread_id=123940 exec_time=0 error_code=0

这 里只能看到`guoqing`.`test`表做了改动，但具体改了什么，就不知道了，那么怎样才能看到到底改了什么呢？
从MySQL 5.1.28开始，mysqlbinlog多了个参数–verbose(或-v)，将改动生成带注释的语句
如果使用两次这个参数(如-v -v)，会生成字段的类型、长度、是否为null等属性信息。如下：
参数：0112inin0000 -v -v

点击(此处)折叠或打开

1. BINLOG ‘
   
2. bA98VhMBAAAAUwAAAOwazAAAAP8AAAAAAAEAB25pcnZhbmEACHRlcm1pbmFsAAwIDw8PDwgREREQ
3. Dw8RwADAAP0C/QIAAAABAJYAlgDcD/7n4wg=
4. bA98Vh8BAAAAKAEAABQczAAAAP8AAAAAAAEAAgAM/////wDw5RUAAAAAAAAkRDVFRUJCNDYtRDI5
5. RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBiODlmMTVjMDk2NmY0Mzdl
6. BwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPKVZv1ydWfA8oAAANQ29mZmVlLzIuMC4xIADw5RUAAAAA
7. AAAkRDVFRUJCNDYtRDI5RC00QTVCLTk5QUYtMkEzRTIwRjE0RkU2IDAxMjlhYWUzYzJkYzQyYTBi
8. ODlmMTVjMDk2NmY0MzdlBwBDQVNISUVSAgBCWOSwAQAAAAAAVnwPbVZv1ydWfA9sAAANQ29mZmVl
9. LzIuMC4xIDjWfrs=
10. ‘/*!*/;
11. ### UPDATE `guoqing`.`test`
12. ### WHERE
13. ### @1=537 /* LONGINT meta=0 nullable=0 is_null=0 */
14. ### @2=10.00 /* DECIMAL(10,2) meta=2562 nullable=1 is_null=0 */
15. ### @3=1000 /* LONGINT meta=0 nullable=1 is_null=0 */
16. ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
17. ### @5=1000609 /* LONGINT meta=0 nullable=1 is_null=0 */
18. ### @6=‘SHOP’ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
19. ### @7=1450950696 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
20. ### SET
21. ### @1=537 /* LONGINT meta=0 nullable=0 is_null=0 */
22. ### @2=10.00 /* DECIMAL(10,2) meta=2562 nullable=1 is_null=0 */
23. ### @3=999 /* LONGINT meta=0 nullable=1 is_null=0 */
24. ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
25. ### @5=1000609 /* LONGINT meta=0 nullable=1 is_null=0 */
26. ### @6=‘SHOP’ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
27. ### @7=1450950696 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

想去掉base64编码，需加参数
–base64-output=DECODE-ROWS

点击(此处)折叠或打开

1. mysqlbinlog –v -v ––base64–output=DECODE–ROWS mysql–bin.000002 |grep –B 70 –A 70 ‘guoqing’ > /home/dba/guoqing.log
   
2.  ### UPDATE `guoqing`.`test`
3. ### WHERE
4. ### @1=537 /* LONGINT meta=0 nullable=0 is_null=0 */
5. ### @2=10.00 /* DECIMAL(10,2) meta=2562 nullable=1 is_null=0 */
6. ### @3=1000 /* LONGINT meta=0 nullable=1 is_null=0 */
7. ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
8. ### @5=1000609 /* LONGINT meta=0 nullable=1 is_null=0 */
9. ### @6=‘SHOP’ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
10. ### @7=1450950696 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
11. ### SET
12. ### @1=537 /* LONGINT meta=0 nullable=0 is_null=0 */
13. ### @2=10.00 /* DECIMAL(10,2) meta=2562 nullable=1 is_null=0 */
14. ### @3=999 /* LONGINT meta=0 nullable=1 is_null=0 */
15. ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
16. ### @5=1000609 /* LONGINT meta=0 nullable=1 is_null=0 */
17. ### @6=‘SHOP’ /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
18. ### @7=1450950696 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */

一般不需要加两个-v，可读性不强

点击(此处)折叠或打开

1. mysqlbinlog –v ––base64–output=DECODE–ROWS mysql–bin.000002 |grep –B 70 –A 70 ‘guoqing’ > /home/dba/guoqing.log

2. #151224 17:51:43 server id 1 end_log_pos 12053052 CRC32 0x2d03726a     Update_rows: table id 296 flags: STMT_END_F
   
3. ### UPDATE `guoqing`.`test`
4. ### WHERE
5. ### @1=537
6. ### @2=10.00
7. ### @3=1000
8. ### @4=0
9. ### @5=1000609
10. ### @6=‘SHOP’
11. ### @7=1450950696
12. ### SET
13. ### @1=537
14. ### @2=10.00
15. ### @3=999
16. ### @4=0
17. ### @5=1000609
18. ### @6=‘SHOP’
19. ### @7=1450950696