Advertisements

How to setup phpMyAdmin on a Raspbian Stretch Lite, Nginx, MariaDB / MySQL and PHP 7 LEMP stack

The phpMyAdmin project is a solid software tool written in PHP, that can help us handle the administration of MySQL / MariaDB over the web.

phpMyAdmin Demo Server 4.7.8 demo screenshot

If you want to build your next project with a Raspbian Stretch Lite, Nginx, MariaDB / MySQL and PHP 7 LEMP stack, you may want to consider setting up phpMyAdmin as well. This post details how you can do so.

Prerequisites

This post assumes that you had setup a Raspberry Pi running a Raspbian Stretch Lite, Nginx, MariaDB / MySQL and PHP 7 LEMP stack. If you had not done so, you may want to reference either the Raspberry Pi 3 WordPress tutorial or Raspberry Pi Zero W WordPress tutorial to setup your LEMP stack.

Using SSH to get into your Raspbian Stretch Lite

Suppose that your Raspbian Stretch Lite had gotten the IP address 192.168.1.109 from your home router and you had the pi user in the system, run the following command on your terminal to SSH into your Raspberry Stretch Lite:

ssh pi@192.168.1.109

Installing mbstring extension for PHP 7.0 on Raspbian Stretch Lite

Without the mbstring extension, phpMyAdmin is unable to split strings correctly and it may result in unexpected results.

Run the following command to install the mbstring extension for PHP 7.0 on Raspbian Stretch Lite:

sudo apt-get install php7.0-mbstring 

Creating a database user for administering your database via phpMyAdmin

The root user of MariaDB / MySQL could be using the Authentication Plugin for authentication.

The Authentication Plugin allows the user to use operating system credentials when connecting to MariaDB via Unix socket. This authentication mode cannot be used for logging into phpMyAdmin for administering your database.

Hence, we need to create a database user for us to login through phpMyAdmin. To do so, we first get into MariaDB interactive shell with the following command:

sudo mariadb

Or the MySQL interactive shell with the following command:

sudo mysql

Once you are inside the interactive shell, run the following commands to create a database user that have the privileges to administer any database in your MariaDB / MySQL server:

CREATE USER 'pmauser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'pmauser'@'localhost';

Allocate a domain name for your phpMyAdmin site

The next step is to designate a domain name for accessing your phpMyAdmin site. If you already own a domain, you may want to create a subdomain out of your existing domain. For instance, I could use pma.techcoil.com for my phpMyAdmin site residing on my Raspbian Stretch Lite.

Getting a copy of phpMyAdmin

Once we had decided the domain for accessing our phpMyAdmin site, we can proceed to download a copy of phpMyAdmin. You can see the list of phpMyAdmin versions that are available for download via their download page.

As of this writing, the latest stable version is version 4.7.8. Proceed to download the english version by running the following commands:

cd /var/www
sudo wget https://files.phpmyadmin.net/phpMyAdmin/4.7.8/phpMyAdmin-4.7.8-english.tar.gz
sudo tar -xvf phpMyAdmin-4.7.8-english.tar.gz
sudo mv phpMyAdmin-4.7.8-english pma.techcoil.com
sudo rm phpMyAdmin-4.7.8-english.tar.gz

Once the command completes, you should have the source codes for phpMyAdmin 4.7.8 available at the /var/www/pma.techcoil.com directory.

Configuring blowfish secret for your phpMyAdmin site

The phpMyAdmin installation directory contains a sample configuration file which we can reference for configuring our phpMyAdmin site.

To configure our phpMyAdmin site, we first make a copy of that configuration file:

sudo cp /var/www/pma.techcoil.com/config.sample.inc.php /var/www/pma.techcoil.com/config.inc.php

Once we had done so, use nano to open up the configuration file for editing:

sudo nano /var/www/pma.techcoil.com/config.inc.php 

Inside the editor, look for $cfg['blowfish_secret'] and provide a 32 character long blowfish secret:

