Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 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.

share|improve this answer
    
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? –  pprakash Dec 18 '12 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. –  Michael - sqlbot Dec 18 '12 at 5:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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