Below are my notes on the installation of PHPMyAdmin on Ubuntu 20.

Install the PHPMyAdmin and related dependency packages

apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl

For the server selection, choose apache2

Then choose and confirm a MySQL application password for phpMyAdmin

I checked dbcommon checked for the database installation.

However, I was unable to continue due to a mariadb error message.

Suggested solutions involving disabling and re-enabling the Validate Password plugin did not work

Instead I had to spring over this point and continue without creating the database.

Then at the completion of the installation process, I went into mysql command line and created the database and user manually (see further below).

Create the PHPMyAdmin Database in mysql/mariadb

login to mysql using: (enter your mysql root password)

root@gemini:/etc/apache2/sites-available# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33108
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

First I tried the suggested solution:

From the prompt, run the following command to disable the Validate Password component.

UNINSTALL COMPONENT “file://component_validate_password”;

This does not work with mariadb!

MariaDB [(none)]> UNINSTALL COMPONENT “file://component_validate_password”;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘COMPONENT “file://component_validate_password”‘ at line 1

MariaDB [(none)]> show databases ;
+——————–+
| Database |
+——————–+
| information_schema |
| kevwells |
| mysql |
| nextcloud |
| performance_schema |
| wordpress |
+——————–+
6 rows in set (0.002 sec)

MariaDB [(none)]>

MariaDB [(none)]> uninstall plugin validate_password;
ERROR 1305 (42000): PLUGIN validate_password does not exist
MariaDB [(none)]>

CREATE DATABASE phpmyadmin DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE phpmyadmin DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.003 sec)

NOTE FOR MARIADB:

USE THE CORRECT QUOTES ‘ ‘   – Else the user definitions etc will not be correct!

root@asus:/etc/apache2# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 100
Server version: 10.3.29-MariaDB-0ubuntu0.20.10.1 Ubuntu 20.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> show databases ;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| wordpress |
+——————–+
5 rows in set (0.000 sec)

Use this command for MariaDB – the older MySQL command version does not work: 

mysql> CREATE USER ‘phpmyadmin’@’localhost’ IDENTIFIED BY ‘*****PASSWORD COMMENTED OUT*****’;
Query OK, 0 rows affected (0.01 sec)

mysql>

NOTE THE ‘ ‘ round phpmyadmin AND round localhost!

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘phpmyadmin’@’localhost’ WITH GRANT OPTION ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>

SHOW GRANTS FOR ‘phpmyadmin’@’localhost’ ;

MariaDB [(none)]> flush privileges ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR ‘phpmyadmin’@localhost;
+———————————————————————————————————————————————-+
| Grants for phpmyadmin@localhost |
+———————————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO `phpmyadmin`@`localhost` IDENTIFIED BY PASSWORD ‘****COMMENTEDOUT!**’ WITH GRANT OPTION |
+———————————————————————————————————————————————-+
1 row in set (0.000 sec)

MariaDB [(none)]>

Then, configure apache2 to enable phpMyAdmin:

nano /etc/apache2/apache2.conf

add the following line to the end of the file:

Include /etc/phpmyadmin/apache.conf

Then restart apache

systemctl restart apache2

you should then be able to access phpmyadmin:

http://localhost/phpmyadmin

enter your phpmyadmin username and your phpmyadmin password that you set as above.

Verify your new user has the right permissions

NOTE

If you made a mistake you can undo the steps above by executing the following commands, – replacing localhost with ‘%’ if you changed it in the previous commands:

eg

DROP USER myuser@localhost;
DROP DATABASE mydb;

Set the Database User Privileges for the phpmyadmin User

however, in phpmyadmin we have the following errors:

mysqli_real_connect(): (HY000/1045): Access denied for user ‘phpmyadmin’@’localhost’ (using password: YES)
Connection for controluser as defined in your configuration failed.

and we cannot see any databases!

The clue comes from the following message:

No Privileges

Database Ascending Collation Action

information_schema utf8_general_ci Check privileges Check privileges
phpmyadmin utf8_unicode_ci Check privileges Check privileges
Total: 2 utf8mb4_general_ci
Note: Enabling the database statistics here might cause heavy traffic between the web server and the MySQL server.
Enable statistics
Open new phpMyAdmin window
mysqli_real_connect(): (HY000/1045): Access denied for user ‘phpmyadmin’@’localhost’ (using password: YES)
Connection for controluser as defined in your configuration failed.

so, we have to increase the privileges for the user phpmyadmin

contents of following file:

root@gemini:/var/www/wordpress# nano /etc/phpmyadmin/config-db.php
root@gemini:/var/www/wordpress#

GNU nano 4.8 /etc/phpmyadmin/config-db.php
<?php
##
## database access settings in php format
## automatically generated from /etc/dbconfig-common/phpmyadmin.conf
## by /usr/sbin/dbconfig-generate-include
##
## by default this file is managed via ucf, so you shouldn’t have to
## worry about manual changes being silently discarded. *however*,
## you’ll probably also want to edit the configuration file mentioned
## above too.
##
$dbuser=’phpmyadmin’;
$dbpass=’****COMMENTED OUT HERE!’;
$basepath=”;
$dbname=’phpmyadmin’;
$dbserver=’localhost’;
$dbport=’3306′;

