Comments on: Auditing login attempts in MySQL

By: Levi Brereton

Levi Brereton — Tue, 28 Mar 2017 06:27:53 +0000

Thanks for writing this nice blog. Datasunrise provide the MySQL audit tool which analyzes incoming traffic and also block unauthorized access

By: shan

shan — Thu, 04 Aug 2016 18:17:45 +0000

@Darren, you need to copy it to /usr/lib/mysql/plugin/

By: Darren

Darren — Wed, 26 Aug 2015 22:22:22 +0000

Thanks!
audit_login.so looks great, but fails on Debian 5.5.44-37.3-log Percona Server:
[ERROR] Can’t open shared library ‘/usr/lib/mysql/plugin/audit_login.so’ (errno: 0 /usr/lib/mysql/plugin/audit_login.so: invalid ELF header)

Percona has thier own audit_log.so plugin, but it needs some documentation.

By: Zaheer Abbas

Zaheer Abbas — Wed, 18 Mar 2015 12:00:50 +0000

How can i detect login attempts month wise in php ???

By: Shlomi Noach

Shlomi Noach — Wed, 18 Sep 2013 06:50:54 +0000

Shameless plug: you can now use the audit_login plugin, to find out about failed or successful logins; where they came from; attempted user credentials etc.
http://code.openark.org/blog/mysql/introducing-audit_login-simple-mysql-login-logfile-based-auditing
https://github.com/outbrain/audit_login

By: Marcos Albe

Marcos Albe — Fri, 15 Feb 2013 16:59:40 +0000

Comment from Daniel: if you tcpdump your mysql traffic, pt-query-digest >= 2.1.9 can detect failed connections now. See bug https://bugs.launchpad.net/percona-toolkit/+bug/1103045

By: Troy Davis

Troy Davis — Mon, 14 Jan 2013 22:36:52 +0000

One more avenue for tailing and searching slow queries, especially with more than one MySQL server, more than one person who cares, or queries that you need to find a day or two later: http://help.papertrailapp.com/kb/configuration/configuring-centralized-logging-from-mysql-query-logs

You can use MySQL’s native syslog support (5.1.20 and later) and send it through the system syslog daemon, or keep logging to a standalone log file with either the system syslogd or a transmitter like remote_syslog (https://github.com/papertrail/remote_syslog). Papertrail gives you the remote syslog target.

I operate it and the standard first thing that I suggest is a nightly email containing slow queries: http://help.papertrailapp.com/kb/how-it-works/alerts. Just getting something pushed out on a regular basis seems to help a ton.

By: Slava Gritsenko

Slava Gritsenko — Fri, 04 Jan 2013 15:52:59 +0000

Some time ago I had a need to temporary log successful connections without using regular log.
I did it in the following way:

Wrote UDF function which accepted 4 arguments: user(),current_user(),now(),connection_id().
Results should be stored into the custom log file.
Finally, defined init-connect to execute this UDF.

PS. I almost don’t have users with SUPER privilege, so this way was worked fine for me.

By: Mark Leith

Mark Leith — Fri, 28 Dec 2012 22:22:53 +0000

There is also the option of creating a specific log with an Audit API plugin using the MYSQL_AUDIT_CONNECTION_CLASS event class:

http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html

This should give less overhead than the general log approach, and more detailed stats than the user statistics approach (which is generally required for proper auditing).