ITPub博客

首页 > 数据库 > MySQL > mysql binlog三种格式和解析

mysql binlog三种格式和解析

原创 MySQL 作者:dbs101 时间:2014-03-17 10:01:21 0 删除 编辑

MySQL binlog介绍:

mysql binlog记录着数据库的变化,包括表创建和表的数据变化。它也可以包含改变的语句
比如delete,insert,update等等语句。它也包含了这些语句执行的时间。binlog有俩个主
要的目的:复制和恢复。

mysql通过传输log来实现复制。在主库生成改变的日志,在备库检索这些日志,并执行日志
的内容。由于网络的延时,备库需要接收到日志会有个延时,时长可能是几秒甚至几分钟。
理想状态下,延时只有1秒。

mysql binlog支持三种格式,分别是rows,statement,mixed。

statement:log文件比较小,不是所有的statement都可以复制,备库和主库不需要具有同样
的版本,insert和select需要更大的行锁。支持基于时间点的恢复。

row:log文件比较大。所有的语句都可以复制。备库和主库的row 结构必须相同。insert,
delete,update需要更少的锁。支持基于时间点的恢复。但是以row格式存储,需要进行解析。

查询当前的bin log。

可以使用show master status或者show binary logs;


点击(此处)折叠或打开

  1. mysql> show master status;
  2. +------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
  5. | log.000022 | 231 | | | 7ff88fbe-a1f3-11e3-a2ea-001b21a78c70:1-8,
  6. c3394d34-a1fa-11e3-a319-001b21a78c70:1-62 |
  7. +------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
  8. 1 row in set (0.00 sec)
  9. mysql> show binary logs;
  10. +------------+-----------+
  11. | Log_name | File_size |
  12. +------------+-----------+
  13. | log.000009 | 404 |
  14. | log.000010 | 254 |
  15. | log.000011 | 3062 |
  16. | log.000012 | 254 |
  17. | log.000013 | 47399051 |
  18. | log.000014 | 3999 |
  19. | log.000015 | 254 |
  20. | log.000016 | 254 |
  21. | log.000017 | 1098 |
  22. | log.000018 | 254 |
  23. | log.000019 | 771 |
  24. | log.000020 | 850 |
  25. | log.000021 | 559 |
  26. | log.000022 | 231 |
  27. +------------+-----------+
  28. 14 rows in set (0.00 sec)

解析binlog;

mysql> show binlog events in  'log.000021';
+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name   | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                |
+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| log.000021 |   4 | Format_desc    |      8888 |         120 | Server ver: 5.6.15-enterprise-commercial-advanced-log, Binlog ver: 4                |
| log.000021 | 120 | Previous_gtids |      8888 |         231 | 7ff88fbe-a1f3-11e3-a2ea-001b21a78c70:1-8,
c3394d34-a1fa-11e3-a319-001b21a78c70:1-61 |
| log.000021 | 231 | Gtid           |      8888 |         279 | SET @@SESSION.GTID_NEXT= 'c3394d34-a1fa-11e3-a319-001b21a78c70:62'                  |
| log.000021 | 279 | Query          |      8888 |         351 | BEGIN                                                                               |
| log.000021 | 351 | Rows_query     |      8888 |         402 | # insert into t2 values (555)                                                       |
| log.000021 | 402 | Table_map      |      8888 |         447 | table_id: 70 (test.t2)                                                              |
| log.000021 | 447 | Write_rows     |      8888 |         487 | table_id: 70 flags: STMT_END_F                                                      |
| log.000021 | 487 | Xid            |      8888 |         518 | COMMIT /* xid=206 */                                                                |
| log.000021 | 518 | Rotate         |      8888 |         559 | log.000022;pos=4                                                                    |
+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

  -v, --verbose       Reconstruct pseudo-SQL statements out of row events. -v
                      -v adds comments on column data types.
  --base64-output=name
                      Determine when the output statements should be
                      base64-encoded BINLOG statements: 'never' disables it and
                      works only for binlogs without row-based events;
                      'decode-rows' decodes row events into commented
                      pseudo-SQL statements if the --verbose option is also
                      given; 'auto' prints base64 only when necessary (i.e.,
                      for row-based events and format description events).  If
                      no --base64-output[=name] option is given at all, the
                      default is 'auto'.
   --start-datetime=name
                      Start reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  -j, --start-position=#
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.
  --stop-datetime=name
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  --stop-position=#   Stop reading the binlog at position N. Applies to the
                      last binlog passed on the command line.
 
mysqlbinlog --start-datetime='2014-03-16 10:00:00' --stop-datetime='2014-03-16 11:00:00' --base64-output=decode-rows log.000021 -vv | more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
# at 120
#140316 10:21:06 server id 8888  end_log_pos 231 CRC32 0x21c2549c       Previous-GTIDs
# 7ff88fbe-a1f3-11e3-a2ea-001b21a78c70:1-8,
# c3394d34-a1fa-11e3-a319-001b21a78c70:1-61
# at 231
#140316 10:21:19 server id 8888  end_log_pos 279 CRC32 0x4282b660       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'c3394d34-a1fa-11e3-a319-001b21a78c70:62'/*!*/;
# at 279
#140316 10:21:19 server id 8888  end_log_pos 351 CRC32 0xa703430d       Query                                                                                           thread_id=12     exec_time=0     error_code=0
SET TIMESTAMP=1394936479/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 351
#140316 10:21:19 server id 8888  end_log_pos 402 CRC32 0x9a6ccd35       Rows_query
# insert into t2 values (555)
# at 402
#140316 10:21:19 server id 8888  end_log_pos 447 CRC32 0x64c0e853       Table_map: `test`.`t2` mapped to number 70
# at 447
#140316 10:21:19 server id 8888  end_log_pos 487 CRC32 0x8978ce79       Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=555 /* INT meta=0 nullable=1 is_null=0 */
# at 487
#140316 10:21:19 server id 8888  end_log_pos 518 CRC32 0xef6ba9f7       Xid = 206
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 518
#140316 10:21:24 server id 8888  end_log_pos 559 CRC32 0xd926f825       Rotate to log.000022  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-1122982/,如需转载,请注明出处,否则将追究法律责任。

下一篇: MYSQL 升级
请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    437848