jeremycole/mysql_binlog

Library for parsing MySQL binary logs in Ruby

Library for parsing MySQL binary logs in Ruby

This library parses a MySQL binary log in pure Ruby and produces hashes as output, much like the following Query event:

{:type=>:query_event,
 :position=>107,
 :filename=>"mysql-bin.000001",
 :header=>
  {:event_type=>2,
   :server_id=>1,
   :flags=>[],
   :event_length=>117,
   :timestamp=>1340414127,
   :next_position=>224},
 :event=>
  {:thread_id=>1,
   :query=>"create table a (id int, a char(100), primary key (id))",
   :status=>
    {:sql_mode=>0,
     :charset=>
      {:character_set_client=>
        {:character_set=>:utf8, :collation=>:utf8_general_ci},
       :collation_connection=>
        {:character_set=>:utf8, :collation=>:utf8_general_ci},
       :collation_server=>
        {:character_set=>:latin1, :collation=>:latin1_swedish_ci}},
     :flags2=>[],
     :catalog=>"std"},
   :elapsed_time=>0,
   :error_code=>0,
   :db=>"test"}}

Status

All event types can be read, but may not be parsed, as not all event types are currently fully supported. Over time this will improve. The current status of event support is documented below.

Event Types

ID	Event Type	Status
1	start_event_v3	Unsupported (deprecated).
2	query_event	Fully supported with all fields parsed.
3	stop_event	Fully supported with all fields parsed.
4	rotate_event	Fully supported with all fields parsed.
5	intvar_event	Fully supported with all fields parsed.
6	load_event	Unsupported (deprecated).
7	slave_event	Unsupported (deprecated).
8	create_file_event	Unsupported (deprecated).
9	append_block_event	Unsupported.
10	exec_load_event	Unsupported (deprecated).
11	delete_file_event	Unsupported.
12	new_load_event	Unsupported (deprecated).
13	rand_event	Fully supported with all fields parsed.
14	user_var_event	Fully supported with all fields parsed.
15	format_description_event	Fully supported with all fields parsed.
16	xid_event	Fully supported with all fields parsed.
17	begin_load_query_event	Unsupported.
18	execute_load_query_event	Unsupported.
19	table_map_event	Fully supported with all fields parsed.
20	pre_ga_write_rows_event	Unsupported (deprecated).
21	pre_ga_update_rows_event	Unsupported (deprecated).
22	pre_ga_delete_rows_event	Unsupported (deprecated).
23	write_rows_event_v1	Fully supported with all fields parsed.
24	update_rows_event_v1	Fully supported with all fields parsed.
25	delete_rows_event_v1	Fully supported with all fields parsed.
26	incident_event	Unsupported.
27	heartbeat_log_event	Unsupported.
28	ignorable_log_event	Unsupported.
29	rows_query_log_event	Fully supported with all fields parsed.
30	write_rows_event_v2	Fully supported with all fields parsed.
31	update_rows_event_v2	Fully supported with all fields parsed.
32	delete_rows_event_v2	Fully supported with all fields parsed.
33	gtid_log_event	Fully supported with all fields parsed.
34	anonymous_gtid_log_event	Fully supported with all fields parsed.
35	previous_gtids_log_event	Fully supported with all fields parsed.
36	transaction_context_event	Unsupported.
37	view_change_event	Unsupported.
38	xa_prepare_log_event	Unsupported.
50	table_metadata_event	Specific to Twitter MySQL 5.5.24.t7+. Fully supported with all fields parsed.

Data Types Supported in Row Events

ID	SQL Data Type	MySQL Internal Type	Status
Numeric Types
1	TINYINT	MYSQL_TYPE_TINY	Fully supported.
2	SMALLINT	MYSQL_TYPE_SHORT	Fully supported.
9	MEDIUMINT	MYSQL_TYPE_INT24	Fully supported.
3	INT	MYSQL_TYPE_LONG	Fully supported.
8	BIGINT	MYSQL_TYPE_LONGLONG	Fully supported.
4	FLOAT	MYSQL_TYPE_FLOAT	Fully supported.
5	DOUBLE	MYSQL_TYPE_DOUBLE	Fully supported.
246	DECIMAL	MYSQL_TYPE_NEWDECIMAL	Fully supported using BigDecimal.
Temporal Types
7	TIMESTAMP	MYSQL_TYPE_TIMESTAMP	Fully supported.
17	TIMESTAMP(n)	MYSQL_TYPE_TIMESTAMP2	Fully supported.
12	DATETIME	MYSQL_TYPE_DATETIME	Fully supported.
18	DATETIME(n)	MYSQL_TYPE_DATETIME2	Fully supported.
10	DATE	MYSQL_TYPE_DATE	Fully supported.
14	DATE	MYSQL_TYPE_NEWDATE	Unsupported.
11	TIME	MYSQL_TYPE_TIME	Fully supported.
19	TIME(n)	MYSQL_TYPE_TIME2	Unsupported.
13	YEAR	MYSQL_TYPE_YEAR	Fully supported.
String Types
15 253 254	CHAR VARCHAR	MYSQL_TYPE_STRING	Fully supported.
249 252 250 251	TINYBLOB BLOB MEDIUMBLOB LONGBLOB	MYSQL_TYPE_BLOB	Fully supported.
Other Types
247	ENUM	MYSQL_TYPE_STRING	Supported, but values returned are internal representations.
248	SET	MYSQL_TYPE_STRING	Supported, but values returned are internal representations.
16	BIT	MYSQL_TYPE_BIT	Supported, treated as integer of appropriate size.
255	GEOMETRY	MYSQL_TYPE_GEOMETRY	Supported, treated as BLOB.
245	JSON	MYSQL_TYPE_JSON	Supported, treated as BLOB.