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-
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 ?