mysql binlog三种格式和解析

最新推荐文章于 2024-03-15 09:28:39 发布

cqr0591

最新推荐文章于 2024-03-15 09:28:39 发布

阅读量149

点赞数

文章标签：数据库 shell

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；

点击(此处)折叠或打开

mysql> show master status;
+------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| log.000022 | 231 | | | 7ff88fbe-a1f3-11e3-a2ea-001b21a78c70:1-8,
c3394d34-a1fa-11e3-a319-001b21a78c70:1-62 |
+------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| log.000009 | 404 |
| log.000010 | 254 |
| log.000011 | 3062 |
| log.000012 | 254 |
| log.000013 | 47399051 |
| log.000014 | 3999 |
| log.000015 | 254 |
| log.000016 | 254 |
| log.000017 | 1098 |
| log.000018 | 254 |
| log.000019 | 771 |
| log.000020 | 850 |
| log.000021 | 559 |
| log.000022 | 231 |
+------------+-----------+
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-1122982/

cqr0591

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql binlog三种格式和解析

MySQL binlog介绍：mysql binlog记录着数据库的变化，包括表创建和表的数据变化。它也可以包含改变的语句比如delete，insert，update等等语句。它也包含了这些语句执行的时间。bi...
复制链接

扫一扫