Cannot log in to phpmyadmin (Ubuntu Server)

[Philip209]

Hello all,

I have been following the “How to install Magento 2.3 and build a webserver” article/video course and have ran into an issue whereby I am unable to log into phpMyAdmin as “root”. I’m not at my home computer right now, but I believe the error message I get is something along the lines of:

mysql_connect(): Access denied for user ‘root’@‘localhost’ (using password: YES) / mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost

I have tried following the instructions on the “Cannot log into phpMyAdmn as root user” topic within the forums, but I don’t follow what it is I need to do command-wise. I’m very new to this so any help will be appreciated!

[digitalstartup]

Hi @Philip209, welcome.

I just saw that you also left a post on the main site. I won’t bother replying to that one.

Setting the MySQL root password
The MySQL root password is set when you run this command:

sudo apt-get install mysql-server -y

And when you run this script, you’re given the option to change it again:

sudo mysql_secure_installation

So, let’s say that you did write down your password correctly. Let’s have a look at these 2 scenarios which I stumbled across by pure chance one day. I think it’s something to do with either the version of MySQL or phpMyAdmin. The default version that gets installed depends on your Web Hosts default repositories.

Scenario A: Password Strength
You’re password is correct, but phpMyAdmin simply won’t let you log in. This is a conflict between what MySQL thinks is a “secure” password and what phpMyAdmin thinks is a “secure” password. So, if MySQL says the password is good but phpMyAdmin disagrees then it won’t let you in.

In this case you should run the sudo mysql_secure_installation script again and update your password to something more secure.

Important Tip
Make sure that your MySQL root password password is:

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

Scenario B: Security Protocols
When I was testing Ubuntu 18.04, it installed an updated version of phpMyAdmin that uses new security protocols. They kinda make sense, but they’re still a bit of a pain. One of those new protocols is not allowing the MySQL root user to login whether you enter the password or not.

In this case, you’ll want to create a magento user/database for MySQL. Rather than repeat myself, I’ll link to this post I wrote when I originally came across the issue that contains the instructions you need.

Hopefully, one of those scenarios relates to your situation and solves it.

Good luck

[Philip209]

Hey Craig,

Many thanks for the prompt and tailored response.

I recall, when I was setting password strength during the MySQL portion of the tutorial, that I set it to medium and the password contained the neccessary parameters, but must’ve missed the “Important Tip” you shared there. I know I had two separate passwords for the MySQL root login and the MqSQL phpMyAdmin login. Both are the same complexity, but different.

When I’m back on my computer this afternoon, I will try re-running the mqsql_secure_installation script again as you suggested; I assume there’s no harm in doing this now as I’ve progressed up to Chapter 4 of the tutorial where I first noticed the issue?

Thanks again,
Josh

[digitalstartup]

No problem. Yes, you can run the mqsql_secure_installation as many times as you like. Again, if it’s not the password itself being the issue then it’s most likely to be Scenario B.

[Philip209]

Cool, will give both scenarios (if needed) a go soon and revert back with the results!

Thanks again, Craig

[Philip209]

Hey Craig,

I have just tested the first scenario with no luck - However, I tried the fix in scenario B and I’ve been able to log in to phpMyAdmin! What a relief!

I realise that when I first tried that fix (before posting this topic) I was creating a password that was getting syntax errors and thought it was something wrong with the command itself. After doing some fiddling, removing the “!” at the start of my password managed to get it accepted and I proceeded the remaining steps no problem.

Guess I just needed to read it again with a bit more sleep… Your help is very much appreciated - On to chapter 4!

[Philip209]

Oh, just realised I missing the “User Accounts” tab along the ribbon at the top and have limited options on the side bar too: any reason for this?

[digitalstartup]

Sorry, for the confusion, I misspoke earlier. I was at work when replying so my head wasn’t completely in the game.

The post that I linked to in Scenario B is to create a “magento” user for the database, not a “superuser”. But that’s fine.

Having said that, did you follow a different set of instructions to the one that I linked to? Because in Step 2 it shows you how to create a database and Step 4 shows you how to link that to the MySQL user you created in Step 3.

[Philip209]

I’m sure I did - I’ve just tried the linked steps from step 1. again and got the below response. Am I best starting again?

[digitalstartup]

This is my result when running the below commands with the following variables:

• Magento Database Name: magento_example_database
• Magento User Name: magento_example_user
• Magento User Password: QGEN!j9lqofjP9EQj9lqofjP9E

SSH Output

mysql> CREATE DATABASE magento_example_database;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'magento_example_user'@'localhost' IDENTIFIED BY 'QGEN!j9lqofjP9EQj9lqofjP9E';
Query OK, 0 rows affected (0.00 sec)

mysql> 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_example_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

phpMyAdmin Screenshot

`

Yes, it might be worth starting again with a fresh set of variables.

[Philip209]

I’ve got the same information there, too, but I need to hit “create” under the database tab:

I still don’t have the User Accounts tab at the top, however… Apologies for the mess

[digitalstartup]

Ah, cool

Only MySQL users with full permissions get the User Account tab. The set of instructions you just went through only creates a “Standard User” with access to its own database.

I think to create a MySQL “superuser” it would be the below commands with the following variables:

• Magento Superuser Name: bob_the_superuser
• Magento Superuser Password: QGEN!j9lqofjP9EQj9lqofjP9E

SSH Output

mysql> CREATE USER 'bob_the_superuser'@'localhost' IDENTIFIED BY 'QGEN!j9lqofjP9EQj9lqofjP9E';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'bob_the_superuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
FLUSH PRIVILEGES;

(Yes it is, I just checked)

Further reading on Creating MySQL users and Granting Permissions

[Philip209]

Wicked stuff, that worked… However, now I’m getting the below error when creating the user along with the database. When I log in as magento-master, there is only the information_schema DB showing…

[Philip209]

I think I figured it out: Seems as if my Superuser account didn’t have Grant permissions so I ran the the below commands which has allowed me to create the account and DB as instructed in chapter 4:

mysql> GRANT GRANT OPTION ON . TO ‘superuseraccount’@'localhost’;
mysql> FLUSH PRIVILEGES;

I can now see the DB when logged into the new account: