Log MySQL login attempts

From time to time there are failed login attempts in our MySQL production server (MySQL dashboard alerts us). Is there a way to log every single success and failed login to the MySQL server without enabling general_log?

We think general_log is not an option due it's a production server with high load.

just to inform the curious one: dig in your error log and presto!

(1). edit my.cnf (Settings documentation found here)

[mysqld]

:#Enter a name for the error log file. Otherwise a default name will be used.

log_error = /var/log/mysql/error

:#defaults to 1. If the value is > 1, aborted connections and access-denied errors for new connection attempts are written to the error log

log_warnings = 2

...

(2). at command run

$ sudo cat /var/log/mysql/error.err | egrep '[aA]ccess denied'

(3). and you have it!

(4). if you need to restrict the user (dos attack or mysql user password recovering attempt in a multi user database), then (http://dev.mysql.com/doc/refman/5.5/en/user-resources.html)

mysql> GRANT USAGE ON * . * TO 'attacker'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 100;

to restrict to only 100 password recovering attempts per hour.

I think the general log might log all login attempts (success and fail) among a lot of other things. The main problem is that the general log will affect your database's performance. You can turn on the general log with the query

SET GLOBAL general_log = 'on'

for newer versions of MySQL.

One can log connect and quit command by using mysql-audit-plugin.

1. Find the right version from mysql-audit-plugin release, I used mysql 5.7, so I used audit-plugin-percona-5.7-1.1.7-805-linux-x86_64.zip.
2. relocate the downloaded so file to the place given by mysqladmin variables | grep plugin_dir.
3. mysql>install plugin audit soname 'libaudit_plugin.so'
4. turning on log function, mysql>set global audit_json_file=ON, by default it logs all successful operations. by setting set global audit_record_cmds='quit,connect' it logs only connects and quits I suppose, according to mysql-audit-plugin configuration.

This is how it looks in file for login and logout:

{"msg-type":"activity","date":"1543740612328","thread-id":"1015112","query-id":"3045222","user":"root","priv_user":"skip-grants user","ip":"172.28.15.10","host":"172.28.15.10","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"11575","_client_version":"5.6.40","_platform":"x86_64","program_name":"mysql"},"cmd":"Quit","query":"Quit"} 

{"msg-type":"activity","date":"1543740724627","thread-id":"1015113","query-id":"0","user":"root","priv_user":"skip-grants user","ip":"172.28.15.10","host":"172.28.15.10","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"11863","_client_version":"5.6.40","_platform":"x86_64","program_name":"mysql"},"cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1543740724629","thread-id":"1015113","query-id":"3045223","user":"root","priv_user":"skip-grants user","ip":"172.28.15.10","host":"172.28.15.10","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"11863","_client_version":"5.6.40","_platform":"x86_64","program_name":"mysql"},"rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}

Hi I don't think it's possible.

As of mysql 5.1.29 - you can specify storage option (table or file) and location and which log you want - error, general, binary or slow query. As far as I know - you can't specify format of the log or what gets logged. I might be wrong - but I think that all login attemps will be logged in general log, and not the error one.

However, assuming your mysql server is running on a separate machine, from you applicaiton server, and you need port 3306 ( or whatever) open and you can't use ssh tunnel, you mysql server still shouldn't be accessible by anyone willy-nilly. I highly recommend not to expose it to web traffic, and if you must ( like in case of the residing somewhere not behind your firewall) bind it to the ip address or ip block of you application server and your admin access ip ( where you are accessing from)

Hope that helps.