Cannot log into phpMyAdmin as root user

A member of our community brought up an issue he ran into the other day. I was able to reproduce this issue after installing phpMyAdmin on Ubuntu 18.04. The repositories in Ubuntu 18.04 appear to download a newer version of phpMyAdmin that comes with an extra security measure. In this case, the security measure is to disable using your MySQL root user on phpMyAdmin.

Important Tip
Make sure that your MySQL passwords are:

  • Longer than 16 characters
  • Contains numbers
  • Contains lower & uppercase characters
  • Contains special characters

And keep them safe for future reference.


A. How do I create a user and database to install Magento?

The following commands assume that you’re using the following variables:

  • MySQL Database Name: magento_database
  • MySQL User Name: magento_user
  • MySQL User Password: QGENj9lqofjP9EQj9lqofjP9E

1. Log into MySQL from the Command Line

sudo mysql -u root -p

You’ll be prompted for your MySQL root user password. Enter this and hit Enter to continue.

2. Create the Magento Database

CREATE DATABASE magento_database;

3. Create the Magento Database User

CREATE USER 'magento_user'@'localhost' IDENTIFIED BY 'QGENj9lqofjP9EQj9lqofjP9E';

4. Permit the Magento User to access the Magento Database

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, REFERENCES ON magento_database.* TO 'magento_user'@'localhost';

5. Exit MySQL

  • Type exit

B. How do I create a MySQL superuser?

The following commands assume that you’re using the following variables:

  • MySQL Superuser Name: bob_the_superuser
  • MySQL Superuser Password: PqQGjP9E9EQjENj9lqofjof9l

1. Log into MySQL from the Command Line

sudo mysql -u root -p

You’ll be prompted for your MySQL root user password. Enter this and hit Enter to continue.

2. Create the Magento Database User

CREATE USER 'bob_the_superuser'@'localhost' IDENTIFIED BY 'PqQGjP9E9EQjENj9lqofjof9l';

3. Permit the Superuser to full privileges

GRANT ALL PRIVILEGES ON * . * TO 'bob_the_superuser'@'localhost';

4. Flush Privileges

FLUSH PRIVILEGES;

5. Exit MySQL

  • Type exit

C. Switch back to ‘mysql_native_password’ plugin (Added 11/11/19)

Important Note: I will fine-tune this option or make it the recommended option once I’ve tested it thoroughly.

I wanted to try something new, long after creating this original video/post. I found a much simpler method results in switching off the Secure MySQL Plugin that checks and verifies passwords. Typically, this gets enabled when you initially run the sudo mysql_secure_installation script during the MySQL installation.

The following command requires you to run it as a root or superuser. It executes a MySQL command directly from the Command Line Interface, without the need to log in/out of the MySQL Console. It also switches from the authentication plugin to the native mysql plugin:

sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PqQGjP9E9EQjENj9lqofjof9l'"

And then you can log into phpMyAdmin using “root” as the Username and “PqQGjP9E9EQjENj9lqofjof9l” as the Password.