Mysql – Log MySQL DB changing queries and users

logsMySQL

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?

Best Answer

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.
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.

Related Solutions

Mysql – How to output MySQL logs to syslog

This is MUCH more simply done this way:

cd to mysql folders:

mkfifo mysql-general.log

in my.cnf tell it:

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Then, configure your syslog/syslog-ng to read the FIFO pipe and do with it as it will..

In my case, I pipe it across net to a centralized server with only the error logs and slow query logs however.

In situations where you want to also keep local copy, just set it to output to table and file as described above.

Mysql – How to enable MySQL general log

The flags you need to activate the general log and the slow on in the MySQL Documentation

log (to activate the general log)
general log (to name the log file)
slow query log (to activate the slow log)
log output (specify either FILE or TABLE for where logging info is written)

I would recommend using log-output=TABLE

It will save the logging of general log and slow log info to MySQL tables

mysql.general_log
mysql.slow_log

You should convert them to MyISAM. That way, you can index the tables by the timestamp and perform SELECT queries from them.

By default, these files use the CSV storage engine.

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.09 sec)

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.05 sec)

mysql>

Convert the general log and index it by event_time:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

Convert the slow log and index it by start_time:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – How to output MySQL logs to syslog

Mysql – How to enable MySQL general log

Related Question