Druckbetankung

... or how to misuse RBR to fill a MySQL table in the hopefully fastest way.

In my session yesterday I didn't had time to talk about this. The slide-desk covers it and I still wanted to document the idea at least.

The starting point is "Row Based Replication stores data in internal MySQL row-format." The slave can apply these events without any SQL parsing involved.

The idea is to create a master plugin that

takes a file or something else that you want to INSERT into the mysql-server
creates a binlog-stream using row-based replication events from it
expose the binlog-stream and pretend to be a mysql-master (listen on port :3306 and handle COM_BINLOG_DUMP)
let the MySQL-server connect to master-plugin using CHANGE MASTER TO and START SLAVE and fetch and apply the binlog-stream natively

The final implementation is in replication-tree.

The master plugin takes a lua script when does all the magic and will hide the details. To give you an idea what is going on behind the scenes a few snippets of Lua code, like how the different binlog-events are represented:

    {
            server_id = 2,
            type = "QUERY_EVENT",
            query = {
                    db_name = "test",
                    query = "DROP TABLE IF EXISTS pump"
            }
    },
    {
            server_id = 2,
            type = "QUERY_EVENT",
            query = {
                    db_name = "test",
                    query = "CREATE TABLE pump ( f1 CHAR(16) NOT NULL, f2 INT, f3 INT )"
            }
    },

generates the two QUERY_EVENTs to create the table we want to insert into:

    -- define a table-map event matching our CREATE TABLE from above
    {
            server_id = 2,
            type = "TABLE_MAP_EVENT",
            table_map = {
                    table_id = 1,
                    db_name = "test",
                    table_name = "pump",
                    fields = {
                            { type = proxy.MYSQL_TYPE_STRING, is_nullable = false },
                            { type = proxy.MYSQL_TYPE_LONG, is_nullable = true },
                            { type = proxy.MYSQL_TYPE_LONG }, -- is_null defaults to true
                    },
            }
    },
    -- full the table with 2 rows
    {
            server_id = 2,
            type = "WRITE_ROWS_EVENT",
            rbr = {
                    table_id = 1,
                    flags    = 0,
                    rows = {
                            {
                                    before  = { "abc", 2, 3 },
                            },
                            {
                                    before  = { "def", 3, 5 },
                            },
                    }
            }
    },

adds the two events that represent a INSERT statement for Row-based-replication-speak. The events are passed to the binlog-generator and are pass out as response to a COM_BINLOG_DUMP response:

local f = binlog.new()
f:seek(4)
... 
local event = f:to_event(event_tbl[event_ndx])
return "\000" ... f:append(event)

To give it a test we load the master-plugin and the script

    $ mysql-proxy \
    --plugins=master \
    --master-address=:4041 \
    --master-lua-script=master.lua

and let the MySQL Server connect to it:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO master_host='127.0.0.1', \
    master_port=4041, \
    master_user='root', \
    master_password='secret';
START SLAVE;
-- importing done ... let's verify it
USE test;
SHOW TABLES;
SELECT SLEEP(0.1); -- wait until the slave has caught up
SELECT * FROM pump;

outputs:

+-----+------+------+
| f1  | f2   | f3   |
+-----+------+------+
| abc |    2 |    3 |
| def |    3 |    5 |
+-----+------+------+

Comments