转 -- MySQL UNDERSTANDING MAX_CONNECT_ERRORS
来源:互联网 发布:手机看最新电影软件 编辑:程序博客网 时间:2024/06/09 02:26
原址如下:
http://mysqlblog.fivefarmers.com/2013/08/08/understanding-max_connect_errors/
UNDERSTANDING MAX_CONNECT_ERRORS
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part 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 what this option does.
Many, if not most, MySQL DBAs are 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 is 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 are permitted.
The use of “interrupted” is 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 you please, and it will have exactly zero impact on somebody trying to brute force their way into your system by guessing user names and passwords. It will lock out a host if somebody does a dumb port scan 100 times successively without trying to log in, but who scans a port 100 times? The useful information from a port scan is divulged in the initial scan:
- MySQL is running on the specified port.
- The version of MySQL is included in the handshake.
- There are (or aren’t) accounts configured to allow access from the client machine, based on error code.
- The default authentication mechanism preferred by the server.
What’s the use of scanning it an additional 99 times when you already have all the information you are going to get?
2. Authentication failures reset the counter
Strange, but true. Not only do authentication failures not increment the host counter, they actuallyreset it to zero – along with all other errors other than handshake interruptions. The only thing that matters is 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 was a successful connection or not. So, if you want to run a dumb port scanner more than 100 times, just make sure you intersperse an actual connection attempt every 99 cycles 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 errors; 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 are off if you use –skip-name-resolve
Because this is all managed in the host cache, if you turn off reverse DNS lookups using –skip-name-resolve – and many people will to avoid potential DNS overhead in creation of new connections – max_connect_errors has zero effect.
4. Localhost and IP loopbacks are 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 are 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 quite possible that blocked hosts can fall out of the cache and cease to be blocked. That has pretty obvious implications for how it can be bypassed by any third party needing to do so.
Conclusion
If you are looking for a mechanism to limit exposure to brute-force attempts to access MySQL, max_connect_errors won’t help 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 log or general query log can be mined to identify such attacks. I filed several feature requests to give even more visibility through PERFORMANCE_SCHEMA and to provide amechanism to restrict access from hosts based on number of failed authorization attempts.
- 转 -- MySQL UNDERSTANDING MAX_CONNECT_ERRORS
- 关于mysql参数max_connect_errors
- Max_connect_errors – MySQL性能参数详解
- Max_connect_errors – MySQL性能参数详解
- Understanding MySQL Internals
- msyql系统变量max_connect_errors
- TRULY Understanding ViewState ( 转)
- Understanding
- Understanding MySQL Internals 2:模块图解
- Understanding MySQL Internals 3:搭建环境
- Understanding “Prototypes” in JavaScript【转】
- Understanding MySQL Internals 1:学生应该如此这般的学习mysql
- 好书推荐-英文原版【Understanding MySQL Internals】(附下载地址)
- [转]Understanding of Objects-Oriented, Classes, Instances
- 理解ruby中的标志(Understanding Ruby Symbols)(转)
- [转自Mellanox官网]Understanding BIOS Configuration for Performance Tuning
- Understanding JBoss
- Understanding Statements
- 【HDU】5296 Annoying problem【树链剖分+分类讨论】
- Android New Relic 4.244 Agent 安装问题
- Oracle中的延迟块的清除方法
- Arraylist.addall(c) 方法
- JSVC启动Tomcat
- 转 -- MySQL UNDERSTANDING MAX_CONNECT_ERRORS
- ReccylerView添加header和footer
- listview复用导致内容错乱的问题
- 查看硬件信息
- 【Android应用开发技术:用户界面】设备适配
- jsp引用js和css路径问题
- WIDECHARTOMULTIBYTE和MULTIBYTETOWIDECHAR函数
- ORA-01033: ORACLE initialization or shutdown in progress (DBD ERROR: OCISessionBegin)
- js继承详解