mariadb 登录认证plugin导致的无法登录问题

mysql用户名密码都正确,在命令行下能正常登录,但是phpmyamdin登录报错:

mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost

这是由于mariadb使用了新的登录认证插件导致的,通过下面的SQL可以查询到:
root用户只能通过unix_socket登录,这是基于安全的考虑

MariaDB [mysql]> select user, plugin from mysql.user;
+------+-------------+
| user | plugin      |
+------+-------------+
| root | unix_socket |
| dvwa |             |
| test |             |
+------+-------------+
3 rows in set (0.00 sec)

因为是本地开发环境,所以,可以把root登录认证插件改为mysql_native_password:

MariaDB [(none)]> update mysql.user set plugin='mysql_native_password' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再次查询,发现已经修改成功:

MariaDB [(none)]> select user, plugin from mysql.user;
+——+———————–+
| user | plugin |
+——+———————–+
| root | mysql_native_password |
| dvwa | |
| test | |
+——+———————–+
3 rows in set (0.00 sec)

刷新权限后,phpmyadmin即可正常登录:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

%1 $ S

发表回复