Install phpMyAdmin on One-Click WordPress App

Introduction

phpMyAdmin is a convenient web-based MySQL database administration tool which can save you tons of time from command driven database management. In this article, I will show you how to install and secure phpMyAdmin on the One-Click WordPress app.

Prerequisites

I assume that you have deployed a One-Click WordPress app from scratch and have logged in as root. Non-root users will need to use the sudo command.

Step One: Install phpMyAdmin

Visit phpMyAdmin official website from your browser, click the link phpMyAdmin-4.4.7-all-languages.tar.bz2 to download an archive file with the same name to your local machine. Then upload it to the directory /var/www/html on your VPS with WinSCP or a similar SFTP tool.

Unzip the archive file with the following commands from your terminal:

cd /var/www/html
tar -jxvf phpMyAdmin-4.4.7-all-languages.tar.bz2

To protect phpMyAdmin from unauthorized access, you should rename the newly-created phpMyAdmin directory to another unusual and private name. We use pmapma here.

mv phpMyAdmin-4.4.7-all-languages pmapma

Step Two: Configure phpMyAdmin

Now, we need to create a configuration file for phpMyAdmin. Make a copy of the file config.default.php and rename it to config.inc.php:

cd pmapma
cp config.sample.inc.php config.inc.php

Edit config.inc.php with the vi text editor.

vi config.inc.php

Fill in the blowfish secret, leave any other parameters alone.

$cfg['blowfish_secret'] = 'InputRandomCharactersHere';

Replace InputRandomCharactersHere with any characters, no more than 46 bits, and do not leave it blank.

Save and quit vi.

:wq

Step Three: Grant Permissions

Visit http://your_host_IP/pmapma from your browser. You will encounter a permission error to the directory /var/lib/php/fpm/session/. You can fix the error by changing the owner of this directory to nginx.

chown nginx /var/lib/php/fpm/session/

Refresh the page from your browser, you will find that the error prompt disappeared. Now you can log in with the MySQL root credential. You can get it from the file /root/.my.cnf.

cat /root/.my.cnf

Step Four: Secure phpMyAdmin

phpMyAdmin is a powerful tool, you would never want an unauthorized user to access it. Thus, we can add an additional authentication gate to the phpMyAdmin log-in interface.

First, you need to create an encrypted password from your terminal.

openssl passwd

Input and confirm the password that you’d like to use. Then an encrypted version of the password you input will display on the screen. Write it down on the paper, we will use it later. The encrypted password should be something like this:

rs4D8QYVwojBI

Now, create an authentication file in the Nginx ciphertext storage directory /etc/nginx/htpasswd/. We will use the file name pma here, remember to replace it with your own file name.

vi /etc/nginx/htpasswd/pma

Add the username you want to use and the encrypted password that you just generated into this file by the following format.

pmauser:rs4D8QYVwojBI

Remember to replace the username pmauser and the encrypted password rs4D8QYVwojBI with your own ones.

Save and quit vi.

:wq

Next, you need to modify the vhost files in /etc/nginx/conf.d: wordpress_http.conf and wordpress_https.conf.

In case of configuration error, create a backup of them.

cp /etc/nginx/conf.d/*.conf /root/

In the file wordpress_http.conf, find the block starting with location ^~ /wp-admin/ {, it should be:

location ^~ /wp-admin/ {
        auth_basic "Restricted";
        auth_basic_user_file /etc/nginx/htpasswd/wpadmin;

        location ~* /.(htaccess|htpasswd) {
            deny all;
        }

        location ~ /.php(?:$|/) {
            fastcgi_split_path_info ^(.+/.php)(/.+)$;
            include fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_param PATH_INFO $fastcgi_path_info;
            fastcgi_pass php-handler-http;
            fastcgi_read_timeout 60s;
        }
    }

Make a copy to the whole block right under it, then modify wp-admin in the first line to pmapma, and wpadmin in the third line to pma. Do not modify any other contents.

location ^~ /pmapma/ {
        auth_basic "Restricted";
        auth_basic_user_file /etc/nginx/htpasswd/pma;

        location ~* /.(htaccess|htpasswd) {
            deny all;
        }

        location ~ /.php(?:$|/) {
            fastcgi_split_path_info ^(.+/.php)(/.+)$;
            include fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_param PATH_INFO $fastcgi_path_info;
            fastcgi_pass php-handler-http;
            fastcgi_read_timeout 60s;
        }
    }

Remember to replace the directory name pmapma and file name pma with your own ones.

Save and quit vi.

:wq

Also, you need to find a similar block in the file wordpress_https.conf and modify the file in the same fashion.

Finally, to put the changes into effect, you need to restart the web server.

service nginx restart && service php-fpm restart

That’s it. You have installed and secured phpMyAdmin on the Vultr One-Click WordPress App.