Mysql – Saving every thesql user login attempts

auditlogsMySQLSecurity

Is there a way to save the username and the IP from which the user tries to login to a MySQL server??

Enabling general-log seems to log everything but that seems to write too much details taking more space and also there is a possibility of saving password details too hence it would be helpful if there are any other options available.

Best Answer

If you're concerned about failed logins, that implies that your server is accessible from the Internet. It shouldn't be.

There is, however, a way to get the information without resorting to the general log.

According to the manual you can trigger logging these failed connections to the error log with the system variable log_warnings:

If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.

This is a dynamic variable, but changing it at run-time does not appear to trigger the logging behavior. Add this to your my.cnf in the [mysqld] section:

log-warnings = 2

Restart the server daemon.

You should then start to see failed logins in your MySQL error log.

I don't know what you mean by "saving password details." The password is sent from the client to the server as...

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

...so you're not going to find the client's attempted password getting logged anywhere. The server doesn't know what password the client used. It only knows whether it used the correct one, by doing its own calculation of the hash the client would have returned if the client had known the right password.

Related Question