Practical P_S: From which hosts are connections being attempted?

MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection.  The manual describes potential causes as follows:

It goes on to make the following statement:

If these kinds of things happen, it might indicate that someone is trying to break into your server! Messages for these types of problems are logged to the general query log if it is enabled.

While not explicitly stated here in the manual, one can also use the MySQL Enterprise Audit plugin to get additional information, instead of the general query log.  This is a commercial-license feature of MySQL Enterprise subscriptions, but like all MySQL commercial products, it is available to download for evaluation purposes from Oracle Software Delivery Cloud.

Here’s an example of a failed login – I’m trying here to connect to my own external IPv6 address, and all user accounts on this instance are restricted to the loopback interface (localhost, ::1, 127.0.0.1):

D:\mysql-advanced-5.6.11-win32>bin\mysql -hfe80::205:9aff:fe3c:7a00%23 -P3308
ERROR 1130 (HY000): Host 'fe80::205:9aff:fe3c:7a00%23' is not allowed to connect to this MySQL server

We can see that aborted_connects was incremented:

mysql> SHOW GLOBAL STATUS LIKE 'aborted_connects';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_connects | 1     |
+------------------+-------+
1 row in set (0.00 sec)

Here’s what this looks like in the output for MySQL Enterprise Audit plugin:

<AUDIT_RECORD TIMESTAMP="2013-07-19T16:50:11" NAME="Connect" CONNECTION_ID="2" 
  STATUS="1130" USER="" PRIV_USER="" OS_LOGIN="" PROXY_USER="" 
  HOST="" IP="fe80::205:9aff:fe3c:7a00%23" DB=""/>
<AUDIT_RECORD TIMESTAMP="2013-07-19T16:50:11" NAME="Quit" CONNECTION_ID="2" STATUS="0"/>
<AUDIT_RECORD TIMESTAMP="2013-07-19T17:29:24" NAME="Query" CONNECTION_ID="1" STATUS="0" 
  SQLTEXT="SHOW GLOBAL STATUS LIKE 'aborted_connect'"/>

It’s also easy to search the audit records for such events, using mysqlauditgrep from MySQL Utilities:

mysqluc> mysqlauditgrep --event-type=Connect \ 
  D:\\mysql-advanced-5.6.11-win32\\data\\audit.log --status=1130 --format=VERTICAL
*************************       1. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:43:51
            IP: fe80::21f:3bff:fe82:bb05%4
          NAME: Connect
 CONNECTION_ID: 2
*************************       2. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:43:59
            IP: fe80::21f:3bff:fe82:bb05%4
          NAME: Connect
 CONNECTION_ID: 4
*************************       3. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:47:02
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 5
*************************       4. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:50:11
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 2
*************************       5. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:31:30
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 3
*************************       6. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:31:56
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 4
*************************       7. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:33:50
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 5
7 rows.

You can also easily filter for “any connection attempt that produced an error”, if you want to look beyond error 1130 – say including attempts which use incorrect passwords:

D:\mysql-advanced-5.6.11-win32>bin\mysql -uroot -P3308 -pnotmypassword
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysqluc> mysqlauditgrep --event-type=Connect \
  D:\\mysql-advanced-5.6.11-win32\\data\\audit.log --status=1-9999 --format=VERTICAL
*************************       1. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:43:51
            IP: fe80::21f:3bff:fe82:bb05%4
          NAME: Connect
 CONNECTION_ID: 2
          HOST: None
          USER: None
*************************       2. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:43:59
            IP: fe80::21f:3bff:fe82:bb05%4
          NAME: Connect
 CONNECTION_ID: 4
          HOST: None
          USER: None
*************************       3. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:47:02
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 5
          HOST: None
          USER: None
*************************       4. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T16:50:11
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 2
          HOST: None
          USER: None
*************************       5. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:31:30
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 3
          HOST: None
          USER: None
*************************       6. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:31:56
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 4
          HOST: None
          USER: None
