In previous versions of Ubuntu, such as 16.04, 14.04, 12.04, etc., installing a LAMP configuration was usually straight-forward:
* install apache2
* install mysql-server
* install phpmyadmin
During the install for MySQL Server, it would prompt for a root password. During the install for phpMyAdmin, it would ask for the MySQL root password. Simple!
Things seem different with Ubuntu 18.04 and newer: MySQL Server does not ask for a root password, the phpMyAdmin setup assumes you aren't using a MySQL root password, and then the web interface fails to connect to MySQL. What use to "just work" now presents me with abort/retry prompts. Yuck!
With a few extra commands, you can get it working like it did in previous versions.
First, install Apache:
sudo apt install apache2
Then install MySQL Server:
sudo apt install mysql-server
Next, we need to make sure that the "Universe" repository is enabled, then install phpMyAdmin:
sudo add-apt-repository universe
sudo apt install phpmyadmin
During the phpMyAdmin setup, when it asks for a password for phpMyAdmin, I just leave it blank and hit Enter (it will auto-generate a password). Also, make sure to select the option for it to auto-configure apache2.
After it installs, we now have to enable root login from the phpMyAdmin web interface.
Log into MySQL to change the authentication method and password for root:
mysql -u root alter user root@localhost identified with mysql_native_password by 'PASSWORD'; flush privileges; exit
Now you can log into phpMyAdmin with the root account.
To change MySQL's authentication back to its 18.04/20.04 default, you can undo the above this way:
mysql -u root -p update mysql.user set plugin='auth_socket' where user='root'; flush privileges; exit
Locked yourself out of MySQL?
First, stop the service:
service mysql stop
Then run MySQL in its "safe mode" and connect:
mkdir -p /var/run/mysqld chown mysql:mysql /var/run/mysqld sudo mysqld_safe --skip-grant-tables & mysql -u root
You can change/correct the authentication plugin (see above), or reset the root password.
To reset the root password, do this:
update mysql.user set authentication_string=password("NewPassword") where user='root';
flush privileges;
exit
You can change the authentication plugin back and forth with one of these MySQL commands:
update mysql.user set plugin='auth_socket' where user='root';
update mysql.user set plugin='mysql_native_password' where user='root';
You can also see if a password is set and look at the current authentication plugins used with this MySQL command:
select user,authentication_string,plugin,host from mysql.user;