Log MySQL DB changing queries and users

Question

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?

Answer 1

1. This sounds like a perfect use of TRIGGERS to me. You say you have transactions, so you must already be using InnoDB. Attach triggers on INSERT, UPDATE, and DELETE on the tables you care about, each one triggering an INSERT into a log table AFTER the table event. There's your SELECT filter, done. You also have total control over what you log and how.

   This is a higher level solution, closer to the application layer, not a low-level system log solution where you have an exact copy of the complete SQL query. But I think it does address some of your concerns.

   InnoDB Triggers have access to stored procedures and all the MySQL functions, so I presume you can find the user's login name in there somewhere. The log time is just NOW().

   You may end up with a lot of triggers to manage, depending on your number of tables, but it's just a copy/paste job for each one. I maintain the code for all triggers in a single reference SQL file along with the table definitions and views for that database.

   I've never tested triggers in a transaction-rich environment to make sure the trigger only fired after the transaction was committed. The docs are thin on this subject, but they do say, "An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully." Sounds conclusive to me. Now you also know you're not logging things that didn't actually work.

2. OR set up replication, even to another mysqld instance on the same box if necessary. Turn on your mysqld full query logging on the slave instance. Failed transactions don't get replicated, and neither do SELECTs.

Answer 2

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.

Answer 3

I and Joel Hanger answered this question back on June 29, 2011.

• My Answer
• Joel's Answer

Joel's answer may sound more palatable for your needs.

As for my answer, should you decide to use the general log as a table, you could do one of the following:

• Make mysql.general_log a MyISAM table, move the /var/log/mysql/mysql/general_log.MYD and /var/log/mysql/mysql/general_log.MYI to a separate data disk. Create symlinks for them in /var/log/mysql/mysql ovewr to the other data mount
• Make mysql.general_log a BLACKHOLE table and Create a Trigger to Write general_log entries into another table