Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

5 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

if the server in question shouldn't have external connections, as configured, I would be worried about some sort of attack against your app server, unless the failed logins are from new applications being rolled out before the user/pass has been configured.

if the server is somehow exposed to outside connections on 3306, unless that is intentional and needed, i would set the configuration as Nick said, and also look at using iptables to restrict traffic to 3306 from just your app servers.

share|improve this answer
    
MySQL automatically denies connections from IP address that don't have at least one matching row in mysql.user –  David M May 14 '10 at 22:20
add comment

At http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ , the author shows a method to capture the packets using tcpdump , and filtering the output based on the string.

This works around your concerns regarding the general_log and performance, although tcpdump might itself incur a minor performance penalty. This solution will also log less data then the General Query Log.

I haven't used this myself, but it sounds very useful.

share|improve this answer
    
Sounds good, but the link does't work for me. –  CarlosH May 15 '10 at 19:08
    
The link works for me this morning, using Firefox. mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging –  Stefan Lasiewski May 17 '10 at 18:18
    
And I would be interested in hearing your experience with this. –  Stefan Lasiewski May 17 '10 at 18:19
add comment

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

(1). edit my.cnf

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

share|improve this answer
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.