phpMyAdmin on MySQL 8.0 [duplicate]

Question

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

Answer 1

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 *

Answer 2

New MySQL 8.0.11 is using caching_sha2_password as 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'.

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