Ubuntu 18.04 LTS after fresh install of MySQL / PHPMyadmin; no root password; unable to login with PHPmyadmin

Question

After a fresh install of Ubuntu 18.04 LTS Bionic Beaver and setting up a LAMP stack as well as installing PHPMyadmin I am unable to login to PHPMyadmin as the password I have give while securing mysql with mysql_secure_installation is not correct, so its seems. I search the internet for solutions, but all solutions provided have no positive result in my case.

A working solution will be appreciated.

Solution 1

I had the same problem. Apparently MySQL was set up by default to use socket-based authentication, which uses the system username, and not a password. To fix the problem, I used the following commands:

Access MySQL as the root user:

sudo mysql   # or sudo mysql -u root

Set the password for the MySQL root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

Replace 'test' above with the actual password you want to use

Solution 2

The solution is to switch authentication method of MySQL from socket authentication ( i.e. auth_socket) to password authentication (i.e. mysql_native_password plugin).

MySQL 5.7 and later versions uses the socket authentication by default. It means you could start MySQL by sudo mysql in the terminal without a password. Even if you create a new root password using mysql_secure_installation, you would not be able to access third party softwares like phpmyadmin which uses password authentication. Following is the solution:

sudo apt install mysql-server
sudo mysql_secure_installation

After entering mysql_secure_installation create a password for root, and click YES for the rest of questions. [You can notice that still MySQL is starting without any password because the default authentication method has not been changed till now ! ]

Now, enter MySQL

sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;

Now you can see in the following output, that root has plugin asauth_socket instead of mysql_native_password

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *832A85D6EC83FA4A19ACFD461F672B95E4540611 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

Now we have to change the plugin to mysql_native_password. Replace the 'password' in the following command with a strong password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Now you can check the auth methods for each user, using the same above command

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *C035F91799F4415B005D146ECEB5ADD4D991031F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *832A85D6EC83FA4A19ACFD461F672B95E4540611 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

If your output is like the above, with plugin for root as mysql_native_password, you are good to go :) Now you can simply access phpmyadmin with the root and its password. Hope this helps.

This link helped me to understand this concept much better

Comments

[Encrypter]

After looking for this everywhere and following a lot of guides, this worked for me. Thanks a lot @heyjoe

[BloodyIron]

FYI the "alter user" string above enabled me to force password authentication for an out of the box Ubuntu 18.04 setup with MySQL. Previously it was letting me in without enforcing password authentication (would accept any password put in by the root user). Thanks for the help @heyjoe ! :D

[Suat Atan]

Sometimes mysql may refuse your password due to password policy. If it happens use a passwords which includes punctation and uppercase and numeric characters