MySQL加密方式之更改（error 1251）

caching_sha2_password To mysql_native_password

MySQL V8后，采用更强的SHA加密方式，caching_sha2_password。如果客户端、JDBC或者App通过mysql_native_password方式加密，在认证的过程中会出现如下错误所示：error 1251 [Client does not support authentication protocol requested by server; consider upgrading MySQL client]。

解决方案，在服务器端更改加密和解密方式如下：

1. 修改权限

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

2. 修改加密方式

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)

或者

If MySQL is already installed with the new authentication method, you may switch to the Legacy method as described below. Open my.ini in C:\ProgramData\MySQL\MySQL Server 8.0\ and find the following line:

default_authentication_plugin=caching_sha2_password

Change the values of the parameter:

default_authentication_plugin=mysql_native_password

Next, change the authentication type for the user, under which you are connecting to the database. Open ‘Command Line Client’ and run the following command:

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

3. 写入生效

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4. root字符加密前后的变化

*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

$A$005$zxW(&Y?ibV=G["LH%h3198Lz1JpYixMup7Oq2fHzpK6WDIfThHMiJhCj3HC8