Add phpMyAdmin to ServerPilot

By default when you set up a ServerPilot instance no GUI app is installed for MySQL interaction, ServerPilot recommend using Adminer which has a similar interface to phpMyAdmin and sits as a standalone single php file which you can use for individual database users.

This guide however goes through using phpMyAdmin instead and using it as the root user to see all databases.

Create Default App in ServerPilot

First create the default app in ServerPilot, which is like a catch all domain for domains not on your server but requests go to it.

Disable the ‘Deny requests….’

 

Create the default app as ‘0default’. Do it using the System User ‘serverpilot’, if you have a paid plan (it is only the user’serverpilot’ on the free plan.)

Install phpMyAdmin

SSH in as the ‘serverpilot’ user.

cd ~/apps/0default

wget https://files.phpmyadmin.net/phpMyAdmin/4.7.9/phpMyAdmin-4.7.9-english.zip

unzip phpMyAdmin-4.7.9-english.zip

mv phpMyAdmin-4.7.9-english public/phpmyadmin

Now the phpMyadmin is in the 0default web root folder accessible via the IP address or server hostname if set up.

After this you can secure access to phpMyadmin by adding a .htaccess file with rules to allow only designated IP addresses.

AuthUserFile /dev/null
AuthGroupFile /dev/null
AuthName "Password Protected Area"
AuthType Basic
order deny,allow
deny from all
# whitelist home IP address
allow from xxx.xxx.xxx.xxx

Get the MySQL root Password

Now you need to get the mysql root password, basically as a root user log in via SSH to your instance and run…

sudo cat /root/.my.cnf

And you’ll get the password.

Set phpMyAdmin Session Time

You can extend the session time of your phpmyadmin interaction by copying the config file, so as the serverpilot user…

su serverpilot

cp 0default/public/phpmyadmin/config.sample.inc.php config.inc.php

Add in the new file…

$cfg['LoginCookieValidity'] = 86400; // 1 day

Also add in a 32 hash secret for $cfg[‘blowfish_secret’]):

Create a new file .user.ini to match the time for PHP with the following

session.gc_maxlifetime = 86400

Backing Up MySQL Automatically

You can use the AutoMySQLBackup which backs up your databases daily, weekly and monthly, so as root …

sudo apt-get install automysqlbackup

The default location for storing the backups is

/var/lib/automysqlbackup

You can change this – by editing – /etc/default/automysqlbackup

So you can set and forget but also run manually with….

automysqlbackup

References—

Tutedepot
ServerPilot KB default app
ServerPilot KB MySQL Backup
ServerPilot KB MySQL Root User
automysqlbackup