Setup phpMyAdmin on Ubuntu 16.04 with Nginx and a Remote MySQL Server

I’ve been rebuilding my lab recently, most notably breaking down my LXD stack into individual Ubuntu virtual machines for each service. One thing I always meant to do but never got around to figuring out was the initial setup of phpMyAdmin with a remote MySQL server. If you’ve ever wanted to do something similar, today is your lucky day.
 

This guide assumes you have already deployed separate MySQL and Nginx (with php 7) servers on Ubuntu 16.04. Here are guides for Nginx and MySQL Server.

MySQL Setup

So much sudo. Let’s just launch a root shell

sudo -i

To allow remote access to the MySQL, open the following file in a text editor

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the following line

bind-address = 127.0.0.1

Either comment it out like so

#bind-address = 127.0.0.1

Or change to the server’s local IP address

bind-address = 192.168.0.100

Restart MySQL to apply changes

service mysql restart

Login to MySQL as root

mysql -u root -p

Create a root user that can login from the web server

CREATE USER 'root'@'web.server.ip.address' IDENTIFIED BY 'supersecurepassword';

Grant all privileges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'web.server.ip.address';

GRANT USAGE ON *.* TO 'root'@'web.server.ip.address' WITH GRANT OPTION;

Create a phpMyAdmin user with access from the web server and grant privileges

CREATE USER 'phpmyadmin'@'web.server.ip.address' IDENTIFIED BY 'supersecurepassword';

GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'phpmyadmin'@'web.server.ip.address';

Flush privileges to have everything take effect

FLUSH PRIVILEGES;

Exit and login to the web server

 

Nginx Setup

Launch a root shell

sudo -i

Update, install phpMyAdmin and some dependancies

apt-get update

apt-get install phpmyadmin php-mbstring php-gettext

When asked Web server to reconfigure automatically: select none

When asked Configure database for phpmyadmin with dbconfig-common? select no

Tell dbcommon-config we want to use a remote database

nano /etc/dbconfig-common/config

Find the following line

dbc_remote_questions_default='false'

Change it to

dbc_remote_questions_default='true'

Save and exit

Invoke dbconfig-common

dpkg-reconfigure phpmyadmin

When prompted reinstall database for phpmyadmin? select yes.

When prompted Connection method for MySQL database of phpmyadmin: select TCP/IP

When prompted Host name of the MySQL database server for phpmyadmin: select new host. Follow the prompts, filling in your MySQL database credentials.

 

Sample Nginx Config

Here’s a sample config for running phpMyAdmin under the subdomain phpmyadmin.example.com

Create an Nginx config file

nano /etc/nginx/sites-available/phpmyadmin

Add the following code

server {
        listen 80;

        root /usr/share/phpmyadmin;

        index index.php;

        server_name phpmyadmin.example.com;

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

        error_page 404 /404.html;
        error_page 500 502 503 504 /50x.html;
        location = /50x.html {
                root /usr/share/nginx/html;
        }

        location ~ \.php$ {
                try_files $uri =404;
                include fastcgi_params;
                fastcgi_index index.php;
                fastcgi_pass unix:/run/php/php7.0-fpm.sock;
                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        }
}

ctrl + y, then return to save and exit.

Create a symbolic link to enabled sites

ln -s /etc/nginx/sites-available/* /etc/nginx/sites-enabled/

Reload Nginx to have the configuration take effect

service nginx reload

Visit phpmyadmin.example.com and log in with your root account. All done!