mysql binglog格式_mysql binlog三种格式和解析

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*/;