最後更新: 2021-08-02

目錄

• Disable validate_password Plugin
• CLI 改 password

 

---

Disable validate_password Plugin

 

改 password 時見到

... Failed! Error: Your password does not satisfy the current policy requirements

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

Settings

[5.7]

/etc/my.cnf

# 修改強度

[mysqld]
# 0 = LOW, 1 = MEDIUM, 2 = STRONG
validate-password-policy = LOW
validate_password_special_char_count = 0

# 完全停用它

validate_password = OFF

 

---

CLI 改 password

 

select User, Host from mysql.user;

mysql -e "flush privileges; ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysql_new_pw'"

 

---

MySQL 8 改 Password

 

# Checking

SELECT user,authentication_string,plugin,host FROM mysql.user;

SHOW VARIABLES LIKE 'default_authentication_plugin';

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

Disable validate_password

UNINSTALL COMPONENT 'file://component_validate_password';

uninstall plugin validate_password;

Checking

SHOW VARIABLES LIKE 'validate_password%';

Empty set (0.01 sec)

改成舊式 password

SET GLOBAL validate_password.policy = 0;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

 

---

Reset

 

0. Check Version First

mysqld --version

1. 停了現在的 mysqld

2. 啟動沒權威控制的 mysqld

mysqld_safe --skip-grant-tables &

OR

mysqld --skip-grant-tables &

3. update password

方式 A: by mysql shell

mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

# stop

killall mysqld

方式 B: by mysqladmin

mysqladmin -u root password 'yourpw'

mysqladmin flush-privileges

 

---

MySQL 5.6

 

# Start mysql without ACL

mysqld_safe --skip-grant-tables &

# Login with root

mysql -u root

P.S.

Mysql 5.6 有 "Anonymous Accounts"

# Reset Password

UPDATE mysql.user SET authentication_string='' WHERE User='root';

ALTER USER user IDENTIFIED BY 'new_password';

P.S.

在 mysql 5.6 上 user table 沒有了 "password" 這個 field

# Checking

SELECT User, Host, HEX(authentication_string) FROM mysql.user;

P.S.

The statement uses HEX() because passwords stored in the authentication_string column might contain binary data that does not display well.

 

---

MySQL

 

mysql_new_pw=????

systemctl stop mysqld

mysqld --defaults-file=/etc/my-resetpw.cnf --user=mysql --skip-grant-tables &

sleep 5

mysql -e "flush privileges; ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysql_new_pw'"

killall mysqld

systemctl start mysqld

 

---

Troubleshot

 

[1]

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

--socket=/var/lib/mysql/mysql.sock

OR

find / -type s