phpMyAdmin on MySQL 8.0

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


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 *

Tags:

Mysql

Database Connection

Phpmyadmin

Mysql 8.0

Recent Posts