1

I wonder if there is an alternative for MySQL general query log. I would like to log (mostly) DDL statements, so I could trace what user DROP database/table etc. I know that enabling general query log may cause some performance degradation.

Questions:

  • can I log/filter DDL statements only with general query log
  • is there an option to specify a location on the file system for general query log
  • is there any other option/solution like logging to rsyslog for instance

Regards

EDIT: Fri Dec 6 10:57:31 GMT 2013

It looks like it's possible to set destination for general query log on the file system however I'm still looking for some alternative way due to performance impact.

  • You can set a cron which runs on top of SHOW PROCESSLIST and filter whatever you want. Not the best solution, I use it – georgecj11 Dec 6 '13 at 16:31
2

A)

Different methods of auditing MySQL server:

  • The error log

  • The slow query log

  • The binary log

  • Custom made triggers

  • Using MySQL Proxy

  • The general log - You can change general log file path to other path by editing value for this General log file variable in my.cnf

You can refer this blog post: http://serge.frezefond.com/2013/04/how-can-we-audit-a-mysql-server/

B)

Using MySQL audit plugins

There are audit plugins released by MySQL and some of the forks, you can try them if you are concerned about security on your MySQL server

1) MySQL Enterprise Audit

Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers.

2) McAfee Audit Plugin - Open source

designed with an emphasis on security and audit requirements. The plugin may be used as a standalone audit solution or configured to feed data to external monitoring tools.

3) MariaDB Audit plugin

Auditing regulations are used by many enterprises to ensure they comply with laws and industry standards. Such regulations often require processes for tracking user access to data in databases.

1

The fastest way to log (at least) DDLs, which will have the least performance impact would be using the binary log. It is stored in a binary format and written to the file system cache by default, so it does not add extra synchronous flush operations to disk. Typical overheads are around 5%, and this can be less if you use 5.6's binlog group commit, filtering at master level or playing around with the 2 main binlog formats (even in row mode, DDLs are stored in statement format). You can transform the binlogs into human-readable logs with the utility mysqlbinlog.

The problem with the binlog is that it doesn't trace usernames and hosts, so it may be useless for your purposes. In any case, activating the binary logs is almost always a good idea.

I think the best option here would be an audit plugin, as you are basically trying to do that (auditing queries and users). There are several options available, and you can even program one yourself, but I like the McAfee audit plugin, which is free, open-source and good enough for this case. Here it is a brief review on how to use it. You can configure it to monitor "drop"s (and/or any other command). This should be less costly than activating the general log (and more convenient), but I have not benchmarked it myself. It can log to a file or to a UNIX socket in json format. This is a sample output:

{"msg-type":"activity","date":"1369155747373","thread-id":"6439",
"query-id":"0","user":"debian-sys-maint",
"priv_user":"debian-sys-maint","host":"localhost",
"cmd":"Connect","query":"Connect"}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.