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.