mysql row binlog event-wdegang
对mysql二进制日志的格式。写的比较简单，讲究着看吧，下面开始：
mysql binlog的格式有三种，分别ROW/STATEMENT/FIXED，我这里只说ROW和STATEMENT格式。大家可以自行测试，mysql的版本是5.6.12.
1，row模式
mysql> show variables like 'bin%format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
1.1 INSERT
mysql> insert into test select 3,'ccc';
mysql> show binlog events in 'bin-log.000013';
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type| Server_id | End_log_pos | Info |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| bin-log.000013 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| bin-log.000013 | 120 | Query | 1 | 192 | BEGIN |
| bin-log.000013 | 192 | Table_map| 1 | 242 | table_id: 461 (test.test) |
| bin-log.000013 | 242 | Write_rows| 1 | 286 | table_id: 461 flags: STMT_END_F |
| bin-log.000013 | 286 | Xid | 1 | 317 | COMMIT /* xid=9485 */ |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)
这个insert会有4个events。query/table_map/write_rows/xid/.info中会纪录相关的信息。query－》BEGINE，Table_map—》table_id（操作的数据库和表信息），xid－》commit。
1.2 update/delete 和insert是同样的这4个事件。
1.3>
mysql>> Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> show binlog events in 'bin-log.000021';
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type| Server_id | End_log_pos | Info |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
| bin-log.000021 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| bin-log.000021 | 120 | Query | 1 | 235 | use `test`;>
| bin-log.000021 | 235 | Query | 1 | 357 | use `test`;> +----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
可以看到alter只有一个事件（query），不管是添加索引还是添加列。显式的begin，都是只有一个query 事件。这个是不是因为这alter是auto commit的原因。
2，statement格式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
2.1 insert
mysql> insert into city select * from world.City;
Query OK, 4079 rows affected, 2 warnings (0.05 sec)
Records: 4079Duplicates: 0Warnings: 2
mysql> show binlog events in 'bin-log.000024';
+----------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| Log_name | Pos | Event_type| Server_id | End_log_pos | Info |
+----------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
| bin-log.000024 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| bin-log.000024 | 120 | Query | 1 | 205 | BEGIN |
| bin-log.000024 | 205 | Query | 1 | 326 | use `test`; insert into city select * from world.City |
| bin-log.000024 | 326 | Xid | 1 | 357 | COMMIT /* xid=33 */ |
+----------------+-----+-------------+-----------+-------------+-------------------------------------------------------+
4 rows in set (0.00 sec)
这里语句模式的binlog的insert事件有3个事件，query－》begin，query－》statement，xid－》commit。这里的第二个query和row模式的events相比，table_map+writer(update/delete)_row事件换成了query事件。
2.2update/delete和insert 类似
2.3 其他
mysql> show binlog events in 'bin-log.000025';
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type| Server_id | End_log_pos | Info |
+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
| bin-log.000025 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
| bin-log.000025 | 120 | Query | 1 | 207 | use `test`; truncate city |
| bin-log.000025 | 207 | Query | 1 | 329 | use `test`;> +----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
对于truncate/alter语句模式下也是只有一个query事件。
最后找到了所有events的定义文件：
enum Log_event_type
{
/*
Every time you update this enum (when you add a type), you have to
fix Format_description_log_event::Format_description_log_event().
*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,/* Unused. Slave_log_event code has been removed. (15th Oct. 2010) */
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/*
NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same> */
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
/*
These event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.
*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
/*
These event numbers are used from 5.1.16 until mysql-trunk-xx
*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/*
Something out of the ordinary happened on the master
*/
INCIDENT_EVENT= 26,
/*
Heartbeat event to be send by master at its> to ensure master's online status to slave
*/
HEARTBEAT_LOG_EVENT= 27,
/*
In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.
*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
/* Version 2 of the Row events */
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
/*
Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers
*/
ENUM_END_EVENT /* end marker */
};
页:
[1]