最後更新: 2021-08-02

目錄

• Disable validate_password Plugin
• CLI 改 password
• MySQL 8.0 的 Password
• mysql_native_password 與 caching_sha2_password
• Reset root password
• Troubleshoot

 

---

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

 

# list user

SELECT User, Host from mysql.user;

# 改 PW

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXX'"

OR

mysql -e "UPDATE mysql.user SET Password=PASSWORD('????') WHERE User='root';"

mysql -e "flush privileges"

 

---

MySQL 8.0 的 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';

OR

UNINSTALL PLUGIN VALIDATE_PASSWORD;

Checking

SHOW VARIABLES LIKE 'validate_password%';

Empty set (0.01 sec)

 

---

mysql_native_password 與 caching_sha2_password

 

* In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password.

MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

• caching_sha2_password (plugins are builtin)
• sha256_password (Deprecated)

The caching_sha2_password plugin has these advantages

• An in-memory cache enables faster reauthentication of users
  who have connected previously when they connect again.
• RSA-based password exchange is available
  regardless of the SSL library against which MySQL is linked.

當 Client 不支援新的 caching_sha2_password 時就會出 Error

... ../common/seaf-db.c(732): Failed to connect to MySQL:
 Plugin caching_sha2_password could not be loaded:
   /usr/lib/mariadb/plugin/caching_sha2_password.so:
     cannot open shared object file: No such file or directory

caching_sha2_password settings

show variables like 'caching_sha2_%';

+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_digest_rounds          | 5000            |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
+----------------------------------------------+-----------------+

caching_sha2_password_auto_generate_rsa_keys

At startup, the server uses this variable to determine
 whether to autogenerate RSA private/public key-pair files
 in the data directory if they do not already exist.

[1] 全面使用 mysql_native_password

/etc/mysql/my.cnf

default_authentication_plugin=mysql_native_password

[2] 設定個別 User 使用 mysql_native_password

SET GLOBAL validate_password.policy = 0;

ALTER USER 'seafile'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your-Password';

FLUSH PRIVILEGES;

RSA encrypt the password Login

mysql -h HOST --get-server-public-key -p

the server sends the RSA public key to the client,
 which uses it to encrypt the password and returns the result to the server.
This option applies to clients that authenticate with the caching_sha2_password authentication plugin.

mysql -h HOST --server-public-key-path=/var/lib/mysql/server-cert.pem -p

The path name to a file in PEM format containing a client-side copy of the public key
 required by the server for RSA key pair-based password exchange.

 

 

---

Reset root password

 

Step 0 - Check Version First

mysqld --version

• MySQL 5.1
• MySQL 5.6

MySQL 5.1

1. 停了現在的 mysqld

2. 啟動沒權威控制的 mysqld

mysqld_safe --skip-grant-tables &

OR

mysqld --skip-grant-tables &

3. update password

By mysql shell(MySQL 5.1)

1. mysql -u root
2. mysql> USE mysql;
3. mysql> ALTER USER 'root' IDENTIFIED BY 'new_password';
4. mysql> quit

4. Start 返原來的 mysqld service

killall mysqld && sleep 10         # restop mysqld

service mysqld start

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

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Or

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

Notes

在 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.

 

Reset password summary

1. systemctl stop mysqld
2. mysqld --defaults-file=/etc/my-resetpw.cnf --user=mysql --skip-grant-tables &
3. sleep 5
4. mysql_new_pw=????
5. mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '$mysql_new_pw'; FLUSH PRIVILEGES;"
6. killall mysqld
7. systemctl start mysqld

 

---

Troubleshot

 

[1] 不能以 unix socket connect mysqld

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

find / -type s -name mysql*              # 找出 socket 的位置

--socket=/tmp/mysql.sock