這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)MySQL中如何使用Connection-Control插件,文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述,閱讀完這篇文章希望大家可以有所收獲。
成都創(chuàng)新互聯(lián)公司主要業(yè)務(wù)有網(wǎng)站營(yíng)銷策劃、網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站、微信公眾號(hào)開(kāi)發(fā)、微信小程序、H5響應(yīng)式網(wǎng)站、程序開(kāi)發(fā)等業(yè)務(wù)。一次合作終身朋友,是我們奉行的宗旨;我們不僅僅把客戶當(dāng)客戶,還把客戶視為我們的合作伙伴,在開(kāi)展業(yè)務(wù)的過(guò)程中,公司還積累了豐富的行業(yè)經(jīng)驗(yàn)、營(yíng)銷型網(wǎng)站建設(shè)資源和合作伙伴關(guān)系資源,并逐漸建立起規(guī)范的客戶服務(wù)和保障體系。
首先查看系統(tǒng)變量plugin_dir,找到插件(Plugins)所在的路徑,如下所示
mysql> select version() from dual; +-----------+ | version() | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) mysql> show variables like 'plugin_dir'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | plugin_dir | /usr/lib64/mysql/plugin/ | +---------------+--------------------------+ 1 row in set (0.01 sec) [root@KerryDB ~]# cd /usr/lib64/mysql/plugin/ [root@KerryDB plugin]# ls -lrt total 76388 -rwxr-xr-x. 1 root root 106696 Sep 20 2019 rewrite_example.so -rwxr-xr-x. 1 root root 104432 Sep 20 2019 mysql_no_login.so -rwxr-xr-x. 1 root root 111464 Sep 20 2019 mypluglib.so -rwxr-xr-x. 1 root root 106648 Sep 20 2019 auth_socket.so -rwxr-xr-x. 1 root root 163560 Sep 20 2019 adt_null.so -rwxr-xr-x. 1 root root 409032 Sep 20 2019 validate_password.so -rwxr-xr-x. 1 root root 9054776 Sep 20 2019 libpluginmecab.so -rwxr-xr-x. 1 root root 344696 Sep 20 2019 authentication_ldap_sasl_client.so -rwxr-xr-x. 1 root root 1145832 Sep 20 2019 rewriter.so -rwxr-xr-x. 1 root root 625944 Sep 20 2019 ha_example.so -rwxr-xr-x. 1 root root 388992 Sep 20 2019 semisync_slave.so -rwxr-xr-x. 1 root root 32368 Sep 20 2019 component_log_sink_json.so -rwxr-xr-x. 1 root root 235200 Sep 20 2019 component_audit_api_message_emit.so -rwxr-xr-x. 1 root root 494720 Sep 20 2019 keyring_udf.so -rwxr-xr-x. 1 root root 149280 Sep 20 2019 component_log_sink_syseventlog.so -rwxr-xr-x. 1 root root 1446024 Sep 20 2019 semisync_master.so -rwxr-xr-x. 1 root root 2277480 Sep 20 2019 mysql_clone.so -rwxr-xr-x. 1 root root 1231376 Sep 20 2019 libmemcached.so -rwxr-xr-x. 1 root root 454096 Sep 20 2019 component_mysqlbackup.so -rwxr-xr-x. 1 root root 193336 Sep 20 2019 component_log_filter_dragnet.so -rwxr-xr-x. 1 root root 1177352 Sep 20 2019 ha_mock.so -rwxr-xr-x. 1 root root 83936 Sep 20 2019 locking_service.so -rwxr-xr-x. 1 root root 1269784 Sep 20 2019 connection_control.so -rwxr-xr-x. 1 root root 1280936 Sep 20 2019 innodb_engine.so -rwxr-xr-x. 1 root root 442304 Sep 20 2019 component_validate_password.so -rwxr-xr-x. 1 root root 1206024 Sep 20 2019 version_token.so -rwxr-xr-x. 1 root root 2338880 Sep 20 2019 keyring_file.so -rwxr-xr-x. 1 root root 2031912 Sep 20 2019 ddl_rewriter.so -rwxr-xr-x. 1 root root 49246400 Sep 20 2019 group_replication.so drwxr-xr-x. 2 root root 4096 Nov 6 2019 debug
安裝插件
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; Query OK, 0 rows affected (0.02 sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; Query OK, 0 rows affected (0.00 sec)
檢查是否安裝成功
mysql> show plugins; mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_LIBRARY = 'CONNECTION_CONTROL.SO'; +------------------------------------------+-----------------------+---------------+-------------+ | PLUGIN_NAME | PLUGIN_LIBRARY | PLUGIN_STATUS | LOAD_OPTION | +------------------------------------------+-----------------------+---------------+-------------+ | CONNECTION_CONTROL | connection_control.so | ACTIVE | FORCE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | connection_control.so | ACTIVE | FORCE | +------------------------------------------+-----------------------+---------------+-------------+ 2 rows in set (0.00 sec) mysql>
設(shè)置系統(tǒng)變量
mysql> show variables like 'connection_control%'; +-------------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------------+------------+ | connection_control_failed_connections_threshold | 3 | | connection_control_max_connection_delay | 2147483647 | | connection_control_min_connection_delay | 1000 | +-------------------------------------------------+------------+
connection_control_failed_connections_threshold #登陸失敗次數(shù)限制,默認(rèn)值為3
connection_control_max_connection_delay #限制重試時(shí)間最大值,單位為毫秒( milliseconds),默認(rèn)值2147483647
connection_control_min_connection_delay #限制重試時(shí)間最小值,單位為毫秒( milliseconds),默認(rèn)值為1000毫秒,也就是1秒
注意事項(xiàng):
1:connection_control_min_connection_delay的值必須小于connection_control_max_connection_delay,connection_control_max_connection_delay不能小于connection_control_min_connection_delay的值。
mysql> set global connection_control_min_connection_delay=60000; Query OK, 0 rows affected (0.00 sec)
注意,命令方式設(shè)置全局系統(tǒng)變量在服務(wù)器重啟后丟失,所以最好的方式在參數(shù)文件my.cnf設(shè)置全局系統(tǒng)變量
-- 配置文件增加以下配置
[mysqld] plugin-load-add = connection_control.so #不是必須 connection-control = FORCE #不是必須 connection-control-failed-login-attempts = FORCE #不是必須 connection_control_min_connection_delay = 60000 connection_control_max_connection_delay = 1800000 connection_control_failed_connections_threshold = 3
三次連續(xù)輸錯(cuò)密碼后,就會(huì)在第四次輸入密碼后掛起
[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) [root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) [root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p Enter password: ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) [root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p Enter password:
注意,MySQL服務(wù)重啟過(guò)后,INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS中的數(shù)據(jù)全部前空。
The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin must be activated for this table to be available, and the CONNECTION_CONTROL plugin must be activated or the table contents will always be empty. See Section 6.4.2, “The Connection-Control Plugins”.
必須激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使用該表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS,并且要么激活CONNECTION_CONTROL插件,要么該表的內(nèi)容始終為空。請(qǐng)參見(jiàn)第6.4.2節(jié)“連接控制插件”。
The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.
該表僅包含已進(jìn)行一次或多次連續(xù)失敗連接嘗試而沒(méi)有隨后成功嘗試的客戶端的行。當(dāng)客戶端成功連接時(shí),其失敗連接計(jì)數(shù)將重置為零,并且服務(wù)器將刪除與該客戶端對(duì)應(yīng)的任何行。
Assigning a value to the connection_control_failed_connections_threshold system variable at runtime resets all accumulated failed-connection counters to zero, which causes the table to become empty.
在運(yùn)行時(shí)為connection_control_failed_connections_threshold系統(tǒng)變量分配一個(gè)值會(huì)將所有累積的失敗連接計(jì)數(shù)器重置為零,這將導(dǎo)致表變空。
解除賬號(hào)延遲響應(yīng)限制
方法1:重啟MySQL實(shí)例
方法2:調(diào)整系統(tǒng)變量connection_control_failed_connections_threshold的值。
mysql> SELECT * FROM -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; +-------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +-------------------+-----------------+ | 'test'@'192.168%' | 5 | +-------------------+-----------------+ 1 row in set (0.00 sec) mysql> mysql> set global connection_control_failed_connections_threshold=2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; Empty set (0.00 sec)
3:卸載插件plugin
mysql> UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; mysql> UNINSTALL PLUGIN CONNECTION_CONTROL;
注意事項(xiàng):
關(guān)于Connection-Control的實(shí)現(xiàn)原理,淘寶數(shù)據(jù)庫(kù)內(nèi)核月報(bào)有篇文章分析過(guò)代碼,介紹過(guò)實(shí)現(xiàn)原理,Connection-Control插件雖然可以防止惡意暴力破解MySQL賬戶,但是可能會(huì)浪費(fèi)MySQL的資源;
比如如果短時(shí)間內(nèi)有大量的惡意攻擊,該插件雖然可以防止破解MySQL賬戶,但是會(huì)消耗主機(jī)資源(每一個(gè)連接創(chuàng)建一個(gè)線程);
如果這里使用了線程池,雖然可以避免消耗主機(jī)資源,但是等線程池中的線程被消耗光,再有新連接來(lái)就會(huì)拒絕服務(wù)。
上述就是小編為大家分享的MySQL中如何使用Connection-Control插件了,如果剛好有類似的疑惑,不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí),歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
當(dāng)前文章:MySQL中如何使用Connection-Control插件
本文路徑:http://muchs.cn/article32/pisdpc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、App開(kāi)發(fā)、軟件開(kāi)發(fā)、響應(yīng)式網(wǎng)站、企業(yè)網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)