Using a database system is a necessity that many people have. But we know that managing a database system like MySQL directly from its command-line client demands certain familiarity with the system, which can be a challenge sometimes.

In this tutorial, we’ll show how to install phpMyAdmin with Nginx server. This is a free software tool that allow users to interact with MySQL through an intuitive interface. We’ll also cover how to

Prerequisites

To follow this guide, you’ll need:

  • An Ubuntu 18.04 server with a LEMP stack secured with ufw.
  • Access to the server as a non-root user with sudo privileges.
  • As phpMyAdmin uses MySQL credentials to authenticate, you should also install an SSL/TLS certificate in order to enable encrypted traffic between server and client.

Otherwise, ensure you enforce access via SSH Tunnels, as we’ll explain along this tutorial.

Step 1 - Installing phpMyAdmin

Through Ubuntu repositories, install phpMyAdmin on your LEMP server. But first, let’s update the server’s package index using the following command:

$

sudo apt update

Then install phpMyAdmin, run the code:

$

sudo apt install phpmyadmin

You’ll be prompted to choose a web server to configure. As we’re using Nginx, we should just press tab and after ok and then go to the next step.

After this, you’ll be asked whether to use dbconfig-common to configure the application database. Select yes. You’ll be also asked to create a password. Here you can just leave it blank and get a random password from phpMyAdmin.

The installation will finish. You’ll need to create a symbolic link from the installation files to Nginx’s document root directory, so that Nginx web server will find and serve the phpMyAdmin files correctly:

$

sudo ln -s /usr/share/phpmyadmin /var/www/html

To access the phpMyAdmin interface, go to your server’s domain name or public IP followed by /phpmyadmin in your web browser:

$

https://server_domain_or_IP/phpmyadmin

Now you should have your phpMyAdmin installed and set up. As you’ve opened your database server to a web interface, it is more vulnerable to attacks. That’s why we’ll cover how to secure and avoid these attacks.

Step 2 - Changing phpMyAdmin’s Default Location

Making your phpMyAdmin installation hard to find is one of the most trivial ways to protect it. Usually bots search for common paths. Changing the interface’s URL from /phpmyadmin to something different will make it harder for any scripts to find your installation and attack it.

With this installation, we’ve created a symbolic link that points to /usr/share/phpmyadmin , where the real application files are placed. To change it’s interface URL, rename the symbolic link.

First, go to the Nginx document root directory and list the files it contains to ger a better sense of the change that will be made:

$$

cd /var/www/html/ ls -l

You’ll get the following output:

total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 phpmyadmin -> /usr/share/phpmyadmin

As you can see, the output shows that there’s a symbolic link called phpmyadmin in the directory. You can rename the link to whatever you’d like. This process will change phpMyAdmin’s access URL, which will help hiding the endpoint from bots hardcoded to find endpoint names.

It is recommended to choose a name that hides the purpose of the endpoint. In this guide, we’ll use /theresnothinghere, but you can choose a name of your preference.

Then, rename the link by following the command:

$$

sudo mv phpmyadmin /theresnothinghere ls -l

Then you should get this output:

total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 theresnothinghere-> /usr/share/phpmyadmin

Check if you have an error message when accessing the old address:

$

https://server_domain_or_IP/phpmyadmin

Now your interface will be available at the new URL you’ve just set up:

https://server_domain_or_IP/theresnothinghere

By hiding phpMyAdmin’s real location on the server, you’re ensuring your interface is secure against automated scans and manual attempts.

Step 3 - Disabling Root Login

On MySQL, the root account is a special administrative account with no restrictions to access the system. It’s not only a privileged account, but also a known login name, which turns it into an obvious target for attacks.

Aiming to minimize risks, we’ll configure phpMyAdmin to deny any login attempts that comes from the user root. With this, even if you give credentials for the user root, you won’t have your access allowed.

As we used dbconfig-common to set up and store phpMyAdmin settings, the default configuration is now stored in the database. We’ll create a new config.inc.php file to define our custom settings.

