After getting the Druckbetankung stuff working and discussing the idea with the replication team we figured out that my presented approach has a nice hack-value, but otherwise is perhaps a bit too complicated. The same result can be achieved by a more simplified approach:
- turn our input data into a RBR based base64-encoded BINLOG-stmt using binlog
- use the
mysqlclient to push the data into the server
The BINLOG stmt is used by mysqlbinlog to pass the RBR-events through the mysql client to the mysql-server. Usually it wraps a FORMAT_DESCRIPTION_EVENT and the TABLE_MAP_EVENT and the *_ROWS_EVENTS. BUT ... it can handle any binlog-event. Just try
$ mysqlbinlog --base64-output=always
to see how that looks like.
A generated example looks like this:
# FORMAT_DESCRIPTION
BINLOG '5hX3SQ8CAAAAZgAAAGYAAAAAAAQANS4xLjI2LW15c3FsLXByb3h5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADmFfdJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC';
# DROP TABLE IF EXISTS ...
BINLOG '5hX3SQICAAAAPgAAAKQAAAAAAAEAAAAAAAAABAAAAAB0ZXN0AERST1AgVEFCTEUgSUYgRVhJU1RTIHB1bXA=';
# CREATE TABLE
BINLOG '5hX3SQICAAAAYgAAAAYBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AENSRUFURSBUQUJMRSBwdW1wICggZjEgVkFSQ0hBUigxNikgTk9UIE5VTEwsIGYyIElOVCwgZjMgSU5UICk=';
# BEGIN
BINLOG '5hX3SQICAAAAKgAAADABAAAAAAEAAAAAAAAABAAAAAB0ZXN0AEJFR0lO';
# INSERT ...
BINLOG '5hX3SQICAAAAUQAAAIEBAAAAAAEAAAAAAAAABAAAAAB0ZXN0AElOU0VSVCBJTlRPIHB1bXAgVkFMVUVTICggJ2l0IHdvcmtzJywgMSwgMiAp';
# TABLE_MAP_EVENT + WRITE_ROWS_EVENT
BINLOG '5hX3SRMCAAAALwAAALABAAAAAAEAAAAAAAAABHRlc3QABHB1bXAAAw8DAwIQAAY=
5hX3SRcCAAAANwAAAOcBAAAAAAEAAAAAAAkAAwcAA2FiYwIAAAADAAAAAANkZWYDAAAABQAAAA==';
# INSERT ...
BINLOG '5hX3SQICAAAATQAAADQCAAAAAAEAAAAAAAAABAAAAAB0ZXN0AElOU0VSVCBJTlRPIHB1bXAgVkFMVUVTICggJ2RvbmUnLCAxLCAyICk=';
# XID .. (aka COMMIT)
BINLOG '5hX3SRACAAAAGwAAAE8CAAAAALYKAAAAAAAA';
Feel free to execute it in your client. It will create a table test.pump and insert 4 rows into it. 2 with normal INSERT stmts, 2 with a RBR-WRITE_ROWS_EVENT.
To make it a loader interface what is faster then LOAD DATA INFILE or multi-row INSERT you have to convert all your data into WRITE_ROWS_EVENTs and push it. As the data is already in the native form it should/may be faster than the 2 other approaches.
Now the interesting question: how much faster is a WRITE_ROWS_EVENT with BASE64 encoding than a multi-row INSERT ?
Comments