MariaDB [mysql]> CREATE USER ‘phpmyadmin’@localhost IDENTIFIED BY ‘**COMMENTED OUT**’;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

MariaDB [(none)]> GRANT ALL privileges ON ‘phpmyadmin’.* TO ‘phpmyadmin’@localhost IDENTIFIED BY ‘*COMMENTED OUT**’;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]>

CREATE USER ‘phpmyadmin’@localhost IDENTIFIED BY ‘** COMMENTED OUT**’;

GRANT ALL privileges ON ‘phpmyadmin’.* TO ‘phpmyadmin’@localhost IDENTIFIED BY ‘** COMMENTED OUT**’;

MariaDB [mysql]> flush privileges ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

this is now better, the errors in phpmyadmin dashboard are gone, but we only see 2 databases.

the information schema and the phpmyadmin one – .the wordpress, nextcloud and mysql ones are not shown.

This is because we have no privileges for these.

MariaDB [mysql]> SHOW GRANTS FOR ‘phpmyadmin’@localhost;
+——————————————————————————————————————-+
| Grants for phpmyadmin@localhost |
+——————————————————————————————————————-+
| GRANT USAGE ON *.* TO `phpmyadmin`@`localhost` IDENTIFIED BY PASSWORD ‘*COMMENTED OUT**’ |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `phpmyadmin`@`localhost` |
+——————————————————————————————————————-+
2 rows in set (0.000 sec)

MariaDB [mysql]>

So, we need to do a

grant create on *.* to phpmyadmin@localhost;

MariaDB [mysql]> SHOW GRANTS FOR ‘phpmyadmin’@localhost;
+——————————————————————————————————————-+
| Grants for phpmyadmin@localhost |
+——————————————————————————————————————-+
| GRANT USAGE ON *.* TO `phpmyadmin`@`localhost` IDENTIFIED BY PASSWORD ‘*****COMMENTED OUT**’ |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `phpmyadmin`@`localhost` |
+——————————————————————————————————————-+
2 rows in set (0.000 sec)

MariaDB [mysql]> grant create on *.* to phpmyadmin@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> flush privileges ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> SHOW GRANTS FOR ‘phpmyadmin’@localhost;
+——————————————————————————————————————–+
| Grants for phpmyadmin@localhost |
+——————————————————————————————————————–+
| GRANT CREATE ON *.* TO `phpmyadmin`@`localhost` IDENTIFIED BY PASSWORD ‘***COMMENTED OUT ***’ |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `phpmyadmin`@`localhost` |
+——————————————————————————————————————–+
2 rows in set (0.000 sec)

MariaDB [mysql]>

note it now looks like this:

GRANT CREATE ON

instead of

GRANT USAGE ON

As a result in phpmyadmin we can now see all the databases displayed and the user “phpmyadmin” has full privileges in the system.

Modify Apache Config 

We also have to modify the apache2.conf:

sudo nano /etc/apache2/apache2.conf

Then add the following line to the end of the file.

Include /etc/phpmyadmin/apache.conf

Then restart apache:

sudo systemctl restart apache2

then, you can login to phpmyadmin using:

http://3.222.27.169/phpmyadmin/

this now works

Add an .htaccess Login Protection Layer

Create an htaccess protection login layer for phpmyadmin. This helps protect the PHPMyAdmin dashboard against hacker attempts by creating a pre-login window which has to be passed first by anyone logging in before they are permitted to reach the actual PHPMyAdmin dashboard login page.

edit the phpmyadmin.conf file in your Apache configuration directory.

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

Add an AllowOverride All directive within the <Directory /usr/share/phpmyadmin> section of the configuration file:

<Directory /usr/share/phpmyadmin>
Options SymLinksIfOwnerMatch
DirectoryIndex index.php
AllowOverride All

</Directory>

 Next, create the .htaccess file for PHPMyAdmin:

nano /usr/share/phpmyadmin/.htaccess

AuthType Basic
AuthName “Restricted Files”
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user

AuthType Basic: This specifies the authentication type you are using. This type implements password authentication by means of a password file that we will create.

AuthName: This sets the message for the authentication dialog box. Keep this generic so unauthorized users don’t gain any information about what is being protected.

AuthUserFile: This defines the location of the password file to be used for authentication. This should be outside of the directory tree that is being served.

Require valid-user: This specifies only authenticated users to be given access to this resource.

The location we selected for our password file is /etc/phpmyadmin/.htpasswd.

Next create this file using the htpasswd utility:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd username

You are then prompted to select and confirm a password for the user you’re creating for .htaccess.

The file will then created with the hashed password that you entered.

htpasswd -c /etc/phpmyadmin/.htpasswd <user we are creating for .htaccess>

root@gemini:/etc/apache2# htpasswd -c /etc/phpmyadmin/.htpasswd <the user name you will deploy for this .htaccess login>
New password: <the password you wish to use for this .htaccess login>
Re-type new password:

root@gemini:/etc/apache2#
root@gemini:/etc/apache2#

Next, set the conf-available/phpmyadmin.conf

root@gemini:/etc/apache2/conf-available# a2enconf phpmyadmin.conf

Then restart Apache to put .htaccess authentication into effect:

sudo systemctl restart apache2

The .htaccess login window prompt will then appear before you can login to the phpmyadmin dashboard.