*************************       7. row *************************
        STATUS: 1130
     TIMESTAMP: 2013-07-19T17:33:50
            IP: fe80::205:9aff:fe3c:7a00%23
          NAME: Connect
 CONNECTION_ID: 5
          HOST: None
          USER: None
*************************       8. row *************************
        STATUS: 1045
     TIMESTAMP: 2013-07-19T18:18:59
            IP: ::1
          NAME: Connect
 CONNECTION_ID: 7
          HOST: localhost
          USER: root
8 rows.

But enough about Enterprise Audit plugin – how else can you get information about which hosts initiate connections which fail to authenticate?  As the manual states, we can find some information about this in the general log:

 

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.02 sec)

mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot -hfe80::205:9aff:fe3c:7a00%23
ERROR 1130 (HY000): Host 'fe80::205:9aff:fe3c:7a00%23' is not allowed to connect
 to this MySQL server

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
...
mysql> SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

mysql>

And here’s what shows in the general query log:

130719 10:31:46	    1 Query	SELECT 1
130719 10:33:31	    1 Quit	
130719 10:33:55	    6 Connect	root@localhost on 
		    6 Query	select @@version_comment limit 1
130719 10:33:58	    6 Query	SELECT 2

That’s interesting – there’s no record of the connection attempt. There is, however, a record when the wrong password is used:

130719 11:18:59	    7 Connect	root@localhost on 
		    7 Connect	Access denied for user 'root'@'localhost' (using password: YES)

So the general query log is useful for identifying some – but not all – failed connection attempts (this is true regardless of the setting of –log-warnings). Fortunately, there are some tools in PERFORMANCE_SCHEMA in MySQL 5.6 which provide a bit more.

The PERFORMANCE_SCHEMA.HOSTS table seems promising, based on the following manual description:

The hosts table contains a row for each host from which clients have connected to the MySQL server. For each host name, the table counts the current and total number of connections.

Unfortunately, there’s a couple of things to note about this table:

  1. Internal threads also appear here, not just external connections.  These internal threads all show with a NULL value in the HOST column.
  2. The table has columns for CURRENT_CONNECTIONS and TOTAL_CONNECTIONS, but nothing indicating number of failed connections.
  3. Most importantly, the connection shows with a NULL HOST value until the authentication is complete.  I’m hopeful this will be seen as a bug and fixed in 5.6, but if not, adding the actual (and known) HOST value for unauthenticated users seems a useful feature request.

Taken together, these three aspects really limit the ability of the HOSTS table to help us, here.  We can’t really tell how many of the NULL hosts are internal threads vs. failed connection attempts, and we can’t tell from which host a connection attempt was made.  On the other hand, it does provide a meaningful list of all hosts from which valid connections have been established.  That can come in handy later.  Here’s a quick example of how the row with NULL values for HOST gets incremented for each failed connection:

mysql> SELECT * FROM performance_schema.hosts\G
*************************** 1. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 20
  TOTAL_CONNECTIONS: 21
*************************** 2. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 1
  TOTAL_CONNECTIONS: 3
2 rows in set (0.00 sec)

mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -hfe80::205:9aff:fe3c:7a00%23 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'fe80::205:9aff:fe3c:7a00%23'
(using password: NO)

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -hfe80::205:9aff:fe3c:7a00%23 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'fe80::205:9aff:fe3c:7a00%23'
(using password: NO)

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> SELECT * FROM performance_schema.hosts\G
*************************** 1. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 20
  TOTAL_CONNECTIONS: 23
*************************** 2. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 1
  TOTAL_CONNECTIONS: 4
2 rows in set (0.00 sec)

It’s worth noting that the HOST column will show as NULL even if the connection authenticates just fine, but is rejected during the authentication phase for other reasons, such as the authenticated user account doesn’t have access to the requested default database, or because it’s using an account with an expired password and a client which doesn’t support that capability.

