MySQL and phpMyAdmin (18.04/20.04/22.04)

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;

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;

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;

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;