phpMyAdmin on MySQL 8 0

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

User · Answer

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

For this login to mysql console using this

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.

User · Answer

As many pointed out in other answers  changing the default authentication plugin of MySQL to native does the trick   Still  since I can t use the new caching sha2 password plugin  I ll wait until compatibility is developed to close the topic

User · Answer

I solved this issue by doing the following    Add default authentication plugin   mysql native password to the  mysqld  section of my cnf Enter mysql and create a new user by doing something like CREATE USER  root   localhost  IDENTIFIED BY  password   Grant privileges as necessary  E g  GRANT ALL PRIVILEGES ON       TO  root   localhost   and then FLUSH PRIVILEGES  Login into phpmyadmin with new user

User · Answer

I solved this with MySQL 8 0 12 by running   mysql upgrade -u root

User · Answer

I solved my problem basically with Andr  s answer   1- Log in to MySQL console with root user   root 9532f0da1a2a    mysql -u root -pPASSWORD   And type the root s password to auth   2- I created a new user   mysql gt  CREATE USER  user   hostname  IDENTIFIED BY  password     3- Grant all privileges to the new user   mysql gt  GRANT ALL PRIVILEGES ON     To  user   hostname     4- Change the Authentication Plugin with the password   mysql gt  ALTER USER user IDENTIFIED WITH mysql native password BY  PASSWORD     Now  phpmyadmin works fine logging the new user

User · Answer

I went to system      preferences -  mysql -  initialize database -  use legacy password encryption instead of strong  -  entered same password    as my config inc php file  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

User · Answer

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

User · Answer

I solved this issue by doing following    Enter to system preferences -  mysql Select  Initialize database  and enter a new root password selecting  Use Legacy Password Encryption   Login into phpmyadmin with the new password

User · Answer

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   That s it  hope my solution works fine for you as well

User · Answer

As  kgr mentioned  MySQL 8 0 11 made some changes to the authentication method   I ve opened a phpMyAdmin bug report about this  https   github com phpmyadmin phpmyadmin issues 14220   MySQL 8 0 4-rc was working fine for me  and I kind of think it s ridiculous for MySQL to make such a change in a patch level release

User · Answer

Create another user with mysql native password option   In terminal   mysql gt  CREATE USER  su   localhost  IDENTIFIED WITH mysql native password BY  123   mysql gt  GRANT ALL PRIVILEGES ON       TO  su   localhost   mysql gt  FLUSH PRIVILEGES

User · Answer

mysql gt  ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  rootpassword     Login through the command line  it will work after that

User · Answer

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   default authentication plugin   mysql native password   See also  Mysql Ref  I know that this is a security issue  but what to do if the tools don t work with caching sha2 password

User · Answer

Log in to MySQL console with root user   root 9532f0da1a2a    mysql -u root -pPASSWORD   and change the Authentication Plugin with the password there   mysql gt  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

User · Answer

You can change the Authentication if u are running on Windows by reconfiguring the installation by running the msi  It will ask for changing the default authentication to legacy  then u can proceed with that option to change the authentication to the legacy one

User · Answer

I used ALTER USER root localhost IDENTIFIED WITH mysql native password BY  PASSWORD   it worked

User · Answer

If you are using the official mysql docker container  there is a simple solution   Add the following line to your docker-compose service   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

User · Answer

in my case  to fix it I preferred to create a new user to use with PhpMyAdmin because modifying the root user has caused native login problems with other applications such as MySQL WorkBench   This is what I did    Log in to MySQL console with root user  mysql -u root -p  enter your password  Let   s create a new user within the MySQL shell    CREATE USER  newMySqlUsername   localhost  IDENTIFIED WITH mysql native password BY  mysqlNewUsernamePassword      At this point the newMysqlUsername has no permissions to do anything with the databases  So is needed to provide the user with access to the information they will need    GRANT ALL PRIVILEGES ON       TO   newMySqlUsername   localhost      Once you have finalized the permissions that you want to set up for your new users  always be sure to reload all the privileges    FLUSH PRIVILEGES     Log out by typing quit or  q  and your changes will now be in effect  we can log in into PhpMyAdmin with the new user and it will have access to the databases  Also you can log back in with this command in terminal    mysql -u newMySqlUsername -p

[mysql] phpMyAdmin on MySQL 8.0

Examples related to mysql

Examples related to phpmyadmin

Examples related to database-connection

Examples related to mysql-8.0