使用連線控制插件提高 MySQL 安全性

這個與帳號資源限制的 MAX_USER_CONNECTIONS 與 MAX_CONNECTIONS 是不同的

MAX_USER_CONNECTIONS 與 MAX_CONNECTIONS 是指定最大上限的數量

而 connection_control 沒有連線數量的上限，而是讓連線失敗的一直增加被重試的 timeout 時間

也就是下次連線時間會一直增加

# 怎麼設定

vi /etc/my.cnf

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
connection_control_min_connection_delay         = 1000  # 連線失敗後，每次連線最小 delay 時間(毫秒)
connection_control_max_connection_delay         = 90000 # 連線失敗後，每次連線最大 delay 時間(毫秒)
connection_control_failed_connections_threshold = 3     # 允許連線失敗的次數

systemctl restart mysqld

# 驗證是否 work

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';

+------------------------------------------+---------------+
| PLUGIN_NAME                              | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+

show global variables like 'connection_control%';

+-------------------------------------------------+----------------------+
| Variable_name                                   | Value                |
+-------------------------------------------------+----------------------+
| connection_control_failed_connections_threshold | 3                    |
| connection_control_max_connection_delay         | 90000                |
| connection_control_min_connection_delay         | 1000                 |
+-------------------------------------------------+----------------------+

# 測試

一直嘗試連線失敗

for i in `seq 1 53`; do echo "try to connect $i ======"; time mysql -u root -p"123" 2>&1 >/dev/null | grep meh; done

結果

在第四次失敗後，之後的秒數都 +1 秒 (為 connection_control_min_connection_delay 的值)

try to connect 1 ======

real	0m0.011s
user	0m0.006s
sys	0m0.005s
try to connect 2 ======

real	0m0.012s
user	0m0.006s
sys	0m0.007s
try to connect 3 ======

real	0m0.013s
user	0m0.007s
sys	0m0.006s
try to connect 4 ======

real	0m1.009s
user	0m0.005s
sys	0m0.004s
try to connect 5 ======

real	0m2.012s
user	0m0.008s
sys	0m0.004s
try to connect 6 ======

real	0m3.010s
user	0m0.007s
sys	0m0.003s
try to connect 7 ======

real	0m4.011s
user	0m0.005s
sys	0m0.005s
try to connect 8 ======

real	0m5.011s
user	0m0.008s
sys	0m0.003s
try to connect 9 ======

real	0m6.010s
user	0m0.007s
sys	0m0.003s

# 查看誰嘗試連線失敗，與次數

SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

+--------------------+-----------------+
| USERHOST           | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' |              11 |
+--------------------+-----------------+

# 重置計數器

如果成功登入計數器也會重算 (成功的這次也會受 connection_control_min_connection_delay 的影響)

SET GLOBAL connection_control_failed_connections_threshold = 3;