Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out

• the General Query Log because of performance issues (it logs everything and that's too much IO)

• the Binary Log because it doesn't log the user's name.

• use a tool like ngrep to catch the network traffic and filter for UPDATE, DELETE etc. because this will get me in a mess with transactions and I can't know if a received query has really been executed.

I couldn't find any settings that would have let me change the behavior of the MySQL-inherent logs, so I'm looking for other solutions. I've come up with two possibilities so far:

• write the general query log to a named pipe and attaching a filter and writer to the other end of the pipe – but I'm concerned about the performance of this…

• transmitting the relevant logs separately to the server but that way I'd have to send the queries twice (once for the DB and again for logging), it would be difficult to assure the logs are in sync with the DB (transactions, locks etc.), and for security reasons it may not be wise to trust the client to really send the logs

Backgound:

• the users access the DB via a Java Desktop Application that opens an SSH tunnel to the MySQL server

• I'm using EclipseLink as persistence provider

• the application makes heavy use of transactions

• the server is running in a shared environment

Do you have a better idea on how to perform my logging?

You could utilize the binary log method by adding a init_connect to log the user into a table*.

CREATE TABLE admin.connections (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    connect_time DATETIME NOT NULL, 
    user_host VARCHAR(50) NOT NULL, 
    connection_id INT UNSIGNED NOT NULL, 
    INDEX idx_connect_time_user_host (connect_time, user_host)
);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user_host, connection_id) 
     VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

Some caveats:

-
Most importantly, this will only work for users without the SUPER privilege, since the init_connect is bypassed for those users.

-
Make sure all your users have INSERT privilege into the admin.connections table

You can then use the values in admin.connections to match up with the binary_log threadID and timestamp

*This idea was taken from here and I haven't personally tried it.

Originally this had a unique index on connect_time/user_host. But this would cause issues with same user connecting in same second. If that is not a problem in your scenario, leave it that way. If it is an issue, just make it a normal index.

StackExchange Database Administrators Q#7985, answer score: 4