MySQL 8.0 上的 phpMyAdmin

更新
较新版本的 phpMyAdmin 解决了这个问题.我已经成功测试了 phpMyAdmin 5.0.1

<小时>

我已经安装了 MySQL 8.0 服务器和 phpMyAdmin,但是当我尝试从浏览器访问它时出现以下错误:

#2054 - 服务器请求客户端未知的身份验证方法mysqli_real_connect():服务器请求客户端未知的身份验证方法[caching_sha2_password]mysqli_real_connect(): (HY000/2054): 服务器请求客户端未知的身份验证方法

我想这一定与实施的强密码和 MySQL 版本的相对新鲜度有关.

但我对最先进的驱动程序和连接配置一无所知.

有人遇到过同样的问题并解决了吗?:D

解决方案

以 root 用户登录 MySQL 控制台:

root@9532f0da1a2a:/# mysql -u root -pPASSWORD

并使用密码更改身份验证插件:

mysql>使用 mysql_native_password 通过密码"更改用户 root 身份;查询正常,0 行受影响(0.08 秒)

您可以在 MySQL 8.0 参考手册中阅读有关首选身份验证插件的更多信息

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

它在 docker 化的环境中完美运行:

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -p 3306:3306 -d mysql:latestdocker exec -it mysql bashmysql -u 根 -pPASSWORD使用 mysql_native_password 通过密码"更改用户 root 身份;出口出口docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

因此您现在可以使用 root/PASSWORD 在 http://localhost:8080 上登录到 phpMyAdmin


mysql/mysql-server

如果你使用 mysql/mysql-server docker 镜像

但请记住,这只是开发环境中的快速而肮脏"的解决方案.更改 MySQL 首选身份验证插件.

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latestdocker exec -it mysql mysql -u root -pPASSWORD -e "ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';";docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

10/04/2018 更新解决方案

通过取消注释 /etc/my.cnf

中的 default_authentication_plugin=mysql_native_password 设置来更改 MySQL 默认身份验证插件

使用风险自负

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latestdocker exec -it mysql sed -i -e 's/# default-authentication-plugin=mysql_native_password/default-authentication-plugin=mysql_native_password/g'/etc/my.cnf码头工人停止mysql;码头工人启动mysqldocker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

于 2019 年 1 月 30 日更新了解决方法

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latestdocker exec -it mysql sed -i -e 's/# default-authentication-plugin=mysql_native_password/default-authentication-plugin=mysql_native_password/g'/etc/my.cnfdocker exec -it mysql mysql -u root -pPASSWORD -e "ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';";码头工人停止mysql;码头工人启动mysqldocker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

default_authentication_plugin

2021 年 9 月 13 日更新解决方案

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

  • 完全用引号 *

UPDATE
Newer versions of phpMyAdmin solved this issue. I've successfully tested with phpMyAdmin 5.0.1


I have installed the MySQL 8.0 server and phpMyAdmin, but when I try to access it from the browser the following errors occur:

#2054 - The server requested authentication method unknown to the client
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

I imagine it must have something to do with the strong passwords implemented and the relative freshness of the MySQL release.

But I know nothing of the most advanced driver and connection configuration.

Has someone faced the same problem and solved it? :D

解决方案

Log in to MySQL console with root user:

root@9532f0da1a2a:/# mysql -u root -pPASSWORD

and change the Authentication Plugin with the password there:

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

You can read more info about the Preferred Authentication Plugin on the MySQL 8.0 Reference Manual

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

It is working perfectly in a dockerized environment:

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -p 3306:3306 -d mysql:latest

docker exec -it mysql bash

mysql -u root -pPASSWORD

ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

exit

exit

docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

So you can now log in to phpMyAdmin on http://localhost:8080 with root / PASSWORD


mysql/mysql-server

If you are using mysql/mysql-server docker image

But remember, it is just a 'quick and dirty' solution in the development environment. It is not wise to change the MySQL Preferred Authentication Plugin.

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latest
docker exec -it mysql mysql -u root -pPASSWORD -e "ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';"
docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

Updated solution at 10/04/2018

Change the MySQL default authentication plugin by uncommenting the default_authentication_plugin=mysql_native_password setting in /etc/my.cnf

use at your own risk

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latest
docker exec -it mysql sed -i -e 's/# default-authentication-plugin=mysql_native_password/default-authentication-plugin=mysql_native_password/g' /etc/my.cnf
docker stop mysql; docker start mysql
docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

Updated workaround at 01/30/2019

docker run --name mysql -e MYSQL_ROOT_PASSWORD=PASSWORD -e MYSQL_ROOT_HOST=% -p 3306:3306 -d mysql/mysql-server:latest
docker exec -it mysql sed -i -e 's/# default-authentication-plugin=mysql_native_password/default-authentication-plugin=mysql_native_password/g' /etc/my.cnf
docker exec -it mysql mysql -u root -pPASSWORD -e "ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';"
docker stop mysql; docker start mysql
docker run --name phpmyadmin -d --link mysql:db -p 8080:80 phpmyadmin/phpmyadmin:latest

default_authentication_plugin

Updated solution at 09/13/2021

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

  • exactly with quotes *

相关文章