MySQL用户锁定

原创

仕强君 2019-05-16 22:17:17 ©著作权

©著作权归作者所有：来自51CTO博客作者仕强君的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 5.5一般可以通过修改改参数来（基线默认是1），debug mysql.err中记录warning，note信息，如果应用端连接数据库由于用户密码异常可以在my.err中记录用户连接失败的提示信息，且多次连接失败会出现连接失败报警。 mysql> show variables like 'log_warnings%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 1 | +---------------+-------+

修改方式： set global log_warnings=2; MySQL 5.5版本官方文档中，并没有用户锁的状态。因此不存在用户会被锁。。

The account-locking capability depends on the presence of the account_locked column in the mysql.user table. For upgrades to MySQL 5.7.6 and later from older versions, run mysql_upgrade to ensure that this column exists. For nonupgraded installations that have no account_locked column, the server treats all accounts as unlocked, and using the ACCOUNT LOCK or ACCOUNT UNLOCK clauses produces an error.

MySQL 5.5的用户信息中没有用户锁的字段 *************************** 4. row *************************** Host: ::1 User: root Password: *13C230E7E4DDA7CCF4BED86963D2B066390F3E56 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: 4 rows in set (0.00 sec)

MySQL 5.7的用户信息中account_locked *************************** 2. row *************************** Host: % User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *D61994AAB9327E97C4BECC73E497951CFC723C99 password_expired: N password_last_changed: 2018-04-14 22:20:00 password_lifetime: NULL account_locked: N 2 rows in set (0.00 sec)

可以通过如下方式查看用户锁状态、但都仅限MySQL 5.7.6以上 mysql> show create user 'root'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for root@% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*D61994AAB9327E97C4BECC73E497951CFC723C99' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |