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.

2 Answers 2


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.

  • Thanks for the info I'll try using log_warnings. If a user try changing the password using PASSWORD() it will have the plain text, isn't it?
    – Prakash
    Dec 18, 2012 at 3:05
  • Not in the error log, no. The solution I described enables logging of failed logins and aborted connections to the error log. Changing your password is a query that would only be logged to the general log if you had that log enabled. In MySQL 5.6.3 and above, even these are scrubbed from the general log and other places (for security) unless you use the new log-raw option introduced in that version. Dec 18, 2012 at 5:44
  • This can be enabled without restarting MySQL with: set global log_warnings=2;
    – A.Badger
    Jan 23, 2017 at 11:20

I recently came across a mysql audit plugin which (finally) logs the authentication requests into the server. It does require compiling some code and loading in the plugin, but it isn't overly difficult.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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