List:Internals« Previous MessageNext Message »
From:a i Date:May 12 2011 8:13am
Subject:patch for forcing user vars into binlog
View as plain text  

I have a mysql 5.x server patch adding 2 minor features for optionally
injecting context information into binlog and wondering whether others feel
they are worthwhile adding to mysql. I used these to support "context" aware
auditing of write events using triggers on a db slave.

Features controlled by 2 options:

binlog-alias-user=<alias-var-name>
Alias USER() as specified user variable for each write command. Note that this
is intended to be used with binlog-force-user-var= in order
to transfer execution context information to a slave's SQL thread.

binlog-force-user-var =<var-name>
Ensure that the specified user variable is included in the binlog. To specify
more than one, use the directive multiple times, once for each user variable to
include. Note that this is intended to allow you to transfer command execution
context to a slave's SQL thread.

Here is outline of it can be used (only master server needs added features):

[master]
edit /etc/my.cnf

...
# xmit context information to binlog
binlog-alias-user=binlog_user
binlog-force-user-var=binlog_user
binlog-force-user-var=external_user
...


[slave]
add some triggers on mydb.mytable

...
CREATE `mytable_after_insert` TRIGGER UPDATE AFTER INSERT ON `mytable`
    FOR EACH ROW  BEGIN
        DECLARE event_id BIGINT DEFAULT 0;

        -- insert event
        INSERT INTO `mydb_audit`.`event`
        (`db_user`, `external_user`, `occured_at`, `target`, `type`)
        VALUES
        (@binlog_user, @external_user, NOW(), 'mytable', 'create');
        SELECT last_insert_id() INTO event_id;

        -- insert data linked to event
        INSERT INTO `mydb_audit`.`mytable`
        (`event_id`, `col1`,`col2`)
        VALUES
        (event_id, NEW.`col1`,NEW.`col2`);
    END
...

 		 	   		  
Thread
patch for forcing user vars into binloga i12 May