This is a recurrent question made by our MySQL Support customers:
How can I best go about auditing login attempts in MySQL?
Logging all the attempts or just the failed ones is a very important task on some scenarios. Unfortunately, there are not too many audit capabilities in MySQL Community so the first option to audit MySQL’s authentication process is to get all the information we need from logs.
General Query Log
The first option is the General Query Log. Let’s see an example:
Enable the log:
1 2 | general_log_file = /var/log/mysql/mysql.log general_log = 1 |
User correctly authenticated:
1 2 | 121227 8:31:49 38 Connect root@localhost on 38 Query select @@version_comment limit 1 |
User not correctly authenticated:
1 2 | 121227 8:32:18 39 Connect root@localhost on 39 Connect Access denied for user 'root'@'localhost' (using password: YES) |
The problem of the General Query Log is that it will log everything so it can cause performance degradation and you will have to deal with very large files on high loaded servers. general_log variable is dynamic so a solution could be enabling and disabling the log just when it’s needed.
Error log
If you only care about failed attempts to login then there is another different and less problematic approach. From 5.5 it’s possible to log access denied messages to the error log.
We just need to enable log_warnings with a value greater than 1:
1 | log_warnings = 2 |
Then check the error log:
1 | 121227 8:44:21 [Warning] Access denied for user 'root'@'localhost' (using password: YES) |
User Statistics
If you are using Percona Server then there is a third option to get information about our users, the User Statistics. As with the previous options we can get the number of connections and failed connections made by a particular user but not the date and time of those attempts. Besides that information we can get other statistics that can be very useful if MySQL is running on a multi-tenant environment or we need to control how resources are used.
Let’s seen an example, first we enable User Statistics in my.cnf:
5.5
1 | userstat = 1 |
5.1
1 | userstat_running = 1 |
Then we get the information about a particular user:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql> select * from user_statistics where user='root'G *************************** 1. row *************************** USER: root TOTAL_CONNECTIONS: 25 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 464 BUSY_TIME: 96 CPU_TIME: 19 BYTES_RECEIVED: 62869617 BYTES_SENT: 14520 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 783051 ROWS_UPDATED: 1017714 TABLE_ROWS_READ: 1484751 SELECT_COMMANDS: 14 UPDATE_COMMANDS: 103 OTHER_COMMANDS: 3556 COMMIT_TRANSACTIONS: 0 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 2 LOST_CONNECTIONS: 16 ACCESS_DENIED: 0 EMPTY_QUERIES: 0 TOTAL_SSL_CONNECTIONS: 0 |
Here we can see that root has done 25 total connections. Two denied connections (bad password) and 16 lost connections (not closed properly). Apart from that information we get the connection time, bytes received and sent, rows accessed, commands executed and so on. Very valuable information.
It is important to mention that these tables are stored in INFORMATION_SCHEMA and that means that after a mysqld restart all the information will be lost. So if you really need that information you should copy it to another table or export to a csv for further analysis.
Conclusion
We don’t have too many audit capabilities in MySQL Community so logging all events and then filter them with custom-made scripts is the best solution we have nowadays. If you are using Percona Server you can get more detailed information about what a particular user is doing. All options can be combined to meet your needs.
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).
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.
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.
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
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
How can i detect login attempts month wise in php ???
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.
@Darren, you need to copy it to /usr/lib/mysql/plugin/
Thanks for writing this nice blog. Datasunrise provide the MySQL audit tool which analyzes incoming traffic and also block unauthorized access