The next table that might help us is the PERFORMANCE_SCHEMA.HOST_CACHE table.  This is a useful addition in MySQL 5.6 – it allows DBAs to monitor the host cache contents, especially to see whether hosts are at risk of being blocked because of excessive connection failures (see documentation on –max-connect-errors).  Unlike the HOSTS table, HOST_CACHE has counters for failed connections only – so it will show failed connection attempts – sometimes.  Like the HOSTS table, the HOST_CACHE table also has a couple of caveats:

  1. It will be empty if you are running with –skip-name-resolve, since the purpose of the host cache is to avoid repeated reverse DNS lookups for the same host.
  2. It doesn’t track connections when name resolution is not required – specifically, it does not capture information on localhost and loopback interfaces.

That means that you typically can’t use the HOST_CACHE table to identify potential problems originating on the same host.  But if you are looking for potential signs that unexpected remote hosts have reached your MySQL Server, this is a good place to look.  Here’s an example output from my earlier testing:

mysql> SELECT * FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 7
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 09:50:11
                                 LAST_SEEN: 2013-07-19 12:46:08
                          FIRST_ERROR_SEEN: 2013-07-19 09:50:11
                           LAST_ERROR_SEEN: 2013-07-19 12:46:08
1 row in set (0.00 sec)

 

There’s a helpful breakdown of different error classes, so that you can isolate out potential root causes.  In our case, COUNT_HOST_ACL_ERRORS was incremented since no user account is allowed to connect from that host.  Note also that COUNT_NAMEINFO_PERMANENT_ERRORS shows 1 – that’s telling us that MySQL couldn’t get a host name from a reverse DNS lookup.  Let’s create an account that can connect from any host, and see what happens:

mysql> CREATE USER test_hc@'%' IDENTIFIED BY 'T3stP@ss';
Query OK, 0 rows affected (0.03 sec)

mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.host_cache\G
Empty set (0.00 sec)

mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -hfe80::205:9aff:fe3c:7a00%23 -utest_hc -pT3stP@ss
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.11-enterprise-commercial-advanced MySQL Enterprise Server -
Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 13:19:16
                                 LAST_SEEN: 2013-07-19 13:19:16
                          FIRST_ERROR_SEEN: 2013-07-19 13:19:16
                           LAST_ERROR_SEEN: 2013-07-19 13:19:16
1 row in set (0.00 sec)

mysql> exit
Bye

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -hfe80::205:9aff:fe3c:7a00%23 -utest_hc -pbadpassword
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_hc'@'fe80::205:9aff:fe3c:7a00%23' (using password: YES)

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.6.11-enterprise-commercial-advanced MySQL Enterprise Server -
Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 1
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 13:19:16
                                 LAST_SEEN: 2013-07-19 13:20:01
                          FIRST_ERROR_SEEN: 2013-07-19 13:19:16
                           LAST_ERROR_SEEN: 2013-07-19 13:20:01
1 row in set (0.00 sec)

We can see that COUNT_AUTHENTICATION_ERRORS was incremented, as we failed to authenticate. Also note that COUNT_HOST_BLOCKED_ERRORS remains zero – the documentation again explains why:

The number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Currently, only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES).

So users entering the wrong password don’t count towards max_connect_errors.  Demonstrating another type of connection error – which again doesn’t increment max_connect_errors – here’s what happens when the test_tc user attempts to connect with a default database of “mysql” (for which it has no privileges):

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -hfe80::205:9aff:fe3c:7a00%23 -utest_hc -pT3stP@ss mysql
Warning: Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'test_hc'@'%' to database 'mysql'

D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
...
mysql> SELECT * FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 1
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 1
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 13:19:16
                                 LAST_SEEN: 2013-07-19 13:26:03
                          FIRST_ERROR_SEEN: 2013-07-19 13:19:16
                           LAST_ERROR_SEEN: 2013-07-19 13:26:03
1 row in set (0.00 sec)

Now we see COUNT_DEFAULT_DATABASE_ERRORS incremented. So what would cause SUM_CONNECT_ERRORS to increment? A connection which doesn’t complete the authentication handshake – such as a port scanner or telnet. Here’s what HOST_CACHE looks like after two consecutive connections using telnet:

