implement row replication binlog protocol

[sidorares]

Would be useful to be able to connect client as slave db (monitoring tools?)

http://dev.mysql.com/doc/internals/en/com-register-slave.html http://dev.mysql.com/doc/internals/en/com-binlog-dump.html http://dev.mysql.com/doc/internals/en/binlog-network-stream.html http://dev.mysql.com/doc/internals/en/binlog-event.html

[eugeneware]

I'll try to capture my thoughts.

1. I think that this API should return a stream.Readable
2. The data events should container whatever the ID of the transaction, the text of the query, and whatever the data is present for that entry/entries as POJS object.
3. I'm not sure how multiple transactions are represented, but the format should be easy enough to deal with if there is nesting, etc.

API-wise I'm thinking of something like:

obj.binLogStream(lastId).pipe(through2.obj(obj, enc, next) {
   console.log(obj);
   next();
});

My actual use case would be to run something like this in a http server, and then as INSERTS, UPDATES, DELETEs occurs, to then broadcast them to the browser through socket.io to indicate that the data has changed. To make building live databases like firebase possible on relational databases, or where you have a nosql database running in parallel to a relational database, and want to keep the nosql database in sync but get some of the benefits of the nosql database, eg: more scalable reads, high availability, full-text search, etc.

Me personally, I'd be replicating into leveldb and building a highly performant app based on the kinds of queries I want to perform.

I'd like to do something similar with the postgres wire protocol too at some stage, so would like the interface to be as general as possible is that's even achievable.

[sidorares]

more links https://github.com/jeremycole/mysql_binlog/blob/master/lib/mysql_binlog/binlog_event_parser.rb https://github.com/HupuInc/node-mysql-listener

[eugeneware]

Ooh, nice!

[sidorares]

I have nearly finished basic events - going to push it in ~ 1hr. Stay tuned :) Still can't find info on how binlog client is using checksums - had to disable it on server to be able to get events

[sidorares]

e.i currently only able to connect binlog stream after doing set global binlog_checksum='NONE'; on server

[eugeneware]

good luck!

[eugeneware]

I found this: https://dev.mysql.com/worklog/task/?id=2540

But perhaps we need to look through the mysql source.

[sidorares]

Yes, I've seen this too. Probably worth looking at server source.

[sidorares]

changed title to reflect progress. Keeping issue open for now to have links handy

[eugeneware]

FYI I did a quick lightning talk at Tim's singapore Talk.js meetup showing off the new feature. Now I'm working on a PEG.js SQL parser so I can more easily get the data out of SQL statements and into JSON.

[shtse8]

I noticed that the latest release is implemented

eventParsers[2] = QueryEvent;
eventParsers[4] = RotateEvent;
eventParsers[15] = FormatDescriptionEvent;
eventParsers[16] = XidEvent;

any plans to support updateRows, writeRows, deleteRows, tableMap and rotate?

[sidorares]

@shtse8 I see you started doing this in #685 , thanks!

[shtse8]

https://github.com/nevill/zongji/blob/master/lib/rows_event.js https://github.com/nevill/zongji/blob/master/lib/binlog_event.js

We can take a reference from the above codes. I believe MySQL can do reactive data well if we implement those four events.

@sidorares I would suggest adding the createBinlogStream() in the readme for people knowing mysql2 can support binlog event as connecting to master as a slave. It's a powerful functionality but I believe many of us don't know mysql2 can support doing it. I just know this after spending some time reading part of source code.