Navigation

How Can We Help?

How To Install PHPMyAdmin

Created On
Last Updated On
byeditor
Print
You are here:
< All Topics

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.

 

 

Tags:
Table of Contents