D:\mysql-advanced-5.6.11-win32>telnet fe80::205:9aff:fe3c:7a00%23 3308
...
D:\mysql-advanced-5.6.11-win32>telnet fe80::205:9aff:fe3c:7a00%23 3308
...
D:\mysql-advanced-5.6.11-win32>bin\mysql -P3308 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

mysql> SELECT * FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 2
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 2
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 1
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 1
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 13:19:16
                                 LAST_SEEN: 2013-07-19 13:32:14
                          FIRST_ERROR_SEEN: 2013-07-19 13:19:16
                           LAST_ERROR_SEEN: 2013-07-19 13:32:25
1 row in set (0.00 sec)

Also note that COUNT_HANDSHAKE_ERRORS was incremented, which is a telltale sign of a “dumb” port scan, client protocol incompatibility, or network issues.  A good place to start looking for potential security problems might be:

  1. Those rows in HOST_CACHE where COUNT_HANDSHAKE_ERRORS > 0, as this might indicate a port scan.
  2. Those rows in HOST_CACHE where COUNT_HOST_ACL_ERRORS > 0, as this might indicate an attempted connection from an unauthorized host (check firewall to make sure appropriate restrictions apply).
  3. Those rows in HOST_CACHE for which corresponding rows in the HOSTS table cannot be found.

The goal of the third criteria is to ignore those hosts from which valid connections are made, but perhaps have a handful of isolated failures or mistaken configurations at one point, and isolate out those hosts from which only failed connection attempts have been made.  It’s not quite as easy as it might seem, as there’s a HOST and IP address in the HOST_CACHE table, but only a HOST column – which can have the host name or IP address in it – in the HOSTS table.  The following query works:

mysql> SELECT * FROM performance_schema.hosts\G
*************************** 1. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 20
  TOTAL_CONNECTIONS: 21
*************************** 2. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 1
  TOTAL_CONNECTIONS: 2
2 rows in set (0.00 sec)

mysql> SELECT hc.*
    -> FROM
    ->  performance_schema.host_cache hc
    -> LEFT JOIN
    ->  performance_schema.hosts h1
    ->    ON (h1.host = hc.host)
    -> LEFT JOIN
    ->  performance_schema.hosts h2
    ->    ON (h2.host = hc.ip)
    -> WHERE h2.host IS NULL
    ->   AND h1.host IS NULL\G
*************************** 1. row ***************************
                                        IP: fe80::205:9aff:fe3c:7a00%23
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 1
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2013-07-19 13:56:37
                                 LAST_SEEN: 2013-07-19 13:56:37
                          FIRST_ERROR_SEEN: 2013-07-19 13:56:37
                           LAST_ERROR_SEEN: 2013-07-19 13:56:37
1 row in set (0.00 sec)

By using PERFORMANCE_SCHEMA tables in MySQL Server 5.6, you can get visibility into failed connection attempts not recorded in the general query log.

2 thoughts on “Practical P_S: From which hosts are connections being attempted?

  1. Many thanks for the description.

    I encountered the following behavior in (5.6.12) host_cache :
    The COUNT_HANDSHAKE_ERRORS field value is growing for only one given host. This causes the value of SUM_CONNECT_ERROR field to grow also, and brings finally to that host to be blocked.

    Is there any way to investigate the reason of COUNT_HANDSHAKE_ERRORS ?

    best regards,
    Avi

    1. Hi Avi,

      Glad to hear you found this information useful. Unfortunately, there’s not much further information around COUNT_HANDSHAKE_ERRORS. These represent network connections established (so server sent handshake packet), but for which the client did not complete the handshake properly. That typically means the server was never given the user name (probably the next item of interest beyond the client host, which you already know). Some “dumb” monitoring scripts may do this; they open a connection to the MySQL server to check that it’s responding, but never log in. With 5.6.12 (because of Bug#69807

Leave a Reply to Todd Farmer Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.