Understanding MySQL Max_connect_errors

From: http://mysqlblog.fivefarmers.com/2013/08/08/understanding-max_connect_errors/

Perhaps like many users, I had certain assumptions about what Max_connect_errors really does–but in looking closely as P Art of investigating the new Performance_schema.  Host_cache table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I ' m writing this blog post-to-help others who hold similar misconceptions of the What's this option does.

Many, if not most, MySQL DBAs is familiar with "host blocked" errors:

C:\mysql-5.5.27-winx64>bin\mysql-utest_mce-p3307-h192.168.2.8error 1129 (HY000): Host ' Crowder ' is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts '

The solution to this problem are readily apparent from the error message–some DBAs might not even bother to glance at the  Documentation regarding this. Even those who do might miss the nuanced explanation of the root cause:

The value of the max_connect_errors system variable determines how many successive interrupted connection requests is permitted.

The use of "interrupted" are surely intentional here, and it's key to understanding the first point I'll make:

1. IT provides no meaningful protection against brute force access attacks

Truly. You can set max_connect_errors to any value, and it'll have exactly zero impact on somebody trying to brute F  Orce their to your system by guessing user names and passwords. It'll lock out a host if somebody does a dumb port scan the times successively without trying to log in, and who scans a  Port times? The useful information from a port scan are divulged in the initial scan:

1. MySQL is running on the specified port.
2. The version of MySQL is included in the handshake.
3. There is (or aren ' t) accounts configured to allow access from the client machine, based on error code.
4. The default authentication mechanism preferred by the server.

What's the use of scanning it a additional The times when you already has all the information is going to get?

2. Authentication Failures Reset the counter

Strange, but true.  does authentication failures not increment the host counter, they actually  reset it to zero  –along with all other errors and than handshake interruptions.  the only Thing that matters are whether the handshake was interrupted or not.  If it wasn ' t interrupted, it counts as "success" and reset the host counter–regardless of whether the end result is a successful connection or not.  so, if you WA NT to run a dumb port scanner more than times, just make sure you intersperse an actual connection attempt every Cles or so to rest the counter.  here ' s my testing of MySQL 5.5 behavior:

Mysql> SELECT @ @global. max_connect_errors;+-----------------------------+|                           @ @global. max_connect_errors |+-----------------------------+| 1 |+-----------------------------+1 row in Set (0.00 sec) mysql> EXITBYED:\MYSQL-5.5.28-WIN32>BIN\MYSQL-UHCT- P3308-h10.159.156.50-ptesterror 1129 (HY000): Host ' tfarmer-mysql.wh.oracle.com ' is blocked because of many connection E Rrors; Unblock with ' mysqladmin flush-hosts ' d:\mysql-5.5.28-win32>bin\mysqladmin-uroot-p3308 flush-hostsd:\ Mysql-5.5.28-win32>start telnet 10.159.156.50 3308d:\mysql-5.5.28-win32>bin\mysql-uhct-p3308-h10.159.156.50 -ptest-baderror 1045 (28000): Access denied for user ' hct ' @ ' tfarmer-mysql.wh.oracle.com ' (using Password:yes) D:\mysql-5 .5.28-win32>start telnet 10.159.156.50 3308d:\mysql-5.5.28-win32>bin\mysql-uhct-p3308-h10.159.156.50-  Ptestwelcome to the MySQL Monitor. Commands End With; or \g....mysql> exit byed:\mysql-5.5.28-win32>bin\mysqladmin-uroot-p3308 Flush-hostsd:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308d:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308d:\mysql-5.5.28-win32>bin\mysql-uhct-p3308-h10.159.156.50-ptesterror 1129 (HY000): Host ' Tfarmer-mysql.wh.oracle.com ' is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts '

3. All bets is off if you use–skip-name-resolve

Because This was all managed in the host cache, if you turn off reverse DNS lookups Using–skip-name-resolve–and many PEO PLE would to avoid potential DNS overhead in creation of new connections–max_connect_errors have zero effect.

4. Localhost and IP loopbacks is excluded

For the same reason as #3, you'll never see host blocked errors when connecting to localhost or via IP loopback interface.  These don ' t go through the DNS reverse lookup and thus the host cache, and is therefore not tracked at all. Whether That's good (nobody can lock up local access) or not, I'll let you decide.

5. The host cache is a fixed size

Marc Alff pointed out to me that the fixed size of the host Cache–along with the LRU purge algorithm used–makes it qui  Te possible this blocked hosts can fall out of the cache and cease to be blocked. that have pretty obvious implications for what it can be bypassed by any third party needing to do so.

Conclusion

If you is looking for a mechanism to limit exposure to brute-force attempts to access MySQL, Max_connect_errors won ' t hel  P you.  If you ' re worried about a SYN flood attack, Max_connect_errors might help you in very specific situations. Performance_schema improvements in MySQL 5.6 expose meaningful information about potential brute-force attacks, but again  –only in situations where the host cache is involved.  Beyond that, the contents of MySQL Enterprise Audit logs or general query log can is mined to identify such attacks. I filed several feature requests to give even more visibility through performance_schema and to provide a mechanism to res Trict access from the hosts based on number of failed authorization attempts.

Understanding MySQL Max_connect_errors