Dtyjlui

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.

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

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

I had to log in with

mysql -u root -p