Even if the PHP files for the software are placed inside /usr/share/phpmyadmin, the application will use setup files located at /etc/phpmyadmin. We will create a new settings file inside /etc/phpmyadmin/conf.d, and name it pma_secure.php:

$

sudo nano /etc/phpmyadmin/conf.d/pma_secure.php

The following settings file has the necessary settings to disable logins without password and root login:

/etc/phpmyadmin/conf.d/pma_secure.php

...

#PhpMyAdmin Settings

#This should be set to a random string of at least 32 chars

$cfg['blowfish_secret'] = '3!#32@3sa(+=4?),5XP:U%%8\34sdfSdg43yH#{o';

$i=0; $i++;

$cfg['Servers'][$i]['auth_type'] = 'cookie'; $cfg['Servers'][$i]['AllowNoPassword'] = false; $cfg['Servers'][$i]['AllowRoot'] = false;

...

After that, save the file by pressing CTRL+X and y in order to confirm changes, and finally ENTER. The changes will be automatically applied. If you reload the login page and try to log in as root, you’ll have your access denied.

With this security measure you’ll be free from brute-force scripts that try to guess the root database password on your server.

Step 4 - Creating an Authentication Gateway

Here we will explain how to create an extra layer of authentication to your phpMyAdmin installation. It will enable you to increase the security.

As we’re dealing with phpMyAdmin, it’s even more important to keep the login interface protected. Users will need to pass through an HTTP authentication prompt before seeing the phpMyAdmin login screen. Nginx provides this natively.

To configure it, we need to create a password file to store the authentication credentials. Nginx requires that passwords be encrypted with the crypt() funcion.

To create the password, just type:

$

openssl passwd

The system will ask you for the password you want to use. The utility will display an encrypted version of the password that will look like this:

O5az.RSPzd.HE

Copy the password, once you’ll need to paste it into the authentication file you’ll create. To create the authentication file (pma_pass) and place it in Nginx configuration directory, use the following command:

$

sudo nano /etc/nginx/pma_pass

In this file, specify the username you want to use, followed by a colon (:), followed by the encrypted version of the password you received from the openssl passwd utility.
We’ll name our user anna, but you can choose a different username.

/etc/nginx/pma_pass

anna:O5az.RSPzd.HE

Once you’ve done that, save and close the file. Now you can modify the Nginx setup file. In this tutorial we’ll use the configuration file located at /etc/nginx/sites-available/example.com. Open the relevant Nginx configuration file for the web location where phpMyAdmin is hosted:

$

sudo nano /etc/nginx/sites-available/example.com

Then locate the server block, and the location / section in it. It’s necessary to create a new location section within this block to match phpMyAdmin’s current path on the server. In this tutorial this location is /theresnothinghere

/etc/nginx/sites-available/default

server { . . .

    location / {
            try_files $uri $uri/ =404;
    }

    location /theresnothinghere {
            # Settings for phpMyAdmin will go here
    }

. . .

}

In the block, we’ll need to configure two different directives: auth_basic to define the message that will be displayed on the authentication prompt, and auth_basic_user_file, that points to the file we just created:

/etc/nginx/sites-available/default

server { . . .

    location /theresnothinghere {
            auth_basic "Admin Login";
            auth_basic_user_file /etc/nginx/pma_pass;
    }


. . .

}

Once you’ve done that, save and close the file. Check the configuration by typing:

$

sudo nginx -t

You should have the following output:

nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

To activate the new authentication gate, reload the web server:

$

sudo systemctl reload nginx

Now when you visit phpMyAdmin URL in your browser, you should be prompted for the username and password you added to the pma_pass file.

Conclusion

In this guide, we learned how to install phpMyAdmin on Ubuntu 18.04 running Nginx as the web server. We also covered advanced methods to secure a phpMyAdmin installation on Ubuntu.

Now you should be able to manage your MySQL databases from a reasonably secure web interface. This user interface exposes most of the functionality available via MySQL command line. You can navigate databases and schema, execute queries, and create new data sets and structures.

Read more about: UbuntuOperating System