25

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.

20

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.

| improve this answer | |
2

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.

| improve this answer | |
  • This is temporary or permanent?, if it is temporary that means that i need to modify /etc/mysql/my.cnf of mysql? – DarckBlezzer Aug 10 '17 at 23:22
  • It is temporary. That approach can work, or you can add --general-log to the flags when you start MySQL. – rmc00 Aug 14 '17 at 15:08
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.

| improve this answer | |
1

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"}
| improve this answer | |
0

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.

| 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
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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