How to Install and Secure phpMyAdmin with Apache on Ubuntu 18.04

If you are looking for an app to handle the administration of MySQL and MariaDB servers over a web-based interface, phpMyAdmin will be a good option. Because it is an open-source PHP application and specifically designed to handle such administrations.
And also phpMyAdmin lets you manage MySQL databases, user accounts, and privileges, execute SQL-statements, import and export data in different formats, etc.
So, in this guide, I am going to tell you how to install and secure phpMyAdmin with Apache or Ubuntu 18.04.

Prerequisites

These are mandatory before installing phpMyAdmin.

Install LAMP (Linux, Apache, MySQL, and PHP) on your Ubuntu Server
Log in as a user with sudo privileges

And also we can recommend access to your phpMyAdmin installation over HTTPS. But that is not mandatory. If your domain is not protected by an SSL already you can secure your Apache with Lets Encrypt on Ubuntu 18.04.

Installing phpMyAdmin

Let’s get to work. It is very simple. Just follow my lead.

First, update the package index and upgrade the system packages to its latest versions:

sudo apt update && sudo apt upgrade

Then install the phpMyAdmin package from the default Ubuntu repositories using the following command:

sudo apt install phpmyadmin

There will be a window prompting you to select the webserver that must be automatically configured to run phpMyAdmin. In that window, select apache by pressing Space and then Enter.

Then another window will pop up asking whether to use dbconfig-common to set up the database. Choose Yes and press Enter.

For phpMyAdmin to register with the database, enter a password. Select OK and then press Enter.

Then you will be directed to confirm the password. Just enter the same password, choose OK, and hit Enter.

When the installation finished, restart Apache:

sudo systemctl restart apache2

Create an Administrative MySQL user

Usually, in Ubuntu systems that are running MySQL 5.7 and later, the root user is set to use the auth_socket authentication method by default.
That particular plugin authenticates users who connect from the localhost through the Unix socket file. In other words, you won’t be able to authenticate as root by giving a password.
So, we’ll create a new administrative user instead of changing the authentication method for the MySQL user root. Additionally, this particular user will have the same privileges as the root user and will be set to use the mysql_native_password authentication method.

And we are gonna use this user to login to the phpMyAdmin dashboard and perform administrative tasks on MySQL or MariaDB servers.
First, you have to log in to the MySQL server as the root user:

sudo mysql

Then execute the following commands from within the MySQL shell. That will create a new administrative user and also grant necessary permissions:

CREATE USER 'padmin'@'localhost' IDENTIFIED BY 'super-strong-password';GRANT ALL PRIVILEGES ON *.* TO 'padmin'@'localhost' WITH GRANT OPTION;

We have named the administrative user padmin. in the above example. You can pick any name that you like and please make sure to set a very strong password.

Accessing phpMyAdmin

To enter the phpMyAdmin interface, first, open your browser and type the domain name or public IP address of your server, followed by /phpmyadmin:

https://your_domain_or_ip_address/phpmyadmin

Then enter the previously created administrative user login credentials and click Go.

So that you will see the phpMyAdmin dashboard like in the below image.

Securing phpMyAdmin

In order to improve security, we are going to set a password to protect the phpMyAdmin directory by setting up a basic authentication.
At first, we’ll create a password file with users with the help of htpasswd tool that comes with the Apache package. And then we will store the .htpasswd file in /etc/phpmyadmin directory:

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

Our user name is padmin in this example also. You can use any name as you like and doesn’t have to be the same as the administrative MySQL user.
After that, you have to confirm the user password.

New password:
Re-type new password:
Adding password for user padmin

To add an additional user, just use the same command without the -c flag:

sudo htpasswd /etc/phpmyadmin/.htpasswd padmin2

Now let’s configure Apache to password protect the phpMyAdmin directory to use the .htpasswd file.
For that, you have to open the phpmyadmin.conf file that was created automatically during the installation of phpMyAdmin:

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

Insert the below green highlighted lines.

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

<Directory /usr/share/phpmyadmin>
    Options  +FollowSymLinks +Multiviews +Indexes  # edit this line
    DirectoryIndex index.php

    AllowOverride None
    AuthType basic
    AuthName "Authentication Required"
    AuthUserFile /etc/phpmyadmin/.htpasswd
    Require valid-user

    <IfModule mod_php5.c>
    ...

Then save and close the file and Restart Apache:

sudo systemctl restart apache2

From now on you will be prompted to enter the login credentials of the previously created user when accessing your phpMyAdmin:

https://your_domain_or_ip_address/phpmyadmin

Once you have entered the basic authentication you will be routed to the phpMyAdmin login page. That is where you need to enter your MySQL administrative user login credentials.

That’s it! You have completed the phpMyAdmin installation on your Ubuntu 18.04 server. Now you can start creating MySQL databases, users, and tables and perform different MySQL queries and operations.

Tags: How to Install and Secure phpMyAdmin with Apache on Ubuntu 18.04