MySQL 8.0 上的 phpMyAdmin

phpMyAdmin on MySQL 8.0

提问by Lucas Noetzold

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

更新
较新版本的 phpMyAdmin 解决了这个问题。我已经成功地测试了 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:

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

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

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

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

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

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

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

回答by András Szabácsik

Log in to MySQL console with rootuser:

以root用户登录 MySQL 控制台：

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

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

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

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化的环境中完美运行：

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:8080with root / PASSWORD

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

---

---

mysql/mysql-server

mysql/mysql 服务器

If you are using mysql/mysql-serverdocker image

如果您使用的是mysql/mysql-serverdocker 镜像

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.

但请记住，它只是开发环境中的“快速而肮脏”的解决方案。更改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

10/04/2018 更新解决方案

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

通过取消注释中的default_authentication_plugin=mysql_native_password设置来更改 MySQL 默认身份验证插件 /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

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

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

default_authentication_plugin

回答by kgr

New MySQL 8.0.11 is using caching_sha2_passwordas default authentication method. I think that phpMyAdmin cannot understand this authentication method. You need to create user with one of the older authentication method, e.g. CREATE USER xyz@localhost IDENTIFIED WITH mysql_native_password BY 'passw0rd'.

新 MySQL 8.0.11caching_sha2_password用作默认身份验证方法。我认为 phpMyAdmin 无法理解这种身份验证方法。您需要使用较旧的身份验证方法之一创建用户，例如CREATE USER xyz@localhost IDENTIFIED WITH mysql_native_password BY 'passw0rd'。

More here https://dev.mysql.com/doc/refman/8.0/en/create-user.htmland here https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html

更多在这里https://dev.mysql.com/doc/refman/8.0/en/create-user.html和这里https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html

回答by sneaky

Another idea: as long as the phpmyadmin and other php tools don't work with it, just add this line to your file /etc/mysql/my.cnf

另一个想法：只要 phpmyadmin 和其他 php 工具不使用它，只需将此行添加到您的文件 /etc/mysql/my.cnf

default_authentication_plugin = mysql_native_password

See also: Mysql Ref

另请参阅： Mysql 参考

I know that this is a security issue, but what to do if the tools don't work with caching_sha2_password?

我知道这是一个安全问题，但是如果这些工具不能使用 caching_sha2_password 怎么办？

回答by Steven

I solved this issue by doing the following:

我通过执行以下操作解决了这个问题：

1. Add default_authentication_plugin = mysql_native_passwordto the
   [mysqld] section of my.cnf
2. Enter mysql and create a new user by doing something like CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
3. Grant privileges as necessary. E.g. GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';and then FLUSH PRIVILEGES;
4. Login into phpmyadmin with new user

1. 添加default_authentication_plugin = mysql_native_password到
   my.cnf的[mysqld] 部分
2. 输入 mysql 并通过执行类似的操作创建一个新用户 CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
3. 根据需要授予特权。例如GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';然后FLUSH PRIVILEGES;
4. 使用新用户登录 phpmyadmin

回答by Claire

I went to system

我去了系统

preferences -> mysql -> initialize database -> use legacy password encryption(instead of strong) -> entered same password

首选项 -> mysql -> 初始化数据库 -> 使用旧密码加密（而不是强密码） -> 输入相同的密码

as my config.inc.phpfile, restarted the apache server and it worked. I was still suspicious about it so I stopped the apache and mysql server and started them again and now it's working.

作为我的config.inc.php文件，重新启动了 apache 服务器并且它工作了。我仍然对此表示怀疑，所以我停止了 apache 和 mysql 服务器并再次启动它们，现在它可以工作了。

回答by Vipan K Verma

 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpassword';

Login through the command line, it will work after that.

通过命令行登录，之后就可以工作了。

回答by Bruno Carazato

I solved my problem basically with András answer:

我基本上用安德拉斯的回答解决了我的问题：

1- Log in to MySQL console with root user:

1- 使用 root 用户登录 MySQL 控制台：

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

And type the root's password to auth.

并输入 root 的密码进行身份验证。

2- I created a new user:

2-我创建了一个新用户：

mysql> CREATE USER 'user'@'hostname' IDENTIFIED BY 'password';

3- Grant all privileges to the new user:

3- 授予新用户所有权限：

mysql> GRANT ALL PRIVILEGES ON *.* To 'user'@'hostname';

4- Change the Authentication Plugin with the password:

4- 使用密码更改身份验证插件：

mysql> ALTER USER user IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Now, phpmyadmin works fine logging the new user.

现在，phpmyadmin 可以很好地记录新用户。

回答by Lakhwinder Singh

To fix this issue I just run one query in my mysqlconsole.

为了解决这个问题，我只在我的mysql控制台中运行了一个查询。

For this login to mysql console using this

为此，使用此登录到 mysql 控制台

mysql -u {username} -p{password}

After this I just run one query as given below:-

在此之后，我只运行一个查询，如下所示：-

ALTER user '{USERNAME}'@'localhost' identified with mysql_native_password by '{PASSWORD}';

when I run this query I got message that query executed. Then login to PHPMYADMIN with username/password.

当我运行此查询时，我收到了该查询已执行的消息。然后使用用户名/密码登录 PHPMYADMIN。

回答by Biró Dani

If you are using the official mysql docker container, there is a simple solution:

如果你使用的是官方的mysql docker容器，有一个简单的解决方案：

Add the following line to your docker-compose service:

将以下行添加到您的 docker-compose 服务中：

command: --default-authentication-plugin=mysql_native_password

Example configuration:

示例配置：

mysql:
     image: mysql:8
     networks:
       - net_internal
     volumes:
       - mysql_data:/var/lib/mysql
     environment:
       - MYSQL_ROOT_PASSWORD=root
       - MYSQL_DATABASE=db
     command: --default-authentication-plugin=mysql_native_password

回答by Stephan

I had this problem, did not find any ini file in Windows, but the solution that worked for me was very simple.
1. Open the mysql installer.
2. Reconfigure mysql server, it is the first link.
3. Go to authentication method.
4. Choose 'Legacy authentication'.
5. Give your password(next field).
6. Apply changes.

我遇到了这个问题，在 Windows 中没有找到任何 ini 文件，但对我有用的解决方案非常简单。
1.打开mysql安装程序。
2.重新配置mysql服务器，是第一个环节。
3. 转到身份验证方法。
4. 选择“传统身份验证”。
5. 提供您的密码（下一个字段）。
6. 应用更改。

That's it, hope my solution works fine for you as well!

就是这样，希望我的解决方案也适用于您！