MySQL數(shù)據(jù)庫(kù)被鎖定的問(wèn)題解決
問(wèn)題
ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
問(wèn)題驗(yàn)證
我們嘗試連接,用命令行也連接失敗。
[clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p Enter password: ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
查看日志
查看mysql數(shù)據(jù)庫(kù)的日志
tail -n 200 /var/log/mysqld.log
解決辦法
進(jìn)入數(shù)據(jù)庫(kù)進(jìn)行刷新錯(cuò)誤連接,并且查看最大數(shù)量為100,并改為1000
修改max_connection_errors的數(shù)量為1000(立即生效)
set global max_connect_errors = 1000;
在my.cnf中[mysqld]下添加(重啟后不失效)
max_connect_errors = 1000
[admin@wmsweb log]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1853635 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> mysql> flush hosts; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%max_connect_errors%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | +--------------------+-------+ 1 row in set (0.01 sec) mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%max_connect_errors%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | +--------------------+-------+ 1 row in set (0.00 sec) mysql>
再次通過(guò)客戶端連接成功
[clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p Enter password: ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' [clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1853636 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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>
根本解決辦法
解決ERROR 1129 (HY000)的方法是執(zhí)行flush host或者 mysqladmin flush-hosts,其目的是為了清空host cache里的信息,那是不是說(shuō)不使用host cache就可以了?
host_cache_size的作用
缺點(diǎn):當(dāng)有一個(gè)新的客戶端連接進(jìn)來(lái)時(shí),MySQL Server都要建立一個(gè)新的記錄,如果DNS解析很慢,無(wú)疑會(huì)影響性能。如果被允許訪問(wèn)的主機(jī)很多,也會(huì)影響性能,這個(gè)與host_cache_size有關(guān),這個(gè)參數(shù)是5.6.5引入的。5.6.8之前默認(rèn)是128,5.6.8之后默認(rèn)是-1,基于max_connections的值動(dòng)態(tài)調(diào)整。所以如果被允許訪問(wèn)的主機(jī)很多,基于LRU算法,先前建立的連接可能會(huì)被擠掉,這些主機(jī)重新進(jìn)來(lái)時(shí),會(huì)再次進(jìn)行DNS查詢。
優(yōu)點(diǎn):通常情況下,主機(jī)名是不變的,而IP是多變的。如果一個(gè)客戶端的IP經(jīng)常變化,那基于IP的授權(quán)將是一個(gè)繁瑣的過(guò)程。因?yàn)槟愫茈y確定IP什么時(shí)候變化。而基于主機(jī)名,只需一次授權(quán)。而且,基于host cache中的失敗信息,可在一定程度上阻止外界的暴 力破 解攻擊。
關(guān)于阻止外界的暴 力破 解攻擊,涉及到max_connect_errors參數(shù),默認(rèn)為100,官方的解釋如下:
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.
如果某個(gè)客戶端的連接達(dá)到了max_connect_errors的限制,將被禁止訪問(wèn),并提示以下錯(cuò)誤:
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
查看當(dāng)前的最大鏈接錯(cuò)誤數(shù).
mysql> show variables like 'max_connect_errors'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | +--------------------+-------+ 1 row in set (0.07 sec)
使host cache不生效(禁用 MySQL DNS 查找)的方式有如下兩種:
A、設(shè)置 host_cache_size 為0
mysql> set global host_cache_size=0;
B、配置skip-name-resolve
編輯mysql配置文件 my.cnf
vi /etc/my.cnf
在 [mysqld] 下面添加 下面這一行
skip-name-resolve
??注意事項(xiàng)
但是需要注意??的是,如果這樣設(shè)置,你將沒(méi)辦法使用127.0.0.1進(jìn)行連接數(shù)據(jù)庫(kù)。
如果禁用了DNS 則 localhost 則不會(huì)解析成回環(huán)地址.登錄報(bào)錯(cuò).
[root@dbserver ~]# mysql -h127.0.0.1 -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
如果該參數(shù)設(shè)置為OFF,則上述方式就會(huì)報(bào)錯(cuò),通過(guò)報(bào)錯(cuò)信息可以看出,它直接將127.0.0.1轉(zhuǎn)化為localhost了。
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p123456 -P3306 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)被鎖定的問(wèn)題解決的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫(kù)被鎖定內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql 觸發(fā)器創(chuàng)建與使用方法示例
這篇文章主要介紹了mysql 觸發(fā)器創(chuàng)建與使用方法,結(jié)合實(shí)例形式分析了mysql 觸發(fā)器基本概念、原理、創(chuàng)建、使用方法及操作注意事項(xiàng),需要的朋友可以參考下2020-05-05mysql數(shù)據(jù)庫(kù)中的索引類(lèi)型和原理解讀
這篇文章主要介紹了mysql數(shù)據(jù)庫(kù)中的索引類(lèi)型和原理,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-02-029種 MySQL數(shù)據(jù)庫(kù)優(yōu)化的技巧
這篇文章小編主要給大家介紹的是 MySQL數(shù)據(jù)庫(kù)優(yōu)化的正確姿勢(shì),九種方法呢?。?!需要的小伙伴趕快收藏起來(lái)吧2021-09-09MySQL錯(cuò)誤代碼1862 your password has expired的解決方法
這篇文章主要為大家詳細(xì)介紹了MySQL錯(cuò)誤代碼1862 your password has expired的解決方法,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2018-08-08