30

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.

2

6 Answers 6

26

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

Edit my.cnf (Settings documentation found here)

MySQL 5

[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

...

MySQL 8

The name of the config variable changed; edit the same file, but add

log_error_verbosity=2

Restart MySQL Server

Restart mysql (e.g. on systems using systemd):

# e.g. on Ubuntu the mysql server service name is "mysql"
sudo systemctl restart mysql

Search the log file for failed login attempts

$ sudo cat /var/log/mysql/error.err | egrep '[aA]ccess denied'

And you have it!

Restricting a user

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.

1
  • 1
    MySQL 8.0 now uses "log_error_verbosity" instead of "log_warnings". Levels are 1=ERROR, 2=ERROR, WARNING, and 3=ERROR, WARNING, INFORMATION
    – stldoug
    Jul 21, 2022 at 21:14
4

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.

2
  • 1
    This is temporary or permanent?, if it is temporary that means that i need to modify /etc/mysql/my.cnf of mysql? Aug 10, 2017 at 23:22
  • 1
    It is temporary. That approach can work, or you can add --general-log to the flags when you start MySQL.
    – rmc00
    Aug 14, 2017 at 15:08
2

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"}
5
  • And where can we find the files?
    – f4d0
    Nov 22, 2021 at 17:52
  • @f4d0 You mean so file? mysql-audit-plugin-release refers to a link. I downloaded from there. If that link is not available anymore, perhaps you can google zip file.
    – Tiina
    Nov 23, 2021 at 1:10
  • I mean the file that contains the login and logout entries (json look like). It is the last example you add to your answer.
    – f4d0
    Nov 23, 2021 at 14:36
  • 1
    @f4d0 it should be located in the same folder as table data. Or maybe you can search for it by find . -name "mysql-audit.json" unless you changed json file name.
    – Tiina
    Nov 24, 2021 at 2:42
  • Thanks @Tiina, very useful. :)
    – f4d0
    Nov 24, 2021 at 17:49
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.

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.

1
  • 1
    MySQL automatically denies connections from IP address that don't have at least one matching row in mysql.user
    – David M
    May 14, 2010 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.

3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .