Ubuntu 20.04 set mysql phpmyadmin root password

Question

I installed a LAMP on Ubuntu 20.04.

(German) https://wiki.ubuntuusers.de/MySQL/Werkzeuge/

It is always a problem to get the root password to login to the localhost/phpmyadmin. In Ubuntu 18.04 there was a good tutorial (several):

SERVER BEENDEN:
  sudo service mysql stop
  sudo mkdir -p /var/run/mysqld
  sudo chown mysql:mysql /var/run/mysqld
  sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
PRÜFEN MIT:
  jobs
STARTEN von MYSQL:
  mysql -u root
    mysql> FLUSH PRIVILEGES;
    mysql> USE mysql; 
    mysql> UPDATE user SET authentication_string=PASSWORD("NEWPASSWORD") WHERE user='root';
    mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';
    mysql> quit
  sudo pkill mysqld
  sudo service mysql start

In the actual ubuntu version it seems that the PASSWORD command is not known. I get the following error.

mysql> UPDATE user SET authentication_string=password('YOURNEWPASSWORD') WHERE user='root';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('YOURNEWPASSWORD') WHERE user='root'' at line 1

My mysql version:

mysql Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))

Answer 1

There are a few parts to this puzzle

To change a password

Login at root from the CLI:

sudo mysql -u root -p

Then

ALTER USER 'root'@'localhost' IDENTIFIED BY '<New-Password-Here>';

To Really Get Things Working

From phpmyadmin.net

Due to changes in the MySQL authentication method, PHP versions prior to 7.4 are unable to authenticate to a MySQL 8.0 blah blah blah blah https://bugs.php.net/bug.php?id=76243.

There is a workaround, that is to set your user account to use the current-style password hash method, mysql_native_password. This unfortunate lack of coordination has caused the incompatibility to affect all PHP applications, not just phpMyAdmin.

So

UPDATE user SET plugin="mysql_native_password" WHERE user='root';

To Really Really Get Things Working... Really

MySQL Have changed their Security Model and root login now requires a sudo. This is ok for the CLI, but it means that PhpMyAdmin and ALL other clients will not be able to use root credentials

The best solution is to create a new user for PhpMyAdmin (or use the existing one if it was created during install) and grant it the required privileges.

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY '<New-Password-Here>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Use this user anywhere you want "root" access.

---

Also make sure you're using the latest verion of PHP. Here is my full install script

PHP

sudo apt install -y php7.4
sudo apt install php7.4-curl php7.4-gd php7.4-json php7.4-mbstring php7.4-xml

APACHE

sudo apt install apache2 libapache2-mod-php7.4

MYSQL

sudo apt install mysql-server php7.4-mysql
sudo mysql_secure_installation

PHPMYADMIN

sudo apt install phpmyadmin