修改MySQL8密码验证规则为cachingsha2password
# 简介
MySQL8.0 引入了新特性 caching_sha2_password 作为默认的密码验证规则,如果 PHP 或者我们的数据库连接工具不支持这个规则就无法连接了。MySQL8 保留了之前的密码验证规则:mysql_native_password,我们可以修改配置文件修改回来。
# 修改配置
# 查看当前用户使用的密码验证插件
mysql> show variables like '%auth%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
2
3
4
5
6
7
# 查看 MySQL8 支持的密码验证插件
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------+---------+
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
1
2
3
4
5
6
7
2
3
4
5
6
7
# 修改数据表用户密码验证规则
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set plugin='mysql_native_password' where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | phpmyadmin | mysql_native_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 修改配置文件
编辑文件 /etc/mysql/mysql.conf.d/mysqld.cnf
,在**[mysqld]**增加 default_authentication_plugin = mysql_native_password。
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[mysqld]
default_authentication_plugin = mysql_native_password
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
重启 MySQL。
╭─root@ubuntu /etc/mysql/mysql.conf.d
╰─➤ service mysql restart;
1
2
2
# 修改密码
停止运行服务。
service mysql stop
编辑配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf
找到'mysqld'添加跳过密码。
[mysqld]
skip-grant-tables
重新启动服务。
service mysql restart
mysql
对原密码进行清空。
use mysql;
update user set authentication_string='' where user='root';
注释掉跳过密码'skip-grant-tables',重启服务。
service mysql restart
设置新密码和加密方式,方便Navicat连接。
use mysql;
update user set plugin='mysql_native_password' where user='root';
设置新密码。
如果任意ip都可以连接
alter user 'root'@'%' IDENTIFIED BY '123456';
如果仅限本地连接
alter user 'root'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
上次更新: 2023/09/19, 14:48:57