$cfg['blowfish_secret'] = '123456789012345678901234567890AB';

Once you had done so, save the file.

Configuring Nginx to proxy HTTP traffic to your phpMyAdmin site

Once we had configured the blowfish secret for phpMyAdmin, the next step is to configure Nginx to proxy HTTP traffic to our PHP web application.

To do so, create a configuration file at /etc/nginx/sites-enabled/pma.techcoil.com.conf with nano:

sudo nano /etc/nginx/sites-enabled/pma.techcoil.com.conf

And paste in the following contents:

server {

        listen 80;
        server_name pma.techcoil.com;
        root /var/www/pma.techcoil.com;
        index index.php;

        location / {
                try_files $uri $uri/ /index.php?$args;
        }

        location ~ \.php$ {
                include fastcgi.conf;
                fastcgi_pass unix:/run/php/php7.0-fpm.sock;
        }

}

Once you had created the configuration file, restart Nginx with the following command:

sudo systemctl restart nginx.service

Editing the hosts file to access your phpMyAdmin site from your work computer

Once you had restarted Nginx on your Raspbian Stretch Lite, go to your computer and add in the following contents to your hosts file:

192.168.1.109 pma.techcoil.com

Accessing your phpMyAdmin site from your work computer

With the entry in your hosts file, you can access your phpMyAdmin site via the domain name that you had provided (for eg. http://pma.techcoil.com/) with your browser. You should be able to see the login screen:

phpMyAdmin 4.7.8 login screen

Key in the details of the database user that you had created earlier to login to the dashboard. Once you had logged in successfully, you should be able to see the following screen:

phpMyAdmin 4.7.8 dashboard screen

Setting up phpMyAdmin configuration storage

A warning message will be displayed at the bottom of the dashboard:

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. Find out why.
Or alternately go to 'Operations' tab of any database to set it up there.

Click on the "Find out why" link and then click on the "Create" link on the next page that follows. After you had clicked on the "Create" link, phpMyAdmin will create a database instance phpmyadmin for it to store configuration information such as navigation histories, bookmarks and etc.

At this point in time, you have a setup that is sufficient for your to administer your MariaDB database on your Raspbian Stretch Lite.

However, if you wish to access your phpMyAdmin site from outside your home network, read on to implement further configurations for your site.

Let's Encrypt your phpMyAdmin site with a browser-trusted SSL/TLS certificates

When you make your phpMyAdmin site communicate in HTTPS, you can be sure that the communication channel between your browser and your phpMyAdmin site is secure.

Fortunately, Let's Encrypt makes it easy for web masters to deploy secure web applications that serve HTTPS.

This section shows how you can secure your phpMyAdmin site with Let's Encrypt.

Prequisites to serving your phpMyAdmin site via HTTPS

In a separate post, I had discussed the topic on how to host multiple websites from home. Following those pointers, you will need the following pieces for your phpMyAdmin site to be accessed from outside your home network with Let's Encrypt browser-trusted certificate:

  • A domain or subdomain name that is mapped to the public IP address that your home router had gotten from the Internet Service Provider.
  • Forwarding of network traffic made to port 80 and 443 of your home router public IP address to the IP address of the Raspberry Pi that contains your phpMyAdmin site. In case you are using the Linksys AC1900 Dual Band Wireless Router, you can checkout this post on how to do so

Installing Certbot on Raspbian Stretch Lite for obtaining Let’s Encrypt’s browser-trusted certificates

In order to use Let's Encrypt facilities, we will need a ACME client to help us get the SSL artefacts from Let's Encrypt. Therefore, you will need to install Certbot on Raspbian Stretch Lite for obtaining Let’s Encrypt’s browser-trusted certificates.

Configuring Nginx to facilitate Certbot in acquiring the SSL certificate for my domain or subdomain

After installing Certbot, proceed to configure Nginx to facilitate Certbot in acquiring the SSL certificate for your domain.

In order to do so, use nano to load /etc/nginx/sites-enabled/pma.techcoil.com.conf:

sudo nano /etc/nginx/sites-enabled/pma.techcoil.com.conf

After the editor loads the file, change the content to look like the following:

server {
 
        listen 80;
        server_name pma.techcoil.com;
        root /var/www/pma.techcoil.com;
        index index.php;
 
        location / {
                try_files $uri $uri/ /index.php?$args;
        }
 
        location ~ \.php$ {
                include fastcgi.conf;
                fastcgi_pass unix:/run/php/php7.0-fpm.sock;
        }

        location ~ /.well-known {
                allow all;
        }
 
}

Once you had changed the content, type Ctrl-X followed by Y to save the file.

Afterwards, restart Nginx with the following command:

sudo systemctl restart nginx.service

Using Certbot to get Let's Encrypt to issue browser-trusted SSL certificate for my domain

After Nginx is ready to facilitate Certbot in acquiring the SSL certificate artefacts, run the following command to acquire them:

sudo certbot certonly -a webroot --webroot-path=/var/www/pma.techcoil.com -d pma.techcoil.com

Generating a strong Diffie-Hellman group

Once Certbot had fetched the SSL certificate artefacts for my domain, generate a Diffie-Hellman group for Nginx to use for exchanging cryptographic keys with its clients:

sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048

Updating the Nginx configurations for serving HTTPS for your phpMyAdmin site

Till this point, you will have the necessary artefacts for Nginx to serve HTTPS. Therefore, you can update the Nginx configurations to use those artefacts in serving HTTPS.

Firstly, use nano to load /etc/nginx/sites-enabled/pma.techcoil.com.conf again:

sudo nano /etc/nginx/sites-enabled/pma.techcoil.com.conf

Once the editor loads the file, replace its content with the following:

upstream php {
        server unix:/run/php/php7.0-fpm.sock;
}

server {
	listen 80;
	server_name  pma.techcoil.com;
	return 301 https://$host$request_uri;
}
     
# For ssl
	ssl on;
    	ssl_certificate /etc/letsencrypt/live/pma.techcoil.com/fullchain.pem;
    	ssl_certificate_key /etc/letsencrypt/live/pma.techcoil.com/privkey.pem;
    	ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    	ssl_prefer_server_ciphers on;
    	ssl_dhparam /etc/ssl/certs/dhparam.pem;
   	ssl_ciphers 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA';
    	ssl_session_timeout 1d;
    	ssl_session_cache shared:SSL:50m;
    	ssl_stapling on;
    	ssl_stapling_verify on;
    	add_header Strict-Transport-Security max-age=15768000;
      
    	default_type  application/octet-stream;
      
    	listen 443;
	server_name pma.techcoil.com;

        root /var/www/pma.techcoil.com;
        index index.php;
 
	location ~ /.well-known {
        	allow all;
    	}
	
        location / {
                try_files $uri $uri/ /index.php?$args;
        }
 
        location ~ \.php$ {
                include fastcgi.conf;
                fastcgi_pass unix:/run/php/php7.0-fpm.sock;
        }
  
}

After that, type Ctrl-X followed by Y to save the configuration file.

Once you had saved the configuration file, run the following command to restart Nginx:

sudo systemctl restart nginx.service

Remove entry from your hosts file

Since you will now be able to access your phpMyAdmin site from outside your home network, you will want to remove the entry from your hosts file on your work computer.

With that, go to your hosts file and remove the entry that you had created earlier:

192.168.1.109 pma.techcoil.com
Advertisements

About Clivant

Clivant a.k.a Chai Heng enjoys composing software and building systems to serve people. He owns techcoil.com and hopes that whatever he had written and built so far had benefited people. All views expressed belongs to him and are not representative of the company that he works/worked for.

Advertisements
Advertisements

Udemy.com Homepage 300x250 Domain names for just 88 cents!

Find a domain starting at $0.88

powered by Namecheap