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 binlog | a i | 12 May |